Thread: Re: We are not following the spec for HAVING without GROUP BY

> What that means is that neither the HAVING clause nor the targetlist
> can use any ungrouped columns except within aggregate calls; that is,
>
>     select col from tab having 2>1

Informix:
select tabname from systables having 2 > 1;  294: The column (tabname) must be in the GROUP BY list.
select tabname from systables group by 1 having 2 > 1;  all rows returned
select tabname from systables group by 1 having 1 > 2;  no rows found

Andreas

On Oracle 9.2 you get 0, 0, 0, and 2 rows.

--Barry


SQL> create table tab (col integer);

Table created.

SQL> select 1 from tab having 1=0;

no rows selected

SQL> select 1 from tab having 1=1;

no rows selected

SQL> insert into tab values (1);

1 row created.

SQL> insert into tab values (2);

1 row created.

SQL> select 1 from tab having 1=0;

no rows selected

SQL> select 1 from tab having 1=1;
        1
----------        1        1

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
JServer Release 9.2.0.1.0 - Production



-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, March 10, 2005 9:45 AM
To: pgsql-hackers@postgresql.org; pgsql-bugs@postgresql.org
Subject: Re: [HACKERS] We are not following the spec for HAVING without
GROUP BY

I wrote:
> This is quite clear that the output of a HAVING clause is a "grouped
> table" no matter whether the query uses GROUP BY or aggregates or not.

> What that means is that neither the HAVING clause nor the targetlist
> can use any ungrouped columns except within aggregate calls; that is,
>     select col from tab having 2>1
> is in fact illegal per SQL spec, because col isn't a grouping column
> (there are no grouping columns in this query).

Actually, it's even more than that: a query with HAVING and no GROUP BY
should always return 1 row (if the HAVING succeeds) or 0 rows (if not).
If there are no aggregates, the entire from/where clause can be thrown
away, because it can have no impact on the result!

Would those of you with access to other DBMSes try this:

create table tab (col integer);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
insert into tab values(1);
insert into tab values(2);
select 1 from tab having 1=0;
select 1 from tab having 1=1;

I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows
from the 4 selects --- that is, the contents of tab make no difference
at all.  (MySQL returns 0, 0, 0, and 2 rows, so they are definitely
copying our mistake...)
        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Barry Lind" <blind@xythos.com> writes:
> On Oracle 9.2 you get 0, 0, 0, and 2 rows.

Really!?  Well, we always knew they were a bit standards-challenged ;-).
I have more faith in DB2 being an accurate implementation of the spec.
        regards, tom lane

Just a quick test on the DBMS I have available at work.


IBM Informix Dynamic Server Version 10.00.TC1TL
Error: The column (id) must be in the GROUP BY list.

Oracle 9.2.0.11
Returns the same records as if where would be used.

MSSQL Express 2005 Beta February TP
Error: Column 'tab.id' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.

MySQL 5.0.2
Returns the same records as if where would be used.

Sybase 12.5
Returns the same records as if where would be used.

Firebird 1.5.2
Error: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
No message for code 335544824 found.
null

Best regards,

Michael Wimmer