MySQL Bugs: #60299: Create Table as SELECT with expression doesn't work right

Bug #60299	Create Table as SELECT with expression doesn't work right
Submitted:	2 Mar 2011 0:21	Modified:	2 Apr 2011 4:11
Reporter:	Greg Kemnitz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.43sp1-enterprise-gpl-advanced-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In most databases, "create table as select" will populate the new table with rows fetched "left to right" from the select.  If you don't have an expression in the SELECT-list, MySQL will also work this way.  But if you have any sort of expression in the select-list, MySQL gets confused and adds a bogus extra column to boot.

Try the following in MySQL:

(root@localhost) [junk] >select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|   55 |
|   17 |
|   22 |
+------+
5 rows in set (0.00 sec)

now do a create table as select:

(root@localhost) [junk] >create table t2 (a int) as select -a from t;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(root@localhost) [junk] >select * from t2;
+------+------+
| a    | -a   |
+------+------+
| NULL |   -1 |
| NULL |   -2 |
| NULL |  -55 |
| NULL |  -17 |
| NULL |  -22 |
+------+------+
5 rows in set (0.00 sec)

Now use an "AS" column name specifier:

(root@localhost) [junk] >create table t2 (a int) as select -a as a from t;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(root@localhost) [junk] >select * from t2;
+------+
| a    |
+------+
|   -1 |
|   -2 |
|  -55 |
|  -17 |
|  -22 |
+------+
5 rows in set (0.00 sec)

How to repeat:
See above

Suggested fix:
The SELECT-list should populate the table's columns from left to right, whether a column is directly fetched or is the result of an expression.

This is clearly documented in the manual (http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html):

"You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL creates new columns for all elements in the SELECT. For example:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=MyISAM SELECT b,c FROM test2;

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. "

So, formally this is not a bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".