Bug #13209 Error "Duplicate column name" when creating a view

• Submitted: 15 Sep 2005 2:29
• Modified: 15 Sep 2005 4:16
• Reporter: Robin Bailes
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.12
• OS: Windows (Windows XP)
• CPU Architecture: Any

[15 Sep 2005 2:29] Robin Bailes

Description:
I tried to create a view joining two tables where each table had a column of the same name.  Every time I tried to create the view I received the error: 
MySQL Error Number 1060
Duplicate column name 'T1_id'

After I renamed the column on one table, so all columns had unique names, I was able to crreate the view.

All work was done in MySQL Administrator 1.1.2

How to repeat:
Create two tables:
Note, each table has a column T1_id

CREATE TABLE `test`.`Table1` (
  `T1_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `T1_data` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`T1_id`)
)
ENGINE = InnoDB;

CREATE TABLE `test`.`table2` (
  `T2_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `T1_id` INTEGER UNSIGNED NOT NULL,
  `T2_data` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`T2_id`)
)
ENGINE = InnoDB;

The following attempts all resulted in the error 
MySQL Error Number 1060
Duplicate column name 'T1_id'

CREATE OR REPLACE VIEW `test`.`View1` AS
SELECT *
FROM table1, table2
WHERE table1.T1_id = table2.T1_id

CREATE OR REPLACE VIEW `test`.`View1` AS
SELECT *
FROM table1, table2
WHERE table1.T1_id = table2.T2_id

CREATE OR REPLACE VIEW `test`.`View1` AS
SELECT *
FROM table1, table2
WHERE table1.T1_data = table2.T2_data

I then changed the name of T1_id in one table:

ALTER TABLE `test`.`table1` CHANGE COLUMN `T1_id` `T1_id_x` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT;

I was able to create the view.

[15 Sep 2005 3:56] Jorge del Conde

mysql> SELECT * FROM table1, table2 WHERE table1.T1_id = table2.T2_id;
+-------+---------+-------+-------+---------+
| T1_id | T1_data | T2_id | T1_id | T2_data |
+-------+---------+-------+-------+---------+
|     1 | hola    |     1 |     2 | hola2   |
+-------+---------+-------+-------+---------+
1 row in set (0.00 sec)

mysql> CREATE OR REPLACE VIEW `test`.`View1` AS
    -> SELECT *
    -> FROM table1, table2
    -> WHERE table1.T1_id = table2.T2_id
    -> ;
ERROR 1060 (42S21): Duplicate column name 'T1_id'
mysql>

[15 Sep 2005 4:16] MySQL Verification Team

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

Additional info:

From the Manual:

http://dev.mysql.com/doc/mysql/en/create-view.html

"Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT  statement."