MySQL Bugs: #15443: Views: can't create view, error 'Duplicate column'

Bug #15443	Views: can't create view, error 'Duplicate column'
Submitted:	2 Dec 2005 18:09	Modified:	2 Dec 2005 20:06
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
I can't create a view that self-joins a table with one column.

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

mysql>   drop view if exists v1, v2;                                                     create table t1 (a int);
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>   insert into t1 (a) values (1), (2),  (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>   create view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql>   select * from t1 left join t1 as v1 on (t1.a=v1.a);
+------+------+
| a    | a    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.01 sec)

mysql>   create view v2 as  select * from t1 left join t1 as v1 on (t1.a=v1.a);
ERROR 1060 (42S21): Duplicate column name 'a'

As you can see, the query is correct, as it passes when is run iin standalone mode.

How to repeat:
drop table if exists t1;
drop view if exists v1, v2;                                                     
create table t1 (a int);
insert into t1 (a) values (1), (2),  (3);
create view v1 as select * from t1;
select * from t1 left join t1 as v1 on (t1.a=v1.a);
create view v2 as  select * from t1 left join t1 as v1 on (t1.a=v1.a);

Sorry, view v1 is, of course, unnecessary.

Thank you for a problem report. But it looks like not a bug for me. Tha manual (http://dev.mysql.com/doc/refman/5.0/en/create-view.html) clearly states:

"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."

So, please, check the proposed workaround and inform about the results.

Yes, Igor has pointed that out to me already. The error message however doesn't give you any hint that the problem is actually in the SELECT  list and not in the ON clause.
That can be considered a bug, probably.