revision-id: 33afc326e05bf90f8f0bb64bbc3e7c00a21711a4 (mariadb-10.3.12-66-g33afc32) parent(s): 09bd2138522787a4e0b015695c462903f4a9e728 author: Igor Babaev committer: Igor Babaev timestamp: 2019-03-04 23:10:30 -0800 message: MDEV-18467 Server crashes in fix_semijoin_strategies_for_picked_join_order If a splittable materialized derived table / view T is used in a inner nest of an outer join with impossible ON condition then T is marked as a constant table. Yet the execution plan to build T is still searched for in spite of the fact that is not needed. So it should be set. --- mysql-test/main/derived_split_innodb.result | 41 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 29 ++++++++++++++++++++ sql/opt_split.cc | 13 +++++---- sql/sql_select.cc | 1 + sql/sql_select.h | 3 ++- 5 files changed, 81 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 21dbd49..b9ed016 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -99,3 +99,44 @@ id select_type table type possible_keys key key_len ref rows Extra 2 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 set join_cache_level=default; DROP TABLE t1,t2; +# +# Bug mdev-18467: join of grouping view and a base table as inner operand +# of left join with on condition containing impossible range +# +create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB; +insert into t1 values (3,33), (7,77), (1,11); +create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB; +insert into t2 values (3,33), (9,99), (1,11); +create view v1 as +select f1, max(f2) as f2 from t2 group by f1; +select t.f2 +from t1 +left join +(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) +on t1.f1=t.f1; +f2 +NULL +NULL +NULL +explain select t.f2 +from t1 +left join +(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) +on t1.f1=t.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY <derived2> const key1 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL PRIMARY NULL NULL NULL 3 Using temporary; Using filesort +set statement optimizer_switch='split_materialized=off' for explain select t.f2 +from t1 +left join +(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) +on t1.f1=t.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY <derived2> const key1 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 DERIVED t2 index NULL PRIMARY 4 NULL 3 +drop view v1; +drop table t1,t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index c3b3bca..1bf70cd 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -94,3 +94,32 @@ eval EXPLAIN $q; set join_cache_level=default; DROP TABLE t1,t2; + +--echo # +--echo # Bug mdev-18467: join of grouping view and a base table as inner operand +--echo # of left join with on condition containing impossible range +--echo # + +create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB; +insert into t1 values (3,33), (7,77), (1,11); + +create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB; +insert into t2 values (3,33), (9,99), (1,11); + +create view v1 as + select f1, max(f2) as f2 from t2 group by f1; + +let $q= +select t.f2 + from t1 + left join + (v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) + on t1.f1=t.f1; + +eval $q; +eval explain $q; +eval set statement optimizer_switch='split_materialized=off' for explain $q; + +drop view v1; + +drop table t1,t2; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index fc3f084..cfac0c9 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -1078,6 +1078,7 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) @param spl_plan info on the splitting plan chosen for the splittable table T remaining_tables the table T is joined just before these tables + is_const_table the table T is a constant table @details If in the final query plan the optimizer has chosen a splitting plan @@ -1091,12 +1092,13 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) */ bool JOIN_TAB::fix_splitting(SplM_plan_info *spl_plan, - table_map remaining_tables) + table_map remaining_tables, + bool is_const_table) { SplM_opt_info *spl_opt_info= table->spl_opt_info; DBUG_ASSERT(table->spl_opt_info != 0); JOIN *md_join= spl_opt_info->join; - if (spl_plan) + if (spl_plan && !is_const_table) { memcpy((char *) md_join->best_positions, (char *) spl_plan->best_positions, @@ -1113,7 +1115,7 @@ bool JOIN_TAB::fix_splitting(SplM_plan_info *spl_plan, remaining_tables, true); } - else + else if (md_join->save_qep) { md_join->restore_query_plan(md_join->save_qep); } @@ -1143,10 +1145,11 @@ bool JOIN::fix_all_splittings_in_plan() { POSITION *cur_pos= &best_positions[tablenr]; JOIN_TAB *tab= cur_pos->table; - if (tablenr >= const_tables && tab->table->is_splittable()) + if (tab->table->is_splittable()) { SplM_plan_info *spl_plan= cur_pos->spl_plan; - if (tab->fix_splitting(spl_plan, all_tables & ~prev_tables)) + if (tab->fix_splitting(spl_plan, all_tables & ~prev_tables, + tablenr < const_tables )) return true; } prev_tables|= tab->table->map; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 439853c..a83d4d2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6689,6 +6689,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) next=tmp; } join->best_ref[idx]=table; + join->positions[idx].spl_plan= 0; } diff --git a/sql/sql_select.h b/sql/sql_select.h index 0e486c1..57d8dab 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -659,7 +659,8 @@ typedef struct st_join_table { void add_keyuses_for_splitting(); SplM_plan_info *choose_best_splitting(double record_count, table_map remaining_tables); - bool fix_splitting(SplM_plan_info *spl_plan, table_map remaining_tables); + bool fix_splitting(SplM_plan_info *spl_plan, table_map remaining_tables, + bool is_const_table); } JOIN_TAB;