Bug #13100 When JOINing tables, more columns with the same name can be selected

• Submitted: 10 Sep 2005 8:50
• Modified: 10 Sep 2005 14:19
• Reporter: Stefano Crimi'
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.12
• OS: Windows (Windows NT)
• CPU Architecture: Any

[10 Sep 2005 8:50] Stefano Crimi'

Description:
When a SELECT * FROM ... INNER JOIN ... is used, the resulting table can have more columns with the same name. If that SELECT query is than used as a subquery, an error arises when the system try to select the duplicated column.

How to repeat:
mysql> create table table1 (a int(10),b int(10));
Query OK, 0 rows affected (0.61 sec)

mysql> create table table2 (a int(10),b int(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 values (1,10),(2,20),(3,30);
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicati: 0  Avvertimenti: 0

mysql> insert into table2 values (1,10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1 inner join table2 on table1.a=table2.a;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |   10 |    1 |   10 |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM (select * from table1 inner join table2 on table1.a=table2.a) A;
ERROR 1060 (42S21): Nome colonna duplicato 'a'

Suggested fix:
When two columns have the same name, rename the second one using a conventional rule (column.1, column.2 or use the table name as a prefix).

[10 Sep 2005 14:19] Hartmut Holzgraefe

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php