| 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 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)
Description: When doing CREATE IF NOT EXIST SELECT, the SELECT is inserted anyway. What the documentation doesn't seem to show is that this is right-aligned, not left-aligned as one may expect. mysql [localhost] {msandbox} (test) > create table t1 (a int not null, b int, c int, d int, primary key (a)); Query OK, 0 rows affected (0.04 sec) mysql [localhost] {msandbox} (test) > insert into t1 values (1,1,1,1); Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (test) > create table if not exists t1 select 2; Query OK, 1 row affected, 2 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > show warnings -> ; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Note | 1050 | Table 't1' already exists | | Warning | 1364 | Field 'a' doesn't have a default value | +---------+------+----------------------------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from t1; +---+------+------+------+ | a | b | c | d | +---+------+------+------+ | 1 | 1 | 1 | 1 | | 0 | NULL | NULL | 2 | +---+------+------+------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > drop table t1; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > create table t1 (a int not null auto_increment, b int, c int, d int, primary key (a)); Query OK, 0 rows affected (0.03 sec) mysql [localhost] {msandbox} (test) > create table if not exists t1 select 1,2; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > select * from t1; +---+------+------+------+ | a | b | c | d | +---+------+------+------+ | 1 | NULL | 1 | 2 | +---+------+------+------+ 1 row in set (0.00 sec) How to repeat: I added a test case to Drizzle for this (we error out as column count doesn't match, so the --error is invalid unless MySQL in strict mode) stewart@willster:~/drizzle/drizzle$ cat tests/t/create_select_column_order.test create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); --error 1364 create table if not exists t1 select 2; select * from t1; drop table t1; create table t1 select 1,2,3; --error 1364 create table if not exists t1 select 1,2; --error 1364 create table if not exists t1 select 1; select * from t1; drop table t1; create table t1 (a int not null primary key auto_increment, b int, c int, d int); create table if not exists t1 select 1,2; select * from t1; drop table t1; result file: create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); create table if not exists t1 select 2; ERROR HY000: Field 'a' doesn't have a default value select * from t1; a b 1 1 drop table t1; create table t1 select 1,2,3; create table if not exists t1 select 1,2; ERROR HY000: Field '1' doesn't have a default value create table if not exists t1 select 1; ERROR HY000: Field '1' doesn't have a default value select * from t1; 1 2 3 1 2 3 drop table t1; create table t1 (a int not null primary key auto_increment, b int, c int, d int); create table if not exists t1 select 1,2; Warnings: Note 1050 Table 't1' already exists select * from t1; a b c d 1 NULL 1 2 drop table t1; Suggested fix: add to docs.