[BUGS] We are not following the spec for HAVING without GROUP BY

#1

I wrote in reference to bug#1528:

Quote:

What the spec actually says, or at least implies, is that a HAVING
clause is to be evaluated only once per group --- where the "group"
is the whole table if there's no GROUP BY clause.

In fact, reading the spec more closely, it is clear that the presence
of HAVING turns the query into a grouped query even if there is no
GROUP BY. I quote SQL92 7.8 again:

7.8 <having clause>

Function

Specify a grouped table derived by the elimination of groups from
^^^^^^^^^^^^^^^^^^^^^^^
the result of the previously specified clause that do not meet the
<search condition>.

...

1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column.

2) The <search condition> is applied to each group of T. The result
of the <having clause> is a grouped table of those groups of T
^^^^^^^^^^^^^^^^^^
for which the result of the <search condition> is true.

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

What we are currently doing with this construct is pretending that it
means

select col from tab where 2>1

but it does not mean that according to the spec.

As I look into this, I find that several warty special cases in the
parser and planner arise from our misunderstanding of this point,
and could be eliminated if we enforced the spec's interpretation.
In particular this whole business of "moving HAVING into WHERE" is
wrong and should go away.

Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

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

On informix 9.21.UC4

Quote:

create table tab (col integer);
select 1 from tab having 1=0;

returns no rows

Quote:

select 1 from tab having 1=1;

returns no rows

Quote:

insert into tab values(1);
insert into tab values(2);
select 1 from tab having 1=0;

returns no rows

Quote:

select 1 from tab having 1=1;

returns 2 rows

regards,
Jaime Casanova

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] We are not following the spec for HAVING without GROUP BY

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] We are not following the spec for HAVING without GROUP BY - 03-09-2005 , 08:23 PM

Re: [BUGS] We are not following the spec for HAVING without GROUP BY - 03-10-2005 , 02:16 PM