From kyle@actarg.com Wed Mar 7 23:13:36 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f27NDax99811 for ; Wed, 7 Mar 2001 18:13:36 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id QAA29018 for ; Wed, 7 Mar 2001 16:13:33 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id QAA23882 for ; Wed, 7 Mar 2001 16:13:50 -0700 Message-ID: <3AA6C0AD.BC8A015A@actarg.com> Date: Wed, 07 Mar 2001 16:13:50 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: A query that doesn't work on 7.1 Content-Type: multipart/mixed; boundary="------------5C353FBEEE70421E46362945" X-Archive-Number: 200103/162 X-Sequence-Number: 1815 This is a multi-part message in MIME format. --------------5C353FBEEE70421E46362945 Content-Type: multipart/alternative; boundary="------------37278568DF2323109AEAA9A1" --------------37278568DF2323109AEAA9A1 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Here's a query that doesn't work on 7.1. Is this a bug or am I doing something wrong? 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' ; --------------37278568DF2323109AEAA9A1 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Here's a query that doesn't work on 7.1.  Is this a bug or am I doing something wrong?

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'
;
 
  --------------37278568DF2323109AEAA9A1-- --------------5C353FBEEE70421E46362945 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" 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 --------------5C353FBEEE70421E46362945-- From tgl@sss.pgh.pa.us Thu Mar 8 00:08:28 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2808Sx91490 for ; Wed, 7 Mar 2001 19:08:28 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f2808D919635; Wed, 7 Mar 2001 19:08:13 -0500 (EST) To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: A query that doesn't work on 7.1 In-reply-to: <3AA6C0AD.BC8A015A@actarg.com> References: <3AA6C0AD.BC8A015A@actarg.com> Comments: In-reply-to Kyle message dated "Wed, 07 Mar 2001 16:13:50 -0700" Date: Wed, 07 Mar 2001 19:08:13 -0500 Message-ID: <19632.984010093@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/164 X-Sequence-Number: 1817 Kyle 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 From tgl@sss.pgh.pa.us Thu Mar 8 01:55:05 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f281t5x23342 for ; Wed, 7 Mar 2001 20:55:05 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f281sx924469; Wed, 7 Mar 2001 20:54:59 -0500 (EST) To: Kyle , pgsql-sql@postgresql.org Subject: Re: A query that doesn't work on 7.1 In-reply-to: <19632.984010093@sss.pgh.pa.us> References: <3AA6C0AD.BC8A015A@actarg.com> <19632.984010093@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 07 Mar 2001 19:08:13 -0500" Date: Wed, 07 Mar 2001 20:54:59 -0500 Message-ID: <24466.984016499@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/167 X-Sequence-Number: 1820 >> 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; /* From kyle@actarg.com Fri Mar 9 00:21:08 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f290L8x46333 for ; Thu, 8 Mar 2001 19:21:08 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id RAA22109; Thu, 8 Mar 2001 17:21:04 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id RAA25929; Thu, 8 Mar 2001 17:21:22 -0700 Message-ID: <3AA82201.C22A5BAB@actarg.com> Date: Thu, 08 Mar 2001 17:21:22 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org Subject: Re: A query that doesn't work on 7.1 References: <3AA6C0AD.BC8A015A@actarg.com> <19632.984010093@sss.pgh.pa.us> <24466.984016499@sss.pgh.pa.us> <3AA7E16D.C9B4FA76@actarg.com> <29675.984081236@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------29FE3A9ED2E949B086304C63" X-Archive-Number: 200103/185 X-Sequence-Number: 1838 This is a multi-part message in MIME format. --------------29FE3A9ED2E949B086304C63 Content-Type: multipart/alternative; boundary="------------0A04642A3C58B2AC79884C54" --------------0A04642A3C58B2AC79884C54 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > > Here's another twist though. Is this a bug too or is this just beyond our reach? > > > 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. > FYI: I got by with kind of a pseudo average (mean, I guess) for now implemented as: min(date) + (max(date) - min(date)/2) > > 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? 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' ; --------------0A04642A3C58B2AC79884C54 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:

> Here's another twist though.  Is this a bug too or is this just beyond our reach?

> 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.
 

FYI:
I got by with kind of a pseudo average (mean, I guess) for now implemented as:

min(date) + (max(date) - min(date)/2)
 

 
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?
 
 

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'
;
 
  --------------0A04642A3C58B2AC79884C54-- --------------29FE3A9ED2E949B086304C63 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" 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 --------------29FE3A9ED2E949B086304C63-- From tgl@sss.pgh.pa.us Fri Mar 9 00:39:29 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f290dTx16506 for ; Thu, 8 Mar 2001 19:39:29 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f290dN903067; Thu, 8 Mar 2001 19:39:23 -0500 (EST) To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: A query that doesn't work on 7.1 In-reply-to: <3AA82201.C22A5BAB@actarg.com> References: <3AA6C0AD.BC8A015A@actarg.com> <19632.984010093@sss.pgh.pa.us> <24466.984016499@sss.pgh.pa.us> <3AA7E16D.C9B4FA76@actarg.com> <29675.984081236@sss.pgh.pa.us> <3AA82201.C22A5BAB@actarg.com> Comments: In-reply-to Kyle message dated "Thu, 08 Mar 2001 17:21:22 -0700" Date: Thu, 08 Mar 2001 19:39:23 -0500 Message-ID: <3064.984098363@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/186 X-Sequence-Number: 1839 Kyle 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