MDEV-8910 Wrong metadata or field type for MAX(COALESCE(string_field))

1 files changed, 172 insertions, 0 deletions

diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result
index 5eefcbb36ea..72e26c50274 100644
--- a/mysql-test/r/func_hybrid_type.result
+++ b/mysql-test/r/func_hybrid_type.result
@@ -3059,5 +3059,177 @@ DROP TABLE t2;
 DROP TABLE t1;
 SET timestamp=DEFAULT;
 #
+# MDEV-8910 Wrong metadata or field type for MAX(COALESCE(string_field))
+#
+CREATE TABLE t1 (c1 TINYBLOB, c2 MEDIUMBLOB, c3 BLOB, c4 LONGBLOB);
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3,
+MAX(COALESCE(c4)) AS c4
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varbinary(255) DEFAULT NULL,
+  `c2` mediumblob,
+  `c3` blob,
+  `c4` longblob
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3,
+MAX(COALESCE(c4)) AS c4
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	255	0	Y	128	31	63
+def					c2	250	16777215	0	Y	128	31	63
+def					c3	252	65535	0	Y	128	31	63
+def					c4	251	4294967295	0	Y	128	31	63
+c1	c2	c3	c4
+NULL	NULL	NULL	NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL,
+  `c2` varchar(255) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+c1	c2
+NULL	NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
+  `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+c1	c2
+NULL	NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL,
+  `c2` varchar(255) DEFAULT NULL,
+  `c3` text
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+def					c3	252	20000	0	Y	0	31	8
+c1	c2	c3
+NULL	NULL	NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
+  `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
+  `c3` text CHARACTER SET utf8
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1,
+MAX(COALESCE(c2)) AS c2,
+MAX(COALESCE(c3)) AS c3
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+def					c3	252	60000	0	Y	0	31	8
+c1	c2	c3
+NULL	NULL	NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+c1
+NULL
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+MAX(COALESCE(c1)) AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+MAX(COALESCE(c1)) AS c1
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+c1
+NULL
+DROP TABLE t2;
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #