Can someone remind me where these benchmark pages are again? :)
On Wed, 6 Oct 1999, Luuk de Boer wrote:
> 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
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
