Re: Clarify the ordering guarantees in combining queries (or lack thereof)

The manual still seems to offer just such a guarantee here:
https://www.postgresql.org/docs/current/sql-select.html#SQL-UNION

> Multiple UNION operators in the same SELECT statement are evaluated left
to right, unless otherwise indicated by parentheses.

In the case of UNION ALL, is this supposed to mean ...

a.) Individual legs are evaluated left to right, but sets returned from
each are not guaranteed to be appended in the same order, nor is the order
within each set guaranteed to be preserved.

b.) Individual legs are evaluated left to right, sets returned from each
are appended in order, but the order within each set is not guaranteed to
be preserved.

c.) Individual legs are evaluated left to right, sets returned from each
are appended in order, and the order within each set is guaranteed to be
preserved.

d.) The manual is outdated. Since the advent of "Parallel Append" in
Postgres 11, left to right evaluation is not guaranteed in all cases.

Obviously, the order *within* each leg is not guaranteed without ORDER BY
attached to it, enclosed in parentheses. But that's an orthogonal issue.
The question is, what of the returned order is preserved after UNION ALL?
And what is *guaranteed*?

I guess the term "evaluated" is ambiguous.

I would love the manual to be clear about this.

Related discussion here:
https://dba.stackexchange.com/questions/316818/are-results-from-union-all-clauses-always-appended-in-order

Regards
Erwin

On Mon, Jun 10, 2024 at 3:03 PM Shay Rojansky <roji(at)roji(dot)org> wrote:

> >> No, there is no guarantee. It's just that UNION ALL works this way today
> >> (preserving the order of the subselects) - and I'm not even sure about
> >> that, it may not preserve the order in all cases, with different
> indexes or
> >> partitioning or a parallel plan, etc.
> >
> > Yeah, that. You can get a parallelized plan today for UNION ALL:
>
> ...
>
> > Since the documentation doesn't make a guarantee there is none.
>
> Thanks all for the confirmation.
>
> I'd still suggest documenting the lack of guarantee; yes, mathematically
> it may be correct to not document lack of guarantees, but users can come
> with various expectations and misunderstandings (I also wasn't clear on
> this specifically for UNION ALL), and it's always good to say this kind of
> thing explicitly.
>