I assume this is a bug of some kind. Here's a neat little example to demonstrate it. If it helps make 7.1 better, great.
If I've done something wrong, I'd like to figure that out too if anyone can help.
This forms a bunch of hypothetical payroll entries and then makes a view which aggregates them by some arbitrary time period. (I've used a function which pulls out the month to group by.) Then I do a query on the view with a subquery as one of the fields that pulls out only a subset of the entries in the group.
The third "select" uses a function to create the subquery. This works (and is my current work-around).
The fourth "select" uses a regular subquery. It gives the error:
psql:datbug.sql:44: ERROR: Sub-SELECT uses un-GROUPed attribute pay_req.wdate from outer query
drop table pay_req;
drop view pay_req_v;
drop function sumr(int4,text,text);
set DateStyle to 'ISO'
create table pay_req (
empl_id int4,
wdate date,
type varchar,
hours float8 not null check (hours >= 0),
primary key (empl_id, wdate)
);
create view pay_req_v as
select empl_id,substr(text(wdate),6,2) as month,sum(hours) as hours from pay_req group by 1,2;
create function sumr(int4,text,text) returns float8 as '
select coalesce(sum(hours),0) from pay_req where empl_id = $1 and type = $2 and substr(text(wdate),6,2) = $3;
' LANGUAGE 'sql';
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-01','r',4);
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-02','r',5);
insert into pay_req (empl_id,wdate,type,hours) values (1000,'2000-Jan-03','o',6);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-01','r',2);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-02','r',3);
insert into pay_req (empl_id,wdate,type,hours) values (1001,'2000-Jan-03','o',4);
select * from pay_req order by empl_id,wdate,type,hours;
select * from pay_req_v order by empl_id,month,hours;
select v.empl_id,month,hours,
sumr(v.empl_id,'r',v.month) as "type-r"
from pay_req_v v where
v.empl_id = 1000 and
v.month = '01'
;
select v.empl_id,month,hours,
(select coalesce(sum(r.hours),0) from pay_req r where r.type = 'r' and r.empl_id = v.empl_id and substr(text(r.wdate),6,2) = v.month) as "type-r"
from pay_req_v v where
v.empl_id = 1000 and
v.month = '01'
;
