From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Christopher Inokuchi <cinokuchi@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
Date: Sun, 09 Mar 2025 13:27:45 -0400
Message-ID: <398522.1741541265@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, cinokuchi@gmail.com, 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: <398522.1741541265@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox