bk commit into 4.1 tree (evgen:1.2488) BUG#15351

[eugene]

Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2488 06/05/28 22:01:38 evgen@stripped +3 -0
  Fixed bug#15351: Wrong collation used for comparison of md5() and sha()
  argument can lead to a wrong result.
  
  md5() and sha() functions treat their arguments as case sensitive strings.
  But when they are compared their arguments were compared as a case
  insensitive strings which leads to two functions with different arguments
  and thus different results to being identical. This can lead to a wrong
  decision made in the range optimizer and thus lead to a wrong result set.
  
  Item_func_md5::fix_length_and_dec() and Item_func_sha::fix_length_and_dec()
  functions now set binary collation on their arguments.

  mysql-test/t/func_str.test
    1.82 06/05/28 21:59:00 evgen@stripped +12 -0
    Added test case for the bug#15351: Wrong collation used for comparison of md5() and
sha()
    argument can lead to a wrong result.

  mysql-test/r/func_str.result
    1.100 06/05/28 21:58:49 evgen@stripped +15 -0
     Added test case for the bug#15351: Wrong collation used for comparison of md5() and
sha()
    argument can lead to a wrong result.

  sql/item_strfunc.cc
    1.242 06/05/28 21:56:57 evgen@stripped +18 -2
    Fixed bug#15351: Wrong collation used for comparison of md5() and sha()
    argument can lead to a wrong result.
    Item_func_md5::fix_length_and_dec() and Item_func_sha::fix_length_and_dec()
    functions now set binary collation on their arguments.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/15351-bug-4.1-mysql

--- 1.241/sql/item_strfunc.cc	2006-05-08 05:39:19 +04:00
+++ 1.242/sql/item_strfunc.cc	2006-05-28 21:56:57 +04:00
@@ -118,7 +118,15 @@
 
 void Item_func_md5::fix_length_and_dec()
 {
-   max_length=32;
+  max_length=32;
+  /*
+    The MD5() function treats its parameter as being a case sensitive. Thus
+    we set binary collation on it so different instances of MD5() will be
+    compared properly.
+  */
+  args[0]->collation.set(
+      get_charset_by_csname(args[0]->collation.collation->csname,
+                            MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE);
 }
 
 
@@ -159,7 +167,15 @@
 
 void Item_func_sha::fix_length_and_dec()
 {
-   max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash
+  max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash
+  /*
+    The SHA() function treats its parameter as being a case sensitive. Thus
+    we set binary collation on it so different instances of MD5() will be
+    compared properly.
+  */
+  args[0]->collation.set(
+      get_charset_by_csname(args[0]->collation.collation->csname,
+                            MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE);
 }
 
 

--- 1.99/mysql-test/r/func_str.result	2006-05-08 05:38:08 +04:00
+++ 1.100/mysql-test/r/func_str.result	2006-05-28 21:58:49 +04:00
@@ -1006,4 +1006,19 @@
 select ifnull(load_file("lkjlkj"),"it's null");
 ifnull(load_file("lkjlkj"),"it's null")
 it's null
+create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
+insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
+select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
+f1	f2
+test	098f6bcd4621d373cade4e832627b4f6
+select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
+f1	f2
+test	098f6bcd4621d373cade4e832627b4f6
+select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
+f1	f2
+test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
+select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
+f1	f2
+test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
+drop table t1;
 End of 4.1 tests

--- 1.81/mysql-test/t/func_str.test	2006-05-08 05:37:13 +04:00
+++ 1.82/mysql-test/t/func_str.test	2006-05-28 21:59:00 +04:00
@@ -669,4 +669,16 @@
 select load_file("lkjlkj");
 select ifnull(load_file("lkjlkj"),"it's null");
 
+#
+# Bug#15351: Wrong collation used for comparison of md5() and sha()
+# parameter can lead to a wrong result.
+#
+create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
+insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
+select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
+select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
+select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
+select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
+drop table t1;
+
 --echo End of 4.1 tests