I did some more thinking on this. The logic is, that where and group by
affect the result set of a select, but order by (and col. alias) only affect
result presentation. This is why order by shoud look at the alias first,
while everything else should look at the real column name first.
(where, group, having ...)
> Good point --- consider this:
> SELECT a, b AS a FROM tt GROUP BY a;
> We do get it right: "ERROR: GROUP BY 'a' is ambiguous".
>
This is wrong, it should use the real column (all other DBMS do this).
> Whereas in
> SELECT a, b AS a FROM tt WHERE a = 1;
> the WHERE clause is taken as referring to the "real" column a.
>
good
> So, unless there's some violation of spec behavior here, there is a
> reason for GROUP BY to behave differently from WHERE. I think I was
> the one who complained that they were different --- I withdraw the
> complaint.
>
No, group by and where have to be the same. Your oringinal complaint was
justified.
> BTW, which behavior should ORDER BY exhibit? I find that
> SELECT a, b AS a FROM tt ORDER BY a;
> is accepted and 'a' is taken to be the real column a. Considering that
> ORDER BY is otherwise much like GROUP BY, I wonder whether it shouldn't
> complain that 'a' is ambiguous...
>
This is wrong, order by needs to use the alias.
I therefore see the following for TODO:use alias before column for order by -- very important
(currently wrong)use real column name before alias for group by -- important
(currently does elog)use alias in where iff it is unambiguous -- feature,
not important
On the other hand, anyone really using such ambiguous names
deserves unpredictable results anyway :-)
Andreas
