Обсуждение: BUG #4779: LIMIT/OFFSET behavior change (possibly related to Top-n)

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

"Thomas S. Chin" <thom@genx.net> writes:
> 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:

No, this is not considered a bug; no such behavior has ever been
promised.  Read the fine manual.

(Your actual problem is that the sort ordering is underspecified, which
means you'll get varying results anyway.)

            regards, tom lane

Ah, okay, found the documentation mentioning this (LIMIT).  I was
looking in the wrong place (ORDER BY clause).

Thanks again for the help.

Tom Lane wrote:
> "Thomas S. Chin" <thom@genx.net> writes:
>> 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:
>
> No, this is not considered a bug; no such behavior has ever been
> promised.  Read the fine manual.
>
> (Your actual problem is that the sort ordering is underspecified, which
> means you'll get varying results anyway.)
>
>             regards, tom lane