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

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
>