Re: BUG #10164: Inconsistent Order When Limit is Applied

sluggy(dot)fan(at)gmail(dot)com writes:
> The documentation states that when the ordered fields all have the same
> value, that the order is implementation-dependent, but shouldn't that
> implementation be consistent no matter what the LIMIT is?

Uh, no. The code is entitled to return the rows containing lower(field1)
= 't' in any order, and there's no reason to think it will make the same
decisions with different LIMITs. (The technical reason for this is that
it's a heapsort with a bounded number of heap elements, so the exact
contents of the heap at the end of the input scan will vary depending on
your LIMIT, and heapsort isn't stable so the positions of equal-keyed
elements after sorting can vary.)

If you want a guaranteed ordering of the rows you'll need to specify
additional sort columns to break ties.

regards, tom lane