MySQL Bugs: #49173: create view fails: MySQL error 1060 "duplicate column name"

Bug #49173	create view fails: MySQL error 1060 "duplicate column name"
Submitted:	28 Nov 2009 3:26	Modified:	28 Nov 2009 13:38
Reporter:	David Sadler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.1.30-community	OS:	Windows (xp media center sp3)
Assigned to:		CPU Architecture:	Any
Tags:	create view, duplicate column name, error 1060

Description:
CONFIG:
XP media center edition sp3 (fully updated)

Server version: 5.1.30-community MySQL Community Server (GPL)
MySQL Query Browser version 1.2.14

[I am using a .net connector.  How do I find out which one is installed?]

mysql> show tables;
+------------------+
| Tables_in_foo_is |
+------------------+
| company          |
| project          |
+------------------+

==

This select works without error.
SELECT p.`company_id`, c.`name`, p.`name`, p.`abbr`, p.`od`
FROM project p, company c
where c.`id` = p.`company_id`;

Create view button is clicked which presents the name view dialogue.
View is named. OK.

The MySQL error 1060 "duplicate column name" is thrown.
Duplicate column name 'name'

The offending columns are c.`name` and p.`name`.

If c.`website` is substituted in the select for c.`name`, the view can be created.

This duplicate column name error should not be thrown if the duplicate column names are in different tables.  Correct?

Thanks,
David

==

DROP TABLE IF EXISTS `foo_is`.`company`;
CREATE TABLE  `foo_is`.`company` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(15) DEFAULT NULL,
  `website` varchar(75) DEFAULT NULL,
  `comment` varchar(150) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `insert_dt` datetime DEFAULT NULL,
  `mod_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `insert_by` varchar(15) DEFAULT NULL,
  `mod_by` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

==

DROP TABLE IF EXISTS `foo_is`.`project`;
CREATE TABLE  `foo_is`.`project` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(15) DEFAULT NULL,
  `od` tinyint(4) DEFAULT NULL,
  `miles` smallint(6) DEFAULT NULL,
  `project_type` varchar(25) DEFAULT NULL,
  `kickoff_dt` date DEFAULT NULL,
  `weldout_dt` date DEFAULT NULL,
  `complete_dt` date DEFAULT NULL,
  `comment` varchar(150) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `insert_dt` datetime DEFAULT NULL,
  `mod_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `insert_by` varchar(15) DEFAULT NULL,
  `mod_by` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_project_company` (`company_id`),
  CONSTRAINT `fk_project_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

How to repeat:
Create the tables as shown.
Execute the select as shown.
Click the Create View button.
Name the view.
Click OK.

Suggested fix:
None.

When you create view, you create a virtual table. Table (and view, virtual table) can not have columns with the same name. Just add unique column alias to SELECT:

SELECT ... c.name as name1, p.name as name2, ...

Please, check if this solves the problem.

Thanks, Valerily.

Valeriy's advice to alias the duplicate names in the select clause solved the problem. 

I will search the doc to see if a switch can be thrown to automatically qualifiy the column names when the view is created.  The switch would automatically add the table name (or letter) to the column name.

Example:
If the auto qualify column name preference is thrown, this select ...
SELECT c.`name`, p.`name`
FROM project p, company c
where c.`id` = p.`company_id`;

... is treated by the view constructor as ...
SELECT c.`name` as c_name, p.`name` as p_name
FROM project p, company c
where c.`id` = p.`company_id`;