MySQL Bugs: #15048: Duplicate Column Names and Creating Temporary Tables from Views

Bug #15048	Duplicate Column Names and Creating Temporary Tables from Views
Submitted:	18 Nov 2005 12:14	Modified:	20 Jun 2006 9:06
Reporter:	Esteban Jesus	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	mysql-5.0.17-nightly-20051112	OS:	MacOS (Mac OS X 10.4.3)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
When creating a temporary table using a selection from a view the operation fails if there is more than one instance of the same column even if they have different names.

How to repeat:
CREATE TABLE foo (
  id int unsigned NOT NULL,
  bar varchar(50) NOT NULL,
  
  PRIMARY KEY (id)
) TYPE=InnoDB CHARACTER SET UTF8;

INSERT INTO foo VALUES (1,'abc');
INSERT INTO foo VALUES (2,'abc');
INSERT INTO foo VALUES (3,'abc');

Creating a temp table as follows works correctly:

CREATE TEMPORARY TABLE temp SELECT id AS something, id, bar FROM foo;

Running 'SELECT * FROM temp;' correctly yields:

+-----------+----+-----+
| something | id | bar |
+-----------+----+-----+
|         1 |  1 | abc |
|         2 |  2 | abc |
|         3 |  3 | abc |
+-----------+----+-----+

Now, if I create a view from the original table like so:

CREATE OR REPLACE aView AS SELECT * FROM foo;

The statement:

CREATE TEMPORARY TABLE temp SELECT id AS something, id, bar FROM aView;

Fails with the error:

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

A workaround can be achieved by tweaking the statement slightly:

CREATE TEMPORARY TABLE temp SELECT (id + 0) AS something, id, bar FROM aView;

Works correctly and, when queried, returns:

+-----------+----+-----+
| something | id | bar |
+-----------+----+-----+
|         1 |  1 | abc |
|         2 |  2 | abc |
|         3 |  3 | abc |
+-----------+----+-----+

It may seem odd to have the same column duplicated under a different name - but there is a legitimate reason for this and, as demonstrated above it works fine so long as the temp table is created from a concrete table rather than a view.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

I tried the test case on mysql-5.0.23. All the statements in it worked.

my test case

Attachment: bug.test (application/octet-stream, text), 412 bytes.