Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] We are not following the spec for HAVING without GROUP BY

From: "Barry Lind" <blind(at)xythos(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>, <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Date: 2005-03-10 18:34:08
Message-ID: 03E7D3E231BB7B4A915A6581D4296CC6FDED56@NSNOVPS00411.nacio.xythos.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Thursday, March 10, 2005 9:45 AM
To: pgsql-hackers(at)postgresql(dot)org; pgsql-bugs(at)postgresql(dot)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


Responses

pgsql-hackers by date

Next:From: Gary DoadesDate: 2005-03-10 19:12:48
Subject: Re: [HACKERS] We are not following the spec for HAVING without
Previous:From: Kevin HaleBoyesDate: 2005-03-10 18:21:14
Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP

pgsql-bugs by date

Next:From: Gary DoadesDate: 2005-03-10 19:12:48
Subject: Re: [HACKERS] We are not following the spec for HAVING without
Previous:From: Kevin HaleBoyesDate: 2005-03-10 18:21:14
Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group