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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: "Thibauld Favre" <tfavre(at)gmail(dot)com>, 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 14:20:55
Message-ID: 8406.1215526855@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> Thibauld Favre wrote:
>> 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.

> Doesn't look like a bug to me.

It isn't; in fact this behavior is specifically disclaimed in the docs
(http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-LIMIT):

	Thus, using different LIMIT/OFFSET values to select different
	subsets of a query result *will give inconsistent results*
	unless you enforce a predictable result ordering with ORDER BY.

Since the query's ORDER BY isn't sufficient to constrain the row
ordering, the observed behavior is covered by this statement.

(FWIW, it's unlikely there's any plan change involved here.  What I
think is happening is that the first row is being selected as the
quicksort pivot item.)

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Simon RiggsDate: 2008-07-08 14:24:07
Subject: Re: BUG #4276: Feature request aggregate indexes
Previous:From: Alvaro HerreraDate: 2008-07-08 13:41:38
Subject: Re: BUG #4286: ORDER BY returns inconsistent results whenusing LIMIT on a integer column set to default values

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