Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values

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