From: | "Thomas S(dot) Chin" <thom(at)genx(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4779: LIMIT/OFFSET behavior change (possibly related to Top-n) |
Date: | 2009-04-24 13:48:48 |
Message-ID: | 200904241348.n3ODmmer065387@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4779
Logged by: Thomas S. Chin
Email address: thom(at)genx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Field | 2009-04-24 14:09:53 | Re: BUG #4763: postgres service unstable, even during install |
Previous Message | Kevin Field | 2009-04-24 13:44:46 | Re: BUG #4763: postgres service unstable, even during install |