Bug or specification?
Hello,
I am getting an error message ("Duplicate column name 'sid'") when I try to execute the following query on tables student(sid, sname, dept)[primary key:sid] and sc(sid, cid, grades)[primary key:{sid, cid}], which runs OK in Oracle, and which seems to be OK according to my (admittedly cursory) reading of MySQL reference manual:
SELECT dept, ROUND(AVG(grades),2) AS avgrades
FROM (SELECT * FROM student a JOIN sc ON a.sid=sc.sid) AS x
GROUP BY dept ORDER BY avgrades DESC;
It executes OK if I change the query as follows:
SELECT dept, ROUND(AVG(grades),2) AS avgrades
FROM (SELECT sc.sid, sc.cid, grades, dept
FROM student a JOIN sc ON a.sid=sc.sid) AS x
GROUP BY dept ORDER BY avgrades DESC;
I am using MySQL Ver 14,12 Dustrub 5.0.67, for Win32 (ia32).
Apparently MySQL interprets "SELECT * " as "SELECT sid, sid,..." without modification. Is this a bug, or is it according to some specification? If so, where is it documented? Please help me (and please don't tell me I didn't have to have a sub-query to get the answer.)