Re: [HACKERS] Open 6.5 items - Mailing list pgsql-hackers

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.