List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:December 4 2009 3:37pm
Subject:bzr commit into mysql-5.1-bugteam branch (davi:3245) Bug#49141
View as plain text  
# At a local mysql-5.1-bugteam repository of davi

 3245 Davi Arnaut	2009-12-04
      Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
      
      The problem was that the multiple evaluations of a ENCODE or
      DECODE function within a single statement caused the random
      generator to be reinitialized at each evaluation, even though
      the parameters were constants.
      
      The solution is to initialize the random generator only once
      if the password (seed) parameter is constant.
      
      This patch borrows code and ideas from Georgi Kodinov's patch.
     @ mysql-test/r/func_str.result
        Add test case result.
     @ mysql-test/r/ps.result
        Add test case result.
     @ mysql-test/t/func_str.test
        Add test case for Bug#49141
     @ mysql-test/t/ps.test
        Add test case for Bug#49141
     @ sql/item_strfunc.cc
        Move seed generation code to a separate method.
        Seed only once if the password (seed) argument
        is constant.
        Remove duplicated code and use a transform method
        to apply encoding or decoding.
     @ sql/item_strfunc.h
        Add parameter to signal whether the PRNG is already seeded.
        Introduce transform method.
        Combine val_str methods.
     @ sql/sql_crypt.cc
        Remove method.
     @ sql/sql_crypt.h
        Seed is supplied as two long integers.

    modified:
      mysql-test/r/func_str.result
      mysql-test/r/ps.result
      mysql-test/t/func_str.test
      mysql-test/t/ps.test
      sql/item_strfunc.cc
      sql/item_strfunc.h
      sql/sql_crypt.cc
      sql/sql_crypt.h
=== modified file 'mysql-test/r/func_str.result'
--- a/mysql-test/r/func_str.result	2009-09-10 10:30:03 +0000
+++ b/mysql-test/r/func_str.result	2009-12-04 15:36:58 +0000
@@ -2558,3 +2558,32 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
 drop table t1;
+#
+# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
+#
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (a VARCHAR(20), b INT);
+CREATE TABLE t2 (a VARCHAR(20), b INT);
+INSERT INTO t1 VALUES ('ABC', 1);
+INSERT INTO t2 VALUES ('ABC', 1);
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+secret
+SELECT DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+secret
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
+FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
+secret
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+INSERT INTO t1 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
+INSERT INTO t2 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
+FROM t2 WHERE t2.b = 1 GROUP BY t2.b;
+DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
+secret
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/ps.result'
--- a/mysql-test/r/ps.result	2009-12-03 13:24:50 +0000
+++ b/mysql-test/r/ps.result	2009-12-04 15:36:58 +0000
@@ -2947,4 +2947,23 @@ execute stmt;
 Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
 drop table t1;
 deallocate prepare stmt;
+#
+# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
+#
+prepare encode from "select encode(?, ?) into @ciphertext";
+prepare decode from "select decode(?, ?) into @plaintext";
+set @str="abc", @key="cba";
+execute encode using @str, @key;
+execute decode using @ciphertext, @key;
+select @plaintext;
+@plaintext
+abc
+set @str="bcd", @key="dcb";
+execute encode using @str, @key;
+execute decode using @ciphertext, @key;
+select @plaintext;
+@plaintext
+bcd
+deallocate prepare encode;
+deallocate prepare decode;
 End of 5.1 tests.

=== modified file 'mysql-test/t/func_str.test'
--- a/mysql-test/t/func_str.test	2009-09-10 10:30:03 +0000
+++ b/mysql-test/t/func_str.test	2009-12-04 15:36:58 +0000
@@ -1318,3 +1318,37 @@ insert into t1 values (-1),(null);
 explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a;
 explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a;
 drop table t1;
+
+--echo #
+--echo # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (a VARCHAR(20), b INT);
+CREATE TABLE t2 (a VARCHAR(20), b INT);
+
+INSERT INTO t1 VALUES ('ABC', 1);
+INSERT INTO t2 VALUES ('ABC', 1);
+
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+  FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+
+SELECT DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
+  FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
+  FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
+
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+
+INSERT INTO t1 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
+INSERT INTO t2 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
+
+SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
+  FROM t2 WHERE t2.b = 1 GROUP BY t2.b;
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/ps.test'
--- a/mysql-test/t/ps.test	2009-12-03 13:24:50 +0000
+++ b/mysql-test/t/ps.test	2009-12-04 15:36:58 +0000
@@ -3032,5 +3032,21 @@ execute stmt;
 drop table t1;
 deallocate prepare stmt;
 
+--echo #
+--echo # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
+--echo #
+
+prepare encode from "select encode(?, ?) into @ciphertext";
+prepare decode from "select decode(?, ?) into @plaintext";
+set @str="abc", @key="cba";
+execute encode using @str, @key;
+execute decode using @ciphertext, @key;
+select @plaintext;
+set @str="bcd", @key="dcb";
+execute encode using @str, @key;
+execute decode using @ciphertext, @key;
+select @plaintext;
+deallocate prepare encode;
+deallocate prepare decode;
 
 --echo End of 5.1 tests.

=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc	2009-11-18 14:50:31 +0000
+++ b/sql/item_strfunc.cc	2009-12-04 15:36:58 +0000
@@ -1720,68 +1720,65 @@ String *Item_func_encrypt::val_str(Strin
 #endif	/* HAVE_CRYPT */
 }
 
+bool Item_func_encode::seed()
+{
+  char buf[80];
+  ulong rand_nr[2];
+  String *key, tmp(buf, sizeof(buf), system_charset_info);
+
+  if (!(key= args[1]->val_str(&tmp)))
+    return TRUE;
+
+  hash_password(rand_nr, key->ptr(), key->length());
+  sql_crypt.init(rand_nr);
+
+  return FALSE;
+}
+
 void Item_func_encode::fix_length_and_dec()
 {
   max_length=args[0]->max_length;
   maybe_null=args[0]->maybe_null || args[1]->maybe_null;
   collation.set(&my_charset_bin);
+  /* Precompute the seed state if the item is constant. */
+  seeded= args[1]->const_item() &&
+          (args[1]->result_type() == STRING_RESULT) && !seed();
 }
 
 String *Item_func_encode::val_str(String *str)
 {
   String *res;
-  char pw_buff[80];
-  String tmp_pw_value(pw_buff, sizeof(pw_buff), system_charset_info);
-  String *password;
   DBUG_ASSERT(fixed == 1);
 
   if (!(res=args[0]->val_str(str)))
   {
-    null_value=1; /* purecov: inspected */
-    return 0; /* purecov: inspected */
+    null_value= 1;
+    return NULL;
   }
 
-  if (!(password=args[1]->val_str(& tmp_pw_value)))
+  if (!seeded && seed())
   {
-    null_value=1;
-    return 0;
+    null_value= 1;
+    return NULL;
   }
 
-  null_value=0;
-  res=copy_if_not_alloced(str,res,res->length());
-  SQL_CRYPT sql_crypt(password->ptr(), password->length());
-  sql_crypt.init();
-  sql_crypt.encode((char*) res->ptr(),res->length());
-  res->set_charset(&my_charset_bin);
+  null_value= 0;
+  res= copy_if_not_alloced(str, res, res->length());
+  transform(res);
+  sql_crypt.reinit();
+
   return res;
 }
 
-String *Item_func_decode::val_str(String *str)
+void Item_func_encode::transform(String *res)
 {
-  String *res;
-  char pw_buff[80];
-  String tmp_pw_value(pw_buff, sizeof(pw_buff), system_charset_info);
-  String *password;
-  DBUG_ASSERT(fixed == 1);
-
-  if (!(res=args[0]->val_str(str)))
-  {
-    null_value=1; /* purecov: inspected */
-    return 0; /* purecov: inspected */
-  }
-
-  if (!(password=args[1]->val_str(& tmp_pw_value)))
-  {
-    null_value=1;
-    return 0;
-  }
+  sql_crypt.encode((char*) res->ptr(),res->length());
+  res->set_charset(&my_charset_bin);
+}
 
-  null_value=0;
-  res=copy_if_not_alloced(str,res,res->length());
-  SQL_CRYPT sql_crypt(password->ptr(), password->length());
-  sql_crypt.init();
+void Item_func_decode::transform(String *res)
+{
   sql_crypt.decode((char*) res->ptr(),res->length());
-  return res;
 }
 
 

=== modified file 'sql/item_strfunc.h'
--- a/sql/item_strfunc.h	2009-06-01 12:00:38 +0000
+++ b/sql/item_strfunc.h	2009-12-04 15:36:58 +0000
@@ -351,12 +351,22 @@ public:
 
 class Item_func_encode :public Item_str_func
 {
+private:
+  /** Whether the PRNG has already been seeded. */
+  bool seeded;
+protected:
+  SQL_CRYPT sql_crypt;
 public:
   Item_func_encode(Item *a, Item *seed):
     Item_str_func(a, seed) {}
   String *val_str(String *);
   void fix_length_and_dec();
   const char *func_name() const { return "encode"; }
+protected:
+  virtual void transform(String *);
+private:
+  /** Provide a seed for the PRNG sequence. */
+  bool seed();
 };
 
 
@@ -364,8 +374,9 @@ class Item_func_decode :public Item_func
 {
 public:
   Item_func_decode(Item *a, Item *seed): Item_func_encode(a, seed) {}
-  String *val_str(String *);
   const char *func_name() const { return "decode"; }
+protected:
+  void transform(String *);
 };
 
 

=== modified file 'sql/sql_crypt.cc'
--- a/sql/sql_crypt.cc	2009-04-23 07:43:42 +0000
+++ b/sql/sql_crypt.cc	2009-12-04 15:36:58 +0000
@@ -28,14 +28,7 @@
 
 #include "mysql_priv.h"
 
-SQL_CRYPT::SQL_CRYPT(const char *password, uint length)
-{
-  ulong rand_nr[2];
-  hash_password(rand_nr,password, length);
-  crypt_init(rand_nr);
-}
-
-void SQL_CRYPT::crypt_init(ulong *rand_nr)
+void SQL_CRYPT::init(ulong *rand_nr)
 {
   uint i;
   randominit(&rand,rand_nr[0],rand_nr[1]);

=== modified file 'sql/sql_crypt.h'
--- a/sql/sql_crypt.h	2009-04-23 07:43:42 +0000
+++ b/sql/sql_crypt.h	2009-12-04 15:36:58 +0000
@@ -23,15 +23,15 @@ class SQL_CRYPT :public Sql_alloc
   struct rand_struct rand,org_rand;
   char decode_buff[256],encode_buff[256];
   uint shift;
-  void crypt_init(ulong *seed);
  public:
-  SQL_CRYPT(const char *seed, uint length);
+  SQL_CRYPT() {}
   SQL_CRYPT(ulong *seed)
   {
-    crypt_init(seed);
+    init(seed);
   }
   ~SQL_CRYPT() {}
-  void init() { shift=0; rand=org_rand; }
+  void init(ulong *seed);
+  void reinit() { shift=0; rand=org_rand; }
   void encode(char *str, uint length);
   void decode(char *str, uint length);
 };


Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20091204153658-fcgo12synkn0qdur.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (davi:3245) Bug#49141Davi Arnaut4 Dec