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