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

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




On Tue, 5 Oct 1999, Luuk de Boer wrote:

> On 4 Oct 99, at 21:18, Bruce Momjian wrote:
> 
> > > <cut>
> > >  
> > > > However, this is an old recollection, and I see on the current page that
> > > > this is no longer the case.  The current page looks much better, though
> > > > somehow you show PostgreSQL doesn't have HAVING or support -- comments. 
> > > > However, I realize such a test is a major project, and you are not going
> > > > to get everything right.
> > > 
> > > ps. I removed all the mailnglists to discuss some little things ...
> > > 
> > > hmmm do you mean having is now supported in postgresql. The 
> > > latest run of crash-me which I watched (last week I believe with 
> > > version 6.5.1) I believe I saw the message HAVING still not 
> > > supported in postgresql. Is that correct or did I do something wrong 
> > > with compiling postgres (just followed the normal procedure as 
> > > stated in the INSTALL file.
> > 
> > We have had HAVING since 6.3.* as I remember.
> 
> I looked into it this morning and found the following thing why crash-
> me is saying that having is not supported.
> We have a table (crash_me) with two columns (a (int)and b (char)) 
> which are filled with one entry (1 and 'a').
> The following thing is comming back to me ...
> query3: select a from crash_me group by a having 
> a > 0                                  
> 
> Got error from query: 'select a from crash_me 
> group by a having a > 0'
> ERROR:  SELECT/HAVING requires aggregates to be 
> valid
> 
> Checking connection
> Having: no
> Having with group function: query1: select a 
> from crash_me group by a having count(*) = 1     
>                        ...(53)
> yes
> Order by alias: yes
> Having on alias: query1: select a as ab from 
> crash_me group by a having ab > 0                
>             ...(53)
> 
> Got error from query: 'select a as ab from 
> crash_me group by a having ab > 0'
> ERROR:  attribute 'ab' not found
> 
> Checking connection
> no 
> 
> We had an if structure around testing having with group function if 
> having was supported and that if structure I removed.
> Could you explain to me what's wrong to the above queries?

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

> 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

Oh, don't get me started again on crashme :(

What is the purpose of the previous query? It seems to be equivalent
to
 select distinct a where a > 0;

We do support the HAVING clause, but apparently disallow some
degenerate cases. If MySQL weren't just a toy db, perhaps they would
start putting real queries into their garbage crashme. There, I feel
better now ;)

postgres=> select b, avg(a) from test group by b having avg(a) > 0;
b|avg
-+---
a|  1
(1 row)
                        - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

The Hermit Hacker <scrappy@hub.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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> 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

Er ... what's wrong with that?  Assuming b is a group by column,
of course...
        regards, tom lane

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> 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
> 
> Er ... what's wrong with that?  Assuming b is a group by column,
> of course...

But can we compare aggs and non-aggs?  I see now that our code is fine:
select relowner from pg_class group by relowner having max(relowner) = relowner;

This returns the proper result, namely the relowner _having_ the max
id.

Having is using an aggregate and non-aggregate, so when I said we only
support aggregates in the HAVING clause, I was wrong.  Looks fine.

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

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> But can we compare aggs and non-aggs?  I see now that our code is fine:

No, you're barking up the wrong tree.  The issue is whether a HAVING
clause that doesn't contain *any* aggregates is legal/reasonable.
It can contain non-aggregated references to GROUP BY columns in
any case.  But without aggregates, there's no semantic difference
from putting the same condition in WHERE.

I believe that planner.c currently has an implementation assumption
that HAVING must have an aggregate (because it hangs the HAVING clause
onto the Agg plan node as a qual clause --- if no Agg node, no place to
perform the HAVING test).  This could be fixed if we felt it was worth
doing.

I can't get excited about changing this from the standpoint of
functionality, because AFAICS there is no added functionality.
But if we're looking bad on a recognized benchmark maybe we
should do something about it.
        regards, tom lane

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > But can we compare aggs and non-aggs?  I see now that our code is fine:
> 
> No, you're barking up the wrong tree.  The issue is whether a HAVING
> clause that doesn't contain *any* aggregates is legal/reasonable.
> It can contain non-aggregated references to GROUP BY columns in
> any case.  But without aggregates, there's no semantic difference
> from putting the same condition in WHERE.
> 
> I believe that planner.c currently has an implementation assumption
> that HAVING must have an aggregate (because it hangs the HAVING clause
> onto the Agg plan node as a qual clause --- if no Agg node, no place to
> perform the HAVING test).  This could be fixed if we felt it was worth
> doing.
> 
> I can't get excited about changing this from the standpoint of
> functionality, because AFAICS there is no added functionality.
> But if we're looking bad on a recognized benchmark maybe we
> should do something about it.

Agreed.  I think there are too many people who get HAVING confused to
allow it.  Better that we should prevent it and make them do it right.

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

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@hub.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@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

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

