Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems...

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