diff options
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 21 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 21 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 13 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 9 | ||||
| -rw-r--r-- | sql/sql_select.h | 2 |
5 files changed, 62 insertions, 4 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 2df5089b064..4298bd8abcb 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1245,4 +1245,25 @@ t1field 2 3 drop table t1,t2; +# +# BUG#787299: Valgrind complains on a join query with two IN subqueries +# +create table t1 (a int); +insert into t1 values (1), (2), (3); +create table t2 as select * from t1; +select * from t1 A, t1 B +where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +a a +1 1 +2 2 +3 3 +explain +select * from t1 A, t1 B +where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 3 Start temporary +1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join) +drop table t1, t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 22335c25655..cded78e4b04 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1253,6 +1253,27 @@ t1field 2 3 drop table t1,t2; +# +# BUG#787299: Valgrind complains on a join query with two IN subqueries +# +create table t1 (a int); +insert into t1 values (1), (2), (3); +create table t2 as select * from t1; +select * from t1 A, t1 B +where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +a a +1 1 +2 2 +3 3 +explain +select * from t1 A, t1 B +where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 3 Start temporary +1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (incremental, BNL join) +drop table t1, t2; set @@optimizer_switch=@save_optimizer_switch; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index eeca05985b8..91185f78aac 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1137,5 +1137,18 @@ WHERE WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); drop table t1,t2; +--echo # +--echo # BUG#787299: Valgrind complains on a join query with two IN subqueries +--echo # +create table t1 (a int); +insert into t1 values (1), (2), (3); +create table t2 as select * from t1; +select * from t1 A, t1 B + where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +explain +select * from t1 A, t1 B + where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); +drop table t1, t2; + # The following command must be the last one the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 96ac9df8a6f..5d1629ba1bc 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2042,7 +2042,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, /* Initialize the state or copy it from prev. tables */ if (idx == join->const_tables) { - pos->first_firstmatch_table= MAX_TABLES; + pos->invalidate_firstmatch_prefix(); pos->first_loosescan_table= MAX_TABLES; pos->dupsweedout_tables= 0; pos->sjm_scan_need_tables= 0; @@ -2107,7 +2107,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, pos->first_firstmatch_rtbl= remaining_tables; } - if (pos->first_firstmatch_table != MAX_TABLES) + if (pos->in_firstmatch_prefix()) { if (outer_corr_tables & pos->first_firstmatch_rtbl) { @@ -2115,7 +2115,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, Trying to add an sj-inner table whose sj-nest has an outer correlated table that was not in the prefix. This means FirstMatch can't be used. */ - pos->first_firstmatch_table= MAX_TABLES; + pos->invalidate_firstmatch_prefix(); } else { @@ -2123,7 +2123,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, pos->firstmatch_need_tables|= sj_inner_tables; } - if (!(pos->firstmatch_need_tables & remaining_tables)) + if (pos->in_firstmatch_prefix() && + !(pos->firstmatch_need_tables & remaining_tables)) { /* Got a complete FirstMatch range. diff --git a/sql/sql_select.h b/sql/sql_select.h index d3ad4f69949..daf35ae63e0 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -584,6 +584,8 @@ typedef struct st_position */ table_map firstmatch_need_tables; + bool in_firstmatch_prefix() { return (first_firstmatch_table != MAX_TABLES); } + void invalidate_firstmatch_prefix() { first_firstmatch_table= MAX_TABLES; } /* Duplicate Weedout strategy */ /* The first table that the strategy will need to handle */ |