MySQL Bugs: #13495: CREATE TABLE ... SELECT reverses the order of columns

Bug #13495	CREATE TABLE ... SELECT reverses the order of columns
Submitted:	26 Sep 2005 18:38	Modified:	26 Sep 2005 19:01
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.12-beta-nt-log	OS:	Windows (Windows 2000)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
CREATE TABLE (x int,y int) select 1 as x

creates a table with columns (y,x) instead of x,y

Console Log:
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.0.12-beta-nt-log |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp,temp1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE temp( x int   , y int   ) select 0 as x;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE temp1( x int   , y int   );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  temp1(x) select 0 as x;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe temp;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y     | int(11) | YES  |     | NULL    |       |
| x     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> describe temp1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       |
| y     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from temp;
+------+------+
| y    | x    |
+------+------+
| NULL |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from temp1;
+------+------+
| x    | y    |
+------+------+
|    0 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql>

How to repeat:
select version();
drop table if exists temp,temp1;
CREATE TABLE temp( x int   , y int   ) select 0 as x;
CREATE TABLE temp1( x int   , y int   );
insert into  temp1(x) select 0 as x;
describe temp;
describe temp1;
select * from temp;
select * from temp1;

Suggested fix:
create columns in the order specified in the CREATE

Please read the Manual at:

http://dev.mysql.com/doc/mysql/en/create-table.html

This creates a MyISAM table with three columns, a, b, and c. Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it.

Thanks,
The SELECT columns may be appended to right but there are no generated columns in the example I gave; All of the columns are explicitly created in the create table().

I think I understand how you got here (because explicit column specification is newer than the simple create table x select ...) but you have end with an odd definition:
create table x (col_specs) select cols ....
The TYPE of the columns in  taken from col_specs but the order is taken from 'cols', except if the col_spec isn't in 'cols'.

I guess the documentation could be improved in this area.

I've added this to the manual:

In a table resulting from CREATE TABLE ... SELECT, columns named only
in the CREATE TABLE part come first. Columns named in both parts or 
only in the SELECT part come after that. The data type of SELECT
columns can be overridden by also specifying the column in the CREATE
TABLE part.

Great. Thanks.