Bug #5645 ambiguous columns using AS

• Submitted: 18 Sep 2004 22:28
• Modified: 24 Sep 2004 13:39
• Reporter: Andreas Götz
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.4
• OS: Windows (WinXP SP2)
• CPU Architecture: Any

[18 Sep 2004 22:28] Andreas Götz

Description:
using AS for aliases can lead to ambious columns- no warning is produced, behaviour is totally different from e.g. oracle. column precendence is unclear

How to repeat:
look at this:

select videodata.*, mediatypes.name AS mediatype
from videodata
left join mediatypes on videodata.mediatype = mediatypes.id

this statement will contain two mediatype columns in the result set

Suggested fix:
check other db systems and implement similiar logic

[24 Sep 2004 13:39] Indrek Siitan

You can get duplicate columns even without as, for example by:

SELECT vd1.*, vd2.* FROM videodata vd1 LEFT JOIN videodata vd2 USING (id);

You will get an error if you're trying to use the ambigous column names in WHERE / ORDER BY / 
GROUP BY clauses, but you're allowed to have them in the resultset, as in most of the APIs you 
can reference fields by the sequence ($row[0], $row[1], etc..) and still get all the columns.