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

"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