Skip site navigation (1) Skip section navigation (2)

Re: BUG #5018: Window function alias

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5018: Window function alias
Date: 2009-08-27 14:29:37
Message-ID: 3276.1251383377@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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

In response to

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2009-08-27 15:00:38
Subject: Re: BUG #4996: postgres.exe memory consumption keeps going up
Previous:From: Heikki LinnakangasDate: 2009-08-27 13:19:44
Subject: Re: BUG #5018: Window function alias

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group