Bug#11926811 / Bug#60625 Illegal mix of collations

Problem: comparison of a DATETIME sp variable and NOW()
led to Illegal mix of collations error when 
character_set_connection=utf8.
Introduced by "WL#2649 Number-to-string conversions".

Error happened in Arg_comparator::set_compare_func(),
because the first argument was errouneously converted to utf8,
while the second argument was not.

Fix: separate agg_arg_charsets_for_comparison() into two functions:

- agg_arg_charsets_for_comparison() - for pure comparison,
  when we don't need to return any string result and therefore
  don't need to convert arguments to @@character_set_connection:
    SELECT a = b;

- agg_arg_charsets_for_string_results_with_comparison() - when
  we need to return a string result, but we also need to do
  comparison internally: SELECT REPLACE(a,b,c)
  If all arguments are numbers:
    SELECT REPLACE(123,2,3) -> 133
  we convert arguments to @@character_set_connection.


  @ mysql-test/include/ctype_numconv.inc
  @ mysql-test/r/ctype_binary.result
  @ mysql-test/r/ctype_cp1251.result
  @ mysql-test/r/ctype_latin1.result
  @ mysql-test/r/ctype_ucs.result
  @ mysql-test/r/ctype_utf8.result
  Adding tests

  @ sql/item.cc
  @ sql/item.h
  @ sql/item_func.cc
  @ sql/item_func.h
  @ sql/item_strfunc.cc

  Introducing and using new function
   agg_item_charsets_for_string_result_with_comparison() and
  its Item_func wrapper agg_arg_charsets_for_string_result_with_comparison().

1 files changed, 25 insertions, 0 deletions

diff --git a/sql/item_func.h b/sql/item_func.h
index e60effc352c..9198b092539 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -165,6 +165,11 @@ public:
   {
     return agg_item_charsets(c, func_name(), items, nitems, flags, item_sep);
   }
+  /*
+    Aggregate arguments for string result, e.g: CONCAT(a,b)
+    - convert to @@character_set_connection if all arguments are numbers
+    - allow DERIVATION_NONE
+  */
   bool agg_arg_charsets_for_string_result(DTCollation &c,
                                           Item **items, uint nitems,
                                           int item_sep= 1)
@@ -172,6 +177,11 @@ public:
     return agg_item_charsets_for_string_result(c, func_name(),
                                                items, nitems, item_sep);
   }
+  /*
+    Aggregate arguments for comparison, e.g: a=b, a LIKE b, a RLIKE b
+    - don't convert to @@character_set_connection if all arguments are numbers
+    - don't allow DERIVATION_NONE
+  */
   bool agg_arg_charsets_for_comparison(DTCollation &c,
                                        Item **items, uint nitems,
                                        int item_sep= 1)
@@ -179,6 +189,21 @@ public:
     return agg_item_charsets_for_comparison(c, func_name(),
                                             items, nitems, item_sep);
   }
+  /*
+    Aggregate arguments for string result, when some comparison
+    is involved internally, e.g: REPLACE(a,b,c)
+    - convert to @@character_set_connection if all arguments are numbers
+    - disallow DERIVATION_NONE
+  */
+  bool agg_arg_charsets_for_string_result_with_comparison(DTCollation &c,
+                                                          Item **items,
+                                                          uint nitems,
+                                                          int item_sep= 1)
+  {
+    return agg_item_charsets_for_string_result_with_comparison(c, func_name(),
+                                                               items, nitems,
+                                                               item_sep);
+  }
   bool walk(Item_processor processor, bool walk_subquery, uchar *arg);
   Item *transform(Item_transformer transformer, uchar *arg);
   Item* compile(Item_analyzer analyzer, uchar **arg_p,