Обсуждение: BUG #5199: Window frame clause wrong (?) behaviour

The following bug has been logged online:

Bug reference:      5199
Logged by:          Iliya Krapchatov
Email address:      my_working@inbox.ru
PostgreSQL version: 8.4.1
Operating system:   Windows XP
Description:        Window frame clause wrong (?) behaviour
Details:

I am new at WINDOW clause using so maybe I've missed something. Following is
the sequence of steps to repeat the problem:

create table t( pk integer );
insert into t(pk) values( 1 );
insert into t(pk) values( 2 );

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

I see following lines in response:
---
ERROR:  cannot override frame clause of window "pkw"
LINE 1: select first_value( pk ) OVER ( pkw ) FROM t
                                      ^

********** Error **********

ERROR: cannot override frame clause of window "pkw"
SQL state: 42P20
Характеристика:31
---

I believe that is a bug because next two queries work fine:

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 );

Iliya Krapchatov wrote:
> select first_value( pk ) OVER ( pkw ) FROM t
> WINDOW pkw AS ( PARTITION BY pk ORDER BY pk RANGE BETWEEN UNBOUNDED
> PRECEDING AND UNBOUNDED FOLLOWING );
>
> I see following lines in response:
> ---
> ERROR:  cannot override frame clause of window "pkw"
> LINE 1: select first_value( pk ) OVER ( pkw ) FROM t

Try without the parenthesis in OVER:

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

I'm quite surprised by the error message that produces, though...

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Try without the parenthesis in OVER:
> ...
> I'm quite surprised by the error message that produces, though...

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.

            regards, tom lane