MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()

14 files changed, 323 insertions, 37 deletions

diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc
index 1329bebb31f..a6b5c820783 100644
--- a/mysql-test/include/ctype_numconv.inc
+++ b/mysql-test/include/ctype_numconv.inc
@@ -1743,6 +1743,11 @@ DROP TABLE t1;
 --echo # Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
 --echo #
 SELECT @@collation_connection, @@character_set_results;
+SELECT
+  CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+  CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+  CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+  CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
 CREATE TABLE t1 AS
 SELECT
   DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -1766,7 +1771,26 @@ SELECT
   HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
   HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
   HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
- 
+
+--echo #
+--echo # MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+--echo #
+SELECT @@collation_connection, @@character_set_results;
+SELECT
+  CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+  CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+  CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+  CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+CREATE TABLE t1 AS
+SELECT
+  ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+  ADDTIME('10:01:01','10:00:00') AS addtime2,
+  DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+  DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
 --echo #
 --echo # Bug#11926811 / Bug#60625 Illegal mix of collations
 --echo #
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index c897108f793..c9370c28da0 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2777,6 +2777,13 @@ DROP TABLE t1;
 SELECT @@collation_connection, @@character_set_results;
 @@collation_connection	@@character_set_results
 binary	binary
+SELECT
+CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
+field_str1	field_str2	field_date	field_datetime
+binary	binary	binary	binary
 CREATE TABLE t1 AS
 SELECT
 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -2786,8 +2793,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `field_str1` varchar(26) DEFAULT NULL,
-  `field1_str2` varchar(26) DEFAULT NULL,
+  `field_str1` varbinary(26) DEFAULT NULL,
+  `field1_str2` varbinary(26) DEFAULT NULL,
   `field_date` date DEFAULT NULL,
   `field_datetime` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -2812,6 +2819,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie
 field_str1	field1_str2	field_date	field_datetime
 323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
 #
+# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+binary	binary
+SELECT
+CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+addtime1	addtime2	date_add1	date_add2
+binary	binary	binary	binary
+CREATE TABLE t1 AS
+SELECT
+ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+ADDTIME('10:01:01','10:00:00') AS addtime2,
+DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `addtime1` varbinary(26) DEFAULT NULL,
+  `addtime2` varbinary(26) DEFAULT NULL,
+  `date_add1` varbinary(26) DEFAULT NULL,
+  `date_add2` varbinary(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+addtime1	addtime2	date_add1	date_add2
+20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
+DROP TABLE t1;
+#
 # Bug#11926811 / Bug#60625 Illegal mix of collations
 #
 SELECT @@collation_connection;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index 5323469dff3..b7e99a363f3 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -3169,6 +3169,13 @@ DROP TABLE t1;
 SELECT @@collation_connection, @@character_set_results;
 @@collation_connection	@@character_set_results
 cp1251_general_ci	cp1251
+SELECT
+CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
+field_str1	field_str2	field_date	field_datetime
+cp1251	cp1251	binary	binary
 CREATE TABLE t1 AS
 SELECT
 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -3178,8 +3185,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `field_str1` varchar(26) DEFAULT NULL,
-  `field1_str2` varchar(26) DEFAULT NULL,
+  `field_str1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL,
+  `field1_str2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL,
   `field_date` date DEFAULT NULL,
   `field_datetime` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -3190,8 +3197,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					field_str1	254	26	10	Y	128	31	63
-def					field1_str2	254	26	19	Y	128	31	63
+def					field_str1	254	26	10	Y	0	31	51
+def					field1_str2	254	26	19	Y	0	31	51
 def					field_date	10	10	10	Y	128	0	63
 def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
@@ -3204,6 +3211,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie
 field_str1	field1_str2	field_date	field_datetime
 323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
 #
+# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+cp1251_general_ci	cp1251
+SELECT
+CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+addtime1	addtime2	date_add1	date_add2
+cp1251	cp1251	cp1251	cp1251
+CREATE TABLE t1 AS
+SELECT
+ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+ADDTIME('10:01:01','10:00:00') AS addtime2,
+DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `addtime1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL,
+  `addtime2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL,
+  `date_add1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL,
+  `date_add2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+addtime1	addtime2	date_add1	date_add2
+20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
+DROP TABLE t1;
+#
 # Bug#11926811 / Bug#60625 Illegal mix of collations
 #
 SELECT @@collation_connection;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index ae459fce8eb..b3d104a14eb 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -3196,6 +3196,13 @@ DROP TABLE t1;
 SELECT @@collation_connection, @@character_set_results;
 @@collation_connection	@@character_set_results
 latin1_swedish_ci	latin1
+SELECT
+CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
+field_str1	field_str2	field_date	field_datetime
+latin1	latin1	binary	binary
 CREATE TABLE t1 AS
 SELECT
 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -3217,8 +3224,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					field_str1	254	26	10	Y	128	31	63
-def					field1_str2	254	26	19	Y	128	31	63
+def					field_str1	254	26	10	Y	0	31	8
+def					field1_str2	254	26	19	Y	0	31	8
 def					field_date	10	10	10	Y	128	0	63
 def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
@@ -3231,6 +3238,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie
 field_str1	field1_str2	field_date	field_datetime
 323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
 #
+# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+latin1_swedish_ci	latin1
+SELECT
+CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+addtime1	addtime2	date_add1	date_add2
+latin1	latin1	latin1	latin1
+CREATE TABLE t1 AS
+SELECT
+ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+ADDTIME('10:01:01','10:00:00') AS addtime2,
+DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `addtime1` varchar(26) DEFAULT NULL,
+  `addtime2` varchar(26) DEFAULT NULL,
+  `date_add1` varchar(26) DEFAULT NULL,
+  `date_add2` varchar(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+addtime1	addtime2	date_add1	date_add2
+20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
+DROP TABLE t1;
+#
 # Bug#11926811 / Bug#60625 Illegal mix of collations
 #
 SELECT @@collation_connection;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 73019fac11f..fad02c23b86 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -4105,6 +4105,13 @@ DROP TABLE t1;
 SELECT @@collation_connection, @@character_set_results;
 @@collation_connection	@@character_set_results
 ucs2_general_ci	latin1
+SELECT
+CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
+field_str1	field_str2	field_date	field_datetime
+ucs2	ucs2	binary	binary
 CREATE TABLE t1 AS
 SELECT
 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -4114,8 +4121,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `field_str1` varchar(26) DEFAULT NULL,
-  `field1_str2` varchar(26) DEFAULT NULL,
+  `field_str1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
+  `field1_str2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
   `field_date` date DEFAULT NULL,
   `field_datetime` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -4126,8 +4133,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					field_str1	254	26	10	Y	128	31	63
-def					field1_str2	254	26	19	Y	128	31	63
+def					field_str1	254	26	10	Y	0	31	8
+def					field1_str2	254	26	19	Y	0	31	8
 def					field_date	10	10	10	Y	128	0	63
 def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
@@ -4138,7 +4145,38 @@ HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
 HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
 HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
 field_str1	field1_str2	field_date	field_datetime
-323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
+0032003000300037002D00300038002D00300032002000320033003A00350039003A00300030	0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
+#
+# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+ucs2_general_ci	latin1
+SELECT
+CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+addtime1	addtime2	date_add1	date_add2
+ucs2	ucs2	ucs2	ucs2
+CREATE TABLE t1 AS
+SELECT
+ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+ADDTIME('10:01:01','10:00:00') AS addtime2,
+DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `addtime1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
+  `addtime2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
+  `date_add1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
+  `date_add2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+addtime1	addtime2	date_add1	date_add2
+20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
+DROP TABLE t1;
 #
 # Bug#11926811 / Bug#60625 Illegal mix of collations
 #
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 714b4183594..253614a8329 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -4935,6 +4935,13 @@ DROP TABLE t1;
 SELECT @@collation_connection, @@character_set_results;
 @@collation_connection	@@character_set_results
 utf8_general_ci	utf8
+SELECT
+CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
+CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
+CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
+field_str1	field_str2	field_date	field_datetime
+utf8	utf8	binary	binary
 CREATE TABLE t1 AS
 SELECT
 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
@@ -4944,8 +4951,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `field_str1` varchar(26) DEFAULT NULL,
-  `field1_str2` varchar(26) DEFAULT NULL,
+  `field_str1` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
+  `field1_str2` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
   `field_date` date DEFAULT NULL,
   `field_datetime` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -4956,8 +4963,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					field_str1	254	26	10	Y	128	31	63
-def					field1_str2	254	26	19	Y	128	31	63
+def					field_str1	254	78	10	Y	0	31	33
+def					field1_str2	254	78	19	Y	0	31	33
 def					field_date	10	10	10	Y	128	0	63
 def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
@@ -4970,6 +4977,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie
 field_str1	field1_str2	field_date	field_datetime
 323030372D30382D30322032333A35393A3030	323030372D30382D30332031373A33323A3030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
 #
+# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+utf8_general_ci	utf8
+SELECT
+CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
+CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
+CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
+CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
+addtime1	addtime2	date_add1	date_add2
+utf8	utf8	utf8	utf8
+CREATE TABLE t1 AS
+SELECT
+ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
+ADDTIME('10:01:01','10:00:00') AS addtime2,
+DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
+DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `addtime1` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
+  `addtime2` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
+  `date_add1` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
+  `date_add2` varchar(26) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+addtime1	addtime2	date_add1	date_add2
+20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
+DROP TABLE t1;
+#
 # Bug#11926811 / Bug#60625 Illegal mix of collations
 #
 SELECT @@collation_connection;
diff --git a/sql/item_func.h b/sql/item_func.h
index 71225c71639..68a64fef3cb 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -41,6 +41,7 @@ protected:
   uint allowed_arg_cols;
   /* maybe_null can't be changed by parameters or used table state */
   bool persistent_maybe_null;
+  String *val_str_from_val_str_ascii(String *str, String *str2);
 public:
   uint arg_count;
   table_map used_tables_cache, not_null_tables_cache;
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 039c4417f7b..cf8ce614b6d 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -78,7 +78,7 @@ String my_empty_string("",default_charset_info);
   Normally conversion does not happen, and val_str_ascii() is immediately
   returned instead.
 */
-String *Item_str_func::val_str_from_val_str_ascii(String *str, String *str2)
+String *Item_func::val_str_from_val_str_ascii(String *str, String *str2)
 {
   DBUG_ASSERT(fixed == 1);
 
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 9b380108542..86e1aa70a6b 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -64,7 +64,6 @@ public:
   enum Item_result result_type () const { return STRING_RESULT; }
   void left_right_max_length();
   bool fix_fields(THD *thd, Item **ref);
-  String *val_str_from_val_str_ascii(String *str, String *str2);
 };
 
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 8557b5ce50d..483620bd2fa 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1438,22 +1438,25 @@ void Item_temporal_func::fix_length_and_dec()
   static const uint max_time_type_width[5]=
   { MAX_DATETIME_WIDTH, MAX_DATETIME_WIDTH, MAX_DATE_WIDTH,
     MAX_DATETIME_WIDTH, MIN_TIME_WIDTH };
+  uint char_length= max_time_type_width[mysql_type_to_time_type(field_type())+2];
 
   set_persist_maybe_null(1);
-  max_length= max_time_type_width[mysql_type_to_time_type(field_type())+2];
   if (decimals)
   {
     if (decimals == NOT_FIXED_DEC)
-      max_length+= TIME_SECOND_PART_DIGITS + 1;
+      char_length+= TIME_SECOND_PART_DIGITS + 1;
     else
     {
       set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
-      max_length+= decimals + 1;
+      char_length+= decimals + 1;
     }
   }
   sql_mode= current_thd->variables.sql_mode &
                  (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE);
-  collation.set(&my_charset_numeric, DERIVATION_NUMERIC, MY_REPERTOIRE_ASCII);
+  collation.set(field_type() == MYSQL_TYPE_STRING ?
+                default_charset() : &my_charset_numeric,
+                DERIVATION_NUMERIC, MY_REPERTOIRE_ASCII);
+  fix_char_length(char_length);
 }
 
 String *Item_temporal_func::val_str(String *str)
@@ -1484,6 +1487,23 @@ double Item_temporal_func::val_real()
 }
 
 
