Re: HAVING and column alias

From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Mike Mascari <mascarm(at)mascari(dot)com> writes:
> > SELECT
> > SUM(p.qty),
> > (SELECT date_trunc('day', sales.active)
> > FROM sales
> > WHERE sales.purchase = p.purchase) AS field1
> > FROM purchases p
> > GROUP BY field1
> > HAVING (field1 IS NOT NULL);
>
> > ERROR: Attribute 'field1' not found
>
> This is definitely illegal per the SQL spec: output column
names are not
> legal per spec in either GROUP BY or HAVING. Postgres is lax
about this
> in GROUP BY (mainly for historical reasons), but not in
HAVING --- and
> even in GROUP BY, we only recognize an output column name if
it is used
> by itself, not as part of an expression. So your HAVING
clause would
> lose even if we applied GROUP-BY-like rules to it.

Okay. I wasn't sure. It appears as a 'feature' tested by mySQL's
crashme, which is obviously not a measurement of SQL spec
compliance, to be sure...

> If you can't restructure the query, I think you'll have to
repeat the
> sub-SELECT in the HAVING clause rather than refer to it via
the field1
> alias.

Okay. I'll have to upgrade, then. Repeating the sub-SELECT in
the HAVING clause generated that same error that I reported
earlier when two sub-SELECTs in the target list are identical in
version 7.2.1.

Thanks, Tom

Mike Mascari
mascarm(at)mascari(dot)com