Обсуждение: BUG #10164: Inconsistent Order When Limit is Applied

The following bug has been logged on the website:

Bug reference:      10164
Logged by:          Brad Lindsay
Email address:      sluggy.fan@gmail.com
PostgreSQL version: 9.3.4
Operating system:   OS X
Description:

I have a query where the order is inconsistent when different LIMITs are
specified.

Here's the setup:

CREATE TABLE "storage_tests" (
    "id"     SERIAL,
    "name"   TEXT DEFAULT NULL,
    "field1" TEXT DEFAULT NULL,
    PRIMARY KEY (id)
);
INSERT INTO storage_tests (id, name, field1) VALUES
    (1, 'name', 'z'), (2, 'name', 'a'), (3, 'named', 't'), (4, 'names',
'b'), (5, 'blank', 'c'),
    (6, 'Harry', 'e'), (7, 'Rose', 'w'), (8, 'Andrew', 'O'), (9, 'George',
'L'), (10, 'Rachel', 'M'),
    (11, 'John', 'm'), (12, 'Anne', 'a'), (13, 'Susan', 'i'), (14, 'Justin',
'M'), (15, 'Virginia', 'a'),
    (16, 'Cynthia', 'J'), (17, 'John', 't'), (18, 'Cynthia', 'R'), (19,
'Robert', 'P'), (20, 'Victor', 'B'),
    (21, 'Henry', 'i'), (22, 'Mark', 'e'), (23, 'Albert', 'J'), (24, 'Lynn',
'a'), (25, 'Tanya', 'ob'),
    (26, 'Michael', 'C'), (27, 'Tony', 'e'), (28, 'Dave', 't'), (29,
'Robbin', 'C'), (30, 'Robert', 'B'),
    (31, 'Ryan', NULL);



Issuing this SQL:

SELECT *
FROM "storage_tests"
ORDER BY LOWER("field1") DESC NULLS LAST

Results in the first four IDs being in this order:
1, 7, 3, 17


When I add a LIMIT of 3, everything still looks good:

SELECT *
FROM "storage_tests"
ORDER BY LOWER("field1") DESC NULLS LAST
OFFSET 0 LIMIT 3

Results in the ids in this order (as expected):
1, 7, 3


However, when I LIMIT 4, the id of 3 moves to the fourth position:

SELECT *
FROM "storage_tests"
ORDER BY LOWER("field1") DESC NULLS LAST
OFFSET 0 LIMIT 4

Results in the following (unexpected) order:
1, 7, 17, 3

If I increase to 5, it drops to the fifth position:
1, 7, 17, 28, 3

If I increase to 6 (the point where there's another row with a different
value in field1) it jumps back to the third position:

1, 7, 3, 17, 28, 18


The documentation states that when the ordered fields all have the same
value, that the order is implementation-dependent, but shouldn't that
implementation be consistent no matter what the LIMIT is?

sluggy.fan@gmail.com writes:
> The documentation states that when the ordered fields all have the same
> value, that the order is implementation-dependent, but shouldn't that
> implementation be consistent no matter what the LIMIT is?

Uh, no.  The code is entitled to return the rows containing lower(field1)
= 't' in any order, and there's no reason to think it will make the same
decisions with different LIMITs.  (The technical reason for this is that
it's a heapsort with a bounded number of heap elements, so the exact
contents of the heap at the end of the input scan will vary depending on
your LIMIT, and heapsort isn't stable so the positions of equal-keyed
elements after sorting can vary.)

If you want a guaranteed ordering of the rows you'll need to specify
additional sort columns to break ties.

            regards, tom lane