MySQL Forums
Forum List  »  Oracle

Bug or specification?
Posted by: Takeo Yamamoto
Date: November 16, 2008 07:17PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Bug or specification?
3942
November 16, 2008 07:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.