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?