> 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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

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@hub.org           secondary: scrappy@{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@hub.org           secondary: scrappy@{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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> I can't get excited about changing this from the standpoint of
> functionality, because AFAICS there is no added functionality.
> But if we're looking bad on a recognized benchmark maybe we
> should do something about it.

We are looking bad on a benchmark designed to show MySQL in the best
possible light, and to show other DBs at their worst. The maintainers
of that benchmark have no interest in changing that emphasis (e.g. we
are still reported as not supporting HAVING, even though we have
demonstrated to them that we do; this is the same pattern we have seen
earlier).

The last time I looked at it, there were ~30% factual errors in the
reported results for Postgres; no telling what errors are there for
other products. imho it is a waste of time to address a bogus
benchmark, unless someone wants to take it up as a hobby. I'm a bit
busy right now ;)
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> > I can't get excited about changing this from the standpoint of
> > functionality, because AFAICS there is no added functionality.
> > But if we're looking bad on a recognized benchmark maybe we
> > should do something about it.
>
> We are looking bad on a benchmark designed to show MySQL in the best
> possible light, and to show other DBs at their worst. The maintainers
> of that benchmark have no interest in changing that emphasis (e.g. we
> are still reported as not supporting HAVING, even though we have
> demonstrated to them that we do; this is the same pattern we have seen
> earlier).
>
> The last time I looked at it, there were ~30% factual errors in the
> reported results for Postgres; no telling what errors are there for
> other products. imho it is a waste of time to address a bogus
> benchmark, unless someone wants to take it up as a hobby. I'm a bit
> busy right now ;)

On a separate note, should we support HAVING without any aggregates?

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.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@michvhf.com   flame-mail: /dev/null # include <std/disclaimers.h>       Have you
seenhttp://www.pop4.net?       Online Campground Directory    http://www.camping-usa.com      Online Giftshop
Superstore   http://www.cloudninegifts.com
 
==========================================================================

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

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

Kinda looks like we gotta, just for compatibility reasons.  Also, if I
read the SQL spec correctly, it does not forbid HAVING w/out aggregates,
so those guys are adhering to the spec.

I'll put it on my todo list --- I'm busy making some other fixes in that
general area anyway.

Next question is should we emit a NOTICE or just silently do it?
(For that matter, should we go so far as to push the HAVING condition
over to become part of WHERE when it has no agg?  Then the speed issue
goes away.)  I kind of like emitting a NOTICE on the grounds of helping
to educate users about the difference between WHERE and HAVING, but
maybe people would just see it as obnoxious.
        regards, tom lane

> Next question is should we emit a NOTICE or just silently do it?
> (For that matter, should we go so far as to push the HAVING condition
> over to become part of WHERE when it has no agg?  Then the speed issue
> goes away.)  I kind of like emitting a NOTICE on the grounds of helping
> to educate users about the difference between WHERE and HAVING, but
> maybe people would just see it as obnoxious.

That is a tough call.  My personal vote is that HAVING is misunderstood
enough to emit a warning.

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

At 10:17 AM 10/6/99 -0400, Tom Lane wrote:

>Next question is should we emit a NOTICE or just silently do it?
>(For that matter, should we go so far as to push the HAVING condition
>over to become part of WHERE when it has no agg?  Then the speed issue
>goes away.)  I kind of like emitting a NOTICE on the grounds of helping
>to educate users about the difference between WHERE and HAVING, but
>maybe people would just see it as obnoxious.

People used to commercial servers like Oracle would just see it as
being obnoxious, I suspect.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

On Wed, 6 Oct 1999, Bruce Momjian wrote:

> > Next question is should we emit a NOTICE or just silently do it?
> > (For that matter, should we go so far as to push the HAVING condition
> > over to become part of WHERE when it has no agg?  Then the speed issue
> > goes away.)  I kind of like emitting a NOTICE on the grounds of helping
> > to educate users about the difference between WHERE and HAVING, but
> > maybe people would just see it as obnoxious.
> 
> That is a tough call.  My personal vote is that HAVING is misunderstood
> enough to emit a warning.

Agreed from here...

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

On Wed, 6 Oct 1999, Thomas Lockhart wrote:

> > I can't get excited about changing this from the standpoint of
> > functionality, because AFAICS there is no added functionality.
> > But if we're looking bad on a recognized benchmark maybe we
> > should do something about it.
> 
> We are looking bad on a benchmark designed to show MySQL in the best
> possible light, and to show other DBs at their worst. The maintainers
> of that benchmark have no interest in changing that emphasis (e.g. we
> are still reported as not supporting HAVING, even though we have
> demonstrated to them that we do; this is the same pattern we have seen
> earlier).
> 
> The last time I looked at it, there were ~30% factual errors in the
> reported results for Postgres; no telling what errors are there for
> other products. imho it is a waste of time to address a bogus
> benchmark, unless someone wants to take it up as a hobby. I'm a bit
> busy right now ;)

