Re: Windowing Function Patch Review -> Standard Conformance

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
departmentDESC)
 
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
departmentDESC)
 
FROM (SELECT department,            SUM(salary) AS salary     FROM employees     GROUP BY department
) t;




Testing with:

create table employees ( id INT primary key, name varchar(30) not null, department varchar(30) not null, salary int not
null,check (salary >= 0)
 
);


insert into employees values(1,'Jeff','IT',10000);
insert into employees values(2,'Sam','IT',12000);

insert into employees values(3,'Richard','Manager',30000);
insert into employees values(4,'Ian','Manager',20000);

insert into employees values(5,'John','IT',60000);
insert into employees values(6,'Matthew','Director',60000);