Обсуждение: BUG #5018: Window function alias

The following bug has been logged online:

Bug reference:      5018
Logged by:          Marko Tiikkaja
Email address:      marko.tiikkaja@cs.helsinki.fi
PostgreSQL version: 8.4.0
Operating system:   Linux
Description:        Window function alias
Details:

I came across this:

=> SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);
ERROR:  window functions not allowed in window definition

Changing the *column alias* to something else gives the expected answer. Is
this really the desired behaviour?

Marko Tiikkaja wrote:
> I came across this:
>
> => SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);
> ERROR:  window functions not allowed in window definition
>
> Changing the *column alias* to something else gives the expected answer. Is
> this really the desired behaviour?

It makes sense if you refer another column:

SELECT foo*2 AS col1, lead(foo) OVER(ORDER BY col1) AS foo
FROM (VALUES(0), (1)) bar(foo);

I'm not sure what the SQL spec says about that, but it seems OK to me.

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

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Marko Tiikkaja wrote:
>> I came across this:
>>
>> => SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);
>> ERROR:  window functions not allowed in window definition
>>
>> Changing the *column alias* to something else gives the expected answer. Is
>> this really the desired behaviour?

> It makes sense if you refer another column:

> SELECT foo*2 AS col1, lead(foo) OVER(ORDER BY col1) AS foo
> FROM (VALUES(0), (1)) bar(foo);

> I'm not sure what the SQL spec says about that, but it seems OK to me.

I think it's a bug.  If you change it to this, it doesn't complain:

regression=# SELECT lead(foo) OVER(ORDER BY foo) AS fool FROM (VALUES(0)) bar(foo);
 fool
------

(1 row)

We're getting bit by interpreting window-function ORDER BY arguments
according to SQL92 rules, in which they could refer to output-column
aliases.  This clearly has the potential to introduce circularity,
as here.  I think it would probably be best if we use strict SQL99
interpretation: window function PARTITION/ORDER arguments cannot be
interpreted as output-column names or numbers.

            regards, tom lane