MySQL Bugs: #23873: SELECT statement works, but not done as CREATE VIEW AS

Bug #23873	SELECT statement works, but not done as CREATE VIEW AS
Submitted:	2 Nov 2006 4:46	Modified:	2 Nov 2006 6:51
Reporter:	Samuel Wright	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows (Win2003)
Assigned to:		CPU Architecture:	Any
Tags:	create, duplicate, VIEW

Description:
If I run the select statement, it runs fine. If I add on 'CREATE VIEW xxx AS ' in front of it, I keep getting error 1060, Duplicate Column Name.

What I have is 6 tables that together make up all my world info data. The foreign key column has the same field name as the column in the table linked to it. It seems that the duplicate columns are removed during the join in the select statement, but when trying to turn that into a view, it's no longer removing the duplicate columns.

How to repeat:
Tables are all InnoDB. I can post their structure but I dont think it's relevant? I changed the database name to 'db' in this example but that's not what it is just in case someone is going to say 'db' is a reserved word and could be the problem.

Here's what i'm trying:
CREATE VIEW db.GeoData AS
SELECT *
FROM db.geoFeature F
JOIN db.geoFeatureType FT ON (FT.FeatureType_str_code = F.FeatureType_str_code)
JOIN db.geoAdmin1 A1 ON (A1.Admin1_str_code = F.Admin1_str_code)
JOIN db.geoCountry C ON (C.Country_str_code = F.Country_str_code)
JOIN db.geoRegion R ON (C.Region_str_code = R.Region_str_code)
ORDER BY FT.FeatureType_str_type, C.Country_str_name, A1.Admin1_str_name, F.Feature_str_name;

ERROR 1060 (42S21):Duplicate column name 'FeatureType_str_code'

The bit that has me confused, is if I do the exact same thing without the top CREATE VIEW line, it works perfectly. I realise I could type out all the fields instead of a *, but there are probably 50-60 fields, and more importantly I want to know why this happens and how to get around it so I know a proper solution not a workaround. The CREATE VIEW doesn't work if I leave out the ORDER BY segment either if that matters.

Using MySQL Community Edition 5.0.27, but it didn't work in 5.0.24 either.

The relevant parts of the SQL script to re-create my database and situation will be attached. I get the same problem whether or not i've loaded the data into the tables.

Suggested fix:
Make the 'CREATE VIEW xxx AS' behave consistantly with when performing the same operation as a normal SELECT. I would expect that the select statement should either work as select or view, or not work as select or view, but not work in 1 situation and not the other.

SQL script to re-create my database and attempt the view

Attachment: MySQL_Bug_Report.txt (text/plain), 7.82 KiB.

It's easy to write a SELECT statement that produces a result set
with duplicate column names:

mysql> drop table if exists t;
Query OK, 0 rows affected (0.07 sec)

mysql> create table t (i int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t (i) values(0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t join t as t2;
+------+------+
| i    | i    |
+------+------+
|    0 |    0 | 
+------+------+
1 row in set (0.01 sec)

But that SELECT won't work as a view because view column names
must be unique:

mysql> create view v as select * from t join t as t2;
ERROR 1060 (42S21): Duplicate column name 'i'

It's necessary to provide unique column names:

mysql> create view v as select t.i, t2.i as i2 from t join t as t2;
Query OK, 0 rows affected (0.32 sec)

In your case, it might be inconvenient to create unique column
names, but the requirement exists nonetheless.

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