Bug #13953 CREATE TABLE with field list and SELECT makes more columns than expected
Submitted: 12 Oct 2005 8:16 Modified: 12 Oct 2005 9:44
Reporter: Alexander Barkov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.x OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[12 Oct 2005 8:16] Alexander Barkov
Description:
drop table if exists t1, t2, t3;
create table t1 (
  path varchar(64) NOT NULL default '',
  name varchar(64) NOT NULL default '',
  PRIMARY KEY  (path)
);
create table t2 (
 category varchar(64) not null default '',
 category1 varchar(64) not null default '',
 category2 varchar(64) not null default ''
);
create table t3 (path varchar(64) not null, c int not null)
as
select category, count(*) FROM t2 group BY category
union
select category1, count(*) FROM t2 group BY category1
union
select category2, count(*) FROM t2 group BY category2
union
select path, 0 FROM t1;
show create table t3;

CREATE TABLE `t3` (
  `path` varchar(64) NOT NULL,
  `c` int(11) NOT NULL,
  `category` varchar(64) NOT NULL default '',
  `count(*)` bigint(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How to repeat:
Run the above script.

Suggested fix:
Expected result is:

CREATE TABLE `t3` (
  `path` varchar(64) NOT NULL,
  `c` int(11) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[12 Oct 2005 9:44] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Thank you for a problem report. It looks strange at the first sight, but it is the inteded and documented behaviour. See http://dev.mysql.com/doc/mysql/en/create-table.html for details:

" In MySQL 5.0, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL creates new columns for all elements in the SELECT. For example:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        TYPE=MyISAM SELECT b,c FROM test2;

This creates a MyISAM table with three columns, a, b, and c. Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it. Take the following example:

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)"