My opinion on this tends to be that, in the HAVING case, we are the only
one that doesn't support it w/o aggregates, so we altho we do follow the
spec, we are making it slightly more difficult to migrate from 'the
others' to us...

So far, Luuk has appeared to be relatively open as far as investigating
the discrepencies in the report...but, since he doesn't *know* PostgreSQL,
he  has no way of knowing what is wrong, and that is where, I think, we
should be trying to help support our end of things...

If Luuk were to come back  and  tell us that he absolutely won't change
anything, then, IMHO, there is a problem...but, thanks to his  test, Bruce
made some changes to how we handle our comments to fix a bug...and Luuk
told us that he fixed the HAVING  test such that HAVING w/o aggregates
doesn't   fail the test...

Benchmarks, IMHO, always try to favor the 'base product' that is being
advertised...but, more often then not, its because the person doing the
benchmarking knows that product well enough to be able to 'tweak' it to
perform better...Luuk, so far as I believe, is willing to be "educated in
PostgreSQL"...I don't think  its right for us to stifle that, is it?


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

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@gmx.net
http://yi.org/peter-e/

> My opinion on this tends to be that, in the HAVING case, we are the only
> one that doesn't support it w/o aggregates, so we altho we do follow the
> spec, we are making it slightly more difficult to migrate from 'the
> others' to us...

We follow the spec in what we support, but the spec *does* allow
HAVING w/o aggregates (and w/o any GROUP BY clause).

Tom, imho we absolutely should *not* emit warnings for unusual but
legal constructs. Our chapter on "syntax" can start addressing these
kinds of topics, but the backend probably isn't the place to teach SQL
style...

> Benchmarks, IMHO, always try to favor the 'base product' that is being
> advertised...but, more often then not, its because the person doing the
> benchmarking knows that product well enough to be able to 'tweak' it to
> perform better...Luuk, so far as I believe, is willing to be "educated in
> PostgreSQL"...I don't think  its right for us to stifle that, is it?

Right. Sorry Luuk for going off on ya...
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> We follow the spec in what we support, but the spec *does* allow
> HAVING w/o aggregates (and w/o any GROUP BY clause).

> Tom, imho we absolutely should *not* emit warnings for unusual but
> legal constructs.

Yeah, I came to the same conclusion while I was working on it last
night.  What I committed will still complain about HAVING that
references an ungrouped variable --- that *is* incorrect per spec ---
but otherwise it will take degenerate cases likeselect 2+2 having 1<2;
without complaint.

Hmm... here is a boundary condition that may or may not be right yet:

regression=> select f1 from int4_tbl having 1 < 2;
ERROR:  Illegal use of aggregates or non-group column in target list

Is this query legal, or not?  The spec sez about HAVING:
        1) If neither a <where clause> nor a <group by clause> is speci-           fied, then let T be the result of
thepreceding <from clause>;    [snip]
 
        1) Let T be the result of the preceding <from clause>, <where           clause>, or <group by clause>. If that
clauseis not a <group           by clause>, then T consists of a single group and does not have           a grouping
column.   [snip]
 
        2) Each <column reference> contained in a <subquery> in the <search           condition> that references a
columnof T shall reference a           grouping column of T or shall be specified within a <set func-           tion
specification>.

In the absence of a GROUP BY clause, it's clearly illegal for the HAVING
condition to reference any columns of the source table except via
aggregates.  It's not quite so clear whether the target list has the same
restriction --- my just-committed code assumes so, but is that right?

I guess the real question here is whether a query like the above should
deliver one row or N.  AFAICS the spec defines the result of this query
as a "grouped table" with one group, and in every other context
involving grouped tables you get only one output row per group; but
I don't see that spelled out for this case.

Comments?  Anyone want to opine on the legality of this, or try it on
some other DBMSes?
        regards, tom lane

> On a separate note, should we support HAVING without any aggregates?

Sure, it is allowed by the SQL92 spec (as are various other
combinations with and without GROUP and HAVING). But it adds no real
functionality, and this is the first report of anyone even trying it,
since the same behavior is covered by simpler, more common queries.
Doesn't seem to be a high priority...
                 - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> > My opinion on this tends to be that, in the HAVING case, we are the only
> > one that doesn't support it w/o aggregates, so we altho we do follow the
> > spec, we are making it slightly more difficult to migrate from 'the
> > others' to us...
> 
> We follow the spec in what we support, but the spec *does* allow
> HAVING w/o aggregates (and w/o any GROUP BY clause).
> 
> Tom, imho we absolutely should *not* emit warnings for unusual but
> legal constructs. Our chapter on "syntax" can start addressing these
> kinds of topics, but the backend probably isn't the place to teach SQL
> style...
> 

OK.  Agreed.

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