3430 Alexander Barkov 2011-04-08
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().
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
3429 Alexander Nozdrin 2011-04-08
A patch for Bug#12325375: THE SERVER ON WINXP DOES NOT ALLOW CONNECTIONS
IF NO DNS-SERVER AVAILABLE.
The thing is that on Windows XP getnameinfo() returns WSANO_DATA
when hostname-lookup is not available. The problem was that
this error code was treated as serious error and the client
connection got rejected.
The fix is to treat all errors from getnameinfo() as not ciritical,
but add IP-address to the host cache only for EAI_NONAME (or WSANO_DATA).
modified:
include/violite.h
sql/hostname.cc
vio/viosocket.c
=== modified file 'mysql-test/include/ctype_numconv.inc'
--- a/mysql-test/include/ctype_numconv.inc 2011-02-10 13:38:18 +0000
+++ b/mysql-test/include/ctype_numconv.inc 2011-04-08 13:15:23 +0000
@@ -1765,6 +1765,22 @@ SELECT
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
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result 2011-02-10 08:18:08 +0000
+++ b/mysql-test/r/ctype_binary.result 2011-04-08 13:15:23 +0000
@@ -2807,6 +2807,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:32:40 +0000
+++ b/mysql-test/r/ctype_cp1251.result 2011-04-08 13:15:23 +0000
@@ -3199,6 +3199,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:43:28 +0000
+++ b/mysql-test/r/ctype_latin1.result 2011-04-08 13:15:23 +0000
@@ -3226,6 +3226,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-03 15:46:30 +0000
+++ b/mysql-test/r/ctype_ucs.result 2011-04-08 13:15:23 +0000
@@ -4060,6 +4060,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:43:28 +0000
+++ b/mysql-test/r/ctype_utf8.result 2011-04-08 13:15:23 +0000
@@ -4938,6 +4938,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-08 08:09:24 +0000
+++ b/sql/item.cc 2011-04-08 13:15:23 +0000
@@ -1746,7 +1746,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-04-08 08:09:24 +0000
+++ b/sql/item.h 2011-04-08 13:15:23 +0000
@@ -53,6 +53,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
@@ -62,6 +64,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)
@@ -1561,7 +1564,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
@@ -1574,6 +1578,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:33:35 +0000
+++ b/sql/item_func.cc 2011-04-08 13:15:23 +0000
@@ -2537,7 +2537,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-08 17:39:25 +0000
+++ b/sql/item_func.h 2011-04-08 13:15:23 +0000
@@ -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,
=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc 2011-03-03 15:46:30 +0000
+++ b/sql/item_strfunc.cc 2011-04-08 13:15:23 +0000
@@ -1168,7 +1168,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);
}
@@ -1458,7 +1458,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());
}
@@ -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());
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.5 branch (alexander.barkov:3429 to 3430) Bug#60625Bug#11926811 | Alexander Barkov | 8 Apr |
