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

Thu, 20 May 1999 08:58:14 +0200
ZEUGSWETTER Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: [HACKERS] Open 6.5 items 
Thus spake ZEUGSWETTER Andreas IZ5
> > 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).

Regardless of what the others do, I prefer our behaviour better.  What if
the column is not in the select list and perhaps is added to the database
table later?  It seems wrong to me that the behaviour of this select
should change if a column, perhaps not relevant to the program doing
the select, is added.  I would prefer that it fail so I could investigate
it to see what I have to change.

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

Well, I don't care only because someone would be nuts to write this.  :-)

> > 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 agree but I wouldn't complain if it gave an error.

> 
> I therefore see the following for TODO:
> 	use alias before column for order by 		-- very important
> (currently wrong)

Yep.

> 	use real column name before alias for group by 	-- important
> (currently does elog)

I prefer the current behaviour.

> 	use alias in where iff it is unambiguous		-- feature,
> not important

Yes.

> On the other hand, anyone really using such ambiguous names
> deserves unpredictable results anyway :-)

Absolutely.  My feeling is that if the select is unambiguous and self
consistent, the intuitive thing should happen.  This means that as
long as they don't make alias names that conflict with column names
that are selected (meaning all column names if '*' is selected) then
the alias should always be taken over the unselected column name.
I am less concerned about the behaviour when the select is ambiguous
on the face of it.

Of course, we should follow the standard wherever it has something to
say on the subject but let's not be overly concerned about what others
do in this situation.  If it's a real problem then let's just elog any
ambiguity and document our reasons for doing so.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 20 May 1999 08:15:46 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Open 6.5 items
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> Thus spake ZEUGSWETTER Andreas IZ5
>> This is wrong, it should use the real column (all other DBMS do this).

> Regardless of what the others do, I prefer our behaviour better.

Er, I think what actually counts is what the SQL92 spec says ...
but I haven't got a copy to look at.

			regards, tom lane

Thu, 20 May 1999 09:18:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Open 6.5 items 