From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Christopher Inokuchi <cinokuchi@gmail.com>,
        Greg Sabino Mullane <htamfids@gmail.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: Window Functions with identical PARTITION BY and ORDER BY clauses
 evaluated separately
In-reply-to: 
 <CAKFQuwaSSm-fe41XEGr1dNSPgnGfHZNJ0mdZmqx9tYMpFVHANg@mail.gmail.com>
References: 
 <CABde6B5va2wMsnM79u_x=n9KUgfKQje_pbLROEBmA9Ru5XWidw@mail.gmail.com>
 <CAKAnmmK2EkwdfT24LUp4JBOfxdB5tT_oqdyFKC7P-_opF7js1g@mail.gmail.com>
 <CABde6B40mE4me17sKLGZ6MspZ4YiaSsK8S4FunP13c+QNT+fKA@mail.gmail.com>
 <1147307.1741392351@sss.pgh.pa.us>
 <CAKFQuwZs9tZEy8TDLtqJJTAfRcLrM+i5TDEuppOXVFhhG3pg7g@mail.gmail.com>
 <29119.1741463452@sss.pgh.pa.us>
 <CAKFQuwaSSm-fe41XEGr1dNSPgnGfHZNJ0mdZmqx9tYMpFVHANg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sat, 08 Mar 2025 18:07:26 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <398521.1741541265.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sun, 09 Mar 2025 13:27:45 -0400
Message-ID: <398522.1741541265@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/398522.1741541265%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Mar 8, 2025 at 12:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> For a shorter fix
>> When multiple window functions are used, all the window functions
>> having syntactically equivalent PARTITION BY and ORDER BY clauses
>> in their window definitions are guaranteed to see the same
>> ordering of the input rows, even if the ORDER BY does not uniquely
>> determine the ordering.

> WFM, the key point is removing the problematic wording and I do find this
> reads better in the end.
> However, I'm having trouble understanding the purpose of the word
> "syntactically" here.  Or even using the "clauses" at all.  Why not:

> "When multiple window functions are used, all the window functions having
> the same partitioning and ordering expressions in their window definitions
> are guaranteed to see the same ordering of the input rows, even if
> the ordering is not deterministic."

Sure, we can lose "syntactically" --- it's probably not even strictly
correct anyway, given that what we really look for is equal() parsed
expression trees.  By the same token though, I don't love "same"
here, because what is "same"?  In particular, in your phrasing
it's not clear whether "PARTITION BY foo" and "ORDER BY foo"
are considered equivalent for this purpose.  So let's go with
my wording less the "syntactically".

			regards, tom lane