From 9f837c6e1a54ea05be92112fe7520ffe0134b260 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 22 Sep 2016 10:03:12 +0400 Subject: MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) --- mysql-test/r/func_compress.result | 24 ++++++++++++ mysql-test/r/func_crypt.result | 18 +++++++++ mysql-test/r/func_str.result | 18 +++++++++ mysql-test/t/func_compress.test | 19 ++++++++++ mysql-test/t/func_crypt.test | 12 ++++++ mysql-test/t/func_str.test | 12 ++++++ sql/item_strfunc.h | 80 +++++++++++++++++++++++++++++---------- 7 files changed, 163 insertions(+), 20 deletions(-) diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index e9c8193685b..1f15c74ff0d 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -160,3 +160,27 @@ set global max_allowed_packet=default; # # End of 5.5 tests # +# +# Start of 10.1 tests +# +# +# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',compress('test')), ('TEST', compress('TEST')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); +f1 HEX(f2) +test 04000000789C2B492D2E0100045D01C1 +TEST 04000000789C0B710D0E0100031D0141 +SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); +f1 HEX(f2) +TEST 04000000789C0B710D0E0100031D0141 +test 04000000789C2B492D2E0100045D01C1 +SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("TEST") OR f2= compress("test")); +f1 HEX(f2) +TEST 04000000789C0B710D0E0100031D0141 +test 04000000789C2B492D2E0100045D01C1 +DROP TABLE t1; +# +# End of 10.1 tests +# diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index c3d7bf67859..a1c33f827c7 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -166,5 +166,23 @@ old_password(CONVERT('foo' USING latin1)) DEALLOCATE PREPARE stmt; # End of func_str_ascii_checksum.inc # +# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',encrypt('test','key')), ('TEST', encrypt('TEST','key')); +SELECT f1 FROM t1 ignore index(k1) WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key')); +f1 +test +TEST +SELECT f1 FROM t1 WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key')); +f1 +TEST +test +SELECT f1 FROM t1 WHERE f1='test' AND (f2= encrypt('TEST','key') OR f2= encrypt('test','key')); +f1 +TEST +test +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 04c6a2bfc0a..d32efe642bb 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -4638,5 +4638,23 @@ Zm9v DEALLOCATE PREPARE stmt; # End of func_str_ascii_checksum.inc # +# MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +# +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(128), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('YQ==',from_base64('YQ==')), ('Yq==', from_base64('Yq==')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); +f1 HEX(f2) +YQ== 61 +Yq== 62 +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 2a06769b2ef..6305df3952d 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -152,3 +152,22 @@ set global max_allowed_packet=default; --echo # --echo # End of 5.5 tests --echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +--echo # + +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',compress('test')), ('TEST', compress('TEST')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("test") OR f2= compress("TEST")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='test' AND (f2= compress("TEST") OR f2= compress("test")); +DROP TABLE t1; + +--echo # +--echo # End of 10.1 tests +--echo # diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index 1504051d91e..dc4bf4663d5 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -81,6 +81,18 @@ DROP TABLE t1; --let func=old_password --source include/func_str_ascii_checksum.inc +--echo # +--echo # MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +--echo # + +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('test',encrypt('test','key')), ('TEST', encrypt('TEST','key')); +SELECT f1 FROM t1 ignore index(k1) WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key')); +SELECT f1 FROM t1 WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key')); +SELECT f1 FROM t1 WHERE f1='test' AND (f2= encrypt('TEST','key') OR f2= encrypt('test','key')); +DROP TABLE t1; + + --echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 98af37053f2..48872edcd4b 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1789,6 +1789,18 @@ DROP TABLE t1; --let func=to_base64 --source include/func_str_ascii_checksum.inc +--echo # +--echo # MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST')) +--echo # + +CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(128), UNIQUE KEY k1 (f1,f2)); +INSERT INTO t1 VALUES ('YQ==',from_base64('YQ==')), ('Yq==', from_base64('Yq==')); +SELECT f1,HEX(f2) FROM t1 ignore index(k1) WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("YQ==") OR f2= from_base64("Yq==")); +SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); +DROP TABLE t1; + + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index db0509e17d7..a56e100a956 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -97,7 +97,9 @@ public: /** - Functions returning a checksum or a hash of the argument. + Functions that return a checksum or a hash of the argument, + or somehow else encode or decode the argument, + returning an ASCII-repertoire string. */ class Item_str_ascii_checksum_func: public Item_str_ascii_func { @@ -114,6 +116,29 @@ public: }; +/** + Functions that return a checksum or a hash of the argument, + or somehow else encode or decode the argument, + returning a binary string. +*/ +class Item_str_binary_checksum_func: public Item_str_func +{ +public: + Item_str_binary_checksum_func(THD *thd, Item *a) + :Item_str_func(thd, a) { } + Item_str_binary_checksum_func(THD *thd, Item *a, Item *b) + :Item_str_func(thd, a, b) { } + bool eq(const Item *item, bool binary_cmp) const + { + /* + Always use binary argument comparison: + FROM_BASE64('test') != FROM_BASE64('TEST') + */ + return Item_func::eq(item, true); + } +}; + + class Item_func_md5 :public Item_str_ascii_checksum_func { String tmp_value; @@ -158,11 +183,12 @@ public: const char *func_name() const { return "to_base64"; } }; -class Item_func_from_base64 :public Item_str_func +class Item_func_from_base64 :public Item_str_binary_checksum_func { String tmp_value; public: - Item_func_from_base64(THD *thd, Item *a): Item_str_func(thd, a) {} + Item_func_from_base64(THD *thd, Item *a) + :Item_str_binary_checksum_func(thd, a) { } String *val_str(String *); void fix_length_and_dec(); const char *func_name() const { return "from_base64"; } @@ -170,7 +196,7 @@ public: #include -class Item_aes_crypt :public Item_str_func +class Item_aes_crypt :public Item_str_binary_checksum_func { enum { AES_KEY_LENGTH = 128 }; void create_key(String *user_key, uchar* key); @@ -178,7 +204,8 @@ class Item_aes_crypt :public Item_str_func protected: int what; public: - Item_aes_crypt(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {} + Item_aes_crypt(THD *thd, Item *a, Item *b) + :Item_str_binary_checksum_func(thd, a, b) {} String *val_str(String *); }; @@ -484,12 +511,14 @@ public: -class Item_func_des_encrypt :public Item_str_func +class Item_func_des_encrypt :public Item_str_binary_checksum_func { String tmp_value,tmp_arg; public: - Item_func_des_encrypt(THD *thd, Item *a): Item_str_func(thd, a) {} - Item_func_des_encrypt(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {} + Item_func_des_encrypt(THD *thd, Item *a) + :Item_str_binary_checksum_func(thd, a) {} + Item_func_des_encrypt(THD *thd, Item *a, Item *b) + :Item_str_binary_checksum_func(thd, a, b) {} String *val_str(String *); void fix_length_and_dec() { @@ -500,12 +529,14 @@ public: const char *func_name() const { return "des_encrypt"; } }; -class Item_func_des_decrypt :public Item_str_func +class Item_func_des_decrypt :public Item_str_binary_checksum_func { String tmp_value; public: - Item_func_des_decrypt(THD *thd, Item *a): Item_str_func(thd, a) {} - Item_func_des_decrypt(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {} + Item_func_des_decrypt(THD *thd, Item *a) + :Item_str_binary_checksum_func(thd, a) {} + Item_func_des_decrypt(THD *thd, Item *a, Item *b) + :Item_str_binary_checksum_func(thd, a, b) {} String *val_str(String *); void fix_length_and_dec() { @@ -518,7 +549,13 @@ public: const char *func_name() const { return "des_decrypt"; } }; -class Item_func_encrypt :public Item_str_func + +/** + QQ: Item_func_encrypt should derive from Item_str_ascii_checksum_func. + However, it should be fixed to handle UCS2, UTF16, UTF32 properly first, + as the underlying crypt() call expects a null-terminated input string. +*/ +class Item_func_encrypt :public Item_str_binary_checksum_func { String tmp_value; @@ -528,11 +565,12 @@ class Item_func_encrypt :public Item_str_func collation.set(&my_charset_bin); } public: - Item_func_encrypt(THD *thd, Item *a): Item_str_func(thd, a) + Item_func_encrypt(THD *thd, Item *a): Item_str_binary_checksum_func(thd, a) { constructor_helper(); } - Item_func_encrypt(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) + Item_func_encrypt(THD *thd, Item *a, Item *b) + :Item_str_binary_checksum_func(thd, a, b) { constructor_helper(); } @@ -548,7 +586,7 @@ public: #include "sql_crypt.h" -class Item_func_encode :public Item_str_func +class Item_func_encode :public Item_str_binary_checksum_func { private: /** Whether the PRNG has already been seeded. */ @@ -557,7 +595,7 @@ protected: SQL_CRYPT sql_crypt; public: Item_func_encode(THD *thd, Item *a, Item *seed_arg): - Item_str_func(thd, a, seed_arg) {} + Item_str_binary_checksum_func(thd, a, seed_arg) {} String *val_str(String *); void fix_length_and_dec(); const char *func_name() const { return "encode"; } @@ -1172,21 +1210,23 @@ public: #define ZLIB_DEPENDED_FUNCTION { null_value=1; return 0; } #endif -class Item_func_compress: public Item_str_func +class Item_func_compress: public Item_str_binary_checksum_func { String buffer; public: - Item_func_compress(THD *thd, Item *a): Item_str_func(thd, a) {} + Item_func_compress(THD *thd, Item *a) + :Item_str_binary_checksum_func(thd, a) {} void fix_length_and_dec(){max_length= (args[0]->max_length*120)/100+12;} const char *func_name() const{return "compress";} String *val_str(String *) ZLIB_DEPENDED_FUNCTION }; -class Item_func_uncompress: public Item_str_func +class Item_func_uncompress: public Item_str_binary_checksum_func { String buffer; public: - Item_func_uncompress(THD *thd, Item *a): Item_str_func(thd, a) {} + Item_func_uncompress(THD *thd, Item *a) + :Item_str_binary_checksum_func(thd, a) {} void fix_length_and_dec(){ maybe_null= 1; max_length= MAX_BLOB_WIDTH; } const char *func_name() const{return "uncompress";} String *val_str(String *) ZLIB_DEPENDED_FUNCTION -- cgit v1.2.1