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!