Bug #22586 CREATE TABLE SELECT adds additional attributes
Submitted: 22 Sep 2006 12:18 Modified: 6 Oct 2006 13:22
Reporter: Oli Sennhauser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.21, 5.0.24, 5.1 BK OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[22 Sep 2006 12:18] Oli Sennhauser 
Description:
CREATE TABLE ... SELECT ... add additional attribute and mixes up rows!

How to repeat:
create table test (id int auto_increment primary key, data varchar(32));
Query OK, 0 rows affected (0.06 sec)

create table mem (id int auto_increment primary key, tab_int int) select null, id from test;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

show create table mem\G
*************************** 1. row ***************************
       Table: mem
Create Table: CREATE TABLE `mem` (
  `tab_int` int(11) default NULL,
  `NULL` binary(0) default NULL,
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Suggested fix:
either bring a warning in create table as select or do not use attributes of select for creation but mixing up is bad.
[22 Sep 2006 12:41] Sveta Smirnova 
Thank you for the report.

Verified as described on Linux using last BK sources:

mysql> create table test (id int auto_increment primary key, data
    -> varchar(32));
Query OK, 0 rows affected (0.08 sec)

mysql> create table mem (id int auto_increment primary key, tab_int int)
    -> select 1, id from test;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mem\G
*************************** 1. row ***************************
       Table: mem
Create Table: CREATE TABLE `mem` (
  `tab_int` int(11) default NULL,
  `1` bigint(1) NOT NULL default '0',
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[22 Sep 2006 19:50] MySQL Verification Team 
just noting the adding of the field is documented.
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

"In a table resulting from CREATE TABLE ... SELECT, columns named only in the CREATE TABLE part come first. Columns named in both parts or only in the SELECT part come after that. The data type of SELECT columns can be overridden by also specifying the column in the CREATE TABLE part."