Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options

On 10/12/22 04:40, David Rowley wrote:
> I've not really done any analysis into which other window functions
> can use this optimisation. The attached only adds support to
> row_number()'s support function and only converts exactly "RANGE
> UNBOUNDED PRECEDING AND CURRENT ROW" into "ROW UNBOUNDED PRECEDING AND
> CURRENT ROW".  That might need to be relaxed a little, but I've done
> no analysis to find that out.

Per spec, the ROW_NUMBER() window function is not even allowed to have a
frame specified.

     b) The window framing clause of WDX shall not be present.

Also, the specification for ROW_NUMBER() is:

     f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:

         COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)


So I don't think we need to test for anything at all and can
indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.

To back this up:

SQL Server returns an error right away if you  try to add a window frame
https://dbfiddle.uk/SplT-F3E

> Msg 10752 Level 15 State 3 Line 1
> The function 'row_number' may not have a window frame.

And Oracle reports a syntax error:

row_number() is defined without a "windowing clause" (in Oravle's nomenclature)

Allowing the same in Postgres (and defaulting to RANGE mode) seems like (a) genuine bug(s) after all.

Regards

Erwin