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

From Thibauld Favre
Subject Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values
Date
Msg-id 6d801c0b0807141522g879b9acief43dbd278804564@mail.gmail.com
Whole thread Raw
In response to Re: BUG #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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@sss.pgh.pa.us>:
> "Heikki Linnakangas" <heikki@enterprisedb.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
>

pgsql-bugs by date:

Previous
From: "Yishai Lerner"
Date:
Subject: BUG #4306: TSearch2 stemming, stop words and lexize behaviour inconsistent
Next
From: Michał Szeląg
Date:
Subject: Re: BUG #4296: Server crashes by restoring database