From a1c762b9dfbf6bb85f1f06d1e7d07b3b38e5920a Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 8 Apr 2011 17:15:23 +0400 Subject: 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(). --- sql/item_strfunc.cc | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'sql/item_strfunc.cc') diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index ad18d5dabbe..e5c47c110f4 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -1168,7 +1168,7 @@ void Item_func_replace::fix_length_and_dec() char_length+= max_substrs * (uint) diff; } - if (agg_arg_charsets_for_comparison(collation, args, 3)) + if (agg_arg_charsets_for_string_result_with_comparison(collation, args, 3)) return; fix_char_length_ulonglong(char_length); } @@ -1458,7 +1458,7 @@ void Item_func_substr::fix_length_and_dec() void Item_func_substr_index::fix_length_and_dec() { - if (agg_arg_charsets_for_comparison(collation, args, 2)) + if (agg_arg_charsets_for_string_result_with_comparison(collation, args, 2)) return; fix_char_length(args[0]->max_char_length()); } @@ -1797,7 +1797,8 @@ void Item_func_trim::fix_length_and_dec() { // Handle character set for args[1] and args[0]. // Note that we pass args[1] as the first item, and args[0] as the second. - if (agg_arg_charsets_for_comparison(collation, &args[1], 2, -1)) + if (agg_arg_charsets_for_string_result_with_comparison(collation, + &args[1], 2, -1)) return; } fix_char_length(args[0]->max_char_length()); -- cgit v1.2.1