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

Re: Windowing Function Patch Review -> Standard Conformance

From: "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com>
To: "David Rowley" <dgrowley(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, heikki(dot)linnakangas(at)enterprisedb(dot)com, "Vladimir Sitnikov" <sitnikov(dot)vladimir(at)gmail(dot)com>
Subject: Re: Windowing Function Patch Review -> Standard Conformance
Date: 2008-11-10 13:48:23
Message-ID: e08cc0400811100548s1d31af73s888062c05c8512bd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2008/11/9 David Rowley <dgrowley(at)gmail(dot)com>:
> Using one of my original test tables I'm testing windowing functions with a
> GROUP BY.
>
> The following query works as I would expect.
>
> -- Works
> SELECT department,
>       SUM(Salary),
>       ROW_NUMBER() OVER (ORDER BY department),
>       SUM(SUM(salary)) OVER (ORDER BY department)
> FROM employees
> GROUP BY department;
>
>
> The following one fails with the message.
> ERROR:  variable not found in subplan target list
>
> -- Does not work.
> SELECT department,
>       SUM(Salary),
>       ROW_NUMBER() OVER (ORDER BY department),
>       SUM(SUM(salary)) OVER (ORDER BY department DESC)
> FROM employees
> GROUP BY department;
>
> I just added the DESC to force it into creating 2 separate windows.
>
> I can re-write the non working query to work using the following:
>
>
> SELECT department,
>       salary,
>       ROW_NUMBER() OVER (ORDER BY department),
>       SUM(salary) OVER (ORDER BY department DESC)
> FROM (SELECT department,
>             SUM(salary) AS salary
>      FROM employees
>      GROUP BY department
> ) t;
>
>

Thank you for your tests again. Attached are both of delta from the
previous and the whole version agains today's HEAD. Note that HEAD has
changed pg_class.h, which conflicted with my local repository so if
you try it update your local with HEAD and apply the patch. Though,
the delta one seem to be applicable without sync.


Regards,

-- 
Hitoshi Harada

Attachment: wfdelta.20081110.patch
Description: text/x-diff (4.1 KB)
Attachment: window_functions.patch.20081110-1.gz
Description: application/x-gzip (46.7 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Hitoshi HaradaDate: 2008-11-10 13:48:59
Subject: Re: Windowing Function Patch Review -> Standard Conformance
Previous:From: Tom LaneDate: 2008-11-10 13:38:23
Subject: Re: pg_do_encoding_conversion glitch

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