MySQL Bugs: #69648: Subselect of literals in FROM throws "Duplicate column name" error

Bug #69648	Subselect of literals in FROM throws "Duplicate column name" error
Submitted:	2 Jul 2013 16:04	Modified:	15 Jul 2013 6:55
Reporter:	David Wheeler	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	duplicate column name, from, subselect

Description:
This query works fine:

mysql> select 'foo', 'foo';
+-----+-----+
| foo | foo |
+-----+-----+
| foo | foo |
+-----+-----+

However, this one does not:

mysql> select * from (select 'foo', 'foo');
ERROR 1248 (42000): Every derived table must have its own alias

I think that either the duplicate column names should be okay in the subselect, or else they should be given unique names by the system.

How to repeat:
Just run this query:

select * from (select 'foo', 'foo');

Suggested fix:
Make unique derived column names, or else allow dupes as can be done outside of a subselect.

Wrong query in the example (cut-and-paste error), sorry. Here is the correct one:

mysql> select * from (select 'foo', 'foo') AS f;
ERROR 1060 (42S21): Duplicate column name 'foo'

Reproducible also with older versions.  At least 5.1.36.

But this works: 
SELECT * FROM (SELECT 'foo','fee') AS f;

/*returns

foo     fee     
------  --------
foo     fee     

*/

5.0.96 is also affected.

Hello David,

Thank you for the report.
I noticed that it works only with PostgreSQL but don't work with MySQL 5.5/5.6 GA( also, doesn't seems to work with other DBMS).
IMO - MySQL and others stick to ANSI SQL standard and PostgreSQL does otherwise.

// PostgreSQL

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)
postgres=# 
postgres=# select * from (select 'foo', 'foo') AS f;
 ?column? | ?column? 
----------+----------
 foo      | foo

// MySQL 5.6/5.5/5.1

mysql> select f.* from (select 'foo', 'foo') AS f;
ERROR 1060 (42S21): Duplicate column name 'foo'

// DB2

[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token ")" was found following "(select 'foo', 'foo'". Expected tokens may include: "<table_expr>". SQLSTATE=42601 (SQL-42601)

// Oracle

[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected (SQL-HY000)

// MS SQL

[Microsoft][ODBC SQL Server Driver][SQL Server]No column was specified for column 1 of 'f'. (SQL-42000) [err was 1 now 1] [state was 42000 now 42000] [Microsoft][ODBC SQL Server Driver][SQL Server]No column was specified for column 2 of 'f'. (SQL-42000) [err was 1 now 1] [state was 42000 now 42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)

Workaround: One workaround I see to avoid "Duplicate column name 'column'".

select * from (select 'foo' as f1, 'foo' as f2) as f;
+-----+-----+
| f1  | f2  |
+-----+-----+
| foo | foo |
+-----+-----+
1 row in set (0.00 sec)

Thanks,
Umesh