Skip site navigation (1) Skip section navigation (2)

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

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Thibauld Favre <tfavre(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4286: ORDER BY returns inconsistent results whenusing LIMIT on a integer column set to default values
Date: 2008-07-08 13:41:38
Message-ID: 20080708134138.GC4095@alvh.no-ip.org (view raw or flat)
Thread:
Lists: 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.

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-07-08 14:20:55
Subject: Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values
Previous:From: Bob ThompsonDate: 2008-07-08 11:04:04
Subject: BUG #4287: Will not boot

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group