The following bug has been logged online:
Bug reference: 4779
Logged by: Thomas S. Chin
Email address: thom@genx.net
PostgreSQL version: 8.3.7
Operating system: Linux tat 2.6.27-gentoo-r7 #1 SMP Fri Jan 2 08:50:09 EST
2009 i686 Intel(R) Core(TM)2 CPU 6700 @ 2.66GHz GenuineIntel GNU/Linux
Description: LIMIT/OFFSET behavior change (possibly related to Top-n)
Details:
To whom it may concern:
I noticed that the behavior of queries that involve LIMIT/OFFSET no longer
return results consistent with the ordering of the same query without
LIMIT/OFFSET:
---
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# CREATE TABLE test (num INTEGER, num2 INTEGER);
CREATE TABLE
test=# INSERT INTO test VALUES(0, 1);
INSERT 0 1
test=# INSERT INTO test VALUES(1, 1);
INSERT 0 1
test=# INSERT INTO test VALUES(1, 1);
INSERT 0 1
test=# INSERT INTO test VALUES(1, 1);
INSERT 0 1
test=# INSERT INTO test VALUES(1, 1);
INSERT 0 1
test=# SELECT num, num2 FROM test ORDER BY num2;
num | num2
-----+------
0 | 1
1 | 1
1 | 1
1 | 1
1 | 1
(5 rows)
test=# SELECT num, num2 FROM test ORDER BY num2 LIMIT 1 OFFSET 0;
num | num2
-----+------
0 | 1
(1 row)
test=# SELECT num, num2 FROM test ORDER BY num2 LIMIT 1 OFFSET 1;
num | num2
-----+------
0 | 1
(1 row)
test=# DROP TABLE test;
DROP TABLE
test=# \q
---
In doing some research, it led me to think it was possibly related to the
new Top-n sorting algorithm. Is the behavior of LIMIT/OFFSET no longer
returning results consistent with the same query without LIMIT/OFFSET
considered a known side-effect of the sort optimization or is this a bug?
Any additional information on this matter would be greatly appreciated.
Thanks guys,
Thomas S. Chin
