Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems... - Mailing list pgsql-hackers

From Vadim Mikheev
Subject Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems...
Date
Msg-id 35F4EAD6.D867CD84@krs.ru
Whole thread Raw
In response to Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems...  (Andreas Zeugswetter <andreas.zeugswetter@telecom.at>)
List pgsql-hackers
Andreas Zeugswetter wrote:
>
> >Also, could someone test is HAVING without aggregates
> >disallowed or not:
> >
> >select a, min (b) from x group by a having a = 0;
>
> allowed in Informix:
>          a       (min)
>  No rows found.

Thanks, Andreas!
I'll comment out some code... Actually, non-aggregate expressions
could be moved to WHERE, but at the moment I'll just allow them in
HAVING.

Ok, there are also some problems with subselects in HAVING
but I haven't time to fix them now:

select a as a2, b as b2, c as c2 into table x2 from x;
select a, sum(b) from x group by a having avg(c) =
    (select max(c2) from x2 where a2 = a/2);
-- ok

select a/2, sum(b) from x group by a/2 having avg(c) =
    (select max(c2) from x2 where a2 = a/2);
-- ERROR:  You must group by the attribute used from outside!
-- this means that GroupBy func doesn't work here...

select a, sum(b) from x group by a having avg(c) =
    (select max(c2) from x2 where a2 = max(b));
-- ERROR:  parser: aggregates not allowed in WHERE clause
-- Is this allowed in another dbms-es ???

-- This is not problem of HAVING but subselects...
select a as f, sum(b) from x group by f having avg(c) =
    (select max(c2) from x2 where a2 = f);
-- ERROR:  attribute 'f' not found
-- Should be aliasing handled in subselects ???

Vadim

pgsql-hackers by date:

Previous
From: "Gerald Fischer"
Date:
Subject: Re: [INTERFACES] Re: DELETE FROM TABLE doesn't work (AGAIN)
Next
From: Sferacarta Software
Date:
Subject: Re[2]: [INTERFACES] Re: DELETE FROM TABLE doesn't work (AGAIN)