pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
A query that doesn't work on 7.1
5+ messages / 2 participants
[nested] [flat]

* A query that doesn't work on 7.1
@ 2001-03-07 23:13 Kyle <kyle@actarg.com>
  2001-03-08 00:08 ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Kyle @ 2001-03-07 23:13 UTC (permalink / raw)
  To: pgsql-sql

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

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] 5+ messages in thread

* Re: A query that doesn't work on 7.1
  2001-03-07 23:13 A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
@ 2001-03-08 00:08 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-08 01:54   ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-03-08 00:08 UTC (permalink / raw)
  To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql

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



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

* Re: A query that doesn't work on 7.1
  2001-03-07 23:13 A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
  2001-03-08 00:08 ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-08 01:54   ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-09 00:21     ` Re: A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-03-08 01:54 UTC (permalink / raw)
  To: Kyle <kyle@actarg.com>; pgsql-sql

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


inline patch 1: src/backend/optimizer/util/clauses.c (+8/-0 lines) download inline-1.patch*** 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; /*
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: A query that doesn't work on 7.1
  2001-03-07 23:13 A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
  2001-03-08 00:08 ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-08 01:54   ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-09 00:21     ` Kyle <kyle@actarg.com>
  2001-03-09 00:39       ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Kyle @ 2001-03-09 00:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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

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] 5+ messages in thread

* Re: A query that doesn't work on 7.1
  2001-03-07 23:13 A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
  2001-03-08 00:08 ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-08 01:54   ` Re: A query that doesn't work on 7.1 Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-09 00:21     ` Re: A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
@ 2001-03-09 00:39       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-03-09 00:39 UTC (permalink / raw)
  To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql

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



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


end of thread, other threads:[~2001-03-09 00:39 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-07 23:13 A query that doesn't work on 7.1 Kyle <kyle@actarg.com>
2001-03-08 00:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-08 01:54   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-09 00:21     ` Kyle <kyle@actarg.com>
2001-03-09 00:39       ` 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