25 August 2013

下面这个SQL会报这个错误:cannot override frame clause of window pkw:

select first_value( pk ) OVER ( pkw ) FROM t 
WINDOW pkw AS ( PARTITION BY pk ORDER BY pk RANGE BETWEEN UNBOUNDED 
PRECEDING AND UNBOUNDED FOLLOWING ); 

而下面两种写法则不会:

select first_value( pk ) OVER ( PARTITION BY pk ORDER BY pk RANGE BETWEEN 
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t; 

select first_value( pk ) OVER ( pkw ) FROM t 
WINDOW pkw AS ( PARTITION BY pk ORDER BY pk ); 

如果把报错SQL微做调整,将OVER后面pkw的括号去掉,则SQL可以正确运行:

select first_value( pk ) OVER pkw FROM t 
WINDOW pkw AS ( PARTITION BY pk ORDER BY pk RANGE BETWEEN UNBOUNDED 
PRECEDING AND UNBOUNDED FOLLOWING ); 

这是PG的一个bug,在4年前有人报过,Tom Lane的回复是:

It's actually correct: the syntax with parens specifies copying

and modifying the named WINDOW definition, but you're not allowed to

copy-and-modify a definition that has a FRAME clause.  (Why the spec

says that is beyond me; replacing the FRAME clause seems sensible

enough, but ...) 

This isn't terribly well explained in our docs, as far as I can

find at the moment.  The SELECT reference page mentions the rule

in the context of a dependent WINDOW-clause definition, but

doesn't explain that OVER with parens acts the same way.

And maybe the error message wording could be improved. 


目前仍然没有修复,最近这个bug被我遇到了,如果有时间,准备提交一个补丁。

详细情况可以看这里