Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: www(dot)postgresql(dot)org-33da24d6(at)kevincox(dot)ca
Subject: Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html
Date: 2023-10-04 14:24:11
Message-ID: 169642945128.656.17921924732262672146@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/queries-order.html
Description:

The document only says this about unsorted queries:

> After a query has produced an output table (after the select list has been
processed) it can optionally be sorted. If sorting is not chosen, the rows
will be returned in an unspecified order. The actual order in that case will
depend on the scan and join plan types and the order on disk, but it must
not be relied on. A particular output ordering can only be guaranteed if the
sort step is explicitly chosen.

It mentions "If sorting is not chosen". This sort of implies that if you
pick a sort the output order is predictable. However I believe that the only
actual guarantee is if the sort columns selected produce a unique value.

For example if you do `ORDER BY name` and have two rows with the same name I
don't think the order of those rows is predictable.

I think the docs should be updated to either:

1. Clearly state that the order **is** consent as long as any sort clause is
present, and specify what that order is.
2. Update the quoted sentence to refer to "If sorting is not chosen or the
sort columns do not form a unique key" instead of just "If sorting is not
chosen".

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2023-10-04 14:45:32 Missing closing ]
Previous Message Laurenz Albe 2023-10-04 07:20:16 Re: unnest multirange, returned order