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

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

From: "Heikki Linnakangas" <heikki(at)enterprisedb(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 when using LIMIT on a integer column set to default values
Date: 2008-07-08 10:46:25
Message-ID: 48734581.9000007@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Bob ThompsonDate: 2008-07-08 11:04:04
Subject: BUG #4287: Will not boot
Previous:From: Thibauld FavreDate: 2008-07-08 10:21:40
Subject: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values

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