create table if not exists select from does columns from right to left

[Monty Taylor]

create table t1 (a int not null, b int, primary key (a));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB
insert into t1 values (1,1);
create table if not exists t1 select 2;

 query 'create table if not exists t1 select 2' failed: 1364: Field 'a' doesn't have a default value

Another example:

create table t1 select 1,2,3;
create table if not exists t1 select 1,2;

select * from t1;
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
| 0 | 1 | 2 |
+---+---+---+
2 rows in set (0.00 sec)

Aroo?

[Jay Pipes (jaypipes)]

Monty, I can verify the first example, but not the second:

drizzle> use test
Database changed
drizzle> create table t1 (a int not null, b int, primary key (a));
Query OK, 0 rows affected (0.01 sec)

drizzle> show create table t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

drizzle> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

drizzle> create table if not exists t1 select 2;
ERROR 1364 (HY000): Field 'a' doesn't have a default value
drizzle> drop table t1insert into t1 values (1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'into t1 values (1,1)' at line 1
drizzle> drop table t1;
Query OK, 0 rows affected (0.00 sec)

drizzle> insert into t1 values (1,1);
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
drizzle> create table t1 select 1,2,3;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

drizzle> create table if not exists t1 select 1,2;
Query OK, 0 rows affected (0.00 sec)

drizzle> select * from t1;
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

Can you verify that your second example no longer occurs, please? Thanks, Jay.

[Stewart Smith (stewart)]

Not a bug. Is (undocumented - see http://bugs.mysql.com/bug.php?id=44814) behaviour to do this. *Why* this is the case - who knows. But do we want to break DML compatibility with this?

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);ERROR 1136 (21S01): Column count doesn't match value count at row 1
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)

I've also added a test case that tests the existence of this behaviour explicitly.