From 945a595aa36ccf90f89cf1ca8cddefe47b394dd3 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 29 Aug 2011 19:57:41 +0400 Subject: BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING - The problem was that the code that made the check whether the subquery is an AND-part of the WHERE clause didn't work correctly for nested subqueries. In particular, grand-child subquery in HAVING was treated as if it was in the WHERE, which eventually caused an assert when replace_where_subcondition looked for the subquery predicate in the WHERE and couldn't find it there. - The fix: Removed implementation of "thd_marker approach". thd->thd_marker was used to determine the location of subquery predicate: setup_conds() would set accordingly it when making the {where|on_expr}->fix_fields(...) call so that AND-parts of the WHERE/ON clauses can determine they are the AND-parts. Item_cond_or::fix_fields(), Item_func::fix_fields(), Item_subselect::fix_fields (this one was missed), and all other items-that-contain-items had to reset thd->thd_marker before calling fix_fields() for their children items, so that the children can see they are not AND-parts of WHERE/ON. - The "thd_marker approach" required that a lot of code in different locations maintains correct value of thd->thd_marker, so it was replaced with: - The new approach with mark_as_condition_AND_part does not keep context in thd->thd_marker. Instead, setup_conds() now calls {where|on_expr}->mark_as_condition_AND_part() and implementations of that function make sure that: - parts of AND-expressions get the mark_as_condition_AND_part() call - Item_in_subselect objects record that they are AND-parts of WHERE/ON --- sql/opt_subselect.cc | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 6c5e177fe1d..a0b7aa242c2 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -340,7 +340,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !select_lex->is_part_of_union() && // 2 !select_lex->group_list.elements && !join->order && // 3 !join->having && !select_lex->with_sum_func && // 4 - thd->thd_marker.emb_on_expr_nest && // 5 + in_subs->emb_on_expr_nest && // 5 select_lex->outer_select()->join && // 6 parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->in_strategy && // 8 @@ -353,7 +353,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join) (void)subquery_types_allow_materialization(in_subs); - in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest; in_subs->is_flattenable_semijoin= TRUE; /* Register the subquery for further processing in flatten_subqueries() */ @@ -434,10 +433,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) If the subquery is an AND-part of WHERE register for being processed with jtbm strategy */ - if (thd->thd_marker.emb_on_expr_nest == NO_JOIN_NEST && + if (in_subs->emb_on_expr_nest == NO_JOIN_NEST && optimizer_flag(thd, OPTIMIZER_SWITCH_SEMIJOIN)) { - in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest; in_subs->is_flattenable_semijoin= FALSE; if (!in_subs->is_registered_semijoin) { -- cgit v1.2.1 From ea8aa329099ee28ec6f1266d8d01a6fc664259cf Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 1 Sep 2011 23:53:12 +0300 Subject: Fix for bug lp:834492 Analysis: In the test query semi-join merges the inner-most subquery into the outer subquery, and the optimization of the merged subquery finds some new index access methods. Later the IN-EXISTS transformation is applied to the unmerged subquery. Since the optimizer is instructed to not consider materialization, it reoptimizes the plan in-place to take into account the new IN-EXISTS conditions. Just before reoptimization JOIN::choose_subquery_plan resets the query plan, which also resets the access methods found during the semi-join merge. Then reoptimization discovers there are no new access methods, but it leaves the query plan in its reset state. Later semi-join crashes because it assumes these access methods are present. Solution: When reoptimizing in-place, reset the query plan only after new access methods were discovered. If no new access methods were discovered, leave the current plan as it was. --- sql/opt_subselect.cc | 15 +++++---------- 1 file changed, 5 insertions(+), 10 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 6c5e177fe1d..b06d80860c5 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4641,17 +4641,12 @@ bool JOIN::choose_subquery_plan(table_map join_tables) const_tables != table_count) { /* - The subquery was not reoptimized either because the user allowed only - the IN-EXISTS strategy, or because materialization was not possible - based on semantic analysis. Cleanup the original plan and reoptimize. + The subquery was not reoptimized with the newly injected IN-EXISTS + conditions either because the user allowed only the IN-EXISTS strategy, + or because materialization was not possible based on semantic analysis. */ - for (uint i= 0; i < table_count; i++) - { - join_tab[i].keyuse= NULL; - join_tab[i].checked_keys.clear_all(); - } - if ((reopt_result= reoptimize(in_to_exists_where, join_tables, NULL)) == - REOPT_ERROR) + reopt_result= reoptimize(in_to_exists_where, join_tables, NULL); + if (reopt_result == REOPT_ERROR) return TRUE; } -- cgit v1.2.1 From da61ecccee7b8147e6fd9406671f33de51b4ca95 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 2 Sep 2011 22:43:35 +0400 Subject: BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization - Make JOIN::get_partial_cost_and_fanout() be able to handle join plans with semi-join-materialization nests. --- sql/opt_subselect.cc | 16 +--------------- 1 file changed, 1 insertion(+), 15 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 579d7b2d9bc..c9dff4f0f67 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4478,20 +4478,6 @@ bool JOIN::choose_subquery_plan(table_map join_tables) outer_join= unit->outer_select() ? unit->outer_select()->join : NULL; if (outer_join && outer_join->table_count > 0) { - /* - The index of the last JOIN_TAB in the outer JOIN where in_subs is - attached (pushed to). - */ - uint max_outer_join_tab_idx; - /* - Make_cond_for_table is called for predicates only in the WHERE/ON - clauses. In all other cases, predicates are not pushed to any - JOIN_TAB, and their join_tab_idx remains MAX_TABLES. Such predicates - are evaluated for each complete row of the outer join. - */ - max_outer_join_tab_idx= (in_subs->get_join_tab_idx() == MAX_TABLES) ? - outer_join->table_count - 1: - in_subs->get_join_tab_idx(); /* TODO: Currently outer_lookup_keys is computed as the number of rows in @@ -4504,7 +4490,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables) If the join order: t1, t2, the number of unique lookup keys is ~ to the number of unique values t2.c2 in the partial join t1 join t2. */ - outer_join->get_partial_cost_and_fanout(max_outer_join_tab_idx, + outer_join->get_partial_cost_and_fanout(in_subs->get_join_tab_idx(), table_map(-1), &dummy, &outer_lookup_keys); -- cgit v1.2.1 From 28a70509123888f3bce4f754124acc7963dca8b4 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 2 Sep 2011 23:44:28 +0400 Subject: BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization - setup_sj_materialization() code failed to take into account that it can be that the first [in join order ordering] table inside semi-join-materialization nest is also an inner table wrt an outer join (that is embedded in the semi-join). This can happen when all of the tables that are inside the semi-join but not inside the outer join are constant. - Made a trivial to not assume that table's embedding join nest is the semi-join nest: instead, walk up the outer join nests until we reach the semi-join nest. --- sql/opt_subselect.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c9dff4f0f67..dc924ec05e9 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2935,6 +2935,11 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) DBUG_ENTER("setup_sj_materialization"); JOIN_TAB *tab= sjm_tab->bush_children->start; TABLE_LIST *emb_sj_nest= tab->table->pos_in_table_list->embedding; + + /* Walk out of outer join nests until we reach the semi-join nest we're in */ + while (!emb_sj_nest->sj_mat_info) + emb_sj_nest= emb_sj_nest->embedding; + SJ_MATERIALIZATION_INFO *sjm= emb_sj_nest->sj_mat_info; THD *thd= tab->join->thd; /* First the calls come to the materialization function */ @@ -2983,6 +2988,9 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) DBUG_ENTER("setup_sj_materialization_part2"); JOIN_TAB *tab= sjm_tab->bush_children->start; TABLE_LIST *emb_sj_nest= tab->table->pos_in_table_list->embedding; + /* Walk out of outer join nests until we reach the semi-join nest we're in */ + while (!emb_sj_nest->sj_mat_info) + emb_sj_nest= emb_sj_nest->embedding; SJ_MATERIALIZATION_INFO *sjm= emb_sj_nest->sj_mat_info; THD *thd= tab->join->thd; uint i; -- cgit v1.2.1 From 6035d0d755a2465421da3ac845ab970c504c90d7 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 5 Sep 2011 19:28:22 +0400 Subject: BUG#834758: Wrong result with innner join, LooseScan, two-column IN() predicate - get_bound_sj_equalities() would produce incorrect bitmap when non-first equality was bound, which resulted in invalid LooseScan plans. --- sql/opt_subselect.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index dc924ec05e9..40a0195e554 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2639,6 +2639,7 @@ ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest, { res |= 1ULL << i; } + i++; } return res; } -- cgit v1.2.1 From e1435a51786f7975da79f62fc430fc3b3bf1a45f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 5 Sep 2011 20:51:37 +0400 Subject: BUG#834739: Wrong result with 3-way inner join, LooseScan,multipart keys - Don't use join buffering for tables that are within ranges that are covered by LooseScan strategy. --- sql/opt_subselect.cc | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 40a0195e554..9ea55390fc5 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3827,6 +3827,8 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, { /* We jump from the last table to the first one */ tab->loosescan_match_tab= tab + pos->n_sj_tables - 1; + for (uint j= i; j < pos->n_sj_tables; j++) + join->join_tab[j].inside_loosescan_range= TRUE; /* Calculate key length */ keylen= 0; -- cgit v1.2.1 From fc6b6435b4c3f5203e6ffc3de289f0413a7ef33f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 6 Sep 2011 17:06:04 +0400 Subject: BUG#823930: Wrong result with semijoin materialization and blob fields - Make subquery_types_allow_materialization() detect a case where create_tmp_table() would create a blob column which would make it impossible to use materialization Non-semi-join materialization worked because it detected that this case and felt back to use IN->EXISTS. Semi-join Materialization cannot easily fallback, so we have to detect this case early. --- sql/opt_subselect.cc | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 9ea55390fc5..3cb13b0b88c 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -559,6 +559,16 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) if (inner->field_type() == MYSQL_TYPE_BLOB || inner->field_type() == MYSQL_TYPE_GEOMETRY) DBUG_RETURN(FALSE); + /* + Materialization also is unable to work when create_tmp_table() will + create a blob column because item->max_length is too big. + The following check is copied from Item::make_string_field(): + */ + if (inner->max_length / inner->collation.collation->mbmaxlen > + CONVERT_IF_BIGGER_TO_BLOB) + { + DBUG_RETURN(FALSE); + } break; case TIME_RESULT: if (mysql_type_to_time_type(outer->field_type()) != -- cgit v1.2.1 From 27cd8d7b70cc22693b9dab35ac2d1e720b8fc7a7 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 17 Sep 2011 23:53:50 +0400 Subject: BUG##849717: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin - If convert_join_subqueries_to_semijoins() decides to wrap Item_in_subselect in Item_in_optimizer, it should do so in prep_on_expr/prep_where, too, as long as they are present. There seems to be two possibilities of how we arrive in this function: - prep_on_expr/prep_where==NULL, and will be set later by simplify_joins() - prep_on_expr/prep_where!=NULL, and it is a copy_and_or_structure()-made copy of on_expr/where. the latter can happen for some (but not all!) nested joins. This bug was that we didn't handle this case. --- sql/opt_subselect.cc | 15 ++++++--------- 1 file changed, 6 insertions(+), 9 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 3cb13b0b88c..beeb0ac878e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -886,12 +886,6 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) do_fix_fields)) DBUG_RETURN(TRUE); in_subq->substitution= NULL; -#if 0 - /* - Don't do the following, because the simplify_join() call is after this - call, and that call will save to prep_wher/prep_on_expr. - */ - /* If this is a prepared statement, repeat the above operation for prep_where (or prep_on_expr). Subquery-to-semijoin conversion is @@ -902,12 +896,15 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) tree= (in_subq->emb_on_expr_nest == NO_JOIN_NEST)? &join->select_lex->prep_where : &(in_subq->emb_on_expr_nest->prep_on_expr); - - if (replace_where_subcondition(join, tree, replace_me, substitute, + /* + prep_on_expr/ prep_where may be NULL in some cases. + If that is the case, do nothing - simplify_joins() will copy + ON/WHERE expression into prep_on_expr/prep_where. + */ + if (*tree && replace_where_subcondition(join, tree, replace_me, substitute, FALSE)) DBUG_RETURN(TRUE); } -#endif /* Revert to the IN->EXISTS strategy in the rare case when the subquery could not be flattened. -- cgit v1.2.1 From 4908d27b57ee00dba3694e300a858b0fcdb224ee Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 26 Sep 2011 13:56:09 +0400 Subject: BUG#858732: Wrong result with semijoin + loosescan + comma join - Fix wrong loop bounds in setup_semijoin_dups_elimination() --- sql/opt_subselect.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index beeb0ac878e..7ac09eaa434 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3834,7 +3834,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, { /* We jump from the last table to the first one */ tab->loosescan_match_tab= tab + pos->n_sj_tables - 1; - for (uint j= i; j < pos->n_sj_tables; j++) + for (uint j= i; j < i + pos->n_sj_tables; j++) join->join_tab[j].inside_loosescan_range= TRUE; /* Calculate key length */ -- cgit v1.2.1 From f5987a0c3ef089439d400217a5784c10fd991f52 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 1 Oct 2011 00:10:03 +0400 Subject: BUG#860553: Crash in create_ref_for_key with semijoin + materialization - The problem was that JOIN::save/restore_query_plan() did not save/restore parts of the query plan that are located inside SJ_MATERIALIZATION_INFO structures. This could cause parts of one plan to be used with another, which led get_best_combination() to constructing non-sensical join plans (and crash). Fixed by saving/restoring SJM parts of the query plans. - check_and_do_in_subquery_rewrites() will not set SUBS_MATERIALIZATION flag when it records that the subquery predicate is to be converted into semi-join. If convert_join_subqueries_to_semijoins() later decides not to convert to semi-join, let it set SUBS_MATERIALIZATION flag, if appropriate. --- sql/opt_subselect.cc | 125 ++++++++++++++++++++++++++++++++------------------- 1 file changed, 79 insertions(+), 46 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 7ac09eaa434..1c317cea808 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -209,6 +209,74 @@ enum_nested_loop_state end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); +/* + Check if Materialization strategy is allowed for given subquery predicate. + + @param thd Thread handle + @param in_subs The subquery predicate + @param child_select The select inside predicate (the function will + check it is the only one) + + @return TRUE - Materialization is applicable + FALSE - Otherwise +*/ + +bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, + st_select_lex *child_select) +{ + st_select_lex_unit* parent_unit= child_select->master_unit(); + /* + Check if the subquery predicate can be executed via materialization. + The required conditions are: + 0. The materialization optimizer switch was set. + 1. Subquery is a single SELECT (not a UNION). + TODO: this is a limitation that can be fixed + 2. Subquery is not a table-less query. In this case there is no + point in materializing. + 2A The upper query is not a table-less SELECT ... FROM DUAL. We + can't do materialization for SELECT .. FROM DUAL because it + does not call setup_subquery_materialization(). We could make + SELECT ... FROM DUAL call that function but that doesn't seem + to be the case that is worth handling. + 3. Either the subquery predicate is a top-level predicate, or at + least one partial match strategy is enabled. If no partial match + strategy is enabled, then materialization cannot be used for + non-top-level queries because it cannot handle NULLs correctly. + 4. Subquery is non-correlated + TODO: + This condition is too restrictive (limitation). It can be extended to: + (Subquery is non-correlated || + Subquery is correlated to any query outer to IN predicate || + (Subquery is correlated to the immediate outer query && + Subquery !contains {GROUP BY, ORDER BY [LIMIT], + aggregate functions}) && subquery predicate is not under "NOT IN")) + + (*) The subquery must be part of a SELECT statement. The current + condition also excludes multi-table update statements. + A note about prepared statements: we want the if-branch to be taken on + PREPARE and each EXECUTE. The rewrites are only done once, but we need + select_lex->sj_subselects list to be populated for every EXECUTE. + + */ + if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0 + !child_select->is_part_of_union() && // 1 + parent_unit->first_select()->leaf_tables.elements && // 2 + thd->lex->sql_command == SQLCOM_SELECT && // * + child_select->outer_select()->leaf_tables.elements && // 2A + subquery_types_allow_materialization(in_subs) && + (in_subs->is_top_level_item() || //3 + optimizer_flag(thd, + OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || //3 + optimizer_flag(thd, + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) && //3 + !in_subs->is_correlated) //4 + { + return TRUE; + } + return FALSE; +} + + /* Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them @@ -381,52 +449,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) */ if (in_subs) { - /* - Check if the subquery predicate can be executed via materialization. - The required conditions are: - 0. The materialization optimizer switch was set. - 1. Subquery is a single SELECT (not a UNION). - TODO: this is a limitation that can be fixed - 2. Subquery is not a table-less query. In this case there is no - point in materializing. - 2A The upper query is not a table-less SELECT ... FROM DUAL. We - can't do materialization for SELECT .. FROM DUAL because it - does not call setup_subquery_materialization(). We could make - SELECT ... FROM DUAL call that function but that doesn't seem - to be the case that is worth handling. - 3. Either the subquery predicate is a top-level predicate, or at - least one partial match strategy is enabled. If no partial match - strategy is enabled, then materialization cannot be used for - non-top-level queries because it cannot handle NULLs correctly. - 4. Subquery is non-correlated - TODO: - This condition is too restrictive (limitation). It can be extended to: - (Subquery is non-correlated || - Subquery is correlated to any query outer to IN predicate || - (Subquery is correlated to the immediate outer query && - Subquery !contains {GROUP BY, ORDER BY [LIMIT], - aggregate functions}) && subquery predicate is not under "NOT IN")) - - (*) The subquery must be part of a SELECT statement. The current - condition also excludes multi-table update statements. - A note about prepared statements: we want the if-branch to be taken on - PREPARE and each EXECUTE. The rewrites are only done once, but we need - select_lex->sj_subselects list to be populated for every EXECUTE. - - */ - if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0 - !select_lex->is_part_of_union() && // 1 - parent_unit->first_select()->leaf_tables.elements && // 2 - thd->lex->sql_command == SQLCOM_SELECT && // * - select_lex->outer_select()->leaf_tables.elements && // 2A - subquery_types_allow_materialization(in_subs) && - (in_subs->is_top_level_item() || //3 - optimizer_flag(thd, - OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || //3 - optimizer_flag(thd, - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) && //3 - !in_subs->is_correlated) //4 - { + if (is_materialization_applicable(thd, in_subs, select_lex)) + { in_subs->in_strategy|= SUBS_MATERIALIZATION; /* @@ -914,6 +938,12 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) check_and_do_in_subquery_rewrites. */ in_subq->in_strategy= SUBS_IN_TO_EXISTS; + if (is_materialization_applicable(thd, in_subq, + in_subq->unit->first_select())) + { + in_subq->in_strategy|= SUBS_MATERIALIZATION; + } + in_subq= li++; } @@ -1766,6 +1796,9 @@ int pull_out_semijoin_tables(JOIN *join) All obtained information is saved and will be used by the main join optimization pass. + + NOTES + Because of Join::reoptimize(), this function may be called multiple times. RETURN FALSE Ok -- cgit v1.2.1 From b45beca3680bf545728382af90111fa76cd5abdd Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 1 Oct 2011 00:55:57 +0400 Subject: BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization - convert_subq_to_jtbm() didn't check that subuqery optimization was successful. If it wasn't (in this example because of @@max_join_size violation), it would proceed further and eventually crash when trying to execute the un-optimized subquery. --- sql/opt_subselect.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1c317cea808..7c11f9c4aea 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1425,7 +1425,8 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, DBUG_ENTER("convert_subq_to_jtbm"); subq_pred->in_strategy &= ~SUBS_IN_TO_EXISTS; - subq_pred->optimize(&rows, &read_time); + if (subq_pred->optimize(&rows, &read_time)) + DBUG_RETURN(TRUE); subq_pred->jtbm_read_time= read_time; subq_pred->jtbm_record_count=rows; -- cgit v1.2.1 From 1bb7a314cd6fbe31a5676ae107ed84f368a00d48 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 4 Oct 2011 02:20:06 +0400 Subject: Fix buildbot failure in previous fix (BUG#861147): - convert_subq_to_jtbm() should always restore the used arena. --- sql/opt_subselect.cc | 11 ++++++++--- 1 file changed, 8 insertions(+), 3 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 7c11f9c4aea..f983e78fdd0 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -858,14 +858,14 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) in_subq->unit->first_select()->join->table_count >= MAX_TABLES) break; if (convert_subq_to_sj(join, in_subq)) - DBUG_RETURN(TRUE); + goto restore_arena_and_fail; } else { if (join->table_count + 1 >= MAX_TABLES) break; if (convert_subq_to_jtbm(join, in_subq, &remove_item)) - DBUG_RETURN(TRUE); + goto restore_arena_and_fail; } if (remove_item) { @@ -874,7 +874,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) Item *replace_me= in_subq->original_item(); if (replace_where_subcondition(join, tree, replace_me, new Item_int(1), FALSE)) - DBUG_RETURN(TRUE); /* purecov: inspected */ + goto restore_arena_and_fail; } } //skip_conversion: @@ -951,6 +951,11 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) thd->restore_active_arena(arena, &backup); join->select_lex->sj_subselects.empty(); DBUG_RETURN(FALSE); + +restore_arena_and_fail: + if (arena) + thd->restore_active_arena(arena, &backup); + DBUG_RETURN(TRUE); } -- cgit v1.2.1 From 2160a25adc7e0ed8ef59dd4426a884610a32802f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 12 Oct 2011 13:19:37 +0400 Subject: BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key - Make advance_sj_state() not to attempt building duplicate removal strategies when we're doing optimization of an SJM-nest. --- sql/opt_subselect.cc | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f983e78fdd0..08afa4b12fb 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2179,6 +2179,17 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, pos->sj_strategy= SJ_OPT_NONE; pos->prefix_dups_producing_tables= join->cur_dups_producing_tables; + + /* We're performing optimization inside SJ-Materialization nest */ + if (join->emb_sjm_nest) + { + pos->invalidate_firstmatch_prefix(); + pos->first_loosescan_table= MAX_TABLES; + pos->dupsweedout_tables= 0; + pos->sjm_scan_need_tables= 0; + return; + } + /* Initialize the state or copy it from prev. tables */ if (idx == join->const_tables) { -- cgit v1.2.1 From 255c04aed8b4159ac8402bc80bab3771c0236d40 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 18 Oct 2011 13:44:12 +0300 Subject: Compiler warning about assigned but not used variables fixed. --- sql/opt_subselect.cc | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 08afa4b12fb..416dcadc67f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3385,7 +3385,7 @@ TABLE *create_duplicate_weedout_tmp_table(THD *thd, bool using_unique_constraint=FALSE; bool use_packed_rows= FALSE; Field *field, *key_field; - uint blob_count, null_pack_length, null_count; + uint null_pack_length, null_count; uchar *null_flags; uchar *pos; DBUG_ENTER("create_duplicate_weedout_tmp_table"); @@ -3466,8 +3466,6 @@ TABLE *create_duplicate_weedout_tmp_table(THD *thd, share->keys_for_keyread.init(); share->keys_in_use.init(); - blob_count= 0; - /* Create the field */ { /* -- cgit v1.2.1 From 9b761df393341bb15d6892ae8def9ae84d07305b Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 2 Nov 2011 22:05:08 +0400 Subject: BUG#878753: Assertion '0' failed in replace_where_subcondition with derived_merge - Remove the assert in replace_where_subcondition (the patch has explanation why) --- sql/opt_subselect.cc | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 416dcadc67f..aa0a8152bbb 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1026,7 +1026,6 @@ static bool replace_where_subcondition(JOIN *join, Item **expr, Item *old_cond, Item *new_cond, bool do_fix_fields) { - //Item **expr= (emb_nest == (TABLE_LIST*)1)? &join->conds : &emb_nest->on_expr; if (*expr == old_cond) { *expr= new_cond; @@ -1050,9 +1049,15 @@ static bool replace_where_subcondition(JOIN *join, Item **expr, } } } - // If we came here it means there were an error during prerequisites check. - DBUG_ASSERT(0); - return TRUE; + /* + We can come to here when + - we're doing replace operations on both on_expr and prep_on_expr + - on_expr is the same as prep_on_expr, or they share a sub-tree + (so, when we do replace in on_expr, we replace in prep_on_expr, too, + and when we try doing a replace in prep_on_expr, the item we wanted + to replace there has already been replaced) + */ + return FALSE; } static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, -- cgit v1.2.1 From 1d721d0106db6636170cdf94070a57f40d44a316 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 12 Nov 2011 11:29:12 +0200 Subject: Fix MySQL BUG#12329653 In MariaDB, when running in ONLY_FULL_GROUP_BY mode, the server produced in incorrect error message that there is an aggregate function without GROUP BY, for artificially created MIN/MAX functions during subquery MIN/MAX optimization. The fix introduces a way to distinguish between artifially created MIN/MAX functions as a result of a rewrite, and normal ones present in the query. The test for ONLY_FULL_GROUP_BY violation now tests in addition if a MIN/MAX function was part of a MIN/MAX subquery rewrite. In order to be able to distinguish these MIN/MAX functions, the patch introduces an additional flag in Item_in_subselect::in_strategy - SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its final choice of a subuqery strategy. In order to make the choice consistent, access to Item_in_subselect::in_strategy is provided via new class methods. ****** Fix MySQL BUG#12329653 In MariaDB, when running in ONLY_FULL_GROUP_BY mode, the server produced in incorrect error message that there is an aggregate function without GROUP BY, for artificially created MIN/MAX functions during subquery MIN/MAX optimization. The fix introduces a way to distinguish between artifially created MIN/MAX functions as a result of a rewrite, and normal ones present in the query. The test for ONLY_FULL_GROUP_BY violation now tests in addition if a MIN/MAX function was part of a MIN/MAX subquery rewrite. In order to be able to distinguish these MIN/MAX functions, the patch introduces an additional flag in Item_in_subselect::in_strategy - SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its final choice of a subuqery strategy. In order to make the choice consistent, access to Item_in_subselect::in_strategy is provided via new class methods. --- sql/opt_subselect.cc | 56 ++++++++++++++++++++++++---------------------------- 1 file changed, 26 insertions(+), 30 deletions(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index aa0a8152bbb..56815a624e2 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -411,7 +411,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) in_subs->emb_on_expr_nest && // 5 select_lex->outer_select()->join && // 6 parent_unit->first_select()->leaf_tables.elements && // 7 - !in_subs->in_strategy && // 8 + !in_subs->has_strategy() && // 8 select_lex->outer_select()->leaf_tables.elements && // 9 !((join->select_options | // 10 select_lex->outer_select()->join->select_options) // 10 @@ -451,7 +451,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { if (is_materialization_applicable(thd, in_subs, select_lex)) { - in_subs->in_strategy|= SUBS_MATERIALIZATION; + in_subs->add_strategy(SUBS_MATERIALIZATION); /* If the subquery is an AND-part of WHERE register for being processed @@ -479,17 +479,18 @@ int check_and_do_in_subquery_rewrites(JOIN *join) possible. */ if (optimizer_flag(thd, OPTIMIZER_SWITCH_IN_TO_EXISTS) || - !in_subs->in_strategy) - { - in_subs->in_strategy|= SUBS_IN_TO_EXISTS; - } + !in_subs->has_strategy()) + in_subs->add_strategy(SUBS_IN_TO_EXISTS); } /* Check if max/min optimization applicable */ - if (allany_subs) - allany_subs->in_strategy|= (allany_subs->is_maxmin_applicable(join) ? - (SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE) : - SUBS_IN_TO_EXISTS); + if (allany_subs && !allany_subs->is_set_strategy()) + { + uchar strategy= (allany_subs->is_maxmin_applicable(join) ? + (SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE) : + SUBS_IN_TO_EXISTS); + allany_subs->add_strategy(strategy); + } /* Transform each subquery predicate according to its overloaded @@ -932,16 +933,12 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) /* Revert to the IN->EXISTS strategy in the rare case when the subquery could not be flattened. - TODO: This is a limitation done for simplicity. Such subqueries could also - be executed via materialization. In order to determine this, we should - re-run the test for materialization that was done in - check_and_do_in_subquery_rewrites. */ - in_subq->in_strategy= SUBS_IN_TO_EXISTS; + in_subq->reset_strategy(SUBS_IN_TO_EXISTS); if (is_materialization_applicable(thd, in_subq, in_subq->unit->first_select())) { - in_subq->in_strategy|= SUBS_MATERIALIZATION; + in_subq->add_strategy(SUBS_MATERIALIZATION); } in_subq= li++; @@ -1260,7 +1257,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) /* 3. Remove the original subquery predicate from the WHERE/ON */ // The subqueries were replaced for Item_int(1) earlier - subq_pred->in_strategy= SUBS_SEMI_JOIN; // for subsequent executions + subq_pred->reset_strategy(SUBS_SEMI_JOIN); // for subsequent executions /*TODO: also reset the 'with_subselect' there. */ /* n. Adjust the parent_join->table_count counter */ @@ -1434,7 +1431,7 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, double read_time; DBUG_ENTER("convert_subq_to_jtbm"); - subq_pred->in_strategy &= ~SUBS_IN_TO_EXISTS; + subq_pred->set_strategy(SUBS_MATERIALIZATION); if (subq_pred->optimize(&rows, &read_time)) DBUG_RETURN(TRUE); @@ -4514,8 +4511,8 @@ bool JOIN::choose_subquery_plan(table_map join_tables) } else return false; - - DBUG_ASSERT(in_subs->in_strategy); /* A strategy must be chosen earlier. */ + /* A strategy must be chosen earlier. */ + DBUG_ASSERT(in_subs->has_strategy()); DBUG_ASSERT(in_to_exists_where || in_to_exists_having); DBUG_ASSERT(!in_to_exists_where || in_to_exists_where->fixed); DBUG_ASSERT(!in_to_exists_having || in_to_exists_having->fixed); @@ -4525,8 +4522,8 @@ bool JOIN::choose_subquery_plan(table_map join_tables) strategies are possible and allowed by the user (checked during the prepare phase. */ - if (in_subs->in_strategy & SUBS_MATERIALIZATION && - in_subs->in_strategy & SUBS_IN_TO_EXISTS) + if (in_subs->test_strategy(SUBS_MATERIALIZATION) && + in_subs->test_strategy(SUBS_IN_TO_EXISTS)) { JOIN *outer_join; JOIN *inner_join= this; @@ -4630,9 +4627,9 @@ bool JOIN::choose_subquery_plan(table_map join_tables) /* C.3 Compare the costs and choose the cheaper strategy. */ if (materialize_strategy_cost >= in_exists_strategy_cost) - in_subs->in_strategy&= ~SUBS_MATERIALIZATION; + in_subs->set_strategy(SUBS_IN_TO_EXISTS); else - in_subs->in_strategy&= ~SUBS_IN_TO_EXISTS; + in_subs->set_strategy(SUBS_MATERIALIZATION); DBUG_PRINT("info", ("mat_strategy_cost: %.2f, mat_cost: %.2f, write_cost: %.2f, lookup_cost: %.2f", @@ -4653,7 +4650,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables) otherwise use materialization. */ - if (in_subs->in_strategy & SUBS_MATERIALIZATION && + if (in_subs->test_strategy(SUBS_MATERIALIZATION) && in_subs->setup_mat_engine()) { /* @@ -4661,11 +4658,10 @@ bool JOIN::choose_subquery_plan(table_map join_tables) but it is not possible to execute it due to limitations in the implementation, fall back to IN-TO-EXISTS. */ - in_subs->in_strategy&= ~SUBS_MATERIALIZATION; - in_subs->in_strategy|= SUBS_IN_TO_EXISTS; + in_subs->set_strategy(SUBS_IN_TO_EXISTS); } - if (in_subs->in_strategy & SUBS_MATERIALIZATION) + if (in_subs->test_strategy(SUBS_MATERIALIZATION)) { /* Restore the original query plan used for materialization. */ if (reopt_result == REOPT_NEW_PLAN) @@ -4690,7 +4686,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables) */ select_limit= in_subs->unit->select_limit_cnt; } - else if (in_subs->in_strategy & SUBS_IN_TO_EXISTS) + else if (in_subs->test_strategy(SUBS_IN_TO_EXISTS)) { if (reopt_result == REOPT_NONE && in_to_exists_where && const_tables != table_count) @@ -4771,7 +4767,7 @@ bool JOIN::choose_tableless_subquery_plan() { Item_in_subselect *in_subs; in_subs= (Item_in_subselect*) subs_predicate; - in_subs->in_strategy= SUBS_IN_TO_EXISTS; + in_subs->set_strategy(SUBS_IN_TO_EXISTS); if (in_subs->create_in_to_exists_cond(this) || in_subs->inject_in_to_exists_cond(this)) return TRUE; -- cgit v1.2.1 From c05e5b9c65f76ba2d3a6844add88c03076b2cb5d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 16 Nov 2011 06:11:25 -0800 Subject: Fixed LP bug #887479. The function setup_semijoin_dups_elimination erroneously assumed that if join_cache_level is set to 3 or 4 then the type of the access to a table cannot be JT_REF or JT_EQ_REF. This could lead to wrong query result sets. --- sql/opt_subselect.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/opt_subselect.cc') diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 56815a624e2..a954811bdfa 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3921,7 +3921,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, if (j != join->const_tables && js_tab->use_quick != 2 && j <= no_jbuf_after && ((js_tab->type == JT_ALL && join_cache_level != 0) || - (join_cache_level > 4 && (tab->type == JT_REF || + (join_cache_level > 2 && (tab->type == JT_REF || tab->type == JT_EQ_REF)))) { /* Looks like we'll be using join buffer */ -- cgit v1.2.1