I wrote in reference to bug#1528:
> 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
resultof 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
clauseis 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
groupedtable 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