The following bug has been logged online: Bug reference: 4286 Logged by: Thibauld Favre Email address: [EMAIL PROTECTED] 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs