diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/item_subselect.cc | 31 | ||||
| -rw-r--r-- | sql/item_subselect.h | 85 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 56 | ||||
| -rw-r--r-- | sql/sql_base.cc | 4 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 4 | ||||
| -rw-r--r-- | sql/sql_select.cc | 8 |
6 files changed, 122 insertions, 66 deletions
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 64bcd2ad2b0..6134903fee6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -158,9 +158,11 @@ void Item_in_subselect::cleanup() delete left_expr_cache; left_expr_cache= NULL; } + /* + TODO: This breaks the commented assert in add_strategy(). + in_strategy&= ~SUBS_STRATEGY_CHOSEN; + */ first_execution= TRUE; - if (in_strategy & SUBS_MATERIALIZATION) - in_strategy= 0; pushed_cond_guards= NULL; Item_subselect::cleanup(); DBUG_VOID_RETURN; @@ -176,10 +178,9 @@ void Item_allany_subselect::cleanup() */ for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) - if (in_strategy & SUBS_MAXMIN_INJECTED) + if (test_strategy(SUBS_MAXMIN_INJECTED)) sl->with_sum_func= false; Item_in_subselect::cleanup(); - } @@ -722,7 +723,7 @@ bool Item_in_subselect::exec() - on a cost-based basis, that takes into account the cost of a cache lookup, the cache hit rate, and the savings per cache hit. */ - if (!left_expr_cache && (in_strategy & SUBS_MATERIALIZATION)) + if (!left_expr_cache && (test_strategy(SUBS_MATERIALIZATION))) init_left_expr_cache(); /* @@ -1186,8 +1187,8 @@ bool Item_in_subselect::test_limit(st_select_lex_unit *unit_arg) Item_in_subselect::Item_in_subselect(Item * left_exp, st_select_lex *select_lex): Item_exists_subselect(), - left_expr_cache(0), first_execution(TRUE), - optimizer(0), pushed_cond_guards(NULL), emb_on_expr_nest(NULL), in_strategy(0), + left_expr_cache(0), first_execution(TRUE), in_strategy(SUBS_NOT_TRANSFORMED), + optimizer(0), pushed_cond_guards(NULL), emb_on_expr_nest(NULL), is_jtbm_merged(FALSE), is_flattenable_semijoin(FALSE), is_registered_semijoin(FALSE), upper_item(0) @@ -1608,7 +1609,7 @@ Item_in_subselect::single_value_transformer(JOIN *join) bool Item_allany_subselect::transform_into_max_min(JOIN *join) { DBUG_ENTER("Item_allany_subselect::transform_into_max_min"); - if (!(in_strategy & (SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE))) + if (!test_strategy(SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE)) DBUG_RETURN(false); Item **place= optimizer->arguments() + 1; THD *thd= join->thd; @@ -1682,7 +1683,7 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) Remove other strategies if any (we already changed the query and can't apply other strategy). */ - in_strategy= SUBS_MAXMIN_INJECTED; + set_strategy(SUBS_MAXMIN_INJECTED); } else { @@ -1694,7 +1695,7 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) Remove other strategies if any (we already changed the query and can't apply other strategy). */ - in_strategy= SUBS_MAXMIN_ENGINE; + set_strategy(SUBS_MAXMIN_ENGINE); } /* The swap is needed for expressions of type 'f1 < ALL ( SELECT ....)' @@ -2414,7 +2415,7 @@ err: void Item_in_subselect::print(String *str, enum_query_type query_type) { - if (in_strategy & SUBS_IN_TO_EXISTS) + if (test_strategy(SUBS_IN_TO_EXISTS)) str->append(STRING_WITH_LEN("<exists>")); else { @@ -2430,8 +2431,7 @@ bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref) uint outer_cols_num; List<Item> *inner_cols; - - if (in_strategy & SUBS_SEMI_JOIN) + if (test_strategy(SUBS_SEMI_JOIN)) return !( (*ref)= new Item_int(1)); /* @@ -2607,8 +2607,7 @@ Item_allany_subselect::select_transformer(JOIN *join) { DBUG_ENTER("Item_allany_subselect::select_transformer"); DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE | - SUBS_IN_TO_EXISTS)) == 0); - in_strategy|= SUBS_IN_TO_EXISTS; + SUBS_IN_TO_EXISTS | SUBS_STRATEGY_CHOSEN)) == 0); if (upper_item) upper_item->show= 1; DBUG_RETURN(select_in_like_transformer(join)); @@ -2617,7 +2616,7 @@ Item_allany_subselect::select_transformer(JOIN *join) void Item_allany_subselect::print(String *str, enum_query_type query_type) { - if (in_strategy & SUBS_IN_TO_EXISTS) + if (test_strategy(SUBS_IN_TO_EXISTS)) str->append(STRING_WITH_LEN("<exists>")); else { diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 85ce7745751..0ec0969e0ae 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -353,17 +353,19 @@ TABLE_LIST * const NO_JOIN_NEST=(TABLE_LIST*)0x1; based on user-set optimizer switches, semantic analysis and cost comparison. */ #define SUBS_NOT_TRANSFORMED 0 /* No execution method was chosen for this IN. */ -#define SUBS_SEMI_JOIN 1 /* IN was converted to semi-join. */ -#define SUBS_IN_TO_EXISTS 2 /* IN was converted to correlated EXISTS. */ -#define SUBS_MATERIALIZATION 4 /* Execute IN via subquery materialization. */ +/* The Final decision about the strategy is made. */ +#define SUBS_STRATEGY_CHOSEN 1 +#define SUBS_SEMI_JOIN 2 /* IN was converted to semi-join. */ +#define SUBS_IN_TO_EXISTS 4 /* IN was converted to correlated EXISTS. */ +#define SUBS_MATERIALIZATION 8 /* Execute IN via subquery materialization. */ /* Partial matching substrategies of MATERIALIZATION. */ -#define SUBS_PARTIAL_MATCH_ROWID_MERGE 8 -#define SUBS_PARTIAL_MATCH_TABLE_SCAN 16 +#define SUBS_PARTIAL_MATCH_ROWID_MERGE 16 +#define SUBS_PARTIAL_MATCH_TABLE_SCAN 32 /* ALL/ANY will be transformed with max/min optimization */ /* The subquery has not aggregates, transform it into a MAX/MIN query. */ -#define SUBS_MAXMIN_INJECTED 32 +#define SUBS_MAXMIN_INJECTED 64 /* The subquery has aggregates, use a special max/min subselect engine. */ -#define SUBS_MAXMIN_ENGINE 64 +#define SUBS_MAXMIN_ENGINE 128 /** @@ -398,6 +400,8 @@ protected: Item *expr; bool was_null; bool abort_on_null; + /* A bitmap of possible execution strategies for an IN predicate. */ + uchar in_strategy; public: Item_in_optimizer *optimizer; protected: @@ -442,11 +446,7 @@ public: */ bool sjm_scan_allowed; double jtbm_read_time; - double jtbm_record_count; - - /* A bitmap of possible execution strategies for an IN predicate. */ - uchar in_strategy; - + double jtbm_record_count; bool is_jtbm_merged; /* @@ -487,9 +487,8 @@ public: Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), - abort_on_null(0), optimizer(0), + abort_on_null(0), in_strategy(SUBS_NOT_TRANSFORMED), optimizer(0), pushed_cond_guards(NULL), func(NULL), emb_on_expr_nest(NULL), - in_strategy(SUBS_NOT_TRANSFORMED), is_jtbm_merged(FALSE), upper_item(0) {} @@ -539,6 +538,64 @@ public: emb_on_expr_nest= embedding; } + bool test_strategy(uchar strategy) + { return test(in_strategy & strategy); } + + /** + Test that the IN strategy was chosen for execution. This is so + when the CHOSEN flag is ON, and there is no other strategy. + */ + bool test_set_strategy(uchar strategy) + { + DBUG_ASSERT(strategy == SUBS_SEMI_JOIN || + strategy == SUBS_IN_TO_EXISTS || + strategy == SUBS_MATERIALIZATION || + strategy == SUBS_PARTIAL_MATCH_ROWID_MERGE || + strategy == SUBS_PARTIAL_MATCH_TABLE_SCAN || + strategy == SUBS_MAXMIN_INJECTED || + strategy == SUBS_MAXMIN_ENGINE); + return ((in_strategy & SUBS_STRATEGY_CHOSEN) && + (in_strategy & ~SUBS_STRATEGY_CHOSEN) == strategy); + } + + bool is_set_strategy() + { return test(in_strategy & SUBS_STRATEGY_CHOSEN); } + + bool has_strategy() + { return in_strategy != SUBS_NOT_TRANSFORMED; } + + void add_strategy (uchar strategy) + { + DBUG_ASSERT(strategy != SUBS_NOT_TRANSFORMED); + DBUG_ASSERT(!(strategy & SUBS_STRATEGY_CHOSEN)); + /* + TODO: PS re-execution breaks this condition, because + check_and_do_in_subquery_rewrites() is called for each reexecution + and re-adds the same strategies. + DBUG_ASSERT(!(in_strategy & SUBS_STRATEGY_CHOSEN)); + */ + in_strategy|= strategy; + } + + void reset_strategy(uchar strategy) + { + DBUG_ASSERT(strategy != SUBS_NOT_TRANSFORMED); + in_strategy= strategy; + } + + void set_strategy(uchar strategy) + { + /* Check that only one strategy is set for execution. */ + DBUG_ASSERT(strategy == SUBS_SEMI_JOIN || + strategy == SUBS_IN_TO_EXISTS || + strategy == SUBS_MATERIALIZATION || + strategy == SUBS_PARTIAL_MATCH_ROWID_MERGE || + strategy == SUBS_PARTIAL_MATCH_TABLE_SCAN || + strategy == SUBS_MAXMIN_INJECTED || + strategy == SUBS_MAXMIN_ENGINE); + in_strategy= (SUBS_STRATEGY_CHOSEN | strategy); + } + friend class Item_ref_null_helper; friend class Item_is_not_null_test; friend class Item_in_optimizer; 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; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index cacec989518..3e27355cc26 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6453,8 +6453,8 @@ find_field_in_tables(THD *thd, Item_ident *item, { Item *subs= sl->master_unit()->item; if (subs->type() == Item::SUBSELECT_ITEM && - ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS && - ((Item_in_subselect*)subs)->in_strategy & SUBS_SEMI_JOIN) + ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS && + ((Item_in_subselect*)subs)->test_strategy(SUBS_SEMI_JOIN)) { continue; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 4a4beb496aa..117a866555c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3676,8 +3676,8 @@ bool st_select_lex::is_merged_child_of(st_select_lex *ancestor) { Item *subs= sl->master_unit()->item; if (subs && subs->type() == Item::SUBSELECT_ITEM && - ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS && - ((Item_in_subselect*)subs)->in_strategy & SUBS_SEMI_JOIN) + ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS && + ((Item_in_subselect*)subs)->test_strategy(SUBS_SEMI_JOIN)) { continue; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b9088a7ce60..8d83b4589d5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -689,6 +689,10 @@ JOIN::prepare(Item ***rref_pointer_array, aggregate functions with implicit grouping (there is no GROUP BY). */ if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list && + !(select_lex->master_unit()->item && + select_lex->master_unit()->item->is_in_predicate() && + ((Item_in_subselect*)select_lex->master_unit()->item)-> + test_set_strategy(SUBS_MAXMIN_INJECTED)) && select_lex->full_group_by_flag == (NON_AGG_FIELD_USED | SUM_FUNC_USED)) { my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS, @@ -813,7 +817,7 @@ inject_jtbm_conds(JOIN *join, List<TABLE_LIST> *join_list, Item **join_where) double rows; double read_time; - subq_pred->in_strategy &= ~SUBS_IN_TO_EXISTS; + DBUG_ASSERT(subq_pred->test_set_strategy(SUBS_MATERIALIZATION)); subq_pred->optimize(&rows, &read_time); subq_pred->jtbm_read_time= read_time; @@ -3181,7 +3185,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, */ bool skip_unprefixed_keyparts= !(join->is_in_subquery() && - ((Item_in_subselect*)join->unit->item)->in_strategy & SUBS_IN_TO_EXISTS); + ((Item_in_subselect*)join->unit->item)->test_strategy(SUBS_IN_TO_EXISTS)); if (keyuse_array->elements && sort_and_filter_keyuse(join->thd, keyuse_array, |