diff options
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 17 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 18 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 17 | ||||
| -rw-r--r-- | sql/sql_select.cc | 7 |
4 files changed, 56 insertions, 3 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 009510276b5..6f90cc868a6 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1831,6 +1831,23 @@ id select_type table type possible_keys key key_len ref rows Extra 3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 3 MATERIALIZED alias4 index c c 11 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) DROP TABLE t1,t2; +# +# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result +# +create table t1 (a int, b int); +insert into t1 values (7,5), (3,3), (5,4), (9,3); +create table t2 (a int, b int, index i_a(a)); +insert into t2 values +(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); +explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where +select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +a b +7 5 +3 3 +drop table t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 44fb9c61f24..964df6735f8 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1867,6 +1867,24 @@ WHERE alias4.c = alias3.b id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1,t2; +# +# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result +# +create table t1 (a int, b int); +insert into t1 values (7,5), (3,3), (5,4), (9,3); +create table t2 (a int, b int, index i_a(a)); +insert into t2 values +(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); +explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +2 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where +select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +a b +7 5 +3 3 +drop table t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 80ba42e7bab..0840029c5e0 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1529,6 +1529,23 @@ WHERE b = a AND a IN ( DROP TABLE t1,t2; +--echo # +--echo # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result +--echo # +create table t1 (a int, b int); +insert into t1 values (7,5), (3,3), (5,4), (9,3); + +create table t2 (a int, b int, index i_a(a)); + +insert into t2 values + (4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); + +explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); + +drop table t1,t2; + + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05fba45792b..292ed9439e4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11490,7 +11490,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, } /* - Check if "item_field=head" equality is already guaranteed to be true + Check if "field_item=head" equality is already guaranteed to be true on upper AND-levels. */ if (upper) @@ -11502,7 +11502,8 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, Item_equal_fields_iterator li(*item_equal); while ((item= li++) != field_item) { - if (item->find_item_equal(upper_levels) == upper) + if (embedding_sjm(item) == field_sjm && + item->find_item_equal(upper_levels) == upper) break; } } @@ -11646,7 +11647,7 @@ static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, if (and_level) { cond_equal= &((Item_cond_and *) cond)->cond_equal; - cond_list->disjoin((List<Item> *) &cond_equal->current_level); + cond_list->disjoin((List<Item> *) &cond_equal->current_level);/* remove Item_equal objects from the AND. */ List_iterator_fast<Item_equal> it(cond_equal->current_level); while ((item_equal= it++)) |