Re: aliases, &c in HAVING clause?

david(at)fetter(dot)org (David Fetter) writes:
> I bumped across this several times, and am wondering what SQL99 and
> SQL200x have to say about column numbers or aliases in HAVING.

SQL99 not only does not allow them in GROUP BY or HAVING, but it doesn't
allow them in ORDER BY either, thereby eliminating the entire wart from
the language. I doubt that SQL200x will reverse field on this decision.

In retrospect it was an error for us to allow aliases in GROUP BY, as
this has caused so much confusion about where they are legal. If it
weren't for backwards-compatibility concerns, I'd vote for adopting the
SQL99 definition (no aliases in any of these clauses).

> are there good reasons why the above shouldn't work?

Well, you showed one: interpreting "2 > 2" as anything other than a
constant expression is just plain weird. But the real reason why this
is bogus is that it violates the fundamental conceptual model of how
SELECT works. The SELECT output list is not supposed to be computed
until after all the other steps are complete, and therefore it's
improper to assume its results are available in GROUP BY or HAVING.

regards, tom lane