Bug #13953 | CREATE TABLE with field list and SELECT makes more columns than expected | ||
---|---|---|---|
Submitted: | 12 Oct 2005 8:16 | Modified: | 12 Oct 2005 9:44 |
Reporter: | Alexander Barkov | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.x | OS: | Linux (Linux) |
Assigned to: | CPU Architecture: | Any |
[12 Oct 2005 8:16]
Alexander Barkov
[12 Oct 2005 9:44]
Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: Thank you for a problem report. It looks strange at the first sight, but it is the inteded and documented behaviour. See http://dev.mysql.com/doc/mysql/en/create-table.html for details: " In MySQL 5.0, 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)) -> TYPE=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. Take the following example: mysql> SELECT * FROM foo; +---+ | n | +---+ | 1 | +---+ mysql> CREATE TABLE bar (m INT) SELECT n FROM foo; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec)"