MySQL Bugs: #15339: Can not utilization USING clause after an ON clause in SELECT queries

Bug #15339	Can not utilization USING clause after an ON clause in SELECT queries
Submitted:	30 Nov 2005 2:29	Modified:	30 Nov 2005 12:16
Reporter:	Eddie Mayfield	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
As of the Windows version of mysql 5.0.16 queries that have a USING clause after an ON clause throw an error of 
ERROR 1052 (23000): Column 'ColumnName' in field list is ambiguous.
The column that is reported to be ambiguous is the first column specified in the USING clause.
This syntax was fine as of Windows version 5.0.1-alpha-nt

How to repeat:
CREATE TABLE a (id INT NOT NULL PRIMARY KEY, value VARCHAR(255));
CREATE TABLE b (id INT NOT NULL PRIMARY KEY, value VARCHAR(255));
CREATE TABLE c (id INT NOT NULL PRIMARY KEY, value VARCHAR(255));
CREATE TABLE d (id INT NOT NULL PRIMARY KEY, value VARCHAR(255));
INSERT INTO a VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO b VALUES (1, 'd'), (2, 'e'), (3, 'f');
INSERT INTO c VALUES (1, 'g'), (2, 'h'), (3, 'i');
INSERT INTO d VALUES (1, 'j'), (2, 'k'), (3, 'l');

#All ON clause
SELECT a.id, a.value, b.value, c.value, d.value
FROM a INNER JOIN b ON a.id=b.id INNER JOIN c ON b.id=c.id INNER JOIN d ON c.id=d.id;
#no error

#All USING clause
SELECT a.id, a.value, b.value, c.value, d.value
FROM a INNER JOIN b USING(id) INNER JOIN c USING(id) INNER JOIN d USING(id);
#no error

#First two USING clause last JOIN ON clause
SELECT a.id, a.value, b.value, c.value, d.value
FROM a INNER JOIN b USING(id) INNER JOIN c USING(id) INNER JOIN d ON c.id=d.id;
#no error

#First two ON clause last JOIN USING clause
SELECT a.id, a.value, b.value, c.value, d.value
FROM a INNER JOIN b ON a.id=b.id INNER JOIN c ON b.id=c.id INNER JOIN d USING(id);
#ERROR 1052 (23000): Column 'id' in field list is ambiguous.

Thanks for your bug report.  I was able to repeat this under XP and FC4 using a recent 5.0bk clone

Everything works just as you described, but it is not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html) carefully.

In your last statement:

SELECT a.id, a.value, b.value, c.value, d.value
FROM a INNER JOIN b ON a.id=b.id INNER JOIN c ON b.id=c.id INNER JOIN d
USING(id);

a INNER JOIN b ON a.id=b.id INNER JOIN c ON b.id=c.id will produce 3 id columns, and, surely, it is not possible to decide which one of them to use for the natural join with table d.