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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql5.1 above OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[30 May 2013 13:44] jiang xiaobing
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;
 }
[31 May 2013 14:49] Umesh Shastry
Hello jiang,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[31 May 2013 14:50] Umesh Shastry
// 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] Umesh Shastry
// 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?