MDEV-5298 Illegal mix of collations on timestamp

Fixed.

11 files changed, 542 insertions, 6 deletions

diff --git a/mysql-test/include/ctype_datetime.inc b/mysql-test/include/ctype_datetime.inc
index dc70f1f38a9..91dd2be57b7 100644
--- a/mysql-test/include/ctype_datetime.inc
+++ b/mysql-test/include/ctype_datetime.inc
@@ -1,6 +1,6 @@
-#
-# Bug#32390 Character sets: casting utf32 to/from date doesn't work
-#
+--echo #
+--echo # Bug#32390 Character sets: casting utf32 to/from date doesn't work
+--echo #
 CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
 SET timestamp=1216359724;
 INSERT INTO t1 VALUES (current_date);
@@ -9,3 +9,43 @@ INSERT INTO t1 VALUES (current_timestamp);
 SELECT s1, hex(s1) FROM t1;
 DROP TABLE t1;
 SET timestamp=0;
+
+--echo #
+--echo # MDEV-5298 Illegal mix of collations on timestamp
+--echo #
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY; 
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY; 
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+DROP TABLE t1;
diff --git a/mysql-test/r/cache_temporal_4265.result b/mysql-test/r/cache_temporal_4265.result
index 1b4b3c2c4aa..b8f13e465de 100644
--- a/mysql-test/r/cache_temporal_4265.result
+++ b/mysql-test/r/cache_temporal_4265.result
@@ -7,4 +7,5 @@ a
 2002-03-04
 Warnings:
 Note	1003	2000-01-01
+Note	1003	2000-01-06
 drop table t1;
diff --git a/mysql-test/r/ctype_latin2.result b/mysql-test/r/ctype_latin2.result
index 84853db288a..5f7e41078b3 100644
--- a/mysql-test/r/ctype_latin2.result
+++ b/mysql-test/r/ctype_latin2.result
@@ -560,6 +560,113 @@ BEBBBADFDF
 select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
 hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
 DFDFBABBBE
+#
+# Bug#32390 Character sets: casting utf32 to/from date doesn't work
+#
+CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
+SET timestamp=1216359724;
+INSERT INTO t1 VALUES (current_date);
+INSERT INTO t1 VALUES (current_time);
+INSERT INTO t1 VALUES (current_timestamp);
+SELECT s1, hex(s1) FROM t1;
+s1	hex(s1)
+2008-07-18	323030382D30372D3138
+09:42:04	30393A34323A3034
+2008-07-18 09:42:04	323030382D30372D31382030393A34323A3034
+DROP TABLE t1;
+SET timestamp=0;
+#
+# MDEV-5298 Illegal mix of collations on timestamp
+#
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+latin2
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+4
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+binary
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+5
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+323030312D30312D30312030303A30303A3030
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+323030312D30312D30312030303A30303A3030
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(64) CHARACTER SET latin2 NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+323030312D30312D30312030303A30303A3030
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+latin2
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+323030312D30312D30312030303A30303A3030
+DROP TABLE t1;
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+DROP TABLE t1;
 set collation_connection=latin2_bin;
 select @@collation_connection;
 @@collation_connection
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 26762ff524b..9f9f6d95be6 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -4869,6 +4869,9 @@ DROP TABLE t1;
 # Test how character set works with date/time
 #
 SET collation_connection=ucs2_general_ci;
+#
+# Bug#32390 Character sets: casting utf32 to/from date doesn't work
+#
 CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
 SET timestamp=1216359724;
 INSERT INTO t1 VALUES (current_date);
@@ -4881,6 +4884,98 @@ s1	hex(s1)
 2008-07-18 08:42:04	0032003000300038002D00300037002D00310038002000300038003A00340032003A00300034
 DROP TABLE t1;
 SET timestamp=0;
+#
+# MDEV-5298 Illegal mix of collations on timestamp
+#
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+ucs2
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+4
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+binary
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+5
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(64) CHARACTER SET ucs2 NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+ucs2
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+DROP TABLE t1;
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+DROP TABLE t1;
 SET NAMES latin1;
 #
 # WL#4013 Unicode german2 collation
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index 0c00ea46d09..c7cd225798c 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -1198,6 +1198,9 @@ NULL
 drop table t1;
 set names latin1;
 SET collation_connection=utf16_general_ci;
+#
+# Bug#32390 Character sets: casting utf32 to/from date doesn't work
+#
 CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
 SET timestamp=1216359724;
 INSERT INTO t1 VALUES (current_date);
@@ -1210,6 +1213,98 @@ s1	hex(s1)
 2008-07-18 08:42:04	0032003000300038002D00300037002D00310038002000300038003A00340032003A00300034
 DROP TABLE t1;
 SET timestamp=0;
