diff options
Diffstat (limited to 'sql/opt_subselect.cc')
| -rw-r--r-- | sql/opt_subselect.cc | 355 |
1 files changed, 215 insertions, 140 deletions
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f82d55f8309..852f91efc00 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1,5 +1,5 @@ /* - Copyright (c) 2010, 2012, Monty Program Ab + Copyright (c) 2010, 2015, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -12,7 +12,7 @@ You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software - Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ /** @file @@ -26,6 +26,7 @@ #pragma implementation // gcc: Class implementation #endif +#include <my_global.h> #include "sql_base.h" #include "sql_select.h" #include "filesort.h" @@ -461,7 +462,7 @@ void best_access_path(JOIN *join, JOIN_TAB *s, static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, Item_in_subselect *subq_pred); -static void remove_sj_conds(Item **tree); +static void remove_sj_conds(THD *thd, Item **tree); static bool is_cond_sj_in_equality(Item *item); static bool sj_table_is_included(JOIN *join, JOIN_TAB *join_tab); static Item *remove_additional_cond(Item* conds); @@ -513,8 +514,6 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, Subquery !contains {GROUP BY, ORDER BY [LIMIT], aggregate functions}) && subquery predicate is not under "NOT IN")) - (*) The subquery must be part of a SELECT or CREATE TABLE ... 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. @@ -523,9 +522,7 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, 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 || // * - thd->lex->sql_command == SQLCOM_CREATE_TABLE) && // * - child_select->outer_select()->leaf_tables.elements && // 2A + child_select->outer_select()->leaf_tables.elements && // 2A subquery_types_allow_materialization(in_subs) && (in_subs->is_top_level_item() || //3 optimizer_flag(thd, @@ -665,6 +662,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) 8. No execution method was already chosen (by a prepared statement) 9. Parent select is not a table-less select 10. Neither parent nor child select have STRAIGHT_JOIN option. + 11. It is first optimisation (the subquery could be moved from ON + clause during first optimisation and then be considered for SJ + on the second when it is too late) */ if (optimizer_flag(thd, OPTIMIZER_SWITCH_SEMIJOIN) && in_subs && // 1 @@ -678,8 +678,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) select_lex->outer_select()->table_list.first && // 9 !((join->select_options | // 10 select_lex->outer_select()->join->select_options) // 10 - & SELECT_STRAIGHT_JOIN)) // 10 - + & SELECT_STRAIGHT_JOIN) && // 10 + select_lex->first_cond_optimization) // 11 { DBUG_PRINT("info", ("Subquery is semi-join conversion candidate")); @@ -692,7 +692,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - select_lex->outer_select()->sj_subselects.push_back(in_subs); + select_lex->outer_select()->sj_subselects.push_back(in_subs, + thd->mem_root); if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; @@ -735,7 +736,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - select_lex->outer_select()->sj_subselects.push_back(in_subs); + select_lex->outer_select()->sj_subselects.push_back(in_subs, + thd->mem_root); if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; @@ -1203,7 +1205,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) in_subq->sj_convert_priority= - test(in_subq->do_not_convert_to_sj) * MAX_TABLES * 2 + + MY_TEST(in_subq->do_not_convert_to_sj) * MAX_TABLES * 2 + in_subq->is_correlated * MAX_TABLES + child_join->outer_tables; } @@ -1259,7 +1261,8 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) Item **tree= (in_subq->emb_on_expr_nest == NO_JOIN_NEST)? &join->conds : &(in_subq->emb_on_expr_nest->on_expr); Item *replace_me= in_subq->original_item(); - if (replace_where_subcondition(join, tree, replace_me, new Item_int(1), + if (replace_where_subcondition(join, tree, replace_me, + new (thd->mem_root) Item_int(thd, 1), FALSE)) goto restore_arena_and_fail; } @@ -1609,7 +1612,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) A3: changes in the TABLE_LIST::outer_join will make everything work automatically. */ - if (!(wrap_nest= alloc_join_nest(parent_join->thd))) + if (!(wrap_nest= alloc_join_nest(thd))) { DBUG_RETURN(TRUE); } @@ -1618,7 +1621,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) wrap_nest->alias= (char*) "(sj-wrap)"; wrap_nest->nested_join->join_list.empty(); - wrap_nest->nested_join->join_list.push_back(outer_tbl); + wrap_nest->nested_join->join_list.push_back(outer_tbl, thd->mem_root); outer_tbl->embedding= wrap_nest; outer_tbl->join_list= &wrap_nest->nested_join->join_list; @@ -1654,7 +1657,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) TABLE_LIST *sj_nest; NESTED_JOIN *nested_join; - if (!(sj_nest= alloc_join_nest(parent_join->thd))) + if (!(sj_nest= alloc_join_nest(thd))) { DBUG_RETURN(TRUE); } @@ -1668,7 +1671,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) subq_pred->left_expr->used_tables(); /* Nests do not participate in those 'chains', so: */ /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/ - emb_join_list->push_back(sj_nest); + emb_join_list->push_back(sj_nest, thd->mem_root); /* nested_join->used_tables and nested_join->not_null_tables are @@ -1688,7 +1691,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) { tl->embedding= sj_nest; tl->join_list= &nested_join->join_list; - nested_join->join_list.push_back(tl); + nested_join->join_list.push_back(tl, thd->mem_root); } /* @@ -1698,7 +1701,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) NOTE: We actually insert them at the front! That's because the order is reversed in this list. */ - parent_lex->leaf_tables.concat(&subq_lex->leaf_tables); + parent_lex->leaf_tables.append(&subq_lex->leaf_tables); if (subq_lex->options & OPTION_SCHEMA_TABLE) parent_lex->options |= OPTION_SCHEMA_TABLE; @@ -1730,7 +1733,12 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) { tl->set_tablenr(table_no); if (tl->is_jtbm()) + { tl->jtbm_table_no= table_no; + Item *dummy= tl->jtbm_subselect; + tl->jtbm_subselect->fix_after_pullout(parent_lex, &dummy, true); + DBUG_ASSERT(dummy == tl->jtbm_subselect); + } SELECT_LEX *old_sl= tl->select_lex; tl->select_lex= parent_join->select_lex; for (TABLE_LIST *emb= tl->embedding; @@ -1753,8 +1761,9 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) DBUG_RETURN(TRUE); thd->lex->current_select=save_lex; - sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables(); - sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() | + table_map subq_pred_used_tables= subq_pred->used_tables(); + sj_nest->nested_join->sj_corr_tables= subq_pred_used_tables; + sj_nest->nested_join->sj_depends_on= subq_pred_used_tables | subq_pred->left_expr->used_tables(); sj_nest->sj_on_expr= subq_lex->join->conds; @@ -1780,7 +1789,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) if (subq_pred->left_expr->cols() == 1) { /* add left = select_list_element */ - nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); + nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr, + thd->mem_root); /* Create Item_func_eq. Note that 1. this is done on the statement, not execution, arena @@ -1791,13 +1801,14 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) with thd->change_item_tree */ Item_func_eq *item_eq= - new Item_func_eq(subq_pred->left_expr_orig, subq_lex->ref_pointer_array[0]); + new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr_orig, + subq_lex->ref_pointer_array[0]); if (!item_eq) DBUG_RETURN(TRUE); if (subq_pred->left_expr_orig != subq_pred->left_expr) thd->change_item_tree(item_eq->arguments(), subq_pred->left_expr); item_eq->in_equality_no= 0; - sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); + sj_nest->sj_on_expr= and_items(thd, sj_nest->sj_on_expr, item_eq); } else if (subq_pred->left_expr->type() == Item::ROW_ITEM) { @@ -1807,11 +1818,12 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) */ for (uint i= 0; i < subq_pred->left_expr->cols(); i++) { - nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr-> - addr(i)); + nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr->addr(i), + thd->mem_root); Item_func_eq *item_eq= - new Item_func_eq(subq_pred->left_expr_orig->element_index(i), - subq_lex->ref_pointer_array[i]); + new (thd->mem_root) + Item_func_eq(thd, subq_pred->left_expr_orig->element_index(i), + subq_lex->ref_pointer_array[i]); if (!item_eq) DBUG_RETURN(TRUE); DBUG_ASSERT(subq_pred->left_expr->element_index(i)->fixed); @@ -1820,7 +1832,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) thd->change_item_tree(item_eq->arguments(), subq_pred->left_expr->element_index(i)); item_eq->in_equality_no= i; - sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); + sj_nest->sj_on_expr= and_items(thd, sj_nest->sj_on_expr, item_eq); } } else @@ -1829,14 +1841,14 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) add row operation left = (select_list_element1, select_list_element2, ...) */ - Item_row *row= new Item_row(subq_lex->pre_fix); + Item_row *row= new (thd->mem_root) Item_row(thd, subq_lex->pre_fix); /* fix fields on subquery was call so they should be the same */ DBUG_ASSERT(subq_pred->left_expr->cols() == row->cols()); if (!row) DBUG_RETURN(TRUE); nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); Item_func_eq *item_eq= - new Item_func_eq(subq_pred->left_expr_orig, row); + new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr_orig, row); if (!item_eq) DBUG_RETURN(TRUE); for (uint i= 0; i < row->cols(); i++) @@ -1845,7 +1857,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) thd->change_item_tree(row->addr(i), subq_lex->ref_pointer_array[i]); } item_eq->in_equality_no= 0; - sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); + sj_nest->sj_on_expr= and_items(thd, sj_nest->sj_on_expr, item_eq); } /* Fix the created equality and AND @@ -1857,7 +1869,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) we have in here). */ if (!sj_nest->sj_on_expr->fixed && - sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr)) + sj_nest->sj_on_expr->fix_fields(thd, &sj_nest->sj_on_expr)) { DBUG_RETURN(TRUE); } @@ -1866,7 +1878,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) Walk through sj nest's WHERE and ON expressions and call item->fix_table_changes() for all items. */ - sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); + sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr, + TRUE); fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list); @@ -1879,11 +1892,11 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) /* Inject sj_on_expr into the parent's WHERE or ON */ if (emb_tbl_nest) { - emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr, + emb_tbl_nest->on_expr= and_items(thd, emb_tbl_nest->on_expr, sj_nest->sj_on_expr); emb_tbl_nest->on_expr->top_level_item(); if (!emb_tbl_nest->on_expr->fixed && - emb_tbl_nest->on_expr->fix_fields(parent_join->thd, + emb_tbl_nest->on_expr->fix_fields(thd, &emb_tbl_nest->on_expr)) { DBUG_RETURN(TRUE); @@ -1892,7 +1905,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) else { /* Inject into the WHERE */ - parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr); + parent_join->conds= and_items(thd, parent_join->conds, sj_nest->sj_on_expr); parent_join->conds->top_level_item(); /* fix_fields must update the properties (e.g. st_select_lex::cond_count of @@ -1901,7 +1914,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) save_lex= thd->lex->current_select; thd->lex->current_select=parent_join->select_lex; if (!parent_join->conds->fixed && - parent_join->conds->fix_fields(parent_join->thd, + parent_join->conds->fix_fields(thd, &parent_join->conds)) { DBUG_RETURN(1); @@ -1915,9 +1928,10 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) Item_func_match *ifm; List_iterator_fast<Item_func_match> li(*(subq_lex->ftfunc_list)); while ((ifm= li++)) - parent_lex->ftfunc_list->push_front(ifm); + parent_lex->ftfunc_list->push_front(ifm, thd->mem_root); } + parent_lex->have_merged_subqueries= TRUE; DBUG_RETURN(FALSE); } @@ -1956,16 +1970,16 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, List<TABLE_LIST> *emb_join_list= &parent_lex->top_join_list; TABLE_LIST *emb_tbl_nest= NULL; // will change when we learn to handle outer joins TABLE_LIST *tl; - DBUG_ENTER("convert_subq_to_jtbm"); bool optimization_delayed= TRUE; - subq_pred->set_strategy(SUBS_MATERIALIZATION); + TABLE_LIST *jtbm; + char *tbl_alias; + DBUG_ENTER("convert_subq_to_jtbm"); + subq_pred->set_strategy(SUBS_MATERIALIZATION); subq_pred->is_jtbm_merged= TRUE; *remove_item= TRUE; - TABLE_LIST *jtbm; - char *tbl_alias; if (!(tbl_alias= (char*)parent_join->thd->calloc(SUBQERY_TEMPTABLE_NAME_MAX_LEN)) || !(jtbm= alloc_join_nest(parent_join->thd))) //todo: this is not a join nest! { @@ -1979,13 +1993,13 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, /* Nests do not participate in those 'chains', so: */ /* jtbm->next_leaf= jtbm->next_local= jtbm->next_global == NULL*/ - emb_join_list->push_back(jtbm); + emb_join_list->push_back(jtbm, parent_join->thd->mem_root); /* Inject the jtbm table into TABLE_LIST::next_leaf list, so that make_join_statistics() and co. can find it. */ - parent_lex->leaf_tables.push_back(jtbm); + parent_lex->leaf_tables.push_back(jtbm, parent_join->thd->mem_root); if (subq_pred->unit->first_select()->options & OPTION_SCHEMA_TABLE) parent_lex->options |= OPTION_SCHEMA_TABLE; @@ -2024,13 +2038,15 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, DBUG_ASSERT(parent_join->table_count < MAX_TABLES); Item *conds= hash_sj_engine->semi_join_conds; - conds->fix_after_pullout(parent_lex, &conds); + conds->fix_after_pullout(parent_lex, &conds, TRUE); DBUG_EXECUTE("where", print_where(conds,"SJ-EXPR", QT_ORDINARY);); create_subquery_temptable_name(tbl_alias, hash_sj_engine->materialize_join-> select_lex->select_number); jtbm->alias= tbl_alias; + + parent_lex->have_merged_subqueries= TRUE; #if 0 /* Inject sj_on_expr into the parent's WHERE or ON */ if (emb_tbl_nest) @@ -2074,7 +2090,7 @@ void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist) while ((table= it++)) { if (table->on_expr) - table->on_expr->fix_after_pullout(new_parent, &table->on_expr); + table->on_expr->fix_after_pullout(new_parent, &table->on_expr, TRUE); if (table->nested_join) fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list); } @@ -2304,7 +2320,7 @@ int pull_out_semijoin_tables(JOIN *join) */ child_li.remove(); sj_nest->nested_join->used_tables &= ~tbl->table->map; - upper_join_list->push_back(tbl); + upper_join_list->push_back(tbl, join->thd->mem_root); tbl->join_list= upper_join_list; tbl->embedding= sj_nest->embedding; } @@ -2452,9 +2468,10 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) double rows= 1.0; while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END) rows *= join->map2table[tableno]->table->quick_condition_rows; - sjm->rows= min(sjm->rows, rows); + sjm->rows= MY_MIN(sjm->rows, rows); } - memcpy(sjm->positions, join->best_positions + join->const_tables, + memcpy((uchar*) sjm->positions, + (uchar*) (join->best_positions + join->const_tables), sizeof(POSITION) * n_tables); /* @@ -2477,7 +2494,7 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) Set the cost to do a full scan of the temptable (will need this to consider doing sjm-scan): */ - sjm->scan_cost.zero(); + sjm->scan_cost.reset(); sjm->scan_cost.add_io(sjm->rows, lookup_cost); sjm->lookup_cost.convert_from_cost(lookup_cost); @@ -2632,7 +2649,7 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables) if (!is_excluded_key) { keyinfo= table->key_info + key; - is_excluded_key= !test(keyinfo->flags & HA_NOSAME); + is_excluded_key= !MY_TEST(keyinfo->flags & HA_NOSAME); } if (!is_excluded_key) { @@ -2647,7 +2664,7 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables) keyuse++; } while (keyuse->key == key && keyuse->table == table); - if (bound_parts == PREV_BITS(uint, keyinfo->key_parts)) + if (bound_parts == PREV_BITS(uint, keyinfo->user_defined_key_parts)) return TRUE; } else @@ -2719,8 +2736,8 @@ bool is_multiple_semi_joins(JOIN *join, POSITION *prefix, uint idx, table_map in if ((emb_sj_nest= prefix[i].table->emb_sj_nest)) { if (inner_tables & emb_sj_nest->sj_inner_tables) - return !test(inner_tables == (emb_sj_nest->sj_inner_tables & - ~join->const_table_map)); + return !MY_TEST(inner_tables == (emb_sj_nest->sj_inner_tables & + ~join->const_table_map)); } } return FALSE; @@ -2759,8 +2776,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, LooseScan detector in best_access_path) */ remaining_tables &= ~new_join_tab->table->map; - table_map dups_producing_tables, prev_dups_producing_tables, - prev_sjm_lookup_tables; + table_map dups_producing_tables, prev_dups_producing_tables= 0, + prev_sjm_lookup_tables= 0; if (idx == join->const_tables) dups_producing_tables= 0; @@ -2771,7 +2788,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, if ((emb_sj_nest= new_join_tab->emb_sj_nest)) dups_producing_tables |= emb_sj_nest->sj_inner_tables; - Semi_join_strategy_picker **strategy, **prev_strategy; + Semi_join_strategy_picker **strategy, **prev_strategy= NULL; if (idx == join->const_tables) { /* First table, initialize pickers */ @@ -2955,12 +2972,12 @@ bool Sj_materialization_picker::check_qep(JOIN *join, else { /* This is SJ-Materialization with lookups */ - COST_VECT prefix_cost; + Cost_estimate prefix_cost; signed int first_tab= (int)idx - mat_info->tables; double prefix_rec_count; if (first_tab < (int)join->const_tables) { - prefix_cost.zero(); + prefix_cost.reset(); prefix_rec_count= 1.0; } else @@ -3491,9 +3508,9 @@ at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab, if (join->positions[idx - i].table->emb_sj_nest != tab->emb_sj_nest) return NULL; } - *loose_scan= test(remaining_tables & ~tab->table->map & - (emb_sj_nest->sj_inner_tables | - emb_sj_nest->nested_join->sj_depends_on)); + *loose_scan= MY_TEST(remaining_tables & ~tab->table->map & + (emb_sj_nest->sj_inner_tables | + emb_sj_nest->nested_join->sj_depends_on)); if (*loose_scan && !emb_sj_nest->sj_subq_pred->sjm_scan_allowed) return NULL; else @@ -3577,8 +3594,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) { POSITION *pos= join->best_positions + tablenr; JOIN_TAB *s= pos->table; - uint first; - LINT_INIT(first); // Set by every branch except SJ_OPT_NONE which doesn't use it + uint UNINIT_VAR(first); // Set by every branch except SJ_OPT_NONE which doesn't use it if ((handled_tabs & s->table->map) || pos->sj_strategy == SJ_OPT_NONE) { @@ -3591,7 +3607,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) SJ_MATERIALIZATION_INFO *sjm= s->emb_sj_nest->sj_mat_info; sjm->is_used= TRUE; sjm->is_sj_scan= FALSE; - memcpy(pos - sjm->tables + 1, sjm->positions, + memcpy((uchar*) (pos - sjm->tables + 1), (uchar*) sjm->positions, sizeof(POSITION) * sjm->tables); recalculate_prefix_record_count(join, tablenr - sjm->tables + 1, tablenr); @@ -3608,8 +3624,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) sjm->is_used= TRUE; sjm->is_sj_scan= TRUE; first= pos->sjmat_picker.sjm_scan_last_inner - sjm->tables + 1; - memcpy(join->best_positions + first, - sjm->positions, sizeof(POSITION) * sjm->tables); + memcpy((uchar*) (join->best_positions + first), + (uchar*) sjm->positions, sizeof(POSITION) * sjm->tables); recalculate_prefix_record_count(join, first, first + sjm->tables); join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE_SCAN; join->best_positions[first].n_sj_tables= sjm->tables; @@ -3781,16 +3797,19 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) 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; + SJ_MATERIALIZATION_INFO *sjm; + THD *thd; + + DBUG_ENTER("setup_sj_materialization"); /* 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; + sjm= emb_sj_nest->sj_mat_info; + thd= tab->join->thd; /* First the calls come to the materialization function */ DBUG_ASSERT(sjm->is_used); @@ -3831,8 +3850,8 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) sjm->table->file->extra(HA_EXTRA_WRITE_CACHE); sjm->table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); - tab->join->sj_tmp_tables.push_back(sjm->table); - tab->join->sjm_info_list.push_back(sjm); + tab->join->sj_tmp_tables.push_back(sjm->table, thd->mem_root); + tab->join->sjm_info_list.push_back(sjm, thd->mem_root); sjm->materialized= FALSE; sjm_tab->table= sjm->table; @@ -3861,7 +3880,7 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) KEY *tmp_key; /* The only index on the temporary table. */ uint tmp_key_parts; /* Number of keyparts in tmp_key. */ tmp_key= sjm->table->key_info; - tmp_key_parts= tmp_key->key_parts; + tmp_key_parts= tmp_key->user_defined_key_parts; /* Create/initialize everything we will need to index lookups into the @@ -3892,12 +3911,12 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) { tab_ref->items[i]= emb_sj_nest->sj_subq_pred->left_expr->element_index(i); - int null_count= test(cur_key_part->field->real_maybe_null()); + int null_count= MY_TEST(cur_key_part->field->real_maybe_null()); *ref_key= new store_key_item(thd, cur_key_part->field, /* TODO: the NULL byte is taken into account in cur_key_part->store_length, so instead of - cur_ref_buff + test(maybe_null), we could + cur_ref_buff + MY_TEST(maybe_null), we could use that information instead. */ cur_ref_buff + null_count, @@ -3929,9 +3948,9 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) */ for (i= 0; i < sjm->tables; i++) { - remove_sj_conds(&tab[i].select_cond); + remove_sj_conds(thd, &tab[i].select_cond); if (tab[i].select) - remove_sj_conds(&tab[i].select->cond); + remove_sj_conds(thd, &tab[i].select->cond); } if (!(sjm->in_equality= create_subq_in_equalities(thd, sjm, emb_sj_nest->sj_subq_pred))) @@ -4074,8 +4093,8 @@ static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, Item *res= NULL; if (subq_pred->left_expr->cols() == 1) { - if (!(res= new Item_func_eq(subq_pred->left_expr, - new Item_field(sjm->table->field[0])))) + if (!(res= new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr, + new (thd->mem_root) Item_field(thd, sjm->table->field[0])))) return NULL; /* purecov: inspected */ } else @@ -4083,9 +4102,9 @@ static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, Item *conj; for (uint i= 0; i < subq_pred->left_expr->cols(); i++) { - if (!(conj= new Item_func_eq(subq_pred->left_expr->element_index(i), - new Item_field(sjm->table->field[i]))) || - !(res= and_items(res, conj))) + if (!(conj= new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr->element_index(i), + new (thd->mem_root) Item_field(thd, sjm->table->field[i]))) || + !(res= and_items(thd, res, conj))) return NULL; /* purecov: inspected */ } } @@ -4097,7 +4116,7 @@ static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, -static void remove_sj_conds(Item **tree) +static void remove_sj_conds(THD *thd, Item **tree) { if (*tree) { @@ -4113,7 +4132,7 @@ static void remove_sj_conds(Item **tree) while ((item= li++)) { if (is_cond_sj_in_equality(item)) - li.replace(new Item_int(1)); + li.replace(new (thd->mem_root) Item_int(thd, 1)); } } } @@ -4126,7 +4145,7 @@ static bool is_cond_sj_in_equality(Item *item) ((Item_func*)item)->functype()== Item_func::EQ_FUNC) { Item_func_eq *item_eq= (Item_func_eq*)item; - return test(item_eq->in_equality_no != UINT_MAX); + return MY_TEST(item_eq->in_equality_no != UINT_MAX); } return FALSE; } @@ -4191,7 +4210,6 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) /* STEP 1: Get temporary table name */ - statistic_increment(thd->status_var.created_tmp_tables, &LOCK_status); if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES)) temp_pool_slot = bitmap_lock_set_next(&temp_pool); @@ -4212,7 +4230,7 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) using_unique_constraint= TRUE; /* STEP 3: Allocate memory for temptable description */ - init_sql_alloc(&own_root, TABLE_ALLOC_BLOCK_SIZE, 0); + init_sql_alloc(&own_root, TABLE_ALLOC_BLOCK_SIZE, 0, MYF(MY_THREAD_SPECIFIC)); if (!multi_alloc_root(&own_root, &table, sizeof(*table), &share, sizeof(*share), @@ -4225,7 +4243,7 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) &tmpname, (uint) strlen(path)+1, &group_buff, (!using_unique_constraint ? uniq_tuple_length_arg : 0), - &bitmaps, bitmap_buffer_size(1)*3, + &bitmaps, bitmap_buffer_size(1)*5, NullS)) { if (temp_pool_slot != MY_BIT_NONE) @@ -4259,7 +4277,6 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) table->s= share; init_tmp_table_share(thd, share, "", 0, tmpname, tmpname); share->blob_field= blob_field; - share->blob_ptr_size= portable_sizeof_char_ptr; share->table_charset= NULL; share->primary_key= MAX_KEY; // Indicate no primary key share->keys_for_keyread.init(); @@ -4301,17 +4318,23 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) share->db_plugin= ha_lock_engine(0, TMP_ENGINE_HTON); table->file= get_new_handler(share, &table->mem_root, share->db_type()); - DBUG_ASSERT(uniq_tuple_length_arg <= table->file->max_key_length()); } else { share->db_plugin= ha_lock_engine(0, heap_hton); table->file= get_new_handler(share, &table->mem_root, share->db_type()); + DBUG_ASSERT(uniq_tuple_length_arg <= table->file->max_key_length()); } if (!table->file) goto err; + if (table->file->set_ha_share_ref(&share->ha_share)) + { + delete table->file; + goto err; + } + null_count=1; null_pack_length= 1; @@ -4381,7 +4404,7 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) share->max_rows= ~(ha_rows) 0; else share->max_rows= (ha_rows) (((share->db_type() == heap_hton) ? - min(thd->variables.tmp_table_size, + MY_MIN(thd->variables.tmp_table_size, thd->variables.max_heap_table_size) : thd->variables.tmp_table_size) / share->reclength); @@ -4393,11 +4416,11 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) { DBUG_PRINT("info",("Creating group key in temporary table")); share->keys=1; - share->uniques= test(using_unique_constraint); + share->uniques= MY_TEST(using_unique_constraint); table->key_info=keyinfo; keyinfo->key_part=key_part_info; keyinfo->flags=HA_NOSAME; - keyinfo->usable_key_parts= keyinfo->key_parts= 1; + keyinfo->usable_key_parts= keyinfo->user_defined_key_parts= 1; keyinfo->key_length=0; keyinfo->rec_per_key=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; @@ -4413,6 +4436,7 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) { if (!(key_field= field->new_key_field(thd->mem_root, table, group_buff, + key_part_info->length, field->null_ptr, field->null_bit))) goto err; @@ -4610,7 +4634,7 @@ int init_dups_weedout(JOIN *join, uint first_table, int first_fanout_table, uint sjtbl->null_bytes= (jt_null_bits + 7)/8; if (sjtbl->create_sj_weedout_tmp_table(thd)) DBUG_RETURN(TRUE); - join->sj_tmp_tables.push_back(sjtbl->tmp_table); + join->sj_tmp_tables.push_back(sjtbl->tmp_table, thd->mem_root); } else { @@ -4635,6 +4659,74 @@ int init_dups_weedout(JOIN *join, uint first_table, int first_fanout_table, uint /* + @brief + Set up semi-join Loose Scan strategy for execution + + @detail + Other strategies are done in setup_semijoin_dups_elimination(), + however, we need to set up Loose Scan earlier, before make_join_select is + called. This is to prevent make_join_select() from switching full index + scans into quick selects (which will break Loose Scan access). + + @return + 0 OK + 1 Error +*/ + +int setup_semijoin_loosescan(JOIN *join) +{ + uint i; + DBUG_ENTER("setup_semijoin_loosescan"); + + POSITION *pos= join->best_positions + join->const_tables; + for (i= join->const_tables ; i < join->top_join_tab_count; ) + { + JOIN_TAB *tab=join->join_tab + i; + switch (pos->sj_strategy) { + case SJ_OPT_MATERIALIZE: + case SJ_OPT_MATERIALIZE_SCAN: + i+= 1; /* join tabs are embedded in the nest */ + pos += pos->n_sj_tables; + break; + case SJ_OPT_LOOSE_SCAN: + { + /* We jump from the last table to the first one */ + tab->loosescan_match_tab= tab + pos->n_sj_tables - 1; + + /* LooseScan requires records to be produced in order */ + if (tab->select && tab->select->quick) + tab->select->quick->need_sorted_output(); + + for (uint j= i; j < i + pos->n_sj_tables; j++) + join->join_tab[j].inside_loosescan_range= TRUE; + + /* Calculate key length */ + uint keylen= 0; + uint keyno= pos->loosescan_picker.loosescan_key; + for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++) + keylen += tab->table->key_info[keyno].key_part[kp].store_length; + + tab->loosescan_key= keyno; + tab->loosescan_key_len= keylen; + if (pos->n_sj_tables > 1) + tab[pos->n_sj_tables - 1].do_firstmatch= tab; + i+= pos->n_sj_tables; + pos+= pos->n_sj_tables; + break; + } + default: + { + i++; + pos++; + break; + } + } + } + DBUG_RETURN(FALSE); +} + + +/* Setup the strategies to eliminate semi-join duplicates. SYNOPSIS @@ -4742,8 +4834,6 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, for (i= join->const_tables ; i < join->top_join_tab_count; ) { JOIN_TAB *tab=join->join_tab + i; - //POSITION *pos= join->best_positions + i; - uint keylen, keyno; switch (pos->sj_strategy) { case SJ_OPT_MATERIALIZE: case SJ_OPT_MATERIALIZE_SCAN: @@ -4753,26 +4843,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, break; case SJ_OPT_LOOSE_SCAN: { - /* We jump from the last table to the first one */ - tab->loosescan_match_tab= tab + pos->n_sj_tables - 1; - - /* LooseScan requires records to be produced in order */ - if (tab->select && tab->select->quick) - tab->select->quick->need_sorted_output(); - - for (uint j= i; j < i + pos->n_sj_tables; j++) - join->join_tab[j].inside_loosescan_range= TRUE; - - /* Calculate key length */ - keylen= 0; - keyno= pos->loosescan_picker.loosescan_key; - for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++) - keylen += tab->table->key_info[keyno].key_part[kp].store_length; - - tab->loosescan_key= keyno; - tab->loosescan_key_len= keylen; - if (pos->n_sj_tables > 1) - tab[pos->n_sj_tables - 1].do_firstmatch= tab; + /* Setup already handled by setup_semijoin_loosescan */ i+= pos->n_sj_tables; pos+= pos->n_sj_tables; break; @@ -5374,13 +5445,14 @@ TABLE *create_dummy_tmp_table(THD *thd) sjm_table_param.init(); sjm_table_param.field_count= 1; List<Item> sjm_table_cols; - Item *column_item= new Item_int(1); - sjm_table_cols.push_back(column_item); + Item *column_item= new (thd->mem_root) Item_int(thd, 1); + sjm_table_cols.push_back(column_item, thd->mem_root); if (!(table= create_tmp_table(thd, &sjm_table_param, sjm_table_cols, (ORDER*) 0, TRUE /* distinct */, 1, /*save_sum_fields*/ - thd->variables.option_bits | TMP_TABLE_ALL_COLUMNS, + thd->variables.option_bits | + TMP_TABLE_ALL_COLUMNS, HA_POS_ERROR /*rows_limit */, (char*)"dummy", TRUE /* Do not open */))) { @@ -5401,8 +5473,8 @@ TABLE *create_dummy_tmp_table(THD *thd) class select_value_catcher :public select_subselect { public: - select_value_catcher(Item_subselect *item_arg) - :select_subselect(item_arg) + select_value_catcher(THD *thd_arg, Item_subselect *item_arg): + select_subselect(thd_arg, item_arg) {} int send_data(List<Item> &items); int setup(List<Item> *items); @@ -5417,16 +5489,16 @@ int select_value_catcher::setup(List<Item> *items) assigned= FALSE; n_elements= items->elements; - if (!(row= (Item_cache**) sql_alloc(sizeof(Item_cache*)*n_elements))) + if (!(row= (Item_cache**) thd->alloc(sizeof(Item_cache*) * n_elements))) return TRUE; Item *sel_item; List_iterator<Item> li(*items); for (uint i= 0; (sel_item= li++); i++) { - if (!(row[i]= Item_cache::get_cache(sel_item))) + if (!(row[i]= Item_cache::get_cache(thd, sel_item))) return TRUE; - row[i]->setup(sel_item); + row[i]->setup(thd, sel_item); } return FALSE; } @@ -5466,6 +5538,7 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, TABLE_LIST *table; NESTED_JOIN *nested_join; List_iterator<TABLE_LIST> li(*join_list); + THD *thd= join->thd; DBUG_ENTER("setup_jtbm_semi_joins"); while ((table= li++)) @@ -5497,7 +5570,7 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, 0 or 1 record. Examples of both cases: select * from ot where col in (select ... from it where 2>3) - select * from ot where col in (select min(it.key) from it) + select * from ot where col in (select MY_MIN(it.key) from it) in this case, the subquery predicate has not been setup for materialization. In particular, there is no materialized temp.table. @@ -5513,10 +5586,11 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, subselect_single_select_engine *engine= (subselect_single_select_engine*)subq_pred->engine; select_value_catcher *new_sink; - if (!(new_sink= new select_value_catcher(subq_pred))) + if (!(new_sink= + new (thd->mem_root) select_value_catcher(thd, subq_pred))) DBUG_RETURN(TRUE); if (new_sink->setup(&engine->select_lex->join->fields_list) || - engine->select_lex->join->change_result(new_sink) || + engine->select_lex->join->change_result(new_sink, NULL) || engine->exec()) { DBUG_RETURN(TRUE); @@ -5533,13 +5607,14 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, Item *eq_cond; for (uint i= 0; i < subq_pred->left_expr->cols(); i++) { - eq_cond= new Item_func_eq(subq_pred->left_expr->element_index(i), - new_sink->row[i]); + eq_cond= new (thd->mem_root) + Item_func_eq(thd, subq_pred->left_expr->element_index(i), + new_sink->row[i]); if (!eq_cond) DBUG_RETURN(1); - if (!((*join_where)= and_items(*join_where, eq_cond)) || - (*join_where)->fix_fields(join->thd, join_where)) + if (!((*join_where)= and_items(thd, *join_where, eq_cond)) || + (*join_where)->fix_fields(thd, join_where)) DBUG_RETURN(1); } } @@ -5551,7 +5626,7 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, /* Set up a dummy TABLE*, optimizer code needs JOIN_TABs to have TABLE */ TABLE *dummy_table; - if (!(dummy_table= create_dummy_tmp_table(join->thd))) + if (!(dummy_table= create_dummy_tmp_table(thd))) DBUG_RETURN(1); table->table= dummy_table; table->table->pos_in_table_list= table; @@ -5577,11 +5652,11 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, Item *sj_conds= hash_sj_engine->semi_join_conds; - (*join_where)= and_items(*join_where, sj_conds); + (*join_where)= and_items(thd, *join_where, sj_conds); if (!(*join_where)->fixed) - (*join_where)->fix_fields(join->thd, join_where); + (*join_where)->fix_fields(thd, join_where); } - table->table->maybe_null= test(join->mixed_implicit_grouping); + table->table->maybe_null= MY_TEST(join->mixed_implicit_grouping); } if ((nested_join= table->nested_join)) @@ -5768,7 +5843,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables) unmodified, and with injected IN->EXISTS predicates. */ inner_read_time_1= inner_join->best_read; - inner_record_count_1= inner_join->record_count; + inner_record_count_1= inner_join->join_record_count; if (in_to_exists_where && const_tables != table_count) { @@ -5869,8 +5944,8 @@ bool JOIN::choose_subquery_plan(table_map join_tables) Item_in_subselect::test_limit). However, once we allow this, here we should set the correct limit if given in the query. */ - in_subs->unit->global_parameters->select_limit= NULL; - in_subs->unit->set_limit(unit->global_parameters); + in_subs->unit->global_parameters()->select_limit= NULL; + in_subs->unit->set_limit(unit->global_parameters()); /* Set the limit of this JOIN object as well, because normally its being set in the beginning of JOIN::optimize, which was already done. |