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

Andreas


Tue, 8 Sep 1998 09:40:30 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
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

Tue, 08 Sep 1998 16:29:10 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems...