#At file:///home/bar/mysql-bzr/mysql-trunk.b60625/ based on revid:alfranio.correia@stripped
3328 Alexander Barkov 2011-04-05
Bug#11926811 / Bug#60625 Illegal mix of collations
Problem: comparison of a DATETIME sp variable and NOW()
lead 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().
modified:
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
sql/item.cc
sql/item.h
sql/item_func.cc
sql/item_func.h
sql/item_strfunc.cc
=== modified file 'mysql-test/include/ctype_numconv.inc'
--- a/mysql-test/include/ctype_numconv.inc 2011-02-10 13:41:16 +0000
+++ b/mysql-test/include/ctype_numconv.inc 2011-04-05 09:04:24 +0000
@@ -1776,7 +1776,23 @@ 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 # Bug#11926811 / Bug#60625 Illegal mix of collations
+--echo #
+SELECT @@collation_connection;
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+ SELECT v_LastPaymentDate < NOW();
+ EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+ SHOW WARNINGS;
+ EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+DELIMITER ;//
+CALL p1;
+DROP PROCEDURE p1;
--echo #
--echo # Bug#52159 returning time type from function and empty left join causes debug assertion
@@ -1797,3 +1813,5 @@ SELECT maketime(`a`,`a`,`a`) FROM t1 GRO
--enable_warnings
DROP TABLE t1;
SET sql_mode=default;
+
+
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result 2011-02-10 08:47:05 +0000
+++ b/mysql-test/r/ctype_binary.result 2011-04-05 09:04:24 +0000
@@ -2830,6 +2830,32 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00'
field_str1 field1_str2 field_date field_datetime
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
+# Bug#11926811 / Bug#60625 Illegal mix of collations
+#
+SELECT @@collation_connection;
+@@collation_connection
+binary
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+SELECT v_LastPaymentDate < NOW();
+EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+SHOW WARNINGS;
+EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+CALL p1;
+v_LastPaymentDate < NOW()
+NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Level Code Message
+Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select concat(v_LastPaymentDate@0,now()) AS `CONCAT(v_LastPaymentDate, NOW())`
+DROP PROCEDURE p1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result 2011-02-18 07:43:02 +0000
+++ b/mysql-test/r/ctype_cp1251.result 2011-04-05 09:04:24 +0000
@@ -3222,6 +3222,32 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00'
field_str1 field1_str2 field_date field_datetime
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
+# Bug#11926811 / Bug#60625 Illegal mix of collations
+#
+SELECT @@collation_connection;
+@@collation_connection
+cp1251_general_ci
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+SELECT v_LastPaymentDate < NOW();
+EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+SHOW WARNINGS;
+EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+CALL p1;
+v_LastPaymentDate < NOW()
+NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Level Code Message
+Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select concat(convert(v_LastPaymentDate@0 using cp1251),now()) AS `CONCAT(v_LastPaymentDate, NOW())`
+DROP PROCEDURE p1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result 2011-03-04 15:55:18 +0000
+++ b/mysql-test/r/ctype_latin1.result 2011-04-05 09:04:24 +0000
@@ -3249,6 +3249,32 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00'
field_str1 field1_str2 field_date field_datetime
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
+# Bug#11926811 / Bug#60625 Illegal mix of collations
+#
+SELECT @@collation_connection;
+@@collation_connection
+latin1_swedish_ci
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+SELECT v_LastPaymentDate < NOW();
+EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+SHOW WARNINGS;
+EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+CALL p1;
+v_LastPaymentDate < NOW()
+NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Level Code Message
+Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select concat(v_LastPaymentDate@0,now()) AS `CONCAT(v_LastPaymentDate, NOW())`
+DROP PROCEDURE p1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result 2011-03-04 14:59:32 +0000
+++ b/mysql-test/r/ctype_ucs.result 2011-04-05 09:04:24 +0000
@@ -4149,6 +4149,32 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00'
field_str1 field1_str2 field_date field_datetime
0032003000300037002D00300038002D00300032002000320033003A00350039003A00300030 0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
+# Bug#11926811 / Bug#60625 Illegal mix of collations
+#
+SELECT @@collation_connection;
+@@collation_connection
+ucs2_general_ci
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+SELECT v_LastPaymentDate < NOW();
+EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+SHOW WARNINGS;
+EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+CALL p1;
+v_LastPaymentDate < NOW()
+NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Level Code Message
+Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select concat(convert(v_LastPaymentDate@0 using ucs2),convert(now() using ucs2)) AS `CONCAT(v_LastPaymentDate, NOW())`
+DROP PROCEDURE p1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result 2011-03-04 15:55:18 +0000
+++ b/mysql-test/r/ctype_utf8.result 2011-04-05 09:04:24 +0000
@@ -5052,6 +5052,32 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00'
field_str1 field1_str2 field_date field_datetime
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
+# Bug#11926811 / Bug#60625 Illegal mix of collations
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf8_general_ci
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
+SELECT v_LastPaymentDate < NOW();
+EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
+SHOW WARNINGS;
+EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
+END//
+CALL p1;
+v_LastPaymentDate < NOW()
+NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Level Code Message
+Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select concat(convert(v_LastPaymentDate@0 using utf8),now()) AS `CONCAT(v_LastPaymentDate, NOW())`
+DROP PROCEDURE p1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2011-04-04 08:47:25 +0000
+++ b/sql/item.cc 2011-04-05 09:04:24 +0000
@@ -1765,7 +1765,8 @@ bool agg_item_collations(DTCollation &c,
}
/* If all arguments where numbers, reset to @@collation_connection */
- if (c.derivation == DERIVATION_NUMERIC)
+ if (flags & MY_COLL_ALLOW_NUMERIC_CONV &&
+ c.derivation == DERIVATION_NUMERIC)
c.set(Item::default_charset(), DERIVATION_COERCIBLE, MY_REPERTOIRE_NUMERIC);
return FALSE;
=== modified file 'sql/item.h'
--- a/sql/item.h 2011-03-22 11:44:40 +0000
+++ b/sql/item.h 2011-04-05 09:04:24 +0000
@@ -49,6 +49,8 @@ char_to_byte_length_safe(uint32 char_len
(i.e. constant).
MY_COLL_ALLOW_CONV - allow any kind of conversion
(combination of the above two)
+ MY_COLL_ALLOW_NUMERIC_CONV - if all items were numbers, convert to
+ @@character_set_connection
MY_COLL_DISALLOW_NONE - don't allow return DERIVATION_NONE
(e.g. when aggregating for comparison)
MY_COLL_CMP_CONV - combination of MY_COLL_ALLOW_CONV
@@ -58,6 +60,7 @@ char_to_byte_length_safe(uint32 char_len
#define MY_COLL_ALLOW_SUPERSET_CONV 1
#define MY_COLL_ALLOW_COERCIBLE_CONV 2
#define MY_COLL_DISALLOW_NONE 4
+#define MY_COLL_ALLOW_NUMERIC_CONV 8
#define MY_COLL_ALLOW_CONV (MY_COLL_ALLOW_SUPERSET_CONV | MY_COLL_ALLOW_COERCIBLE_CONV)
#define MY_COLL_CMP_CONV (MY_COLL_ALLOW_CONV | MY_COLL_DISALLOW_NONE)
@@ -1609,7 +1612,8 @@ agg_item_charsets_for_string_result(DTCo
int item_sep= 1)
{
uint flags= MY_COLL_ALLOW_SUPERSET_CONV |
- MY_COLL_ALLOW_COERCIBLE_CONV;
+ MY_COLL_ALLOW_COERCIBLE_CONV |
+ MY_COLL_ALLOW_NUMERIC_CONV;
return agg_item_charsets(c, name, items, nitems, flags, item_sep);
}
inline bool
@@ -1622,6 +1626,19 @@ agg_item_charsets_for_comparison(DTColla
MY_COLL_DISALLOW_NONE;
return agg_item_charsets(c, name, items, nitems, flags, item_sep);
}
+inline bool
+agg_item_charsets_for_string_result_with_comparison(DTCollation &c,
+ const char *name,
+ Item **items, uint nitems,
+ int item_sep= 1)
+{
+ uint flags= MY_COLL_ALLOW_SUPERSET_CONV |
+ MY_COLL_ALLOW_COERCIBLE_CONV |
+ MY_COLL_ALLOW_NUMERIC_CONV |
+ MY_COLL_DISALLOW_NONE;
+ return agg_item_charsets(c, name, items, nitems, flags, item_sep);
+}
+
class Item_num: public Item_basic_constant
{
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2011-03-28 13:38:03 +0000
+++ b/sql/item_func.cc 2011-04-05 09:04:24 +0000
@@ -2550,7 +2550,8 @@ void Item_func_min_max::fix_length_and_d
}
if (cmp_type == STRING_RESULT)
{
- agg_arg_charsets_for_comparison(collation, args, arg_count);
+ agg_arg_charsets_for_string_result_with_comparison(collation,
+ args, arg_count);
if (datetime_found)
{
thd= current_thd;
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2011-03-22 11:44:40 +0000
+++ b/sql/item_func.h 2011-04-05 09:04:24 +0000
@@ -164,6 +164,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)
@@ -171,6 +176,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)
@@ -178,6 +188,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,
=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc 2011-03-24 10:37:16 +0000
+++ b/sql/item_strfunc.cc 2011-04-05 09:04:24 +0000
@@ -1261,7 +1261,7 @@ void Item_func_replace::fix_length_and_d
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);
}
@@ -1551,7 +1551,7 @@ void Item_func_substr::fix_length_and_de
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());
}
@@ -1890,7 +1890,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());
Attachment: [text/bzr-bundle] bzr/alexander.barkov@oracle.com-20110405090424-geq9dw50t4tco7jd.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (alexander.barkov:3328) Bug#60625Bug#11926811 | Alexander Barkov | 5 Apr |
