Thread: Re: [HACKERS] Open 6.5 items
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
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.
"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