pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with subquery joined to a view
2+ messages / 2 participants
[nested] [flat]

* Problem with subquery joined to a view
@ 2000-11-21 17:51 Kyle <kyle@actarg.com>
  2000-11-21 18:16 ` Re: Problem with subquery joined to a view Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Kyle @ 2000-11-21 17:51 UTC (permalink / raw)
  To: pgsql-sql

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'
;

Attachments:

kyle.vcf (text/x-vcard, 184B) download begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: Problem with subquery joined to a view
  2000-11-21 17:51 Problem with subquery joined to a view Kyle <kyle@actarg.com>
@ 2000-11-21 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-11-21 18:16 UTC (permalink / raw)
  To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql

Kyle <kyle@actarg.com> writes:
> 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

This appears to work correctly in current sources.  In 7.0.* and before
grouped views do not work very well, because the rewriter effectively
expands them in-line, thus converting your outer query into a grouped
query (or making it not work at all, if it was already grouped :-().

See related bug report just a few minutes ago on pgsql-bugs.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-11-21 18:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-21 17:51 Problem with subquery joined to a view Kyle <kyle@actarg.com>
2000-11-21 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox