Bug #15370 Can not utilize USING clause after an ON clause in SELECT queries

• Submitted: 30 Nov 2005 23:16
• Modified: 30 Nov 2005 23:38
• Reporter: Eddie Mayfield
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.16-nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[30 Nov 2005 23:16] Eddie Mayfield

Description:
As of mysql version 5.0.16-nt a USING clause can not be used after an ON clause. The error of ERROR 1052 (23000): Column 'ColumnName' in field list is ambiguous is thrown. Where column name is the first column used in the USING clause.
This syntax worked correctly as of mysql 5.0.1-alpha

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 clauses
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 clauses
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 clauses and 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 cluases and 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

[30 Nov 2005 23:38] MySQL Verification Team

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

See: http://bugs.mysql.com/bug.php?id=15339