Bug #47262 CREATE TABLE SELECT ignore field comments
Submitted: 10 Sep 2009 22:29 Modified: 11 Sep 2009 4:01
Reporter: Harald Groven Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1, 5.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: comments createtable
Triage: Triaged: D2 (Serious)

[10 Sep 2009 22:29] Harald Groven
Description:
Field COMMENTs added in a CREATE TABLE statement in a CREATE TABLE SELECT are overwritten if a field in the SELECT statement has the same name. Field datatyper however, are correctely parsed from the CREATE tot the SELECT. 

How to repeat:
CREATE TABLE test (
  f1 varchar(100) NOT NULL COMMENT 'this comment will be erased',
  f2 varchar(100) NOT NULL COMMENT 'this comment will be visible') 
SELECT 
	'hi techsupport' AS f1
;
[10 Sep 2009 23:02] Peter Laursen
additionally the columns-order is reversed:

CREATE TABLE test (
  f1 VARCHAR(100) NOT NULL COMMENT 'this comment will be erased',
  f2 VARCHAR(100) NOT NULL COMMENT 'this comment will be visible') 
SELECT 'hi techsupport' AS f1;
	
SHOW CREATE TABLE test; 
/*
CREATE TABLE `test` (
  `f2` VARCHAR(100) NOT NULL COMMENT 'this comment will be visible',
  `f1` VARCHAR(100) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
*/
[11 Sep 2009 4:01] Valeriy Kravchuk
Thank you for the problem report. Verified just as described. Column comment is ignored and fields are created in unexpected order.