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

2024-06-10 Thread Erwin Brandstetter
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

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

2022-07-14 Thread Shay Rojansky
>> 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

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

2022-07-14 Thread David G. Johnston
On Thursday, July 14, 2022, Shay Rojansky wrote: > > If there's a guarantee that UNION ALL preserves ordering - as Tom seems to > indicate in the thread quoted above - then the above works. If there's no > such guarantee, then AFAIK the above can't be rewritten; putting the ORDER > BY outside - o

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

2022-07-14 Thread Tom Lane
Pantelis Theodosiou writes: > On Thu, Jul 14, 2022 at 9:16 AM Shay Rojansky wrote: >> I was trying to understand what - if any - are the guarantees with >> regards to ordering for combining queries (UNION/UNION ALL/...). > No, there is no guarantee. It's just that UNION ALL works this way today

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

2022-07-14 Thread Pantelis Theodosiou
On Thu, Jul 14, 2022 at 9:16 AM Shay Rojansky wrote: > > >> I was trying to understand what - if any - are the guarantees with > regards to ordering for combining queries (UNION/UNION ALL/...). From this > message[1], it seems that UNION ALL does preserve the ordering of the > operand queries, wh

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

2022-07-14 Thread Shay Rojansky
>> I was trying to understand what - if any - are the guarantees with regards to ordering for combining queries (UNION/UNION ALL/...). From this message[1], it seems that UNION ALL does preserve the ordering of the operand queries, whereas UNION does not (presumably neither do INTERSECT, INTERSECT

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

2022-07-13 Thread David G. Johnston
On Wed, Jul 13, 2022 at 5:08 PM Shay Rojansky wrote: > Greetings. > > I was trying to understand what - if any - are the guarantees with regards > to ordering for combining queries (UNION/UNION ALL/...). From this > message[1], it seems that UNION ALL does preserve the ordering of the > operand q

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

2022-07-13 Thread Shay Rojansky
Greetings. I was trying to understand what - if any - are the guarantees with regards to ordering for combining queries (UNION/UNION ALL/...). From this message[1], it seems that UNION ALL does preserve the ordering of the operand queries, whereas UNION does not (presumably neither do INTERSECT, I