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.

8 files changed, 149 insertions, 50 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 2a2fe50ad0f..d144e84dfdc 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -2558,3 +2558,32 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 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;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index f64d58ff8f4..c26a324d00b 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -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.
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 66b9eabd385..8942b0a2faf 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -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;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 4870f4836df..b0930a42b41 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -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.
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 183a628f8e4..b6d3f45f4c2 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1720,68 +1720,65 @@ String *Item_func_encrypt::val_str(String *str)
 #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;
 }
 
 
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 2cdb45100ae..59241872e63 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -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_encode
 {
 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 *);
 };
 
 
diff --git a/sql/sql_crypt.cc b/sql/sql_crypt.cc
index c4f93cc2a33..3d7d248782b 100644
--- a/sql/sql_crypt.cc
+++ b/sql/sql_crypt.cc
@@ -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]);
diff --git a/sql/sql_crypt.h b/sql/sql_crypt.h
index a5a6bee8a58..2b56c387bd1 100644
--- a/sql/sql_crypt.h
+++ b/sql/sql_crypt.h
@@ -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);
 };