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

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