From 5e4aa1a2f8a4c2be30bac06717505b3591af7965 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 8 Sep 2017 11:26:35 -0700 Subject: Fixed the bug mdev-13709. Currently condition pushdown into materialized views / derived tables is not implemented yet (see mdev-12387) and grouping views are optimized early when subqueries are converted to semi-joins in convert_join_subqueries_to_semijoins(). If a subquery that is converted to a semi-join uses a grouping view this view is optimized in two phases. For such a view V only the first phase of optimization is done after the conversion of subqueries of the outer join into semi-joins. At the same time the reference of the view V appears in the join expression of the outer join. In fixed code there was an attempt to push conditions into this view and to optimize it after this. This triggered the second phase of the optimization of the view and it was done prematurely. The second phase of the optimization for the materialized view is supposed to be called after the splitting condition is pushed into the view in the call of JOIN::improve_chosen_plan for the outer join. The fix blocks the attempt to push conditions into splittable views if they have been already partly optimized and the following optimization for them. The test case of the patch shows that the code for mdev-13369 basically supported the splitting technique for materialized views / derived tables. The patch also replaces the name of the state JOIN::OPTIMIZATION_IN_STAGE_2 for JOIN::OPTIMIZATION_PHASE_1_DONE and fixes a bug in TABLE_LIST::fetch_number_of_rows() --- sql/sql_select.cc | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bdf85d0b2fc..530dc471b7c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1109,7 +1109,7 @@ int JOIN::optimize() { int res= 0; join_optimization_state init_state= optimization_state; - if (optimization_state == JOIN::OPTIMIZATION_IN_STAGE_2) + if (optimization_state == JOIN::OPTIMIZATION_PHASE_1_DONE) res= optimize_stage2(); else { @@ -1121,7 +1121,7 @@ int JOIN::optimize() res= optimize_inner(); } if (!with_two_phase_optimization || - init_state == JOIN::OPTIMIZATION_IN_STAGE_2) + init_state == JOIN::OPTIMIZATION_PHASE_1_DONE) { if (!res && have_query_plan != QEP_DELETED) build_explain(); @@ -1339,6 +1339,11 @@ JOIN::optimize_inner() */ if (tbl->is_materialized_derived()) { + JOIN *join= tbl->get_unit()->first_select()->join; + if (join && + join->optimization_state == JOIN::OPTIMIZATION_PHASE_1_DONE && + join->with_two_phase_optimization) + continue; /* Do not push conditions from where into materialized inner tables of outer joins: this is not valid. @@ -1533,7 +1538,7 @@ JOIN::optimize_inner() setup_subq_exit: with_two_phase_optimization= check_two_phase_optimization(thd); if (with_two_phase_optimization) - optimization_state= JOIN::OPTIMIZATION_IN_STAGE_2; + optimization_state= JOIN::OPTIMIZATION_PHASE_1_DONE; else { if (optimize_stage2()) @@ -1554,7 +1559,7 @@ int JOIN::optimize_stage2() goto setup_subq_exit; if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) - DBUG_RETURN(1); + DBUG_RETURN(1); if (thd->check_killed()) DBUG_RETURN(1); -- cgit v1.2.1 From 61074d0426be2cae821cb18ae6bf5818a42ef27c Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 9 Sep 2017 20:35:05 -0700 Subject: Fixed the bug mdev-13710. This patch corrects the code of the patch for mdev-13369 that introduced the splitting technique when using materialized derived tables / views with GROUP BY. The second actual parameters of the call of the method JOIN::reoptimize() in the function JOIN::push_splitting_cond_into_derived() was calculated incorrectly. This could cause different failures for queries using derived tables or views with GROUP BY when their FROM lists contained empty or single-row tables. --- sql/sql_select.cc | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 530dc471b7c..ad7e333d41b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9073,12 +9073,10 @@ bool JOIN::push_splitting_cond_into_derived(THD *thd, Item *cond) { enum_reopt_result reopt_result= REOPT_NONE; table_map all_table_map= 0; - for (JOIN_TAB *tab= join_tab + const_tables; + for (JOIN_TAB *tab= join_tab; tab < join_tab + top_join_tab_count; tab++) - { all_table_map|= tab->table->map; - } - reopt_result= reoptimize(cond, all_table_map, NULL); + reopt_result= reoptimize(cond, all_table_map & ~const_table_map, NULL); if (reopt_result == REOPT_ERROR) return true; if (inject_cond_into_where(cond)) -- cgit v1.2.1