Re: [HACKERS] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

Luuk...

I brought this up with the -hackers list, and, in generally, it
appears to be felt that the query, which you use in the crashme test to
test HAVING, isn't necessarily valid ...

Basically:

select a from test group by a having a > 0;

could be more efficiently written as:

select a from test where a > 0 group by a;

I'm personally curious, though...how does Oracle/Informix and
other RDBMS systems handle this? Do they let it pass, or do they give an
error also?

I think the general concensus, at this time, is to change the
ERROR to a NOTICE, with a comment that using a WHERE would be more
efficient then the HAVING...and, unless someone can come up with an
instance that would make sense (ie. why you'd do it with HAVING vs WHERE),
I'm in agreement with them...

Since we obviously do support HAVING, and, I believe, follow the
SQL92 spec on it, is there any way of getting the crashme test fixed to
not use the above query as a basis for whether an RDBMS supports HAVING or
not?

thanks...

On Tue, 5 Oct 1999, Tom Lane wrote:

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > Anyone want to comment on this one? Just tested with v6.5.0 and it still
> > exists there...
>
> > vhosts=> create table test ( a int, b char );
> > CREATE
> > vhosts=> insert into test values ( 1, 'a' );
> > INSERT 149258 1
> > vhosts=> select a from test group by a having a > 0;
> > ERROR: SELECT/HAVING requires aggregates to be valid
>
> That's not a bug, it means what it says: HAVING clauses should contain
> aggregate functions. Otherwise they might as well be WHERE clauses.
> (In this example, flushing rows with negative a before the group step,
> rather than after, is obviously a win, not least because it would
> allow the use of an index on a.)
>
> However, I can't see anything in the SQL92 spec that requires you to
> use HAVING intelligently, so maybe this error should be downgraded to
> a notice? "HAVING with no aggregates would be faster as a WHERE"
> (but we'll do it anyway to satisfy pedants...)
>
> regards, tom lane
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org