Bug #29635 Simple JOIN of two tables containing same column name generates error 1052

• Submitted: 9 Jul 2007 3:19
• Modified: 9 Jul 2007 6:33
• Reporter: Steve Shell
• Status: Can't repeat
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.41
• OS: Windows (Windows XP SP2)
• CPU Architecture: Any

[9 Jul 2007 3:19] Steve Shell

Description:
The following select fails on 5.0.41 but works on 5.0.24a (I just upgraded).

CREATE database sample;
USE sample;
CREATE TABLE t (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c int );
CREATE TABLE q (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT );

SELECT c from t join q using (a,b);

Returns Error 1052, Column 'ROW' in field list is ambiguous.

With 5.0.24a the SELECT returns no rows and a column named 'C', as I would expect.

Using this syntax:
SELECT c from t LEFT OUTER JOIN q using (a,b);
returns 0 rows one column named 'C'.  A left outer join returns what I would expect.

Using this syntax:

SELECT c,t.row from t join q using (a,b);
returns 0 rows with one column named 'C' and one column named 'ROW'

How to repeat:
CREATE database sample;
USE sample;
CREATE TABLE t (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c int );
CREATE TABLE q (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT );

SELECT c from t join q using (a,b);
Also
SELECT c from t join q;

Returns Error 1052, Column 'ROW' in field list is ambiguous.

Suggested fix:
Return to 5.0.24a behavior.  Unreferenced duplicate column name in join should not cause query to fail.

[9 Jul 2007 6:33] Valeriy Kravchuk

Thank you for a problem report. I can not repeat the behaviour described with 5.0.44:

mysql> CREATE TABLE t (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c int )
;
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE q (ROW INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT );
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT c from t join q using (a,b);
Empty set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.44-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

So, looks like this bug is already fixed in current code. Please, build from current sources or wait for 5.0.45 to be released soon.