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.

4 files changed, 46 insertions, 14 deletions

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;