Thread: A query that doesn't work on 7.1
The last two selects yield:
ERROR: Sub-SELECT uses un-GROUPed attribute m1.ropnum from outer query
Basically, everything works until I apply the avg() function and try to aggregate the results.
drop table mtr;
create table mtr (
ttype varchar(2), --record type
ropnum int4, --order number
minum int4, --item number
pnum varchar(18),
tdate date,
primary key (ttype,ropnum,minum)
);
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('po',1000,1,2000,'2001-Jan-30');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,1,2001,'2001-Jan-10');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,2,2002,'2001-Jan-12');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,3,2003,'2001-Jan-14');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('po',1001,1,2000,'2001-Feb-28');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,1,2011,'2001-Feb-01');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,2,2012,'2001-Feb-02');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,3,2013,'2001-Feb-03');
--The finish date is represented by the tdate of a po type record
--The start date is found by the earliest of the wm type records with the same ropnum,minum fields
--This lists the start and finish dates
select
(select min(tdate) from mtr where ttype = 'wm' and ropnum = m1.ropnum) as start,
m1.tdate as finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;
--Now I try to find the average number of days between start and finish for the part
select
avg(date_part('day',(start::datetime - finish::datetime)::timespan)) from
(select
(select min(tdate) from mtr where ttype = 'wm' and ropnum = m1.ropnum) as start,
m1.tdate::datetime as finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
) as dates
;
--Here I try a different method
-- select
select
avg(date_part('day',((select min(tdate) from mtr where ttype = 'wm' and ropnum = m1.ropnum)::datetime - m1.tdate::datetime)::timespan))
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;
Attachment
Kyle <kyle@actarg.com> writes: > Here's a query that doesn't work on 7.1. Is this a bug or am I doing > something wrong? Hmm, I think you have found some subtle bugs in aggregate processing; offhand it seems like both these queries should be legal. I'll see what I can do about it. regards, tom lane
>> Here's a query that doesn't work on 7.1. Is this a bug or am I doing >> something wrong? > Hmm, I think you have found some subtle bugs in aggregate processing; > offhand it seems like both these queries should be legal. I'll see what > I can do about it. Not so subtle after all :-(. Curious that no one noticed this before. Here's the patch if you need it right away... regards, tom lane *** src/backend/optimizer/util/clauses.c.orig Mon Feb 12 20:26:43 2001 --- src/backend/optimizer/util/clauses.c Wed Mar 7 20:49:01 2001 *************** *** 540,545 **** --- 540,553 ---- Query *context) { if (node == NULL) + return false; + + /* + * If we find an aggregate function, do not recurse into its + * arguments. Subplans invoked within aggregate calls are allowed + * to receive ungrouped variables. + */ + if (IsA(node, Aggref)) return false; /*
> Here's another twist though. Is this a bug too or is this just beyond our reach?FYI:> psql:lead1.sql:64: ERROR: Unable to select an aggregate function avg(date)
It's just that we don't have any avg() function for date --- nor for
timestamp, which is a little more surprising.
I got by with kind of a pseudo average (mean, I guess) for now implemented as:
min(date) + (max(date) - min(date)/2)
When you say "gin up" are you talking about C, PL/XXX, or just casts?
You could probably gin up a usable avg(timestamp) using the avg(float8)
routines, since a timestamp is really just a double under the hood.
BTW, here's another question:
Here's a view of a union. The two selects are fast when executed individually (and explain confirms that they use indexes). When I query the view, though, it is slow (explain says the scans are sequential).
Is this expected or a bug?
-- Simulate a table of lead times
create view vend_v_lead as select
p.pnum as pnum,'vend' as type,ldtime::float8 as lead,0 as aging from vend_price v, prd_part p where v.pnum = p.pnum
union select
p.pnum,'hist',date_part('day',(m.tdate::datetime-m.mtr_date::datetime)::timespan),0 from mtr_reg m, prd_part p where m.pnum = p.pnum and m.ttype = 'po' and m.status = 'clsd'
;
Attachment
Kyle <kyle@actarg.com> writes: >> You could probably gin up a usable avg(timestamp) using the avg(float8) >> routines, since a timestamp is really just a double under the hood. > When you say "gin up" are you talking about C, PL/XXX, or just casts? I was thinking of full-scale cheating: make a new pg_aggregate entry for avg(timestamp) that points at the existing pg_proc entries that support avg(float8). CREATE AGGREGATE won't do this for you, but there's always good old INSERT INTO ... > Here's a view of a union. The two selects are fast when executed > individually (and explain confirms that they use indexes). When I > query the view, though, it is slow (explain says the scans are > sequential). > Is this expected or a bug? Seems odd. Need enough info to reproduce, please? regards, tom lane