MySQL Bugs: #12617: CREATE TABLE ... SELECT does not respect column order

Bug #12617	CREATE TABLE ... SELECT does not respect column order
Submitted:	17 Aug 2005 8:19	Modified:	18 Aug 2005 21:52
Reporter:	Carsten Pedersen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.11	OS:	Any (all)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
create table (col1 type1, col2 type2) SELECT x as col1 will reverse the column order of the resulting table. 

How to repeat:

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

mysql> create table t (a tinyint, b tinyint) select (1) as a;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b     | tinyint(4) | YES  |     | NULL    |       |
| a     | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

note how b comes before a, even though the order was specified the other way around. Also note that the column *types* are respected, even if the ordering is not. Clearly the table definition is not being ignored completely.

Suggested fix:
If the columns are specified in the CREATE TABLE clause, then this should be respected in column ordering as well as in the resulting number of columns (rewording "as a" to "as c" will produce a table with three columns, even though only two were specified). An error should occur if they do not match. If columns are not specified, then the server should of course do what it can to find matching column types.

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> 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 1 to server version: 5.0.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t (a tinyint, b tinyint) select (1) as a;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b     | tinyint(4) | YES  |     | NULL    |       |
| a     | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

This behaviour is documented in http://dev.mysql.com/doc/mysql/en/create-table.html

"Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it."