On Mon, Feb 23, 2004 at 06:36:48PM -0500, Tom Lane wrote: > [EMAIL PROTECTED] (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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend