Re: Result set stability in queries sorted by column with non-unique values

Hi,
I am sure the variation depends on the query plan decided by the engine at
the time of execution, in short, AFAIK (as far as I know) the answer is No.
There is no guarantee that the rows will be returned in the exact order.

Some of the possible answers to be further explored would be
A) if we want the data to be always randomly ordered explore function
random(), or is it rand() I guess? to be included in the query.
B) if we want the data to be arbitrarily ordered but maintain the order for
each query execution, save a randomly ordered same number of records in a
different table and then write a joined query.

I hope it helps.

Thanks & Regards
Niranjan D. Pandit
On 15-Apr-2015 5:11 pm, "Michele Bosi" <michele(dot)bosi(at)gmail(dot)com> wrote:

> Hi all,
>
> I would like to know if postgresql is guaranteed to return the rows
> always in the same order in the following case:
>
> table "boxes"
> -------------------
> width | height
> 1 | 5
> 1 | 3
> 1 | 7
> 1 | 2
> 1 | 4
>
> SELECT * FROM boxes ORDER BY width;
>
> result:
> 1 | 5
> 1 | 3
> 1 | 7
> 1 | 2
> 1 | 4
>
> In practice we've seen that postgresql returns the rows in some kind
> of stably arbitrary order (which is expected because "width" contains
> non unique values) but the subtle ambiguity here (which is important
> for our application) is whether postresql is guaranteed to always
> return the same arbitrary order in a stable fashion or if it can (even
> theoretically) return an always different aribtrary order every time I
> perform the query, say for example:
>
> SELECT * FROM boxes ORDER BY width;
> 1 | 3
> 1 | 5
> 1 | 7
> 1 | 4
> 1 | 2
>
> SELECT * FROM boxes ORDER BY width;
> 1 | 2
> 1 | 5
> 1 | 7
> 1 | 4
> 1 | 3
>
> etc.
>
> The only two relevant lines I've found in the docs
> (http://www.postgresql.org/docs/current/interactive/queries-order.html)
> are:
>
> "A particular output ordering can only be guaranteed if the sort step
> is explicitly chosen." which does not tell us in what order rows with
> same sorting-key value are returned.
>
> and
>
> "If sorting is not chosen, the rows will be returned in an unspecified
> order." which I would expect to be the case also when sorting is
> chosen BUT the sorting-key values are always the same. However this
> still does not tell us if the "unspecified order" is always the same
> or not, ie. if it's stable. Another way to put this last point would
> be: is "SELECT * FROM boxes LIMIT 3" guaranteed to always return the
> same 3 rows (assuming I don't insert or delete new rows) or can
> theoretically return any different 3 every time I query?
>
> Thank you.
>
>
> --
> Sent via pgsql-in-general mailing list (pgsql-in-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-in-general
>