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: "Thibauld Favre" <tfavre(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org, alvherre(at)commandprompt(dot)com
Subject: Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values
Date: 2008-07-14 22:22:47
Message-ID: 6d801c0b0807141522g879b9acief43dbd278804564@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

Thank you for your kind answers and for not having killed me despite
the fact that I report a bu^H^H documented feature. It had no impact
on me since there's an easy workaround to this behaviour (like you
reported), I just really thought it was a bug...
Sorry guys,

Thibauld

2008/7/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "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

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2008-07-15 01:35:41
Subject: Re: BUG #4191: Include hint for Windows-like locals in documentation
Previous:From: Yishai LernerDate: 2008-07-14 21:04:41
Subject: BUG #4306: TSearch2 stemming, stop words and lexize behaviour inconsistent

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