Re: Two Window aggregate node for logically same over clause

Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> writes:
> On Thu, Oct 5, 2023 at 8:53 PM "Anitha S" <anitha(dot)sg(at)zohocorp(dot)com> wrote:
>> We have observed that for logically same over clause two different window aggregate nodes are created in plan.
>> The below query contains two window functions. Both Over clause contain the same partition & order clause in it. But in one over clause ordering option is mentioned as ascending but not in another over clause which represents the default option "ascending".

> Another angle is to ask: Why would the query add ASC to one window
> specification and not the other?

Yeah. I can't get excited about doing anything about this. We
promise to merge identical window clauses, but these aren't identical.
If you say "let's merge semantically equivalent clauses", that's
opening a fairly large can of worms --- for example, ought we to
recognize that "x + 1.0" and "x + 1.00" are equivalent? Or even
"x" and "x + 0"? (I'm pretty sure I've seen query hacks recommended
that depend on our *not* detecting that.)

Also, it would be an extremely bad idea IMO to change the way
equal() deals with this, which means that transformWindowFuncCall
would have to use bespoke code not equal() to check for matches.
That'd be ugly and a permanent maintenance gotcha.

regards, tom lane