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

From: "Gill, Jerry T(dot)" <JTGill(at)west(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <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:13:31
Message-ID: 9D87A98A6510F24C817257895EF4282A05460F90@omacex08.corp.westworlds.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Here is your Sql run in a DB2 database.
connect to phoenix

Database Connection Information

Database server = DB2/LINUX 8.1.5
SQL authorization ID = GILL
Local database alias = PHOENIX

create table tab (col integer)
DB20000I The SQL command completed successfully.

select 1 from tab having 1=0

1
-----------

0 record(s) selected.

select 1 from tab having 1=1

1
-----------
1

1 record(s) selected.

insert into tab values(1)
DB20000I The SQL command completed successfully.

insert into tab values(2)
DB20000I The SQL command completed successfully.

select 1 from tab having 1=0

1
-----------

0 record(s) selected.

select 1 from tab having 1=1

1
-----------
1

1 record(s) selected.

Hope that helps.
-Jgill

-----Original Message-----
From: pgsql-bugs-owner(at)postgresql(dot)org
[mailto:pgsql-bugs-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Thursday, March 10, 2005 11:45 AM
To: pgsql-hackers(at)postgresql(dot)org; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] [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 6: Have you searched our list archives?

http://archives.postgresql.org

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin HaleBoyes 2005-03-10 18:21:14 Re: [HACKERS] We are not following the spec for HAVING without GROUP
Previous Message Tom Lane 2005-03-10 17:44:50 Re: [HACKERS] We are not following the spec for HAVING without GROUP BY

Browse pgsql-hackers by date

  From Date Subject
Next Message Bort, Paul 2005-03-10 18:15:47 Re: Raw size
Previous Message Bort, Paul 2005-03-10 18:09:11 Re: We are not following the spec for HAVING without GR