| Bug #69352 | upper(), lower() error in some case | ||
|---|---|---|---|
| Submitted: | 30 May 2013 13:44 | Modified: | 9 Sep 2013 9:28 |
| Reporter: | jiang xiaobing | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | mysql5.1 above | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2013 14:49]
MySQL Verification Team
Hello jiang, Thank you for the report. Verified as described. Thanks, Umesh
[31 May 2013 14:50]
MySQL Verification Team
// 5.5.32 is affected
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.32 |
+-----------+
1 row in set (0.00 sec)
mysql> select @urs:='abc@163.com',@urs1:=substring_index(@urs,'@',-1),upper(substring_index(@urs,'@',-1));
+---------------------+-------------------------------------+-------------------------------------+
| @urs:='abc@163.com' | @urs1:=substring_index(@urs,'@',-1) | upper(substring_index(@urs,'@',-1)) |
+---------------------+-------------------------------------+-------------------------------------+
| abc@163.com | 163.com | 163 COM |
+---------------------+-------------------------------------+-------------------------------------+
1 row in set (0.00 sec)
mysql> select @urs:='abc@163.com',@urs1:=substring_index(@urs,'@',-1),substring_index(@urs,'@',-1);
+---------------------+-------------------------------------+------------------------------+
| @urs:='abc@163.com' | @urs1:=substring_index(@urs,'@',-1) | substring_index(@urs,'@',-1) |
+---------------------+-------------------------------------+------------------------------+
| abc@163.com | 163.com | 163.com |
+---------------------+-------------------------------------+------------------------------+
1 row in set (0.00 sec)
mysql> select upper('163.com');
+------------------+
| upper('163.com') |
+------------------+
| 163.COM |
+------------------+
1 row in set (0.00 sec)
// 5.6.12 is not affected
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.6.12-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> select @urs:='abc@163.com',@urs1:=substring_index(@urs,'@',-1),upper(substring_index(@urs,'@',-1));
+---------------------+-------------------------------------+-------------------------------------+
| @urs:='abc@163.com' | @urs1:=substring_index(@urs,'@',-1) | upper(substring_index(@urs,'@',-1)) |
+---------------------+-------------------------------------+-------------------------------------+
| abc@163.com | 163.com | 163.COM |
+---------------------+-------------------------------------+-------------------------------------+
1 row in set (0.02 sec)
[31 May 2013 14:56]
MySQL Verification Team
// 5.5.32 lower()
mysql> select @urs:='ABC@163.COM',@urs1:=substring_index(@urs,'@',-1),lower(substring_index(@urs,'@',-1));
+---------------------+-------------------------------------+-------------------------------------+
| @urs:='ABC@163.COM' | @urs1:=substring_index(@urs,'@',-1) | lower(substring_index(@urs,'@',-1)) |
+---------------------+-------------------------------------+-------------------------------------+
| ABC@163.COM | 163.COM | 163 com |
+---------------------+-------------------------------------+-------------------------------------+
mysql> select lower('163.COM');
+------------------+
| lower('163.COM') |
+------------------+
| 163.com |
+------------------+
1 row in set (0.00 sec)
[7 Sep 2013 12:42]
Valeriy Kravchuk
This is still repeatable in 5.5.33, but seems fixed in 5.6.13:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @urs:='abc@163.com',@urs1:=substring_index(@urs,'@',-1),upper(subs
tring_index(@urs,'@',-1));
+---------------------+-------------------------------------+-------------------
------------------+
| @urs:='abc@163.com' | @urs1:=substring_index(@urs,'@',-1) | upper(substring_in
dex(@urs,'@',-1)) |
+---------------------+-------------------------------------+-------------------
------------------+
| abc@163.com | 163.com | 163.COM
|
+---------------------+-------------------------------------+-------------------
------------------+
1 row in set (0.17 sec)
[9 Sep 2013 7:43]
Manyi Lu
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Fixed in 5.6.
[9 Sep 2013 9:28]
jiang xiaobing
where is the changelog about this bug?
Description: when using upper(), lower(), this is a bug at some special case. How to repeat: mysql> select @urs:='abc@163.com',@urs1:=substring_index(@urs,'@',-1),upper(substring_index(@urs,'@',-1)); +---------------------+-------------------------------------+-------------------------------------+ | @urs:='abc@163.com' | @urs1:=substring_index(@urs,'@',-1) | upper(substring_index(@urs,'@',-1)) | +---------------------+-------------------------------------+-------------------------------------+ | abc@163.com | 163.com | 163 COM | +---------------------+-------------------------------------+-------------------------------------+ 1 row in set (0.00 sec) Suggested fix: when a function will modify the value, it should not using the str String. because when it be called, it will recursively call Item_xx_xx::val_str(). this patch just fix this issue, other similar function should be reviewed. === modified file 'sql/item_strfunc.cc' --- sql/item_strfunc.cc 2012-08-06 05:10:03 +0000 +++ sql/item_strfunc.cc 2013-05-30 13:12:48 +0000 @@ -1261,25 +1261,13 @@ return 0; /* purecov: inspected */ } null_value=0; - if (multiply == 1) - { - uint len; - res= copy_if_not_alloced(str,res,res->length()); - len= converter(collation.collation, (char*) res->ptr(), res->length(), - (char*) res->ptr(), res->length()); - DBUG_ASSERT(len <= res->length()); - res->length(len); - } - else - { - uint len= res->length() * multiply; - tmp_value.alloc(len); - tmp_value.set_charset(collation.collation); - len= converter(collation.collation, (char*) res->ptr(), res->length(), - (char*) tmp_value.ptr(), len); - tmp_value.length(len); - res= &tmp_value; - } + uint len= res->length() * multiply; + tmp_value.alloc(len); + tmp_value.set_charset(collation.collation); + len= converter(collation.collation, (char*) res->ptr(), res->length(), + (char*) tmp_value.ptr(), len); + tmp_value.length(len); + res= &tmp_value; return res; }