Re: aliases, &c in HAVING clause?

On Mon, Feb 23, 2004 at 06:36:48PM -0500, Tom Lane wrote:
> david@fetter.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.

Right.

> 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).

Hmm.  If I were going to flog for the other side, it would be on the
grounds of error prevention with aliases.

Just in general, isn't it better to write a piece of code (here, a
possibly-complicated aggregate) just once and refer to it elsewhere
rather than have to write a separate copy of it everywhere it's used?
That's one of the fundamental design principles on which structured
programming, subroutines and objects are based.

> > 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.

Um, and the rest of SQL is...normal?!? ;)

> 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.

Maybe I'm missing something, but if I recall right, the "fundamental
principle" of a SELECT is that it's a PROJECTion & RESTRICTion of the
full cross-product of the relations in question.  I haven't checked,
but I'm pretty certain that PostgreSQL doesn't do things that way at
the implementation level.

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!