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

On Wed, 12 Oct 2022 at 05:33, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:

> 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:
https://dbfiddle.uk/l0Yk8Lw5

row_number() is defined without a "windowing clause" (in Oravle's
nomenclature)
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#i81407
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions144.htm#i86310

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

Regards
Erwin