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

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

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

If we allow them, then people can do things like:

HAVING max(a) > b

which seems strange. Would we handle that?

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Oct 5, Tom Lane mentioned:

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

Oh please God, NO! The next thing they want is SELECT FROM HAVING to
replace WHERE. That is merely the reverse case of what you so humbly
suggested. HAVING doesn't stand after GROUP BY for no reason, AFAIC.

Of course personally, I would love to kill SQL altogether and invent
something better, but not by the end of this day . . .

Peter

--
Peter Eisentraut - peter_e(at)gmx(dot)net
http://yi.org/peter-e/

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

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

Maybe we should support the HAVING without aggregates. What do others
think?

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

On Wed, 6 Oct 1999, Bruce Momjian wrote:

> > 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.
>
> Maybe we should support the HAVING without aggregates. What do others
> think?

If we are the only one that doesn't, it just makes it harder for those
moving from Oracle/Informix/etc if they happen to be using such queries...

How hard would it be to implement?

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

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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org

> > > 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.
> >
> > Maybe we should support the HAVING without aggregates. What do others
> > think?
>
> If we are the only one that doesn't, it just makes it harder for those
> moving from Oracle/Informix/etc if they happen to be using such queries...
>
> How hard would it be to implement?

Not hard. I will add it to the TODO list.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Wed, 6 Oct 1999, The Hermit Hacker wrote:

> > Maybe we should support the HAVING without aggregates. What do others
> > think?
>
> If we are the only one that doesn't, it just makes it harder for those
> moving from Oracle/Informix/etc if they happen to be using such queries...

I just tried it on a very old Sybase (ver 4 something, before ODBC was
available for it) and it works on that.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)com flame-mail: /dev/null
# include <std/disclaimers.h> Have you seen http://www.pop4.net?
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================