1# 2# MDEV-16325 CREATE..SELECT..UNION creates a wrong field type for old varchar 3# 4CREATE PROCEDURE p1(col VARCHAR(32)) 5BEGIN 6EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2_simple AS SELECT col FROM t1old','col',col); 7SHOW CREATE TABLE t2_simple; 8DROP TABLE t2_simple; 9EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2_union_vv AS SELECT col FROM t1old UNION SELECT col FROM t1old','col',col); 10SHOW CREATE TABLE t2_union_vv; 11DROP TABLE t2_union_vv; 12EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2_union_vn AS SELECT col FROM t1old UNION SELECT NULL','col',col); 13SHOW CREATE TABLE t2_union_vn; 14DROP TABLE t2_union_vn; 15EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2_union_nv AS SELECT NULL AS col UNION SELECT col FROM t1old','col',col); 16SHOW CREATE TABLE t2_union_nv; 17DROP TABLE t2_union_nv; 18EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2 AS SELECT 19 COALESCE(col), 20 COALESCE(col,col), 21 COALESCE(col,NULL), 22 COALESCE(NULL,col) 23 FROM t1old', 'col', col); 24SHOW CREATE TABLE t2; 25DROP TABLE t2; 26EXECUTE IMMEDIATE REPLACE('CREATE TABLE t2 AS SELECT 27 LEAST(col,col), 28 LEAST(col,NULL), 29 LEAST(NULL,col) 30 FROM t1old','col',col); 31SHOW CREATE TABLE t2; 32DROP TABLE t2; 33END; 34$$ 35TRUNCATE TABLE t1old; 36SHOW CREATE TABLE t1old; 37Table Create Table 38t1old CREATE TABLE `t1old` ( 39 `v` varchar(30)/*old*/ DEFAULT NULL, 40 `c` char(3) DEFAULT NULL, 41 `e` enum('abc','def','ghi') DEFAULT NULL, 42 `t` text DEFAULT NULL 43) ENGINE=MyISAM DEFAULT CHARSET=latin1 44CALL p1('v'); 45Table Create Table 46t2_simple CREATE TABLE `t2_simple` ( 47 `v` varchar(30) DEFAULT NULL 48) ENGINE=MyISAM DEFAULT CHARSET=latin1 49Table Create Table 50t2_union_vv CREATE TABLE `t2_union_vv` ( 51 `v` varchar(30) DEFAULT NULL 52) ENGINE=MyISAM DEFAULT CHARSET=latin1 53Table Create Table 54t2_union_vn CREATE TABLE `t2_union_vn` ( 55 `v` varchar(30) DEFAULT NULL 56) ENGINE=MyISAM DEFAULT CHARSET=latin1 57Table Create Table 58t2_union_nv CREATE TABLE `t2_union_nv` ( 59 `v` varchar(30) DEFAULT NULL 60) ENGINE=MyISAM DEFAULT CHARSET=latin1 61Table Create Table 62t2 CREATE TABLE `t2` ( 63 `COALESCE(v)` varchar(30) DEFAULT NULL, 64 `COALESCE(v,v)` varchar(30) DEFAULT NULL, 65 `COALESCE(v,NULL)` varchar(30) DEFAULT NULL, 66 `COALESCE(NULL,v)` varchar(30) DEFAULT NULL 67) ENGINE=MyISAM DEFAULT CHARSET=latin1 68Table Create Table 69t2 CREATE TABLE `t2` ( 70 `LEAST(v,v)` varchar(30) DEFAULT NULL, 71 `LEAST(v,NULL)` varchar(30) DEFAULT NULL, 72 `LEAST(NULL,v)` varchar(30) DEFAULT NULL 73) ENGINE=MyISAM DEFAULT CHARSET=latin1 74DROP TABLE t1old; 75TRUNCATE TABLE t1old; 76SHOW CREATE TABLE t1old; 77Table Create Table 78t1old CREATE TABLE `t1old` ( 79 `a` varbinary(255)/*old*/ DEFAULT NULL, 80 `b` varchar(255)/*old*/ DEFAULT NULL 81) ENGINE=MyISAM DEFAULT CHARSET=latin1 82CALL p1('a'); 83Table Create Table 84t2_simple CREATE TABLE `t2_simple` ( 85 `a` varbinary(255) DEFAULT NULL 86) ENGINE=MyISAM DEFAULT CHARSET=latin1 87Table Create Table 88t2_union_vv CREATE TABLE `t2_union_vv` ( 89 `a` varbinary(255) DEFAULT NULL 90) ENGINE=MyISAM DEFAULT CHARSET=latin1 91Table Create Table 92t2_union_vn CREATE TABLE `t2_union_vn` ( 93 `a` varbinary(255) DEFAULT NULL 94) ENGINE=MyISAM DEFAULT CHARSET=latin1 95Table Create Table 96t2_union_nv CREATE TABLE `t2_union_nv` ( 97 `a` varbinary(255) DEFAULT NULL 98) ENGINE=MyISAM DEFAULT CHARSET=latin1 99Table Create Table 100t2 CREATE TABLE `t2` ( 101 `COALESCE(a)` varbinary(255) DEFAULT NULL, 102 `COALESCE(a,a)` varbinary(255) DEFAULT NULL, 103 `COALESCE(a,NULL)` varbinary(255) DEFAULT NULL, 104 `COALESCE(NULL,a)` varbinary(255) DEFAULT NULL 105) ENGINE=MyISAM DEFAULT CHARSET=latin1 106Table Create Table 107t2 CREATE TABLE `t2` ( 108 `LEAST(a,a)` varbinary(255) DEFAULT NULL, 109 `LEAST(a,NULL)` varbinary(255) DEFAULT NULL, 110 `LEAST(NULL,a)` varbinary(255) DEFAULT NULL 111) ENGINE=MyISAM DEFAULT CHARSET=latin1 112DROP TABLE t1old; 113DROP PROCEDURE p1; 114