MySQL Bugs: #54493: NULL is treated as a column name in 'CREATE TABLE...AS SELECT NULL, NULL'

Bug #54493	NULL is treated as a column name in 'CREATE TABLE...AS SELECT NULL, NULL'
Submitted:	14 Jun 2010 18:17	Modified:	18 Jun 2010 3:43
Reporter:	Randall Raboy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.92-bzr, 5.1.47	OS:	Linux (2.6.33.5-112.fc13.i686)
Assigned to:		CPU Architecture:	Any
Tags:	CREATE TABLE, DDL, SELECT

Description:
I got an error(ERROR 1060 (42S21): Duplicate column name 'NULL') after I executed this statement:

CREATE TABLE temp(a INT, b INT) AS SELECT NULL, NULL;

but this statement does not cause an error:

CREATE TABLE temp(a INT) AS SELECT NULL;

and also when I executed this statement:

SELECT NULL, NULL;

gives me the correct result:

+------+------+
| NULL | NULL |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

How to repeat:
execute this sql in any mysql client(i only tried in mysql command and phpMyAdmin but i think it should be the same in all other clients):

CREATE TABLE temp(a INT, b INT) AS SELECT NULL, NULL;

Verified just as described:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE temp(a INT, b INT) AS SELECT NULL, NULL;
ERROR 1060 (42S21): Duplicate column name 'NULL'

Note that this is NOT specific to NULL:

mysql> CREATE TABLE temp(a INT, b INT) AS SELECT 1, 1;
ERROR 1060 (42S21): Duplicate column name '1'
mysql> CREATE TABLE temp(a INT, b INT) AS SELECT 1, 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Also note that there is a workaround:

mysql> CREATE TABLE temp2(a INT, b INT) AS SELECT NULL as a, NULL as b;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

But still, I see no good reason for this error message when explicit column names are given for the table.

The name of the column, unless specified explicitly, is taken from the autogenerated name, and for constants the autogenerated name matches the string representation of the constant. 
mysql> select 1,1;
Field   1:  `1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

Field   2:  `1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+---+---+
| 1 | 1 |
+---+---+
| 1 | 1 | 
+---+---+
1 row in set (0.00 sec)

There is no consistency if the 'SELECT 1, 1' will yield no error while on the 'CREATE TABLE..AS  SELECT 1, 1' will cause an error.