+#
+# MDEV-5298 Illegal mix of collations on timestamp
+#
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+utf16
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+4
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+binary
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+5
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(64) CHARACTER SET utf16 NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
+DROP TABLE t1;
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+DROP TABLE t1;
 SET NAMES latin1;
 SET collation_connection=utf16_general_ci;
 CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0;
diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result
index a2ac921af0c..7e33665ddd1 100644
--- a/mysql-test/r/ctype_utf16le.result
+++ b/mysql-test/r/ctype_utf16le.result
@@ -1469,6 +1469,9 @@ drop table t1;
 #
 # Test how CHARACTER SET works with date/time
 #
+#
+# Bug#32390 Character sets: casting utf32 to/from date doesn't work
+#
 CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
 SET timestamp=1216359724;
 INSERT INTO t1 VALUES (current_date);
@@ -1482,6 +1485,98 @@ s1	hex(s1)
 DROP TABLE t1;
 SET timestamp=0;
 #
+# MDEV-5298 Illegal mix of collations on timestamp
+#
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+utf16le
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+4
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+binary
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+5
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(64) CHARACTER SET utf16le NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf16le
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
+DROP TABLE t1;
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+DROP TABLE t1;
+#
 # Bug#33073 Character sets: ordering fails with utf32
 #
 SET NAMES utf8, collation_connection=utf16le_general_ci;
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index d1c65749b99..45222ed0aba 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -1146,6 +1146,9 @@ Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
 SET collation_connection=utf32_general_ci;
+#
+# Bug#32390 Character sets: casting utf32 to/from date doesn't work
+#
 CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
 SET timestamp=1216359724;
 INSERT INTO t1 VALUES (current_date);
@@ -1158,6 +1161,98 @@ s1	hex(s1)
 2008-07-18 08:42:04	000000320000003000000030000000380000002D00000030000000370000002D00000031000000380000002000000030000000380000003A00000034000000320000003A0000003000000034
 DROP TABLE t1;
 SET timestamp=0;
+#
+# MDEV-5298 Illegal mix of collations on timestamp
+#
+SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+utf32
+SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
+4
+SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+binary
+SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
+COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
+5
+SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
+4
+SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
+000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030
+SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+4
+SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
+HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030
+CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(64) CHARACTER SET utf32 NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('');
+SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030
+SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+utf32
+SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+2
+SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
+HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
+000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030
+DROP TABLE t1;
+CREATE TABLE t1 (t TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
+t
+2001-01-01 00:00:00
+DROP TABLE t1;
 SET NAMES latin1;
 set collation_connection=utf32_general_ci;
 drop table if exists t1;
diff --git a/mysql-test/t/ctype_latin2.test b/mysql-test/t/ctype_latin2.test
index 91def43eb03..e80515993f2 100644
--- a/mysql-test/t/ctype_latin2.test
+++ b/mysql-test/t/ctype_latin2.test
@@ -65,6 +65,7 @@ drop table t1;
 set names latin2;
 --source include/weight_string.inc
 --source include/weight_string_l1.inc
+--source include/ctype_datetime.inc
 
 set collation_connection=latin2_bin;
 --source include/weight_string.inc
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e6688a5daa9..714eb8be7ee 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -549,8 +549,8 @@ void Item_bool_func2::fix_length_and_dec()
   */
 
   DTCollation coll;
-  if (args[0]->result_type() == STRING_RESULT &&
-      args[1]->result_type() == STRING_RESULT &&
+  if (args[0]->cmp_type() == STRING_RESULT &&
+      args[1]->cmp_type() == STRING_RESULT &&
       agg_arg_charsets_for_comparison(coll, args, 2))
     return;
     
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index a50443ad348..6eaeec0804f 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1467,7 +1467,9 @@ void Item_temporal_func::fix_length_and_dec()
                  (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE);
   collation.set(field_type() == MYSQL_TYPE_STRING ?
                 default_charset() : &my_charset_numeric,
-                DERIVATION_NUMERIC, MY_REPERTOIRE_ASCII);
+                field_type() == MYSQL_TYPE_STRING ?
+                DERIVATION_COERCIBLE : DERIVATION_NUMERIC,
+                MY_REPERTOIRE_ASCII);
   fix_char_length(char_length);
 }
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 99c41961aba..76f84b88795 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -524,6 +524,11 @@ public:
   Item_temporal_hybrid_func(Item *a,Item *b)
     :Item_temporal_func(a,b) {}
   enum_field_types field_type() const { return cached_field_type; }
+  Item_result cmp_type() const
+  {
+    return cached_field_type == MYSQL_TYPE_STRING ?
+           STRING_RESULT : TIME_RESULT;
+  }
   const CHARSET_INFO *charset_for_protocol() const
   {
     /*