Re: [HACKERS] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> We follow the spec in what we support, but the spec *does* allow
> HAVING w/o aggregates (and w/o any GROUP BY clause).

> Tom, imho we absolutely should *not* emit warnings for unusual but
> legal constructs.

Yeah, I came to the same conclusion while I was working on it last
night. What I committed will still complain about HAVING that
references an ungrouped variable --- that *is* incorrect per spec ---
but otherwise it will take degenerate cases like
select 2+2 having 1<2;
without complaint.

Hmm... here is a boundary condition that may or may not be right yet:

regression=> select f1 from int4_tbl having 1 < 2;
ERROR: Illegal use of aggregates or non-group column in target list

Is this query legal, or not? The spec sez about HAVING:

1) If neither a <where clause> nor a <group by clause> is speci-
fied, then let T be the result of the preceding <from clause>;
[snip]

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.
[snip]

2) Each <column reference> contained in a <subquery> in the <search
condition> that references a column of T shall reference a
grouping column of T or shall be specified within a <set func-
tion specification>.

In the absence of a GROUP BY clause, it's clearly illegal for the HAVING
condition to reference any columns of the source table except via
aggregates. It's not quite so clear whether the target list has the same
restriction --- my just-committed code assumes so, but is that right?

I guess the real question here is whether a query like the above should
deliver one row or N. AFAICS the spec defines the result of this query
as a "grouped table" with one group, and in every other context
involving grouped tables you get only one output row per group; but
I don't see that spelled out for this case.

Comments? Anyone want to opine on the legality of this, or try it on
some other DBMSes?

regards, tom lane