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

Thibauld Favre escribió:

> 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');

But they all have the same popularity of 0, so what's the problem?
Maybe what you need is to change the ORDER BY clause:

SELECT name FROM a ORDER BY popularity, name LIMIT 1;

Or perhaps you need to rephrase your query to say "give me all the
packages that have a popularity greater or equal than the lowest
popularity among the N less-popular packages"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.