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

BUG #4224: issue with LIMIT and ORDER BY

From: "Lawrence Cohan" <lawrencec(at)1shoppingcart(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4224: issue with LIMIT and ORDER BY
Date: 2008-06-05 18:15:29
Message-ID: 200806051815.m55IFTKW093929@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4224
Logged by:          Lawrence Cohan
Email address:      lawrencec(at)1shoppingcart(dot)com
PostgreSQL version: 8.2.5
Operating system:   red hat 4.1.1-52
Description:        issue with LIMIT and ORDER BY
Details: 

Following queries run FOREVER in PG if an index exists on the "id" column
which is a integer - serial and PKey on the table. 

SELECT id FROM orders WHERE merchant_id = xxxxxx ORDER BY id DESC LIMIT 31
-- or 30, 29, 28, 27, 26, 25

or 

SELECT id FROM clients WHERE merchant_id = XXXXXX ORDER BY id LIMIT 3 -- or
1, 2.

With different limits we get different results but  the queris are running
forever with DESC as well. 
This is a serrios issue as PG documentation says that:

"When using LIMIT, it is important to use an ORDER BY clause that constrains
the result rows into a unique order. Otherwise you will get an unpredictable
subset of the query's rows. You might be asking for the tenth through
twentieth rows, but tenth through twentieth in what ordering?"

Responses

pgsql-bugs by date

Next:From: hubert depesz lubaczewskiDate: 2008-06-05 18:41:27
Subject: Re: BUG #4224: issue with LIMIT and ORDER BY
Previous:From: Simon RiggsDate: 2008-06-05 17:28:26
Subject: Re: BUG #3110: Online Backup introduces Duplicate OIDs

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