On Wed, 12 Oct 2022 at 05:33, Vik Fearing <v...@postgresfriends.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
