Thibauld Favre wrote:
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.
Doesn't look like a bug to me. All the rows have the same value in popularity, so the "ORDER BY popularity" doesn't force any particular order. This is effectively the same as if there was no ORDER BY at all; the database is free to return the rows in any random order it wishes.
You can use ORDER BY popularity, name DESC for the order you were expecting..
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
