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

Greg Stark <gsstark(at)mit(dot)edu> writes:
> It sort of seems like "select aggregate(col) from tab" with no GROUP BY clause
> is a bit of a special case. The consistent thing to do would be to return no
> records.

I don't think so. SQL99 defines this stuff in a way that might make you
feel better: it says that the presence of either HAVING or any aggregate
functions in the target list implies "GROUP BY ()", which is the case
that they identify as <grand total> in the <group by clause> syntax.
Basically this legitimizes the concept of turning the whole input table
into one group, which is what's really going on here. We get this right
in the case where it's driven by the appearance of aggregate functions,
but not when it's just driven by HAVING.

> It seems like all that's needed is a simple flag on the Aggregate node that
> says whether to output a single record if there are no input records or to
> output no records.

The implementation problem is that there *is* no aggregate node if there
are no aggregates. The definitional problem is that we are allowing
cases that are illegal per spec and are going to be difficult to
continue to support if we handle all the spec-required cases properly.

regards, tom lane