BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4286
Logged by:          Thibauld Favre
Email address:      tfavre@gmail.com
PostgreSQL version: 8.3.3
Operating system:   Ubuntu Hardy Heron (8.04.1)
Description:        ORDER BY returns inconsistent results when using LIMIT
on a integer column set to default values
Details:

Hi,

Here's a little SQL script that recreate the bug I encounter on my app.
Basically, on certain circonstances, the first value of the table (here 'a')
is constantly returned at the end of the result set, thus creating
inconsistency between queries. I'm not sure I'm clear so here's the little
script:

DROP TABLE IF EXISTS a;
CREATE TABLE a (
    id serial PRIMARY KEY,
    name text NOT NULL,
    popularity integer NOT NULL default 0
);

INSERT INTO a (name) VALUES
('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'),
('i'), ('j'), ('k'), ('l'), ('m'), ('n'), ('o');

SELECT name FROM a ORDER BY popularity LIMIT 1; -- OK
SELECT name FROM a ORDER BY popularity LIMIT 2; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 3; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 4; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 5; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 6; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 7; -- INCONSISTENT RESULT
SELECT name FROM a ORDER BY popularity LIMIT 8; -- OK
SELECT name FROM a ORDER BY popularity LIMIT 9; -- OK

Here's what I get as a result on my server. See how 'a' is systematically
put at the end of the result set until the LIMIT clause reaches the value 8.
Above 8, the results get consistent again. Note that the value of 8 is table
specific: if the test was too be performed on another table, the value
changes.

 name
------
 a
(1 ligne)

 name
------
 b
 a
(2 lignes)

 name
------
 b
 c
 a
(3 lignes)

 name
------
 b
 c
 d
 a
(4 lignes)

 name
------
 b
 c
 d
 e
 a
(5 lignes)

 name
------
 b
 c
 d
 e
 f
 a
(6 lignes)

 name
------
 b
 c
 d
 e
 f
 g
 a
(7 lignes)

 name
------
 a
 b
 c
 d
 e
 f
 g
 h
(8 lignes)


Please do not hesitate to contact me if you have any questions regarding
this bug.
Besides this little anoyance, thanks for making postresql such a great
tool!

Thibauld Favre