+String *Item_temporal_hybrid_func::val_str_ascii(String *str)
+{
+  DBUG_ASSERT(fixed == 1);
+  MYSQL_TIME ltime;
+
+  if (get_date(&ltime, 0) ||
+      (null_value= my_TIME_to_str(&ltime, str, decimals)))
+    return (String *) 0;
+
+  /* Check that the returned timestamp type matches to the function type */
+  DBUG_ASSERT(cached_field_type == MYSQL_TYPE_STRING ||
+              ltime.time_type == MYSQL_TIMESTAMP_NONE ||
+              mysql_type_to_time_type(cached_field_type) == ltime.time_type);
+  return str;
+}
+
+
 bool Item_func_from_days::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
   longlong value=args[0]->val_int();
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 11e84cfc1cd..029e3b17cf1 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -505,6 +505,45 @@ public:
 };
 
 
+/**
+  Abstract class for functions returning TIME, DATE, DATETIME or string values,
+  whose data type depends on parameters and is set at fix_fields time.
+*/
+class Item_temporal_hybrid_func: public Item_temporal_func
+{
+protected:
+  enum_field_types cached_field_type; // TIME, DATE, DATETIME or STRING
+  String ascii_buf; // Conversion buffer
+public:
+  Item_temporal_hybrid_func(Item *a,Item *b)
+    :Item_temporal_func(a,b) {}
+  enum_field_types field_type() const { return cached_field_type; }
+  const CHARSET_INFO *charset_for_protocol() const
+  {
+    /*
+      Can return TIME, DATE, DATETIME or VARCHAR depending on arguments.
+      Send using "binary" when TIME, DATE or DATETIME,
+      or using collation.collation when VARCHAR
+      (which is fixed from @@collation_connection in fix_length_and_dec).
+    */
+    DBUG_ASSERT(fixed == 1);
+    return cached_field_type == MYSQL_TYPE_STRING ?
+           collation.collation : &my_charset_bin;
+  }
+  /**
+    Return string value in ASCII character set.
+  */
+  String *val_str_ascii(String *str);
+  /**
+    Return string value in @@character_set_connection.
+  */
+  String *val_str(String *str)
+  {
+    return val_str_from_val_str_ascii(str, &ascii_buf);
+  }
+};
+
+
 class Item_datefunc :public Item_temporal_func
 {
 public:
@@ -761,17 +800,15 @@ public:
 };
 
 
