From ebb130b189263120d4ce1ed430d80cd026b38698 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 2 Jun 2011 23:48:33 +0400 Subject: BUG#787299: Valgrind complains on a join query with two IN subqueries - Don't attempt to construct FirstMatch access method if we've just figured three lines above that it can't be used (because join prefix doesn't have the needed tables), and so have set pos->first_firstmatch_table= MAX_TABLES Attempts to analyze join->positions[MAX_TABLES] caused valgrind warnings --- mysql-test/r/subselect_sj.result | 21 +++++++++++++++++++++ mysql-test/r/subselect_sj_jcl6.result | 21 +++++++++++++++++++++ mysql-test/t/subselect_sj.test | 13 +++++++++++++ sql/opt_subselect.cc | 9 +++++---- 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 */ -- cgit v1.2.1