MDEV-12617 CASE and CASE-alike hybrid functions do not preserve exact data types

3 files changed, 194 insertions, 136 deletions

diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result
index dfc552cc58c..a8fd8a2d38a 100644
--- a/mysql-test/r/func_hybrid_type.result
+++ b/mysql-test/r/func_hybrid_type.result
@@ -1000,14 +1000,14 @@ t2	CREATE TABLE `t2` (
   `least____a_a` int(11) DEFAULT NULL,
   `greatest_a_a` int(11) DEFAULT NULL,
   `___________b` bit(8) DEFAULT NULL,
-  `case_______b` int(8) unsigned DEFAULT NULL,
-  `case_____b_b` int(8) unsigned DEFAULT NULL,
-  `coalesce___b` int(8) unsigned DEFAULT NULL,
-  `coalesce_b_b` int(8) unsigned DEFAULT NULL,
-  `if_______b_b` int(8) unsigned DEFAULT NULL,
+  `case_______b` bit(8) DEFAULT NULL,
+  `case_____b_b` bit(8) DEFAULT NULL,
+  `coalesce___b` bit(8) DEFAULT NULL,
+  `coalesce_b_b` bit(8) DEFAULT NULL,
+  `if_______b_b` bit(8) DEFAULT NULL,
   `ifnull___b_b` bit(8) DEFAULT NULL,
-  `least____b_b` int(8) unsigned DEFAULT NULL,
-  `greatest_b_b` int(8) unsigned DEFAULT NULL
+  `least____b_b` bit(8) DEFAULT NULL,
+  `greatest_b_b` bit(8) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -1119,18 +1119,18 @@ GREATEST(a, b)                            AS greatest_a_b,
 GREATEST(b, a)                            AS greatest_b_a
 FROM t1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					case_____a_b	8	10	10	Y	32928	0	63
-def					case_____b_a	8	10	2	Y	32928	0	63
-def					coalesce_a_b	8	10	10	Y	32928	0	63
-def					coalesce_b_a	8	10	2	Y	32928	0	63
-def					if_______a_b	8	10	2	Y	32928	0	63
-def					if_______b_a	8	10	10	Y	32928	0	63
-def					ifnull___a_b	8	10	10	Y	32928	0	63
-def					ifnull___b_a	8	10	2	Y	32928	0	63
-def					least____a_b	8	10	2	Y	32928	0	63
-def					least____b_a	8	10	2	Y	32928	0	63
-def					greatest_a_b	8	10	10	Y	32928	0	63
-def					greatest_b_a	8	10	10	Y	32928	0	63
+def					case_____a_b	3	10	10	Y	32928	0	63
+def					case_____b_a	3	10	2	Y	32928	0	63
+def					coalesce_a_b	3	10	10	Y	32928	0	63
+def					coalesce_b_a	3	10	2	Y	32928	0	63
+def					if_______a_b	3	10	2	Y	32928	0	63
+def					if_______b_a	3	10	10	Y	32928	0	63
+def					ifnull___a_b	3	10	10	Y	32928	0	63
+def					ifnull___b_a	3	10	2	Y	32928	0	63
+def					least____a_b	3	10	2	Y	32928	0	63
+def					least____b_a	3	10	2	Y	32928	0	63
+def					greatest_a_b	3	10	10	Y	32928	0	63
+def					greatest_b_a	3	10	10	Y	32928	0	63
 case_____a_b	4294967295
 case_____b_a	50
 coalesce_a_b	4294967295
@@ -1177,14 +1177,14 @@ t2	CREATE TABLE `t2` (
   `least____a_a` int(10) unsigned DEFAULT NULL,
   `greatest_a_a` int(10) unsigned DEFAULT NULL,
   `___________b` bit(8) DEFAULT NULL,
-  `case_______b` int(8) unsigned DEFAULT NULL,
-  `case_____b_b` int(8) unsigned DEFAULT NULL,
-  `coalesce___b` int(8) unsigned DEFAULT NULL,
-  `coalesce_b_b` int(8) unsigned DEFAULT NULL,
-  `if_______b_b` int(8) unsigned DEFAULT NULL,
+  `case_______b` bit(8) DEFAULT NULL,
+  `case_____b_b` bit(8) DEFAULT NULL,
+  `coalesce___b` bit(8) DEFAULT NULL,
+  `coalesce_b_b` bit(8) DEFAULT NULL,
+  `if_______b_b` bit(8) DEFAULT NULL,
   `ifnull___b_b` bit(8) DEFAULT NULL,
-  `least____b_b` int(8) unsigned DEFAULT NULL,
-  `greatest_b_b` int(8) unsigned DEFAULT NULL
+  `least____b_b` bit(8) DEFAULT NULL,
+  `greatest_b_b` bit(8) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -1212,8 +1212,8 @@ t2	CREATE TABLE `t2` (
   `coalesce_b_a` int(10) unsigned DEFAULT NULL,
   `if_______a_b` int(10) unsigned DEFAULT NULL,
   `if_______b_a` int(10) unsigned DEFAULT NULL,
-  `ifnull___a_b` bigint(10) unsigned DEFAULT NULL,
-  `ifnull___b_a` bigint(10) unsigned DEFAULT NULL,
+  `ifnull___a_b` int(10) unsigned DEFAULT NULL,
+  `ifnull___b_a` int(10) unsigned DEFAULT NULL,
   `least____a_b` int(10) unsigned DEFAULT NULL,
   `least____b_a` int(10) unsigned DEFAULT NULL,
   `greatest_a_b` int(10) unsigned DEFAULT NULL,
@@ -1345,23 +1345,23 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `___________a` bit(7) DEFAULT NULL,
-  `case_______a` int(7) unsigned DEFAULT NULL,
-  `case_____a_a` int(7) unsigned DEFAULT NULL,
-  `coalesce___a` int(7) unsigned DEFAULT NULL,
-  `coalesce_a_a` int(7) unsigned DEFAULT NULL,
-  `if_______a_a` int(7) unsigned DEFAULT NULL,
+  `case_______a` bit(7) DEFAULT NULL,
+  `case_____a_a` bit(7) DEFAULT NULL,
+  `coalesce___a` bit(7) DEFAULT NULL,
+  `coalesce_a_a` bit(7) DEFAULT NULL,
+  `if_______a_a` bit(7) DEFAULT NULL,
   `ifnull___a_a` bit(7) DEFAULT NULL,
-  `least____a_a` int(7) unsigned DEFAULT NULL,
-  `greatest_a_a` int(7) unsigned DEFAULT NULL,
+  `least____a_a` bit(7) DEFAULT NULL,
+  `greatest_a_a` bit(7) DEFAULT NULL,
   `___________b` bit(8) DEFAULT NULL,
-  `case_______b` int(8) unsigned DEFAULT NULL,
-  `case_____b_b` int(8) unsigned DEFAULT NULL,
-  `coalesce___b` int(8) unsigned DEFAULT NULL,
-  `coalesce_b_b` int(8) unsigned DEFAULT NULL,
-  `if_______b_b` int(8) unsigned DEFAULT NULL,
+  `case_______b` bit(8) DEFAULT NULL,
+  `case_____b_b` bit(8) DEFAULT NULL,
+  `coalesce___b` bit(8) DEFAULT NULL,
+  `coalesce_b_b` bit(8) DEFAULT NULL,
+  `if_______b_b` bit(8) DEFAULT NULL,
   `ifnull___b_b` bit(8) DEFAULT NULL,
-  `least____b_b` int(8) unsigned DEFAULT NULL,
-  `greatest_b_b` int(8) unsigned DEFAULT NULL
+  `least____b_b` bit(8) DEFAULT NULL,
+  `greatest_b_b` bit(8) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -1383,18 +1383,18 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `case_____a_b` int(8) unsigned DEFAULT NULL,
-  `case_____b_a` int(8) unsigned DEFAULT NULL,
-  `coalesce_a_b` int(8) unsigned DEFAULT NULL,
-  `coalesce_b_a` int(8) unsigned DEFAULT NULL,
-  `if_______a_b` int(8) unsigned DEFAULT NULL,
-  `if_______b_a` int(8) unsigned DEFAULT NULL,
+  `case_____a_b` bit(8) DEFAULT NULL,
+  `case_____b_a` bit(8) DEFAULT NULL,
+  `coalesce_a_b` bit(8) DEFAULT NULL,
+  `coalesce_b_a` bit(8) DEFAULT NULL,
+  `if_______a_b` bit(8) DEFAULT NULL,
+  `if_______b_a` bit(8) DEFAULT NULL,
   `ifnull___a_b` bit(8) DEFAULT NULL,
   `ifnull___b_a` bit(8) DEFAULT NULL,
-  `least____a_b` int(8) unsigned DEFAULT NULL,
-  `least____b_a` int(8) unsigned DEFAULT NULL,
-  `greatest_a_b` int(8) unsigned DEFAULT NULL,
-  `greatest_b_a` int(8) unsigned DEFAULT NULL
+  `least____a_b` bit(8) DEFAULT NULL,
+  `least____b_a` bit(8) DEFAULT NULL,
+  `greatest_a_b` bit(8) DEFAULT NULL,
+  `greatest_b_a` bit(8) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -1429,8 +1429,8 @@ def					coalesce___a	4	12	1	Y	32896	31	63
 def					coalesce_a_a	4	12	1	Y	32896	31	63
 def					if_______a_a	4	12	1	Y	32896	31	63
 def					ifnull___a_a	4	12	1	Y	32896	31	63
-def					least____a_a	5	23	1	Y	32896	31	63
-def					greatest_a_a	5	23	1	Y	32896	31	63
+def					least____a_a	4	23	1	Y	32896	31	63
+def					greatest_a_a	4	23	1	Y	32896	31	63
 def	test	t1	t1	b	___________b	2	6	6	Y	32768	0	63
 def					case_______b	2	6	6	Y	32896	0	63
 def					case_____b_b	2	6	6	Y	32896	0	63
@@ -1522,23 +1522,23 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `___________a` float DEFAULT NULL,
-  `case_______a` double DEFAULT NULL,
-  `case_____a_a` double DEFAULT NULL,
-  `coalesce___a` double DEFAULT NULL,
-  `coalesce_a_a` double DEFAULT NULL,
-  `if_______a_a` double DEFAULT NULL,
+  `case_______a` float DEFAULT NULL,
+  `case_____a_a` float DEFAULT NULL,
+  `coalesce___a` float DEFAULT NULL,
+  `coalesce_a_a` float DEFAULT NULL,
+  `if_______a_a` float DEFAULT NULL,
   `ifnull___a_a` float DEFAULT NULL,
-  `least____a_a` double DEFAULT NULL,
-  `greatest_a_a` double DEFAULT NULL,
+  `least____a_a` float DEFAULT NULL,
+  `greatest_a_a` float DEFAULT NULL,
   `___________b` smallint(6) DEFAULT NULL,
-  `case_______b` int(6) DEFAULT NULL,
-  `case_____b_b` int(6) DEFAULT NULL,
-  `coalesce___b` int(6) DEFAULT NULL,
-  `coalesce_b_b` int(6) DEFAULT NULL,
-  `if_______b_b` int(6) DEFAULT NULL,
+  `case_______b` smallint(6) DEFAULT NULL,
+  `case_____b_b` smallint(6) DEFAULT NULL,
+  `coalesce___b` smallint(6) DEFAULT NULL,
+  `coalesce_b_b` smallint(6) DEFAULT NULL,
+  `if_______b_b` smallint(6) DEFAULT NULL,
   `ifnull___b_b` smallint(6) DEFAULT NULL,
-  `least____b_b` int(6) DEFAULT NULL,
-  `greatest_b_b` int(6) DEFAULT NULL
+  `least____b_b` smallint(6) DEFAULT NULL,
+  `greatest_b_b` smallint(6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -1560,12 +1560,12 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `case_____a_b` double DEFAULT NULL,
-  `case_____b_a` double DEFAULT NULL,
-  `coalesce_a_b` double DEFAULT NULL,
-  `coalesce_b_a` double DEFAULT NULL,
-  `if_______a_b` double DEFAULT NULL,
-  `if_______b_a` double DEFAULT NULL,
+  `case_____a_b` float DEFAULT NULL,
+  `case_____b_a` float DEFAULT NULL,
+  `coalesce_a_b` float DEFAULT NULL,
+  `coalesce_b_a` float DEFAULT NULL,
+  `if_______a_b` float DEFAULT NULL,
+  `if_______b_a` float DEFAULT NULL,
   `ifnull___a_b` float DEFAULT NULL,
   `ifnull___b_a` float DEFAULT NULL,
   `least____a_b` double DEFAULT NULL,
@@ -1885,14 +1885,14 @@ t2	CREATE TABLE `t2` (
   `least____a_a` int(11) DEFAULT NULL,
   `greatest_a_a` int(11) DEFAULT NULL,
   `___________b` year(4) DEFAULT NULL,
-  `case_______b` int(4) unsigned DEFAULT NULL,
-  `case_____b_b` int(4) unsigned DEFAULT NULL,
-  `coalesce___b` int(4) unsigned DEFAULT NULL,
-  `coalesce_b_b` int(4) unsigned DEFAULT NULL,
-  `if_______b_b` int(4) unsigned DEFAULT NULL,
+  `case_______b` year(4) DEFAULT NULL,
+  `case_____b_b` year(4) DEFAULT NULL,
+  `coalesce___b` year(4) DEFAULT NULL,
+  `coalesce_b_b` year(4) DEFAULT NULL,
+  `if_______b_b` year(4) DEFAULT NULL,
   `ifnull___b_b` year(4) DEFAULT NULL,
-  `least____b_b` int(4) unsigned DEFAULT NULL,
-  `greatest_b_b` int(4) unsigned DEFAULT NULL
+  `least____b_b` year(4) DEFAULT NULL,
+  `greatest_b_b` year(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -2062,14 +2062,14 @@ t2	CREATE TABLE `t2` (
   `least____a_a` int(10) unsigned DEFAULT NULL,
   `greatest_a_a` int(10) unsigned DEFAULT NULL,
   `___________b` year(4) DEFAULT NULL,
-  `case_______b` int(4) unsigned DEFAULT NULL,
-  `case_____b_b` int(4) unsigned DEFAULT NULL,
-  `coalesce___b` int(4) unsigned DEFAULT NULL,
-  `coalesce_b_b` int(4) unsigned DEFAULT NULL,
-  `if_______b_b` int(4) unsigned DEFAULT NULL,
+  `case_______b` year(4) DEFAULT NULL,
+  `case_____b_b` year(4) DEFAULT NULL,
+  `coalesce___b` year(4) DEFAULT NULL,
+  `coalesce_b_b` year(4) DEFAULT NULL,
+  `if_______b_b` year(4) DEFAULT NULL,
   `ifnull___b_b` year(4) DEFAULT NULL,
-  `least____b_b` int(4) unsigned DEFAULT NULL,
-  `greatest_b_b` int(4) unsigned DEFAULT NULL
+  `least____b_b` year(4) DEFAULT NULL,
+  `greatest_b_b` year(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -3512,5 +3512,40 @@ t2	CREATE TABLE `t2` (
 DROP TABLE t2;
 DROP TABLE t1;
 #
+# MDEV-12617 CASE and CASE-alike hybrid functions do not preserve exact data types
+#
+CREATE TABLE t1 (a FLOAT(10,2));
+CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a)` float(10,2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2, t1;
+CREATE TABLE t1 (a FLOAT(10,2));
+CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `LEAST(a,a)` float(19,2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2, t1;
+CREATE TABLE t1 (a TINYINT(1));
+CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a)` tinyint(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2, t1;
+CREATE TABLE t1 (a TINYINT(1));
+CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `LEAST(a,a)` tinyint(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2, t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 8bbdfb2f90b..2fa89a2d001 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -613,22 +613,22 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `NULLIF(c_tinyint,  1)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_smallint)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_tinyint)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_int)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_bigint)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_float)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_double)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_decimal103)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_varchar10)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_text)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_blob)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_enum)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_datetime3)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_timestamp3)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_date)` int(4) DEFAULT NULL,
-  `NULLIF(c_tinyint,  c_time)` int(4) DEFAULT NULL
+  `NULLIF(c_tinyint,  1)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_smallint)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_tinyint)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_int)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_bigint)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_float)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_double)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_decimal103)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_varchar10)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_text)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_blob)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_enum)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_datetime3)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_timestamp3)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_date)` tinyint(4) DEFAULT NULL,
+  `NULLIF(c_tinyint,  c_time)` tinyint(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
@@ -652,22 +652,22 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `NULLIF(c_smallint,  1)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_smallint)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_tinyint)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_int)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_bigint)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_float)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_double)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_decimal103)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_varchar10)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_text)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_blob)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_enum)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_datetime3)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_timestamp3)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_date)` int(6) DEFAULT NULL,
-  `NULLIF(c_smallint,  c_time)` int(6) DEFAULT NULL
+  `NULLIF(c_smallint,  1)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_smallint)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_tinyint)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_int)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_bigint)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_float)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_double)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_decimal103)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_varchar10)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_text)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_blob)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_enum)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_datetime3)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_timestamp3)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_date)` smallint(6) DEFAULT NULL,
+  `NULLIF(c_smallint,  c_time)` smallint(6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
@@ -769,22 +769,22 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `NULLIF(c_float,  1)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_smallint)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_tinyint)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_int)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_bigint)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_float)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_double)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_decimal103)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_varchar10)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_text)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_blob)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_enum)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_datetime3)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_timestamp3)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_date)` double DEFAULT NULL,
-  `NULLIF(c_float,  c_time)` double DEFAULT NULL
+  `NULLIF(c_float,  1)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_smallint)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_tinyint)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_int)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_bigint)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_float)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_double)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_decimal103)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_varchar10)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_text)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_blob)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_enum)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_datetime3)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_timestamp3)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_date)` float DEFAULT NULL,
+  `NULLIF(c_float,  c_time)` float DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
diff --git a/mysql-test/t/func_hybrid_type.test b/mysql-test/t/func_hybrid_type.test
index 918320a68c5..0957dfd33ec 100644
--- a/mysql-test/t/func_hybrid_type.test
+++ b/mysql-test/t/func_hybrid_type.test
@@ -510,6 +510,29 @@ SHOW CREATE TABLE t2;
 DROP TABLE t2;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-12617 CASE and CASE-alike hybrid functions do not preserve exact data types
+--echo #
+
+CREATE TABLE t1 (a FLOAT(10,2));
+CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2, t1;
+
+CREATE TABLE t1 (a FLOAT(10,2));
+CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2, t1;
+
+CREATE TABLE t1 (a TINYINT(1));
+CREATE TABLE t2 AS SELECT COALESCE(a) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2, t1;
+
+CREATE TABLE t1 (a TINYINT(1));
+CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2, t1;
 
 --echo #
 --echo # End of 10.3 tests