Bug #44814 | undocumented: create select fills columns right to left | ||
---|---|---|---|
Submitted: | 12 May 2009 7:32 | Modified: | 13 May 2009 19:38 |
Reporter: | Stewart Smith | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 4.1, 5.0, 5,1, 6.0 bzr | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[12 May 2009 7:32]
Stewart Smith
[12 May 2009 8:19]
Sveta Smirnova
Thank you for the report. Verified as described.
[13 May 2009 19:38]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Will add this to http://dev.mysql.com/doc/refman/5.1/en/create-table.html: For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows: * The table definition given in the CREATE TABLE part is ignored. No error occurs, even if the definition does not match that of the existing table. * If there is a mismatch between the number of columns in the table and the number of columns produced by the SELECT part, the selected values are assigned to the rightmost columns. For example, if the table contains n columns and the SELECT produces m columns, where m < n, the selected values are assigned to the m rightmost columns in the table. The initial n − m columns are assigned their default values, either those specified explicitly in the column definition or the implicit column data type default if the definition contains no default. * If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error. The following example illustrates IF NOT EXISTS handling: mysql> CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +------+------+------+------+ | i1 | i2 | i3 | i4 | +------+------+------+------+ | 0 | NULL | 1 | 2 | +------+------+------+------+ 1 row in set (0.00 sec)