Bug#31384 DATE_ADD() and DATE_SUB() return binary data

Problem: DATE_ADD() is a hybrid function and can return
DATE, DATETIME or VARCHAR data type depending on arguments.

In case of VARCHAR data type, DATE_ADD() reported "binary" character set,
which was wrong.

Fix: make DATE_ADD() return @character_set_connection in VARCHAR context.
 @ mysql-test/include/ctype_numconv.inc
   Adding tests
 @ mysql-test/r/ctype_binary.result
   Adding tests
 @ mysql-test/r/ctype_cp1251.result
   Adding tests
 @ mysql-test/r/ctype_latin1.result
   Adding tests
 @ mysql-test/r/ctype_ucs.result
   Adding tests
 @ mysql-test/r/ctype_utf8.result
   Adding tests
 @ sql/item_strfunc.cc
  - Moving code from Item_str_ascii_func::val_str() to
  Item_str_func::val_str_from_val_str_ascii(), as
  this code needs to be shared by Item_date_add_interval.
  - Adding str2 parameter to be used as a buffer, instead of
   using private ascii_buf member.
 @ sql/item_strfunc.h
  - Moving code from Item_str_ascii_func::val_str() to
  Item_str_func::val_str_from_val_str_ascii()
  - Removing "String *val_str_convert_from_ascii(String *str, String *ascii_buf)"
    prototype as it was neither used nor declared.
 @ sql/item_timefunc.h
  - Overwriting parent's charset_for_protocol() method,
    becase we need to behave differenlty in VARCHAR and DATE/DATETYPE context.
  - Adding ascii_buf for conversion.
  - Adding val_str_ascii() prototype.
  - Adding val_str() which uses newly added
    Item_str_func::val_str_from_val_str_ascii(),
    passing ascii_buf as a conversion buffer.

10 files changed, 272 insertions, 14 deletions

diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc
index 83d69cfa40d..974951b6b6f 100644
--- a/mysql-test/include/ctype_numconv.inc
+++ b/mysql-test/include/ctype_numconv.inc
@@ -1738,6 +1738,32 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+--echo #
+SELECT @@collation_connection, @@character_set_results;
+CREATE TABLE t1 AS
+SELECT
+  DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+  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;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--enable_metadata
+SELECT
+  DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+  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;
+--disable_metadata
+SELECT
+  HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+  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 # Bug#52159 returning time type from function and empty left join causes debug assertion
 --echo #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 4d526a86ade..58cd4b3524b 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2767,6 +2767,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	date_column	date_column	9	NULL	1	Using where
 DROP TABLE t1;
 #
+# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+binary	binary
+CREATE TABLE t1 AS
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+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;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `field_str1` varbinary(29) DEFAULT NULL,
+  `field1_str2` varbinary(29) DEFAULT NULL,
+  `field_date` date DEFAULT NULL,
+  `field_datetime` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+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	29	10	Y	128	31	63
+def					field1_str2	254	29	19	Y	128	31	63
+def					field_date	10	29	10	Y	128	31	63
+def					field_datetime	12	29	19	Y	128	31	63
+field_str1	field1_str2	field_date	field_datetime
+2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
+SELECT
+HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+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
+#
 # Bug#52159 returning time type from function and empty left join causes debug assertion
 #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index b65230e94b4..9a781c61e91 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -3157,6 +3157,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	date_column	date_column	9	NULL	1	Using where
 DROP TABLE t1;
 #
+# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+cp1251_general_ci	cp1251
+CREATE TABLE t1 AS
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+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;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `field_str1` varchar(29) CHARACTER SET cp1251 DEFAULT NULL,
+  `field1_str2` varchar(29) CHARACTER SET cp1251 DEFAULT NULL,
+  `field_date` date DEFAULT NULL,
+  `field_datetime` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+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	29	10	Y	0	31	51
+def					field1_str2	254	29	19	Y	0	31	51
+def					field_date	10	29	10	Y	128	31	63
+def					field_datetime	12	29	19	Y	128	31	63
+field_str1	field1_str2	field_date	field_datetime
+2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
+SELECT
+HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+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
+#
 # Bug#52159 returning time type from function and empty left join causes debug assertion
 #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index ac84b169ec3..6f773270dff 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -3186,6 +3186,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	date_column	date_column	9	NULL	1	Using where
 DROP TABLE t1;
 #
+# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+latin1_swedish_ci	latin1
+CREATE TABLE t1 AS
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+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;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `field_str1` varchar(29) DEFAULT NULL,
+  `field1_str2` varchar(29) DEFAULT NULL,
+  `field_date` date DEFAULT NULL,
+  `field_datetime` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+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	29	10	Y	0	31	8
+def					field1_str2	254	29	19	Y	0	31	8
+def					field_date	10	29	10	Y	128	31	63
+def					field_datetime	12	29	19	Y	128	31	63
+field_str1	field1_str2	field_date	field_datetime
+2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
+SELECT
+HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+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
+#
 # Bug#52159 returning time type from function and empty left join causes debug assertion
 #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 11d5117bbe1..0ee80b921a1 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -4009,6 +4009,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 DROP TABLE t1;
 #
+# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+ucs2_general_ci	latin1
+CREATE TABLE t1 AS
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+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;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `field_str1` varchar(29) CHARACTER SET ucs2 DEFAULT NULL,
+  `field1_str2` varchar(29) CHARACTER SET ucs2 DEFAULT NULL,
+  `field_date` date DEFAULT NULL,
+  `field_datetime` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+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	29	10	Y	0	31	8
+def					field1_str2	254	29	19	Y	0	31	8
+def					field_date	10	29	10	Y	128	31	63
+def					field_datetime	12	29	19	Y	128	31	63
+field_str1	field1_str2	field_date	field_datetime
+2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
+SELECT
+HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+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
+0032003000300037002D00300038002D00300032002000320033003A00350039003A00300030	0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
+#
 # Bug#52159 returning time type from function and empty left join causes debug assertion
 #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 5763885d5f3..ababfe51621 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -4898,6 +4898,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	date_column	date_column	9	NULL	1	Using where
 DROP TABLE t1;
 #
+# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
+#
+SELECT @@collation_connection, @@character_set_results;
+@@collation_connection	@@character_set_results
+utf8_general_ci	utf8
+CREATE TABLE t1 AS
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
+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;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `field_str1` varchar(29) CHARACTER SET utf8 DEFAULT NULL,
+  `field1_str2` varchar(29) CHARACTER SET utf8 DEFAULT NULL,
+  `field_date` date DEFAULT NULL,
+  `field_datetime` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SELECT
+DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
+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	87	10	Y	0	31	33
+def					field1_str2	254	87	19	Y	0	31	33
+def					field_date	10	29	10	Y	128	31	63
+def					field_datetime	12	29	19	Y	128	31	63
+field_str1	field1_str2	field_date	field_datetime
+2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
+SELECT
+HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
+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
+#
 # Bug#52159 returning time type from function and empty left join causes debug assertion
 #
 CREATE FUNCTION f1() RETURNS TIME RETURN 1;
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 25eb08ffa75..0248c133f60 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -70,7 +70,7 @@ String my_empty_string("",default_charset_info);
   Normally conversion does not happen, and val_str_ascii() is immediately
   returned instead.
 */
-String *Item_str_ascii_func::val_str(String *str)
+String *Item_str_func::val_str_from_val_str_ascii(String *str, String *str2)
 {
   DBUG_ASSERT(fixed == 1);
 
@@ -82,19 +82,19 @@ String *Item_str_ascii_func::val_str(String *str)
     return res;
   }
   
-  DBUG_ASSERT(str != &ascii_buf);
+  DBUG_ASSERT(str != str2);
   
   uint errors;
-  String *res= val_str_ascii(&ascii_buf);
+  String *res= val_str_ascii(str);
   if (!res)
     return 0;
   
-  if ((null_value= str->copy(res->ptr(), res->length(),
-                             &my_charset_latin1, collation.collation,
-                             &errors)))
+  if ((null_value= str2->copy(res->ptr(), res->length(),
+                              &my_charset_latin1, collation.collation,
+                              &errors)))
     return 0;
   
-  return str;
+  return str2;
 }
 
 
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index e26f9b4467a..fef4f9f975d 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -51,6 +51,7 @@ 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);
 };
 
 
@@ -66,8 +67,10 @@ public:
   Item_str_ascii_func(Item *a) :Item_str_func(a) {}
   Item_str_ascii_func(Item *a,Item *b) :Item_str_func(a,b) {}
   Item_str_ascii_func(Item *a,Item *b,Item *c) :Item_str_func(a,b,c) {}
-  String *val_str_convert_from_ascii(String *str, String *ascii_buf);
-  String *val_str(String *str);
+  String *val_str(String *str)
+  {
+    return val_str_from_val_str_ascii(str, &ascii_buf);
+  }
   virtual String *val_str_ascii(String *)= 0;
 };
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 73b8b6047e0..3d587ace335 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2205,8 +2205,6 @@ void Item_date_add_interval::fix_length_and_dec()
   enum_field_types arg0_field_type;
 
   maybe_null=1;
-  fix_length_and_charset_datetime(MAX_DATETIME_FULL_WIDTH);
-  value.alloc(max_length);
 
   /*
     The field type for the result of an Item_date function is defined as
@@ -2231,6 +2229,21 @@ void Item_date_add_interval::fix_length_and_dec()
     else
       cached_field_type= MYSQL_TYPE_DATETIME;
   }
+
+  if (cached_field_type == MYSQL_TYPE_STRING)
+  {
+    /* Behave as a usual string function when return type is VARCHAR. */
+    fix_length_and_charset(MAX_DATETIME_FULL_WIDTH, default_charset());
+  }
+  else
+  {
+    /*
+      Follow the "Number-to-string conversion" rules as in WorkLog 2649
+      when return type is DATE or DATETIME.
+    */
+    fix_length_and_charset_datetime(MAX_DATETIME_FULL_WIDTH);
+  }
+  value.alloc(max_length);
 }
 
 
@@ -2253,7 +2266,7 @@ bool Item_date_add_interval::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 }
 
 
-String *Item_date_add_interval::val_str(String *str)
+String *Item_date_add_interval::val_str_ascii(String *str)
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index b8c6b1d38e4..9732e8dc360 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -773,16 +773,32 @@ class Item_date_add_interval :public Item_date_func
 {
   String value;
   enum_field_types cached_field_type;
-
+  String ascii_buf;
 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_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {}
-  String *val_str(String *);
+  String *val_str_ascii(String *str);
+  String *val_str(String *str)
+  {
+    return val_str_from_val_str_ascii(str, &ascii_buf);
+  }
   const char *func_name() const { return "date_add_interval"; }
   void fix_length_and_dec();
   enum_field_types field_type() const { return cached_field_type; }
+  CHARSET_INFO *charset_for_protocol(void) const
+  {
+    /*
+      DATE_ADD() can return DATE, DATETIME or VARCHAR depending on arguments.
+      Send using "binary" when DATE or DATETIME,
+      or using collation.collation when VARCHAR
+      (which was 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;
+  }
   longlong val_int();
   bool get_date(MYSQL_TIME *res, uint fuzzy_date);
   bool eq(const Item *item, bool binary_cmp) const;