-class Item_date_add_interval :public Item_temporal_func
+class Item_date_add_interval :public Item_temporal_hybrid_func
 {
-  enum_field_types cached_field_type;
 public:
   const interval_type int_type; // keep it public
   const bool date_sub_interval; // keep it public
   Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg)
-    :Item_temporal_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {}
+    :Item_temporal_hybrid_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {}
   const char *func_name() const { return "date_add_interval"; }
   void fix_length_and_dec();
-  enum_field_types field_type() const { return cached_field_type; }
   bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
   bool eq(const Item *item, bool binary_cmp) const;
   void print(String *str, enum_query_type query_type);
@@ -909,16 +946,14 @@ public:
 };
 
 
-class Item_func_add_time :public Item_temporal_func
+class Item_func_add_time :public Item_temporal_hybrid_func
 {
   const bool is_date;
   int sign;
-  enum_field_types cached_field_type;
 
 public:
   Item_func_add_time(Item *a, Item *b, bool type_arg, bool neg_arg)
-    :Item_temporal_func(a, b), is_date(type_arg) { sign= neg_arg ? -1 : 1; }
-  enum_field_types field_type() const { return cached_field_type; }
+    :Item_temporal_hybrid_func(a, b), is_date(type_arg) { sign= neg_arg ? -1 : 1; }
   void fix_length_and_dec();
   bool get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date);
   void print(String *str, enum_query_type query_type);
@@ -1011,18 +1046,16 @@ public:
 };
 
 
-class Item_func_str_to_date :public Item_temporal_func
+class Item_func_str_to_date :public Item_temporal_hybrid_func
 {
-  enum_field_types cached_field_type;
   timestamp_type cached_timestamp_type;
   bool const_item;
 public:
   Item_func_str_to_date(Item *a, Item *b)
-    :Item_temporal_func(a, b), const_item(false)
+    :Item_temporal_hybrid_func(a, b), const_item(false)
   {}
   bool get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date);
   const char *func_name() const { return "str_to_date"; }
-  enum_field_types field_type() const { return cached_field_type; }
   void fix_length_and_dec();
 };
 
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 4ad39bad14a..2cbc616559d 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -56,7 +56,7 @@
 #define MIN_TIME_WIDTH          10      /* -HHH:MM:SS */
 #define MAX_TIME_WIDTH          16      /* -DDDDDD HH:MM:SS */
 #define MAX_TIME_FULL_WIDTH     23      /* -DDDDDD HH:MM:SS.###### */
-#define MAX_DATETIME_FULL_WIDTH 29	/* YYYY-MM-DD HH:MM:SS.###### AM */
+#define MAX_DATETIME_FULL_WIDTH 26	/* YYYY-MM-DD HH:MM:SS.###### */
 #define MAX_DATETIME_WIDTH	19	/* YYYY-MM-DD HH:MM:SS */
 #define MAX_DATETIME_COMPRESSED_WIDTH 14  /* YYYYMMDDHHMMSS */
 #define MAX_DATETIME_PRECISION  6
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 69f92b41ea4..9611b947aa3 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -813,6 +813,23 @@ const char *get_date_time_format_str(KNOWN_DATE_TIME_FORMAT *format,
   }
 }
 
+
+/**
+  Convert TIME/DATE/DATETIME value to String.
+  @param l_time   DATE value
+  @param OUT str  String to convert to
+  @param dec      Number of fractional digits.
+*/
+bool my_TIME_to_str(const MYSQL_TIME *ltime, String *str, uint dec)
+{
+  if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
+    return true;
+  str->set_charset(&my_charset_numeric);
+  str->length(my_TIME_to_str(ltime, const_cast<char*>(str->ptr()), dec));
+  return false;
+}
+
+
 void make_truncated_value_warning(THD *thd,
                                   Sql_condition::enum_warning_level level,
                                   const ErrConv *sval,
diff --git a/sql/sql_time.h b/sql/sql_time.h
index 47b300d51cc..928e257ab54 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -70,6 +70,8 @@ extern DATE_TIME_FORMAT *date_time_format_copy(THD *thd,
 					       DATE_TIME_FORMAT *format);
 const char *get_date_time_format_str(KNOWN_DATE_TIME_FORMAT *format,
 				     timestamp_type type);
+bool my_TIME_to_str(const MYSQL_TIME *ltime, String *str, uint dec);
+
 /* MYSQL_TIME operations */
 bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type,
                        INTERVAL interval);