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:
None 
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
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.
[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)