Skip site navigation (1) Skip section navigation (2)

BUG #4779: LIMIT/OFFSET behavior change (possibly related to Top-n)

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 (view raw or flat)
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

Responses

pgsql-bugs by date

Next:From: Kevin FieldDate: 2009-04-24 14:09:53
Subject: Re: BUG #4763: postgres service unstable, even during install
Previous:From: Kevin FieldDate: 2009-04-24 13:44:46
Subject: Re: BUG #4763: postgres service unstable, even during install

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group