MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types

Adding test cases from the bug report only.

The problem was earlier fixed by:
MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

modified:
  mysql-test/r/func_time.result
  mysql-test/t/func_time.test

2 files changed, 74 insertions, 0 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index eec9a4e7ea3..8aba949ad55 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -2033,6 +2033,56 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
 #
+# MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
+#
+CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
+SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
+CASE WHEN 0 THEN dt2 ELSE t3 END
+0000-00-00 00:00:00.567
+CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
+SELECT * FROM t2;
+CASE WHEN 0 THEN dt2 ELSE t3 END
+0000-00-00 00:00:00.567
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+CASE WHEN 0 THEN dt2 ELSE t3 END	datetime(3)	YES		NULL	
+DROP TABLE t2;
+SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1;
+CASE WHEN 1 THEN dt2 ELSE t3 END
+2001-01-01 00:00:00.120
+SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1;
+CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END)
+2001-01-01 00:00:00.120
+SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1;
+CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END)
+2001-01-01 00:00:00.120
+SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1;
+CONCAT(CASE WHEN 1 THEN d ELSE t3 END)
+2002-01-01 00:00:00.000
+SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
+CASE WHEN 1 THEN t3 ELSE d END
+0000-00-00 00:00:00.567
+SELECT COALESCE(d, t3) FROM t1;
+COALESCE(d, t3)
+2002-01-01 00:00:00.000
+SELECT CONCAT(COALESCE(d, t3)) FROM t1;
+CONCAT(COALESCE(d, t3))
+2002-01-01 00:00:00.000
+SELECT COALESCE(dt2, t3) FROM t1;
+COALESCE(dt2, t3)
+2001-01-01 00:00:00.120
+SELECT CONCAT(COALESCE(dt2, t3)) FROM t1;
+CONCAT(COALESCE(dt2, t3))
+2001-01-01 00:00:00.120
+SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
+IFNULL(dt2, t3)	CONCAT(IFNULL(dt2, t3))
+2001-01-01 00:00:00.120	2001-01-01 00:00:00.120
+SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
+IFNULL(d, t3)	CONCAT(IFNULL(d, t3))
+2002-01-01 00:00:00.000	2002-01-01 00:00:00.000
+DROP TABLE t1;
+#
 # MDEV-4724 Some temporal functions do not preserve microseconds
 #
 SELECT MAKETIME(10,10,10.231);
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 87be964cd7e..62df3221723 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1258,6 +1258,30 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
+--echo #
+CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
+SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
+CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2;
+SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1;
+SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1;
+SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1;
+SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1;
+SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
+SELECT COALESCE(d, t3) FROM t1;
+SELECT CONCAT(COALESCE(d, t3)) FROM t1;
+SELECT COALESCE(dt2, t3) FROM t1;
+SELECT CONCAT(COALESCE(dt2, t3)) FROM t1;
+SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
+SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
+DROP TABLE t1;
+
+
+--echo #
 --echo # MDEV-4724 Some temporal functions do not preserve microseconds
 --echo #
 SELECT MAKETIME(10,10,10.231);