diff options
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_jcl6.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_mat.result | 9 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 26 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 48 |
7 files changed, 130 insertions, 11 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 23e216a282a..edcdf08e3bb 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1538,4 +1538,28 @@ AND t1.f1 = t2.f1 ; f1 f1 DROP TABLE t1, t2; set optimizer_switch=@save_802965; +# +# BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106 +# +CREATE TABLE t1 ( f1 int) ; +INSERT INTO t1 VALUES (1),(1); +CREATE TABLE t2 ( f2 int) ; +INSERT INTO t2 VALUES (1),(1); +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (1),(1); +SELECT * +FROM t1 +WHERE t1.f1 IN ( +SELECT t2.f2 +FROM t2 +LEFT JOIN ( +SELECT * +FROM t3 +) AS alias1 +ON alias1.f3 = t2.f2 +); +f1 +1 +1 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 7a770abb474..22847232fce 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -266,10 +266,10 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (flat, BNL join) +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t1 index NULL a 5 NULL 10 Using index 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary +1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; FirstMatch(t0) drop table t0, t1,t2,t3; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 5292e70e100..ad8e02e0d05 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -273,10 +273,10 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (flat, BNL join) 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary +1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; FirstMatch(t0) drop table t0, t1,t2,t3; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 83507909cf1..0706525c6a1 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -275,10 +275,11 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (flat, BNL join) -1 PRIMARY t2 ref a a 5 test.t1.a 1 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 +2 SUBQUERY t1 index NULL a 5 NULL 10 Using index +2 SUBQUERY t2 ref a a 5 test.t1.a 1 Using where; Using index +2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index drop table t0, t1,t2,t3; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 684d3d805da..fb9c330496a 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1547,6 +1547,30 @@ AND t1.f1 = t2.f1 ; f1 f1 DROP TABLE t1, t2; set optimizer_switch=@save_802965; +# +# BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106 +# +CREATE TABLE t1 ( f1 int) ; +INSERT INTO t1 VALUES (1),(1); +CREATE TABLE t2 ( f2 int) ; +INSERT INTO t2 VALUES (1),(1); +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (1),(1); +SELECT * +FROM t1 +WHERE t1.f1 IN ( +SELECT t2.f2 +FROM t2 +LEFT JOIN ( +SELECT * +FROM t3 +) AS alias1 +ON alias1.f3 = t2.f2 +); +f1 +1 +1 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_sj_tmp; # # 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 46c8306e144..c389a4dda97 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1398,5 +1398,31 @@ AND t1.f1 = t2.f1 ; DROP TABLE t1, t2; set optimizer_switch=@save_802965; +--echo # +--echo # BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106 +--echo # +CREATE TABLE t1 ( f1 int) ; +INSERT INTO t1 VALUES (1),(1); + +CREATE TABLE t2 ( f2 int) ; +INSERT INTO t2 VALUES (1),(1); + +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (1),(1); + +SELECT * +FROM t1 +WHERE t1.f1 IN ( + SELECT t2.f2 + FROM t2 + LEFT JOIN ( + SELECT * + FROM t3 + ) AS alias1 + ON alias1.f3 = t2.f2 +); + +DROP TABLE t1,t2,t3; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 529485afec0..7bfb9c91e23 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1493,6 +1493,25 @@ void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist) } +static void set_emb_join_nest(List<TABLE_LIST> *tables, TABLE_LIST *emb_sj_nest) +{ + List_iterator<TABLE_LIST> it(*tables); + TABLE_LIST *tbl; + while ((tbl= it++)) + { + /* + Note: check for nested_join first. + derived-merged tables have tbl->table!=NULL && + tbl->table->reginfo==NULL. + */ + if (tbl->nested_join) + set_emb_join_nest(&tbl->nested_join->join_list, emb_sj_nest); + else if (tbl->table) + tbl->table->reginfo.join_tab->emb_sj_nest= emb_sj_nest; + + } +} + /* Pull tables out of semi-join nests, if possible @@ -1548,10 +1567,34 @@ int pull_out_semijoin_tables(JOIN *join) /* Try pulling out of the each of the semi-joins */ while ((sj_nest= sj_list_it++)) { - /* Action #1: Mark the constant tables to be pulled out */ - table_map pulled_tables= 0; List_iterator<TABLE_LIST> child_li(sj_nest->nested_join->join_list); TABLE_LIST *tbl; + + /* + Don't do table pull-out for nested joins (if we get nested joins here, it + means these are outer joins. It is theoretically possible to do pull-out + for some of the outer tables but we dont support this currently. + */ + bool have_join_nest_children= FALSE; + + set_emb_join_nest(&sj_nest->nested_join->join_list, sj_nest); + + while ((tbl= child_li++)) + { + if (tbl->nested_join) + { + have_join_nest_children= TRUE; + break; + } + } + + + table_map pulled_tables= 0; + if (have_join_nest_children) + goto skip; + + /* Action #1: Mark the constant tables to be pulled out */ + child_li.rewind(); while ((tbl= child_li++)) { if (tbl->table) @@ -1623,6 +1666,7 @@ int pull_out_semijoin_tables(JOIN *join) } while (pulled_a_table); child_li.rewind(); + skip: /* Action #3: Move the pulled out TABLE_LIST elements to the parents. */ |