On Mon, 5 Apr 2004, Tom Lane wrote:
> This example strikes me as a good reason why we ought to deprecate and
> eventually remove the capability for GROUP BY to reference output-list
> aliases. This is not legal per SQL spec,
Sticking to the SQL spec is (almost) always good.
> matches. This would effectively change the precedence for resolving
> "GROUP BY x" to be (1) x as a local variable, (2) x as a targetlist
> alias, (3) x as an outer variable; whereas the present search order is
> (1), (3), (2). AFAICS this does not break compatibility with either
> SQL92 or SQL99 because both of them allow only case (1).
This sounds to me as a usable solution that should be in forever or until
this pg extension is removed.
> However this could break existing queries that are relying on the
> non-aliased behavior.
This would be programs written for 7.4, In 7.3 and older the search order
was (1), (2) and there was no (3) if I understood you correctly? I'd
rather stay compatible with older programs. I (as you) don't expect a lot
of GROUP BY constant_value_from_outer_query.
> Or we could bite the bullet and deprecate/remove the alias-reference
> feature. I think I was the one who put it in originally for GROUP BY,
> but in hindsight it was a terrible idea
Well, there are cases where it's nice to be able to do it. It's also
something that a lot of people expect to work. I don't mind if you just
implement what the standard says, but it's not my call to decide this.
--
/Dennis Björklund