diff options
author | Alexander Barkov <bar@mariadb.org> | 2015-08-29 19:26:30 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.org> | 2015-08-29 19:26:30 +0400 |
commit | f071a12498e47921ce2dcc472ff6dafaf16d4d61 (patch) | |
tree | b0f6c0d59f3f168b6c56f7864443fe75d9f45d10 | |
parent | 09fb51255aea93025d782641906a552040d008c9 (diff) | |
download | mariadb-git-f071a12498e47921ce2dcc472ff6dafaf16d4d61.tar.gz |
MDEV-8688 Wrong result for
SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1';
-rw-r--r-- | mysql-test/r/ctype_cp1251.result | 31 | ||||
-rw-r--r-- | mysql-test/r/ctype_utf8.result | 26 | ||||
-rw-r--r-- | mysql-test/t/ctype_cp1251.test | 22 | ||||
-rw-r--r-- | mysql-test/t/ctype_utf8.test | 14 | ||||
-rw-r--r-- | sql/item_cmpfunc.cc | 1 | ||||
-rw-r--r-- | sql/item_cmpfunc.h | 14 |
6 files changed, 108 insertions, 0 deletions
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 7a91abbe4f5..ffe16753c5c 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -3334,3 +3334,34 @@ DROP TABLE t1; # # End of 5.5 tests # +# +# Start of 10.1 tests +# +# +# MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci); +INSERT INTO t1 VALUES (' 1'),('`1'); +SELECT * FROM t1 WHERE a IN (1,2,3); +a + 1 +SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +a + 1 +SELECT * FROM t1 WHERE a IN (1,2,3,'4') AND a=' 1'; +a + 1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' 1') and (`test`.`t1`.`a` in (1,2,3))) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3,'x') AND a=' 1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' 1') and (`test`.`t1`.`a` in (1,2,3,'x'))) +DROP TABLE t1; +# +# End of 10.1 tests +# diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index c20680a2a3e..b1325ffe9ed 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -10166,5 +10166,31 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 10) and (`test`.`t1`.`a` = '1e1')) DROP TABLE t1; # +# MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; +# +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('1e1'),('1ë1'); +SELECT * FROM t1 WHERE a IN (1,2); +a +1ë1 +SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; +a +1ë1 +SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; +a +1ë1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '1ë1') and (`test`.`t1`.`a` in (1,2))) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '1ë1') and (`test`.`t1`.`a` in (1,2,'x'))) +DROP TABLE IF EXISTS t1; +# # End of 10.1 tests # diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test index 0455f579922..6630497db8f 100644 --- a/mysql-test/t/ctype_cp1251.test +++ b/mysql-test/t/ctype_cp1251.test @@ -107,3 +107,25 @@ DROP TABLE t1; --echo # --echo # End of 5.5 tests --echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci); +INSERT INTO t1 VALUES (' 1'),('`1'); +SELECT * FROM t1 WHERE a IN (1,2,3); +SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +SELECT * FROM t1 WHERE a IN (1,2,3,'4') AND a=' 1'; +# Equality should not propagate ' 1' to IN: incompatible comparison context +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3,'x') AND a=' 1'; +DROP TABLE t1; + + +--echo # +--echo # End of 10.1 tests +--echo # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index e89247dc0cc..bb767900a8e 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1818,6 +1818,20 @@ SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); DROP TABLE t1; +--echo # +--echo # MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; +--echo # +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('1e1'),('1ë1'); +SELECT * FROM t1 WHERE a IN (1,2); +SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; +SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; +# Equality should not propagate '1ë1' to IN: incompatible comparison context +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; +DROP TABLE IF EXISTS t1; + --echo # --echo # End of 10.1 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7abfa4360a9..dcb743cc9fc 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3999,6 +3999,7 @@ void Item_func_in::fix_length_and_dec() if (m_compare_type == STRING_RESULT && agg_arg_charsets_for_comparison(cmp_collation, args, arg_count)) return; + args[0]->cmp_context= m_compare_type; arg_types_compatible= TRUE; if (m_compare_type == ROW_RESULT) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index e6e8fdd0bb9..88f329cc519 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1392,6 +1392,20 @@ public: void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, table_map usable_tables, SARGABLE_PARAM **sargables); SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr); + Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) + { + /* + In case when arg_types_compatible is false, + fix_length_and_dec() leaves args[0]->cmp_context equal to + IMPOSSIBLE_CONTEXT. In this case it's not safe to replace the field to an + equal constant, because Item_field::can_be_substituted_to_equal_item() + won't be able to check compatibility between + Item_equal->compare_collation() and this->compare_collation(). + */ + return arg_types_compatible ? + Item_func_opt_neg::propagate_equal_fields(thd, ctx, cond) : + this; + } virtual void print(String *str, enum_query_type query_type); enum Functype functype() const { return IN_FUNC; } const char *func_name() const { return " IN "; } |