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

From Alvaro Herrera
Subject Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values
Date
Msg-id 20080708134138.GC4095@alvh.no-ip.org
Whole thread Raw
In response to BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values  ("Thibauld Favre" <tfavre@gmail.com>)
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: "Bob Thompson"
Date:
Subject: BUG #4287: Will not boot
Next
From: Simon Riggs
Date:
Subject: Re: BUG #4276: Feature request aggregate indexes