On 5 Oct 99, at 22:23, The Hermit Hacker wrote:
>
> 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 bruce and hermit for all the comments,
I looked into the book "The SQL Standard" fourth edition of Date
and in the appendixes page 439 they have an example which they
discuss. The example is: select count(*) as x from mt having 0 = 0;
with an empty table they say logically correct it should return one
column and no rows but sql gives a table of one column and one
row. So I think it's true that HAVING has to have an aggregation
but it will also be possible use a non-aggregation.
If I look in our crash-me output page (this is a handy thing for this
kind of questions) and look for all the other db's to see what they
do I can say the following thing:
Informix,Access,Adabas,db2,empress,ms-sql,oracle,solid and
sybase are all supporting non-aggregation in having clause.
At this moment everyone except postgres is supporting it.
The change which I can made is to remove the if structure around
the having tests so that having with group functions will also be
tested in the crash-me test.
I will try the patch of bruce for the comment part. It shouldn't be the
way that the perl module is stripping the comments of the querie
but it is possible and if it is possible it will be a bug in the DBD
postgresql perl module.
PS. the benchmark results of postgres 6.5.2 are also added to the
benchmark result page.
Greetz...
Luuk
