diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 2959 |
1 files changed, 2061 insertions, 898 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 529ecd78836..11f112dc54d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -29,6 +29,7 @@ #pragma implementation // gcc: Class implementation #endif +#include <my_global.h> #include "sql_priv.h" #include "unireg.h" #include "sql_select.h" @@ -51,6 +52,7 @@ #include "opt_subselect.h" #include "log_slow.h" #include "sql_derived.h" +#include "sql_statistics.h" #include "debug_sync.h" // DEBUG_SYNC #include <m_ctype.h> @@ -64,8 +66,6 @@ const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref", "index_merge", "hash_ALL", "hash_range", "hash_index", "hash_index_merge" }; -const char *copy_to_tmp_table= "Copying to tmp table"; - struct st_sargable_param; static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array); @@ -88,12 +88,14 @@ void best_access_path(JOIN *join, JOIN_TAB *s, POSITION *pos, POSITION *loose_scan_pos); static void optimize_straight_join(JOIN *join, table_map join_tables); static bool greedy_search(JOIN *join, table_map remaining_tables, - uint depth, uint prune_level); + uint depth, uint prune_level, + uint use_cond_selectivity); static bool best_extension_by_limited_search(JOIN *join, table_map remaining_tables, uint idx, double record_count, double read_time, uint depth, - uint prune_level); + uint prune_level, + uint use_cond_selectivity); static uint determine_search_depth(JOIN* join); C_MODE_START static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2); @@ -105,7 +107,7 @@ C_MODE_END tested and approved. */ static bool find_best(JOIN *join,table_map rest_tables,uint index, - double record_count,double read_time); + double record_count,double read_time, uint use_cond_selectivity); static uint cache_record_length(JOIN *join,uint index); bool get_best_combination(JOIN *join); static store_key *get_store_key(THD *thd, @@ -133,7 +135,8 @@ static COND *build_equal_items(JOIN *join, COND *cond, COND_EQUAL *inherited, List<TABLE_LIST> *join_list, bool ignore_on_conds, - COND_EQUAL **cond_equal_ref); + COND_EQUAL **cond_equal_ref, + bool link_equal_fields= FALSE); static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, COND *cond, COND_EQUAL *cond_equal, @@ -149,12 +152,10 @@ static uint build_bitmap_for_nested_joins(List<TABLE_LIST> *join_list, static COND *optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, bool ignore_on_conds, - Item::cond_result *cond_value, - COND_EQUAL **cond_equal); + Item::cond_result *cond_value, + COND_EQUAL **cond_equal, + int flags= 0); bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); -static bool create_internal_tmp_table_from_heap2(THD *, TABLE *, - ENGINE_COLUMNDEF *, ENGINE_COLUMNDEF **, - int, bool, handlerton *, const char *, bool *); static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table, Procedure *proc); @@ -285,6 +286,59 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, ORDER *order, List<Item> &fields, List<Item> &all_fields, bool is_group_field, bool add_to_all_fields); +static double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, + table_map rem_tables); + +#ifndef DBUG_OFF + +/* + SHOW EXPLAIN testing: wait for, and serve n_calls APC requests. +*/ +void dbug_serve_apcs(THD *thd, int n_calls) +{ + const char *save_proc_info= thd->proc_info; + + /* Busy-wait for n_calls APC requests to arrive and be processed */ + int n_apcs= thd->apc_target.n_calls_processed + n_calls; + while (thd->apc_target.n_calls_processed < n_apcs) + { + /* This is so that mysqltest knows we're ready to serve requests: */ + thd_proc_info(thd, "show_explain_trap"); + my_sleep(30000); + thd_proc_info(thd, save_proc_info); + if (thd->check_killed()) + break; + } +} + + +/* + Debugging: check if @name=value, comparing as integer + + Intended usage: + + DBUG_EXECUTE_IF("show_explain_probe_2", + if (dbug_user_var_equals_int(thd, "select_id", select_id)) + dbug_serve_apcs(thd, 1); + ); + +*/ + +bool dbug_user_var_equals_int(THD *thd, const char *name, int value) +{ + user_var_entry *var; + LEX_STRING varname= {(char*)name, strlen(name)}; + if ((var= get_variable(&thd->user_vars, varname, FALSE))) + { + bool null_value; + longlong var_value= var->val_int(&null_value); + if (!null_value && var_value == value) + return TRUE; + } + return FALSE; +} +#endif + /** This handles SELECT with and without UNION. */ @@ -334,7 +388,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, If LIMIT ROWS EXAMINED interrupted query execution, issue a warning, continue with normal processing and produce an incomplete query result. */ - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT, ER(ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT), thd->accessed_rows_and_keys, @@ -394,7 +448,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, this field from inner subqueries. @return Status - @retval true An error occured. + @retval true An error occurred. @retval false OK. */ @@ -567,7 +621,9 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, List<Item> &all_fields, COND **conds, ORDER *order, - ORDER *group, bool *hidden_group_fields) + ORDER *group, + bool *hidden_group_fields, + uint *reserved) { int res; st_select_lex *const select= thd->lex->current_select; @@ -581,6 +637,13 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, thd->lex->allow_sum_func&= ~((nesting_map)1 << select->nest_level); res= setup_conds(thd, tables, leaves, conds); + if (thd->lex->current_select->first_cond_optimization) + { + if (!res && *conds && ! thd->lex->current_select->merged_into) + (*reserved)= (*conds)->exists2in_reserved_items(); + else + (*reserved)= 0; + } /* it's not wrong to have non-aggregated columns in a WHERE */ select->set_non_agg_field_used(saved_non_agg_field_used); @@ -735,7 +798,7 @@ JOIN::prepare(Item ***rref_pointer_array, setup_without_group(thd, (*rref_pointer_array), tables_list, select_lex->leaf_tables, fields_list, all_fields, &conds, order, group_list, - &hidden_group_fields)) + &hidden_group_fields, &select_lex->select_n_reserved)) DBUG_RETURN(-1); /* purecov: inspected */ ref_pointer_array= *rref_pointer_array; @@ -973,6 +1036,34 @@ err: DBUG_RETURN(res); /* purecov: inspected */ } +int JOIN::optimize() +{ + bool was_optimized= optimized; + int res= optimize_inner(); + /* + If we're inside a non-correlated subquery, this function may be + called for the second time after the subquery has been executed + and deleted. The second call will not produce a valid query plan, it will + short-circuit because optimized==TRUE. + + "was_optimized != optimized" is here to handle this case: + - first optimization starts, gets an error (from a const. cheap + subquery), returns 1 + - another JOIN::optimize() call made, and now join->optimize() will + return 0, even though we never had a query plan. + */ + if (was_optimized != optimized && !res && have_query_plan != QEP_DELETED) + { + create_explain_query_if_not_exists(thd->lex, thd->mem_root); + have_query_plan= QEP_AVAILABLE; + save_explain_data(thd->lex->explain, false /* can overwrite */, + need_tmp, + !skip_sort_order && !no_order && (order || group_list), + select_distinct); + } + return res; +} + /** global select optimisation. @@ -987,7 +1078,7 @@ err: */ int -JOIN::optimize() +JOIN::optimize_inner() { ulonglong select_opts_for_readinfo; uint no_jbuf_after; @@ -1000,7 +1091,7 @@ JOIN::optimize() optimized= 1; DEBUG_SYNC(thd, "before_join_optimize"); - thd_proc_info(thd, "optimizing"); + THD_STAGE_INFO(thd, stage_optimizing); set_allowed_join_cache_types(); need_distinct= TRUE; @@ -1020,6 +1111,24 @@ JOIN::optimize() // Update used tables after all handling derived table procedures select_lex->update_used_tables(); + /* + In fact we transform underlying subqueries after their 'prepare' phase and + before 'optimize' from upper query 'optimize' to allow semijoin + conversion happened (which done in the same way. + */ + if(select_lex->first_cond_optimization && + conds && conds->walk(&Item::exists2in_processor, 0, (uchar *)thd)) + DBUG_RETURN(1); + /* +TODO: make view to decide if it is possible to write to WHERE directly or make Semi-Joins able to process ON condition if it is possible + for (TABLE_LIST *tbl= tables_list; tbl; tbl= tbl->next_local) + { + if (tbl->on_expr && + tbl->on_expr->walk(&Item::exists2in_processor, 0, (uchar *)thd)) + DBUG_RETURN(1); + } + */ + if (transform_max_min_subquery()) DBUG_RETURN(1); /* purecov: inspected */ @@ -1111,7 +1220,7 @@ JOIN::optimize() DBUG_RETURN(1); conds= optimize_cond(this, conds, join_list, FALSE, - &cond_value, &cond_equal); + &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); if (thd->is_error()) { @@ -1178,15 +1287,16 @@ JOIN::optimize() (tbl->embedding && tbl->embedding->sj_on_expr)) { Item *prune_cond= tbl->on_expr? tbl->on_expr : conds; - tbl->table->no_partitions_used= prune_partitions(thd, tbl->table, - prune_cond); - } + tbl->table->all_partitions_pruned_away= prune_partitions(thd, + tbl->table, + prune_cond); + } } } #endif /* - Try to optimize count(*), min() and max() to const fields if + Try to optimize count(*), MY_MIN() and MY_MAX() to const fields if there is implicit grouping (aggregate functions but no group_list). In this case, the result set shall only contain one row. @@ -1260,7 +1370,7 @@ JOIN::optimize() MEM_UNDEFINED(&sort_by_table, sizeof(sort_by_table)); /* Calculate how to do the join */ - thd_proc_info(thd, "statistics"); + THD_STAGE_INFO(thd, stage_statistics); if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) || thd->is_fatal_error) { @@ -1285,7 +1395,7 @@ JOIN::optimize() select_distinct= select_distinct && (const_tables != table_count); } - thd_proc_info(thd, "preparing"); + THD_STAGE_INFO(thd, stage_preparing); if (result->initialize_tables(this)) { DBUG_PRINT("error",("Error: initialize_tables() failed")); @@ -1457,6 +1567,9 @@ JOIN::optimize() /* Cache constant expressions in WHERE, HAVING, ON clauses. */ cache_const_exprs(); + if (setup_semijoin_loosescan(this)) + DBUG_RETURN(1); + if (make_join_select(this, select, conds)) { zero_result_cause= @@ -1513,9 +1626,10 @@ JOIN::optimize() We have found that grouping can be removed since groups correspond to only one row anyway, but we still have to guarantee correct result order. The line below effectively rewrites the query from GROUP BY - <fields> to ORDER BY <fields>. There are two exceptions: + <fields> to ORDER BY <fields>. There are three exceptions: - if skip_sort_order is set (see above), then we can simply skip GROUP BY; + - if we are in a subquery, we don't have to maintain order - we can only rewrite ORDER BY if the ORDER BY fields are 'compatible' with the GROUP BY ones, i.e. either one is a prefix of another. We only check if the ORDER BY is a prefix of GROUP BY. In this case @@ -1525,7 +1639,13 @@ JOIN::optimize() 'order' as is. */ if (!order || test_if_subpart(group_list, order)) - order= skip_sort_order ? 0 : group_list; + { + if (skip_sort_order || + select_lex->master_unit()->item) // This is a subquery + order= NULL; + else + order= group_list; + } /* If we have an IGNORE INDEX FOR GROUP BY(fields) clause, this must be rewritten to IGNORE INDEX FOR ORDER BY(fields). @@ -1569,8 +1689,10 @@ JOIN::optimize() JOIN_TAB *tab= &join_tab[const_tables]; bool all_order_fields_used; if (order) + { skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1, &tab->table->keys_in_use_for_order_by); + } if ((group_list=create_distinct_group(thd, select_lex->ref_pointer_array, order, fields_list, all_fields, &all_order_fields_used))) @@ -1695,7 +1817,7 @@ JOIN::optimize() /* Perform FULLTEXT search before all regular searches */ if (!(select_options & SELECT_DESCRIBE)) - init_ftfuncs(thd, select_lex, test(order)); + init_ftfuncs(thd, select_lex, MY_TEST(order)); if (optimize_unflattened_subqueries()) DBUG_RETURN(1); @@ -1870,7 +1992,7 @@ int JOIN::init_execution() if (need_tmp) { DBUG_PRINT("info",("Creating tmp table")); - thd_proc_info(thd, "Creating tmp table"); + THD_STAGE_INFO(thd, stage_copying_to_tmp_table); init_items_ref_array(); @@ -1915,7 +2037,7 @@ int JOIN::init_execution() if (group_list && simple_group) { DBUG_PRINT("info",("Sorting for group")); - thd_proc_info(thd, "Sorting for group"); + THD_STAGE_INFO(thd, stage_sorting_for_group); if (create_sort_index(thd, this, group_list, HA_POS_ERROR, HA_POS_ERROR, FALSE) || alloc_group_fields(this, group_list) || @@ -1938,7 +2060,8 @@ int JOIN::init_execution() if (!group_list && ! exec_tmp_table1->distinct && order && simple_order) { - thd_proc_info(thd, "Sorting for order"); + DBUG_PRINT("info",("Sorting for order")); + THD_STAGE_INFO(thd, stage_sorting_for_order); if (create_sort_index(thd, this, order, HA_POS_ERROR, HA_POS_ERROR, TRUE)) { @@ -2153,8 +2276,7 @@ JOIN::reinit() DBUG_ENTER("JOIN::reinit"); unit->offset_limit_cnt= (ha_rows)(select_lex->offset_limit ? - select_lex->offset_limit->val_uint() : - ULL(0)); + select_lex->offset_limit->val_uint() : 0); first_record= 0; cleaned= false; @@ -2213,7 +2335,7 @@ JOIN::reinit() } if (!(select_options & SELECT_DESCRIBE)) - init_ftfuncs(thd, select_lex, test(order)); + init_ftfuncs(thd, select_lex, MY_TEST(order)); DBUG_RETURN(0); } @@ -2253,6 +2375,59 @@ JOIN::save_join_tab() } +void JOIN::save_explain_data(Explain_query *output, bool can_overwrite, + bool need_tmp_table, bool need_order, + bool distinct) +{ + if (select_lex->select_number != UINT_MAX && + select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && + have_query_plan != JOIN::QEP_NOT_PRESENT_YET && + have_query_plan != JOIN::QEP_DELETED && // this happens when there was + // no QEP ever, but then + //cleanup() is called multiple times + output && // for "SET" command in SPs. + (can_overwrite? true: !output->get_select(select_lex->select_number))) + { + const char *message= NULL; + if (!table_count || !tables_list || zero_result_cause) + { + /* It's a degenerate join */ + message= zero_result_cause ? zero_result_cause : "No tables used"; + } + save_explain_data_intern(thd->lex->explain, need_tmp_table, need_order, + distinct, message); + } +} + + +void JOIN::exec() +{ + DBUG_EXECUTE_IF("show_explain_probe_join_exec_start", + if (dbug_user_var_equals_int(thd, + "show_explain_probe_select_id", + select_lex->select_number)) + dbug_serve_apcs(thd, 1); + ); + exec_inner(); + + if (!exec_saved_explain) + { + save_explain_data(thd->lex->explain, true /* can overwrite */, + need_tmp, + order != 0 && !skip_sort_order, + select_distinct); + exec_saved_explain= true; + } + + DBUG_EXECUTE_IF("show_explain_probe_join_exec_end", + if (dbug_user_var_equals_int(thd, + "show_explain_probe_select_id", + select_lex->select_number)) + dbug_serve_apcs(thd, 1); + ); +} + + /** Exec select. @@ -2264,15 +2439,17 @@ JOIN::save_join_tab() @todo When can we have here thd->net.report_error not zero? */ -void -JOIN::exec() + +void JOIN::exec_inner() { List<Item> *columns_list= &fields_list; int tmp_error; DBUG_ENTER("JOIN::exec"); - thd_proc_info(thd, "executing"); + const bool has_group_by= this->group; + + THD_STAGE_INFO(thd, stage_executing); error= 0; if (procedure) { @@ -2280,7 +2457,8 @@ JOIN::exec() if (procedure->change_columns(procedure_fields_list) || result->prepare(procedure_fields_list, unit)) { - thd->limit_found_rows= thd->examined_row_count= 0; + thd->set_examined_row_count(0); + thd->limit_found_rows= 0; DBUG_VOID_RETURN; } columns_list= &procedure_fields_list; @@ -2320,7 +2498,7 @@ JOIN::exec() error= 1; else send_records= ((select_options & OPTION_FOUND_ROWS) ? 1 : - thd->sent_row_count); + thd->get_sent_row_count()); } else send_records= 0; @@ -2332,7 +2510,7 @@ JOIN::exec() } /* Single select (without union) always returns 0 or 1 row */ thd->limit_found_rows= send_records; - thd->examined_row_count= 0; + thd->set_examined_row_count(0); DBUG_VOID_RETURN; } /* @@ -2385,7 +2563,7 @@ JOIN::exec() Item *cur_const_item; while ((cur_const_item= const_item_it++)) { - cur_const_item->val_str(&cur_const_item->str_value); + cur_const_item->val_str(); // This caches val_str() to Item::str_value if (thd->is_error()) { error= thd->is_error(); @@ -2474,13 +2652,13 @@ JOIN::exec() curr_tmp_table= exec_tmp_table1; /* Copy data to the temporary table */ - thd_proc_info(thd, copy_to_tmp_table); + THD_STAGE_INFO(thd, stage_copying_to_tmp_table); DBUG_PRINT("info", ("%s", thd->proc_info)); if (!curr_join->sort_and_group && curr_join->const_tables != curr_join->table_count) { JOIN_TAB *first_tab= curr_join->join_tab + curr_join->const_tables; - first_tab->sorted= test(first_tab->loosescan_match_tab); + first_tab->sorted= MY_TEST(first_tab->loosescan_match_tab); } Procedure *save_proc= curr_join->procedure; @@ -2575,6 +2753,10 @@ JOIN::exec() DBUG_PRINT("info",("Creating group table")); /* Free first data from old join */ + + /* + psergey-todo: this is the place of pre-mature JOIN::free call. + */ curr_join->join_free(); if (curr_join->make_simple_join(this, curr_tmp_table)) DBUG_VOID_RETURN; @@ -2615,11 +2797,12 @@ JOIN::exec() } if (curr_join->group_list) { - thd_proc_info(thd, "Creating sort index"); if (curr_join->join_tab == join_tab && save_join_tab()) { DBUG_VOID_RETURN; } + DBUG_PRINT("info",("Sorting for index")); + THD_STAGE_INFO(thd, stage_creating_sort_index); if (create_sort_index(thd, curr_join, curr_join->group_list, HA_POS_ERROR, HA_POS_ERROR, FALSE) || make_group_fields(this, curr_join)) @@ -2629,7 +2812,7 @@ JOIN::exec() sortorder= curr_join->sortorder; } - thd_proc_info(thd, "Copying to group table"); + THD_STAGE_INFO(thd, stage_copying_to_group_table); DBUG_PRINT("info", ("%s", thd->proc_info)); if (curr_join != this) { @@ -2655,7 +2838,7 @@ JOIN::exec() curr_join->const_tables != curr_join->table_count) { JOIN_TAB *first_tab= curr_join->join_tab + curr_join->const_tables; - first_tab->sorted= test(first_tab->loosescan_match_tab); + first_tab->sorted= MY_TEST(first_tab->loosescan_match_tab); } tmp_error= -1; if (setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) || @@ -2704,7 +2887,7 @@ JOIN::exec() if (curr_join->select_distinct && ! curr_join->group_list) { - thd_proc_info(thd, "Removing duplicates"); + THD_STAGE_INFO(thd, stage_removing_duplicates); if (remove_duplicates(curr_join, curr_tmp_table, *curr_fields_list, curr_join->tmp_having)) DBUG_VOID_RETURN; @@ -2775,7 +2958,7 @@ JOIN::exec() if (curr_join->group_list || curr_join->order) { DBUG_PRINT("info",("Sorting for send_result_set_metadata")); - thd_proc_info(thd, "Sorting result"); + THD_STAGE_INFO(thd, stage_sorting_result); /* If we have already done the group, add HAVING to sorted table */ if (curr_join->tmp_having && ! curr_join->group_list && ! curr_join->sort_and_group) @@ -2783,6 +2966,7 @@ JOIN::exec() JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables]; table_map used_tables= (curr_join->const_table_map | curr_table->table->map); + curr_join->tmp_having->update_used_tables(); Item* sort_table_cond= make_cond_for_table(thd, curr_join->tmp_having, used_tables, @@ -2875,12 +3059,39 @@ JOIN::exec() the query. XXX: it's never shown in EXPLAIN! OPTION_FOUND_ROWS supersedes LIMIT and is taken into account. */ - if (create_sort_index(thd, curr_join, - curr_join->group_list ? - curr_join->group_list : curr_join->order, - curr_join->select_limit, - (select_options & OPTION_FOUND_ROWS ? - HA_POS_ERROR : unit->select_limit_cnt), + DBUG_PRINT("info",("Sorting for order by/group by")); + ORDER *order_arg= + curr_join->group_list ? curr_join->group_list : curr_join->order; + /* + filesort_limit: Return only this many rows from filesort(). + We can use select_limit_cnt only if we have no group_by and 1 table. + This allows us to use Bounded_queue for queries like: + "select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1;" + select_limit == HA_POS_ERROR (we need a full table scan) + unit->select_limit_cnt == 1 (we only need one row in the result set) + */ + const ha_rows filesort_limit_arg= + (has_group_by || curr_join->table_count > 1) + ? curr_join->select_limit : unit->select_limit_cnt; + const ha_rows select_limit_arg= + select_options & OPTION_FOUND_ROWS + ? HA_POS_ERROR : unit->select_limit_cnt; + curr_join->filesort_found_rows= filesort_limit_arg != HA_POS_ERROR; + + DBUG_PRINT("info", ("has_group_by %d " + "curr_join->table_count %d " + "curr_join->m_select_limit %d " + "unit->select_limit_cnt %d", + has_group_by, + curr_join->table_count, + (int) curr_join->select_limit, + (int) unit->select_limit_cnt)); + + if (create_sort_index(thd, + curr_join, + order_arg, + filesort_limit_arg, + select_limit_arg, curr_join->group_list ? FALSE : TRUE)) DBUG_VOID_RETURN; sortorder= curr_join->sortorder; @@ -2906,18 +3117,26 @@ JOIN::exec() curr_join->fields= curr_fields_list; curr_join->procedure= procedure; - thd_proc_info(thd, "Sending data"); + THD_STAGE_INFO(thd, stage_sending_data); DBUG_PRINT("info", ("%s", thd->proc_info)); result->send_result_set_metadata((procedure ? curr_join->procedure_fields_list : *curr_fields_list), Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF); error= do_select(curr_join, curr_fields_list, NULL, procedure); thd->limit_found_rows= curr_join->send_records - curr_join->duplicate_rows; + if (curr_join->order && curr_join->sortorder && + curr_join->filesort_found_rows) + { + /* Use info provided by filesort. */ + DBUG_ASSERT(curr_join->table_count > curr_join->const_tables); + JOIN_TAB *tab= curr_join->join_tab + curr_join->const_tables; + thd->limit_found_rows= tab->records; + } /* Accumulate the counts from all join iterations of all join parts. */ - thd->examined_row_count+= curr_join->examined_rows; + thd->inc_examined_row_count(curr_join->examined_rows); DBUG_PRINT("counts", ("thd->examined_row_count: %lu", - (ulong) thd->examined_row_count)); + (ulong) thd->get_examined_row_count())); /* With EXPLAIN EXTENDED we have to restore original ref_array @@ -2966,6 +3185,7 @@ JOIN::destroy() */ tmp_table_param.cleanup(); tmp_join->tmp_table_param.copy_field= 0; + cleanup(1); DBUG_RETURN(tmp_join->destroy()); } cond_equal= 0; @@ -3053,7 +3273,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex) { - bool err; + int err= 0; bool free_join= 1; DBUG_ENTER("mysql_select"); @@ -3071,11 +3291,6 @@ mysql_select(THD *thd, Item ***rref_pointer_array, { if (select_lex->linkage != GLOBAL_OPTIONS_TYPE) { - //here is EXPLAIN of subselect or derived table - if (join->change_result(result)) - { - DBUG_RETURN(TRUE); - } /* Original join tabs might be overwritten at first subselect execution. So we need to restore them. @@ -3108,7 +3323,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, if (!(join= new JOIN(thd, fields, select_options, result))) DBUG_RETURN(TRUE); - thd_proc_info(thd, "init"); + THD_STAGE_INFO(thd, stage_init); thd->lex->used_tables=0; if ((err= join->prepare(rref_pointer_array, tables, wild_num, conds, og_num, order, false, group, having, proc_param, @@ -3143,11 +3358,11 @@ mysql_select(THD *thd, Item ***rref_pointer_array, err: if (free_join) { - thd_proc_info(thd, "end"); + THD_STAGE_INFO(thd, stage_end); err|= select_lex->cleanup(); DBUG_RETURN(err || thd->is_error()); } - DBUG_RETURN(join->error); + DBUG_RETURN(join->error ? join->error: err); } @@ -3262,11 +3477,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, table_vector[i]=s->table=table=tables->table; table->pos_in_table_list= tables; error= tables->fetch_number_of_rows(); + set_statistics_for_table(join->thd, table); + bitmap_clear_all(&table->cond_set); #ifdef WITH_PARTITION_STORAGE_ENGINE - const bool no_partitions_used= table->no_partitions_used; + const bool all_partitions_pruned_away= table->all_partitions_pruned_away; #else - const bool no_partitions_used= FALSE; + const bool all_partitions_pruned_away= FALSE; #endif DBUG_EXECUTE_IF("bug11747970_raise_error", @@ -3287,8 +3504,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, s->dependent= tables->dep_tables; if (tables->schema_table) - table->file->stats.records= 2; - table->quick_condition_rows= table->file->stats.records; + table->file->stats.records= table->used_stat_records= 2; + table->quick_condition_rows= table->stat_records(); s->on_expr_ref= &tables->on_expr; if (*s->on_expr_ref) @@ -3297,7 +3514,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (!table->is_filled_at_execution() && ((!table->file->stats.records && (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) || - no_partitions_used) && !embedding) + all_partitions_pruned_away) && !embedding) { // Empty table s->dependent= 0; // Ignore LEFT JOIN depend. no_rows_const_tables |= table->map; @@ -3341,7 +3558,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, (table->s->system || (table->file->stats.records <= 1 && (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) || - no_partitions_used) && + all_partitions_pruned_away) && !s->dependent && !table->fulltext_searched && !join->no_const_tables) { @@ -3591,7 +3808,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, (!embedding || (embedding->sj_on_expr && !embedding->embedding))) { key_map base_part, base_const_ref, base_eq_part; - base_part.set_prefix(keyinfo->key_parts); + base_part.set_prefix(keyinfo->user_defined_key_parts); base_const_ref= const_ref; base_const_ref.intersect(base_part); base_eq_part= eq_part; @@ -3734,7 +3951,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, This is can't be to high as otherwise we are likely to use table scan. */ - s->worst_seeks= min((double) s->found_records / 10, + s->worst_seeks= MY_MIN((double) s->found_records / 10, (double) s->read_time*3); if (s->worst_seeks < 2.0) // Fix for small tables s->worst_seeks=2.0; @@ -3744,6 +3961,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, all select distinct fields participate in one index. */ add_group_and_distinct_keys(join, s); + + s->table->cond_selectivity= 1.0; /* Perform range analysis if there are keys it could use (1). @@ -3752,7 +3971,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, Don't do range analysis for materialized subqueries (4). Don't do range analysis for materialized derived tables (5) */ - if (!s->const_keys.is_clear_all() && // (1) + if ((!s->const_keys.is_clear_all() || + !bitmap_is_clear_all(&s->table->cond_set)) && // (1) (!s->table->pos_in_table_list->embedding || // (2) (s->table->pos_in_table_list->embedding && // (3) s->table->pos_in_table_list->embedding->sj_on_expr)) && // (3) @@ -3760,20 +3980,37 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, !(s->table->pos_in_table_list->derived && // (5) s->table->pos_in_table_list->is_materialized_derived())) // (5) { - ha_rows records; - SQL_SELECT *select; - select= make_select(s->table, found_const_table_map, - found_const_table_map, - *s->on_expr_ref ? *s->on_expr_ref : conds, - 1, &error); - if (!select) - goto error; - records= get_quick_record_count(join->thd, select, s->table, - &s->const_keys, join->row_limit); - s->quick=select->quick; - s->needed_reg=select->needed_reg; - select->quick=0; - if (records == 0 && s->table->reginfo.impossible_range) + bool impossible_range= FALSE; + ha_rows records= HA_POS_ERROR; + SQL_SELECT *select= 0; + if (!s->const_keys.is_clear_all()) + { + select= make_select(s->table, found_const_table_map, + found_const_table_map, + *s->on_expr_ref ? *s->on_expr_ref : conds, + 1, &error); + if (!select) + goto error; + records= get_quick_record_count(join->thd, select, s->table, + &s->const_keys, join->row_limit); + s->quick=select->quick; + s->needed_reg=select->needed_reg; + select->quick=0; + impossible_range= records == 0 && s->table->reginfo.impossible_range; + } + if (!impossible_range) + { + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + calculate_cond_selectivity_for_table(join->thd, s->table, + *s->on_expr_ref ? + *s->on_expr_ref : conds); + if (s->table->reginfo.impossible_range) + { + impossible_range= TRUE; + records= 0; + } + } + if (impossible_range) { /* Impossible WHERE or ON expression @@ -3787,7 +4024,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (*s->on_expr_ref) { /* Generate empty row */ - s->info= "Impossible ON condition"; + s->info= ET_IMPOSSIBLE_ON_CONDITION; found_const_table_map|= s->table->map; s->type= JT_CONST; mark_as_null_row(s->table); // All fields are NULL @@ -3798,8 +4035,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, s->found_records=records; s->read_time= s->quick ? s->quick->read_time : 0.0; } - delete select; + if (select) + delete select; } + } if (pull_out_semijoin_tables(join)) @@ -3871,7 +4110,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, DEBUG_SYNC(join->thd, "inside_make_join_statistics"); /* Generate an execution plan from the found optimal join order. */ - DBUG_RETURN(join->thd->killed || get_best_combination(join)); + DBUG_RETURN(join->thd->check_killed() || get_best_combination(join)); error: /* @@ -4170,11 +4409,12 @@ add_key_field(JOIN *join, else if (!(field->flags & PART_KEY_FLAG)) { // Don't remove column IS NULL on a LEFT JOIN table - if (!eq_func || (*value)->type() != Item::NULL_ITEM || - !field->table->maybe_null || field->null_ptr) - return; // Not a key. Skip it - optimize= KEY_OPTIMIZE_EXISTS; - DBUG_ASSERT(num_values == 1); + if (eq_func && (*value)->type() == Item::NULL_ITEM && + field->table->maybe_null && !field->null_ptr) + { + optimize= KEY_OPTIMIZE_EXISTS; + DBUG_ASSERT(num_values == 1); + } } if (optimize != KEY_OPTIMIZE_EXISTS) { @@ -4223,7 +4463,10 @@ add_key_field(JOIN *join, break; } if (is_const) + { stat[0].const_keys.merge(possible_keys); + bitmap_set_bit(&field->table->cond_set, field->field_index); + } else if (!eq_func) { /* @@ -4240,6 +4483,32 @@ add_key_field(JOIN *join, if (!eq_func) // eq_func is NEVER true when num_values > 1 return; + if ((*value)->cmp_type() == TIME_RESULT && + field->cmp_type() != TIME_RESULT) + return; + + /* + Note, for ITEM/ENUM columns: + - field->cmp_type() returns INT_RESULT + - field->result_type() returns STRING_RESULT + - field->type() returns MYSQL_TYPE_STRING + + Using field->real_type() to detect ENUM/SET, + as they need a special handling: + - Conditions between a ENUM/SET filter and a TIME expression + cannot be optimized. They were filtered out in the previous if block. + - It's Ok to use ref access for an ENUM/SET field compared to an + INT/REAL/DECIMAL expression. + - It's Ok to use ref for an ENUM/SET field compared to a STRING + expression if the collation of the field and the collation of + the condition match. + */ + if ((field->real_type() == MYSQL_TYPE_ENUM || + field->real_type() == MYSQL_TYPE_SET) && + (*value)->cmp_type () == STRING_RESULT && + field->charset() != cond->compare_collation()) + return; + /* We can't use indexes when comparing a string index to a number or two strings if the effective collation @@ -4250,7 +4519,7 @@ add_key_field(JOIN *join, { if ((*value)->cmp_type() != STRING_RESULT) return; - if (((Field_str*)field)->charset() != cond->compare_collation()) + if (field->charset() != cond->compare_collation()) return; } } @@ -4730,7 +4999,18 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field) } -#define FT_KEYPART (MAX_REF_PARTS+10) +/* + A key part number that means we're using a fulltext scan. + + In order not to confuse it with regular equalities, we need to pick + a number that's greater than MAX_REF_PARTS. + + Hash Join code stores field->field_index in KEYUSE::keypart, so the + number needs to be bigger than MAX_FIELDS, also. + + CAUTION: sql_test.cc has its own definition of FT_KEYPART. +*/ +#define FT_KEYPART (MAX_FIELDS+10) static bool add_ft_keys(DYNAMIC_ARRAY *keyuse_array, @@ -4811,8 +5091,8 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) if (a->keypart != b->keypart) return (int) (a->keypart - b->keypart); // Place const values before other ones - if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) - - test((b->used_tables & ~OUTER_REF_TABLE_BIT)))) + if ((res= MY_TEST((a->used_tables & ~OUTER_REF_TABLE_BIT)) - + MY_TEST((b->used_tables & ~OUTER_REF_TABLE_BIT)))) return res; /* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */ return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) - @@ -4947,7 +5227,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, uint and_level,i; KEY_FIELD *key_fields, *end, *field; uint sz; - uint m= max(select_lex->max_equal_elems,1); + uint m= MY_MAX(select_lex->max_equal_elems,1); DBUG_ENTER("update_ref_and_keys"); DBUG_PRINT("enter", ("normal_tables: %llx", normal_tables)); @@ -4993,7 +5273,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, can be not more than select_lex->max_equal_elems such substitutions. */ - sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))* + sz= MY_MAX(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))* ((sel->cond_count*2 + sel->between_count)*m+1); if (!(key_fields=(KEY_FIELD*) thd->alloc(sz))) DBUG_RETURN(TRUE); /* purecov: inspected */ @@ -5004,7 +5284,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, /* set a barrier for the array of SARGABLE_PARAM */ (*sargables)[0].field= 0; - if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64)) + if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64, + MYF(MY_THREAD_SPECIFIC))) DBUG_RETURN(TRUE); if (cond) @@ -5104,7 +5385,8 @@ static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse, { if (!use->is_for_hash_join()) { - if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL) + if (!(use->used_tables & ~OUTER_REF_TABLE_BIT) && + use->optimize != KEY_OPTIMIZE_REF_OR_NULL) use->table->const_key_parts[use->key]|= use->keypart_map; if (use->keypart != FT_KEYPART) { @@ -5175,7 +5457,7 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array) DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END); TABLE *tmp_table=join->table[tablenr]; if (tmp_table) // already created - keyuse->ref_table_rows= max(tmp_table->file->stats.records, 100); + keyuse->ref_table_rows= MY_MAX(tmp_table->file->stats.records, 100); } } /* @@ -5367,6 +5649,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) join->positions[idx].table= table; join->positions[idx].key=key; join->positions[idx].records_read=1.0; /* This is a const table */ + join->positions[idx].cond_selectivity= 1.0; join->positions[idx].ref_depend_map= 0; // join->positions[idx].loosescan_key= MAX_KEY; /* Not a LooseScan */ @@ -5386,12 +5669,39 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) } -/* Estimate of the number matching candidates in the joined table */ +/* + Estimate how many records we will get if we read just this table and apply + a part of WHERE that can be checked for it. + + @detail + Estimate how many records we will get if we + - read the given table with its "independent" access method (either quick + select or full table/index scan), + - apply the part of WHERE that refers only to this table. + + @seealso + table_cond_selectivity() produces selectivity of condition that is checked + after joining rows from this table to rows from preceding tables. +*/ inline -ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint) +double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, + uint use_cond_selectivity) { - ha_rows records= s->found_records; + ha_rows records; + double dbl_records; + + if (use_cond_selectivity > 1) + { + TABLE *table= s->table; + double sel= table->cond_selectivity; + double table_records= table->stat_records(); + dbl_records= table_records * sel; + return dbl_records; + } + + records = s->found_records; + /* If there is a filtering condition on the table (i.e. ref analyzer found at least one "table.keyXpartY= exprZ", where exprZ refers only to tables @@ -5411,7 +5721,8 @@ ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint) if (s->table->quick_condition_rows != s->found_records) records= s->table->quick_condition_rows; - return records; + dbl_records= records; + return dbl_records; } @@ -5454,6 +5765,7 @@ best_access_path(JOIN *join, POSITION *loose_scan_pos) { THD *thd= join->thd; + uint use_cond_selectivity= thd->variables.optimizer_use_condition_selectivity; KEYUSE *best_key= 0; uint best_max_key_part= 0; my_bool found_constraint= 0; @@ -5595,7 +5907,7 @@ best_access_path(JOIN *join, } else { - found_constraint= test(found_part); + found_constraint= MY_TEST(found_part); loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part); /* Check if we found full key */ @@ -5604,7 +5916,7 @@ best_access_path(JOIN *join, { /* use eq key */ max_key_part= (uint) ~0; if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || - test(key_flags & HA_EXT_NOSAME)) + MY_TEST(key_flags & HA_EXT_NOSAME)) { tmp = prev_record_reads(join->positions, idx, found_ref); records=1.0; @@ -5641,7 +5953,7 @@ best_access_path(JOIN *join, else { uint key_parts= table->actual_n_key_parts(keyinfo); - if (!(records=keyinfo->rec_per_key[key_parts-1])) + if (!(records= keyinfo->actual_rec_per_key(key_parts-1))) { /* Prefer longer keys */ records= ((double) s->records / (double) rec * @@ -5678,7 +5990,7 @@ best_access_path(JOIN *join, tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); else tmp= table->file->read_time(key, 1, - (ha_rows) min(tmp,s->worst_seeks)); + (ha_rows) MY_MIN(tmp,s->worst_seeks)); tmp= COST_MULT(tmp, record_count); } } @@ -5691,7 +6003,7 @@ best_access_path(JOIN *join, */ if ((found_part & 1) && (!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) || - found_part == PREV_BITS(uint,keyinfo->key_parts))) + found_part == PREV_BITS(uint,keyinfo->user_defined_key_parts))) { max_key_part= max_part_bit(found_part); /* @@ -5735,14 +6047,14 @@ best_access_path(JOIN *join, */ if (table->quick_keys.is_set(key) && !found_ref && //(C1) table->quick_key_parts[key] == max_key_part && //(C2) - table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3) + table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part)) //(C3) { tmp= records= (double) table->quick_rows[key]; } else { /* Check if we have statistic about the distribution */ - if ((records= keyinfo->rec_per_key[max_key_part-1])) + if ((records= keyinfo->actual_rec_per_key(max_key_part-1))) { /* Fix for the case where the index statistics is too @@ -5785,7 +6097,7 @@ best_access_path(JOIN *join, */ double rec_per_key; if (!(rec_per_key=(double) - keyinfo->rec_per_key[keyinfo->key_parts-1])) + keyinfo->rec_per_key[keyinfo->user_defined_key_parts-1])) rec_per_key=(double) s->records/rec+1; if (!s->records) @@ -5795,10 +6107,10 @@ best_access_path(JOIN *join, else { double a=s->records*0.01; - if (keyinfo->key_parts > 1) + if (keyinfo->user_defined_key_parts > 1) tmp= (max_key_part * (rec_per_key - a) + - a*keyinfo->key_parts - rec_per_key)/ - (keyinfo->key_parts-1); + a*keyinfo->user_defined_key_parts - rec_per_key)/ + (keyinfo->user_defined_key_parts-1); else tmp= a; set_if_bigger(tmp,1.0); @@ -5829,8 +6141,8 @@ best_access_path(JOIN *join, table->quick_key_parts[key] <= max_key_part && const_part & ((key_part_map)1 << table->quick_key_parts[key]) && - table->quick_n_ranges[key] == 1 + test(ref_or_null_part & - const_part) && + table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part & + const_part) && records > (double) table->quick_rows[key]) { tmp= records= (double) table->quick_rows[key]; @@ -5843,7 +6155,7 @@ best_access_path(JOIN *join, tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); else tmp= table->file->read_time(key, 1, - (ha_rows) min(tmp,s->worst_seeks)); + (ha_rows) MY_MIN(tmp,s->worst_seeks)); tmp= COST_MULT(tmp, record_count); } else @@ -5885,7 +6197,8 @@ best_access_path(JOIN *join, { double join_sel= 0.1; /* Estimate the cost of the hash join access to the table */ - ha_rows rnd_records= matching_candidates_in_table(s, found_constraint); + double rnd_records= matching_candidates_in_table(s, found_constraint, + use_cond_selectivity); tmp= s->quick ? s->quick->read_time : s->scan_time(); double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; @@ -5901,7 +6214,7 @@ best_access_path(JOIN *join, COST_MULT((record_count*join_sel) / TIME_FOR_COMPARE, rnd_records)); best= tmp; - records= rows2double(rnd_records); + records= rnd_records; best_key= hj_start_key; best_ref_depends_map= 0; best_uses_jbuf= TRUE; @@ -5948,7 +6261,8 @@ best_access_path(JOIN *join, !(s->table->force_index && best_key && !s->quick) && // (4) !(best_key && s->table->pos_in_table_list->jtbm_subselect)) // (5) { // Check full join - ha_rows rnd_records= matching_candidates_in_table(s, found_constraint); + double rnd_records= matching_candidates_in_table(s, found_constraint, + use_cond_selectivity); /* Range optimizer never proposes a RANGE if it isn't better @@ -5976,7 +6290,11 @@ best_access_path(JOIN *join, else { /* Estimate cost of reading table. */ - tmp= s->scan_time(); + if (s->table->force_index && !best_key) // index scan + tmp= s->table->file->read_time(s->ref.key, 1, s->records); + else // table scan + tmp= s->scan_time(); + if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache { /* @@ -6021,12 +6339,12 @@ best_access_path(JOIN *join, will ensure that this will be used */ best= tmp; - records= rows2double(rnd_records); + records= rnd_records; best_key= 0; /* range/index_merge/ALL/index access method are "independent", so: */ best_ref_depends_map= 0; - best_uses_jbuf= test(!disable_jbuf && !((s->table->map & - join->outer_join))); + best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & + join->outer_join))); } } @@ -6092,6 +6410,7 @@ static void choose_initial_table_order(JOIN *join) TABLE_LIST *emb_subq; JOIN_TAB **tab= join->best_ref + join->const_tables; JOIN_TAB **tabs_end= tab + join->table_count - join->const_tables; + DBUG_ENTER("choose_initial_table_order"); /* Find where the top-level JOIN_TABs end and subquery JOIN_TABs start */ for (; tab != tabs_end; tab++) { @@ -6101,7 +6420,7 @@ static void choose_initial_table_order(JOIN *join) uint n_subquery_tabs= tabs_end - tab; if (!n_subquery_tabs) - return; + DBUG_VOID_RETURN; /* Copy the subquery JOIN_TABs to a separate array */ JOIN_TAB *subquery_tabs[MAX_TABLES]; @@ -6156,6 +6475,7 @@ static void choose_initial_table_order(JOIN *join) subq_tab += n_subquery_tables - 1; } } + DBUG_VOID_RETURN; } @@ -6187,7 +6507,9 @@ choose_plan(JOIN *join, table_map join_tables) { uint search_depth= join->thd->variables.optimizer_search_depth; uint prune_level= join->thd->variables.optimizer_prune_level; - bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN); + uint use_cond_selectivity= + join->thd->variables.optimizer_use_condition_selectivity; + bool straight_join= MY_TEST(join->select_options & SELECT_STRAIGHT_JOIN); DBUG_ENTER("choose_plan"); join->cur_embedding_map= 0; @@ -6242,15 +6564,19 @@ choose_plan(JOIN *join, table_map join_tables) the greedy version. Will be removed when greedy_search is approved. */ join->best_read= DBL_MAX; - if (find_best(join, join_tables, join->const_tables, 1.0, 0.0)) + if (find_best(join, join_tables, join->const_tables, 1.0, 0.0, + use_cond_selectivity)) + { DBUG_RETURN(TRUE); + } } else { if (search_depth == 0) /* Automatically determine a reasonable value for 'search_depth' */ search_depth= determine_search_depth(join); - if (greedy_search(join, join_tables, search_depth, prune_level)) + if (greedy_search(join, join_tables, search_depth, prune_level, + use_cond_selectivity)) DBUG_RETURN(TRUE); } } @@ -6524,6 +6850,8 @@ optimize_straight_join(JOIN *join, table_map join_tables) bool disable_jbuf= join->thd->variables.join_cache_level == 0; double record_count= 1.0; double read_time= 0.0; + uint use_cond_selectivity= + join->thd->variables.optimizer_use_condition_selectivity; POSITION loose_scan_pos; for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) @@ -6541,6 +6869,11 @@ optimize_straight_join(JOIN *join, table_map join_tables) &loose_scan_pos); join_tables&= ~(s->table->map); + double pushdown_cond_selectivity= 1.0; + if (use_cond_selectivity > 1) + pushdown_cond_selectivity= table_cond_selectivity(join, idx, s, + join_tables); + join->positions[idx].cond_selectivity= pushdown_cond_selectivity; ++idx; } @@ -6628,6 +6961,8 @@ optimize_straight_join(JOIN *join, table_map join_tables) @param search_depth controlls the exhaustiveness of the search @param prune_level the pruning heuristics that should be applied during search + @param use_cond_selectivity specifies how the selectivity of the conditions + pushed to a table should be taken into account @retval FALSE ok @@ -6639,7 +6974,8 @@ static bool greedy_search(JOIN *join, table_map remaining_tables, uint search_depth, - uint prune_level) + uint prune_level, + uint use_cond_selectivity) { double record_count= 1.0; double read_time= 0.0; @@ -6650,7 +6986,6 @@ greedy_search(JOIN *join, JOIN_TAB *best_table; // the next plan node to be added to the curr QEP // ==join->tables or # tables in the sj-mat nest we're optimizing uint n_tables __attribute__((unused)); - DBUG_ENTER("greedy_search"); /* number of tables that remain to be optimized */ @@ -6665,7 +7000,8 @@ greedy_search(JOIN *join, /* Find the extension of the current QEP with the lowest cost */ join->best_read= DBL_MAX; if (best_extension_by_limited_search(join, remaining_tables, idx, record_count, - read_time, search_depth, prune_level)) + read_time, search_depth, prune_level, + use_cond_selectivity)) DBUG_RETURN(TRUE); /* 'best_read < DBL_MAX' means that optimizer managed to find @@ -6890,7 +7226,7 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, double JOIN::get_examined_rows() { - ha_rows examined_rows; + double examined_rows; double prev_fanout= 1; double records; JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); @@ -6913,6 +7249,351 @@ double JOIN::get_examined_rows() /** + @brief + Get the selectivity of equalities between columns when joining a table + + @param join The optimized join + @param idx The number of tables in the evaluated partual join + @param s The table to be joined for evaluation + @param rem_tables The bitmap of tables to be joined later + @param keyparts The number of key parts to used when joining s + @param ref_keyuse_steps Array of references to keyuses employed to join s +*/ + +static +double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, + table_map rem_tables, uint keyparts, + uint16 *ref_keyuse_steps) +{ + double sel= 1.0; + COND_EQUAL *cond_equal= join->cond_equal; + + if (!cond_equal || !cond_equal->current_level.elements) + return sel; + + if (!s->keyuse) + return sel; + + Item_equal *item_equal; + List_iterator_fast<Item_equal> it(cond_equal->current_level); + TABLE *table= s->table; + table_map table_bit= table->map; + POSITION *pos= &join->positions[idx]; + + while ((item_equal= it++)) + { + /* + Check whether we need to take into account the selectivity of + multiple equality item_equal. If this is the case multiply + the current value of sel by this selectivity + */ + table_map used_tables= item_equal->used_tables(); + if (!(used_tables & table_bit)) + continue; + if (item_equal->get_const()) + continue; + + Field *fld; + bool adjust_sel= FALSE; + Item_equal_fields_iterator fi(*item_equal); + while((fi++) && !adjust_sel) + { + Field *fld= fi.get_curr_field(); + if (fld->table->map != table_bit) + continue; + if (pos->key == 0) + adjust_sel= TRUE; + else + { + uint i; + KEYUSE *keyuse= pos->key; + uint key= keyuse->key; + for (i= 0; i < keyparts; i++) + { + if (i > 0) + keyuse+= ref_keyuse_steps[i-1]; + uint fldno; + if (is_hash_join_key_no(key)) + fldno= keyuse->keypart; + else + fldno= table->key_info[key].key_part[i].fieldnr - 1; + if (fld->field_index == fldno) + break; + } + keyuse= pos->key; + + if (i == keyparts) + { + /* + Field fld is included in multiple equality item_equal + and is not a part of the ref key. + The selectivity of the multiple equality must be taken + into account unless one of the ref arguments is + equal to fld. + */ + adjust_sel= TRUE; + for (uint j= 0; j < keyparts && adjust_sel; j++) + { + if (j > 0) + keyuse+= ref_keyuse_steps[j-1]; + Item *ref_item= keyuse->val; + if (ref_item->real_item()->type() == Item::FIELD_ITEM) + { + Item_field *field_item= (Item_field *) (ref_item->real_item()); + if (item_equal->contains(field_item->field)) + adjust_sel= FALSE; + } + } + } + } + } + if (adjust_sel) + { + /* + If ref == 0 and there are no fields in the multiple equality + item_equal that belong to the tables joined prior to s + then the selectivity of multiple equality will be set to 1.0. + */ + double eq_fld_sel= 1.0; + fi.rewind(); + while ((fi++)) + { + double curr_eq_fld_sel; + fld= fi.get_curr_field(); + if (!(fld->table->map & ~(table_bit | rem_tables))) + continue; + curr_eq_fld_sel= get_column_avg_frequency(fld) / + fld->table->stat_records(); + if (curr_eq_fld_sel < 1.0) + set_if_bigger(eq_fld_sel, curr_eq_fld_sel); + } + sel*= eq_fld_sel; + } + } + return sel; +} + + +/** + @brief + Get the selectivity of conditions when joining a table + + @param join The optimized join + @param s The table to be joined for evaluation + @param rem_tables The bitmap of tables to be joined later + + @detail + Get selectivity of conditions that can be applied when joining this table + with previous tables. + + For quick selects and full table scans, selectivity of COND(this_table) + is accounted for in matching_candidates_in_table(). Here, we only count + selectivity of COND(this_table, previous_tables). + + For other access methods, we need to calculate selectivity of the whole + condition, "COND(this_table) AND COND(this_table, previous_tables)". + + @retval + selectivity of the conditions imposed on the rows of s +*/ + +static +double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, + table_map rem_tables) +{ + uint16 ref_keyuse_steps[MAX_REF_PARTS - 1]; + Field *field; + TABLE *table= s->table; + MY_BITMAP *read_set= table->read_set; + double sel= s->table->cond_selectivity; + POSITION *pos= &join->positions[idx]; + uint keyparts= 0; + uint found_part_ref_or_null= 0; + + if (pos->key != 0) + { + /* + A ref access or hash join is used for this table. ref access is created + from + + tbl.keypart1=expr1 AND tbl.keypart2=expr2 AND ... + + and it will only return rows for which this condition is satisified. + Suppose, certain expr{i} is a constant. Since ref access only returns + rows that satisfy + + tbl.keypart{i}=const (*) + + then selectivity of this equality should not be counted in return value + of this function. This function uses the value of + + table->cond_selectivity=selectivity(COND(tbl)) (**) + + as a starting point. This value includes selectivity of equality (*). We + should somehow discount it. + + Looking at calculate_cond_selectivity_for_table(), one can see that that + the value is not necessarily a direct multiplicand in + table->cond_selectivity + + There are three possible ways to discount + 1. There is a potential range access on t.keypart{i}=const. + (an important special case: the used ref access has a const prefix for + which a range estimate is available) + + 2. The field has a histogram. field[x]->cond_selectivity has the data. + + 3. Use index stats on this index: + rec_per_key[key_part+1]/rec_per_key[key_part] + + (TODO: more details about the "t.key=othertable.col" case) + */ + KEYUSE *keyuse= pos->key; + KEYUSE *prev_ref_keyuse= keyuse; + uint key= keyuse->key; + + /* + Check if we have a prefix of key=const that matches a quick select. + */ + if (!is_hash_join_key_no(key)) + { + table_map quick_key_map= (table_map(1) << table->quick_key_parts[key]) - 1; + if (table->quick_rows[key] && + !(quick_key_map & ~table->const_key_parts[key])) + { + /* + Ok, there is an equality for each of the key parts used by the + quick select. This means, quick select's estimate can be reused to + discount the selectivity of a prefix of a ref access. + */ + for (; quick_key_map & 1 ; quick_key_map>>= 1) + { + while (keyuse->table == table && keyuse->key == key && + keyuse->keypart == keyparts) + { + keyuse++; + } + keyparts++; + } + sel /= (double)table->quick_rows[key] / (double) table->stat_records(); + } + } + + /* + Go through the "keypart{N}=..." equalities and find those that were + already taken into account in table->cond_selectivity. + */ + keyuse= pos->key; + keyparts=0; + while (keyuse->table == table && keyuse->key == key) + { + if (!(keyuse->used_tables & (rem_tables | table->map))) + { + if (are_tables_local(s, keyuse->val->used_tables())) + { + if (is_hash_join_key_no(key)) + { + if (keyparts == keyuse->keypart) + keyparts++; + } + else + { + if (keyparts == keyuse->keypart && + !((keyuse->val->used_tables()) & ~pos->ref_depend_map) && + !(found_part_ref_or_null & keyuse->optimize)) + { + /* Found a KEYUSE object that will be used by ref access */ + keyparts++; + found_part_ref_or_null|= keyuse->optimize & ~KEY_OPTIMIZE_EQ; + } + } + + if (keyparts > keyuse->keypart) + { + /* Ok this is the keyuse that will be used for ref access */ + uint fldno; + if (is_hash_join_key_no(key)) + fldno= keyuse->keypart; + else + fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1; + if (keyuse->val->const_item()) + { + if (table->field[fldno]->cond_selectivity > 0) + { + sel /= table->field[fldno]->cond_selectivity; + set_if_smaller(sel, 1.0); + } + /* + TODO: we could do better here: + 1. cond_selectivity might be =1 (the default) because quick + select on some index prevented us from analyzing + histogram for this column. + 2. we could get an estimate through this? + rec_per_key[key_part-1] / rec_per_key[key_part] + */ + } + if (keyparts > 1) + { + ref_keyuse_steps[keyparts-2]= keyuse - prev_ref_keyuse; + prev_ref_keyuse= keyuse; + } + } + } + } + keyuse++; + } + } + else + { + /* + The table is accessed with full table scan, or quick select. + Selectivity of COND(table) is already accounted for in + matching_candidates_in_table(). + */ + sel= 1; + } + + /* + If the field f from the table is equal to a field from one the + earlier joined tables then the selectivity of the range conditions + over the field f must be discounted. + + We need to discount selectivity only if we're using ref-based + access method (and have sel!=1). + If we use ALL/range/index_merge, then sel==1, and no need to discount. + */ + if (pos->key != NULL) + { + for (Field **f_ptr=table->field ; (field= *f_ptr) ; f_ptr++) + { + if (!bitmap_is_set(read_set, field->field_index) || + !field->next_equal_field) + continue; + for (Field *next_field= field->next_equal_field; + next_field != field; + next_field= next_field->next_equal_field) + { + if (!(next_field->table->map & rem_tables) && next_field->table != table) + { + if (field->cond_selectivity > 0) + { + sel/= field->cond_selectivity; + set_if_smaller(sel, 1.0); + } + break; + } + } + } + } + + sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables, + keyparts, ref_keyuse_steps); + + return sel; +} + + +/** Find a good, possibly optimal, query execution plan (QEP) by a possibly exhaustive search. @@ -7022,6 +7703,8 @@ double JOIN::get_examined_rows() @param prune_level pruning heuristics that should be applied during optimization (values: 0 = EXHAUSTIVE, 1 = PRUNE_BY_TIME_OR_ROWS) + @param use_cond_selectivity specifies how the selectivity of the conditions + pushed to a table should be taken into account @retval FALSE ok @@ -7036,12 +7719,21 @@ best_extension_by_limited_search(JOIN *join, double record_count, double read_time, uint search_depth, - uint prune_level) + uint prune_level, + uint use_cond_selectivity) { DBUG_ENTER("best_extension_by_limited_search"); THD *thd= join->thd; - if (thd->killed) // Abort + + DBUG_EXECUTE_IF("show_explain_probe_best_ext_lim_search", + if (dbug_user_var_equals_int(thd, + "show_explain_probe_select_id", + join->select_lex->select_number)) + dbug_serve_apcs(thd, 1); + ); + + if (thd->check_killed()) // Abort DBUG_RETURN(TRUE); DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx, @@ -7140,16 +7832,25 @@ best_extension_by_limited_search(JOIN *join, } } + double pushdown_cond_selectivity= 1.0; + if (use_cond_selectivity > 1) + pushdown_cond_selectivity= table_cond_selectivity(join, idx, s, + remaining_tables & + ~real_table_bit); + join->positions[idx].cond_selectivity= pushdown_cond_selectivity; + double partial_join_cardinality= current_record_count * + pushdown_cond_selectivity; if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables ) { /* Recursively expand the current partial plan */ swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); if (best_extension_by_limited_search(join, remaining_tables & ~real_table_bit, idx + 1, - current_record_count, + partial_join_cardinality, current_read_time, search_depth - 1, - prune_level)) + prune_level, + use_cond_selectivity)) DBUG_RETURN(TRUE); swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); } @@ -7161,13 +7862,17 @@ best_extension_by_limited_search(JOIN *join, if (join->sort_by_table && join->sort_by_table != join->positions[join->const_tables].table->table) - /* We have to make a temp table */ + /* + We may have to make a temp table, note that this is only a + heuristic since we cannot know for sure at this point. + Hence it may be wrong. + */ current_read_time= COST_ADD(current_read_time, current_record_count); if (current_read_time < join->best_read) { memcpy((uchar*) join->best_positions, (uchar*) join->positions, sizeof(POSITION) * (idx + 1)); - join->record_count= current_record_count; + join->record_count= partial_join_cardinality; join->best_read= current_read_time - 0.001; } DBUG_EXECUTE("opt", print_plan(join, idx+1, @@ -7195,11 +7900,11 @@ best_extension_by_limited_search(JOIN *join, */ static bool find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, - double read_time) + double read_time, uint use_cond_selectivity) { DBUG_ENTER("find_best"); THD *thd= join->thd; - if (thd->killed) + if (thd->check_killed()) DBUG_RETURN(TRUE); if (!rest_tables) { @@ -7247,20 +7952,30 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, advance_sj_state(join, rest_tables, idx, ¤t_record_count, ¤t_read_time, &loose_scan_pos); - if (best_record_count > current_record_count || + double pushdown_cond_selectivity= 1.0; + if (use_cond_selectivity > 1) + pushdown_cond_selectivity= table_cond_selectivity(join, idx, s, + rest_tables & + ~real_table_bit); + join->positions[idx].cond_selectivity= pushdown_cond_selectivity; + double partial_join_cardinality= current_record_count * + pushdown_cond_selectivity; + + if (best_record_count > partial_join_cardinality || best_read_time > current_read_time || (idx == join->const_tables && s->table == join->sort_by_table)) { - if (best_record_count >= current_record_count && + if (best_record_count >= partial_join_cardinality && best_read_time >= current_read_time && (!(s->key_dependent & rest_tables) || records < 2.0)) { - best_record_count=current_record_count; + best_record_count= partial_join_cardinality; best_read_time=current_read_time; } swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); if (find_best(join,rest_tables & ~real_table_bit,idx+1, - current_record_count,current_read_time)) + partial_join_cardinality,current_read_time, + use_cond_selectivity)) DBUG_RETURN(TRUE); swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); } @@ -7882,7 +8597,9 @@ get_best_combination(JOIN *join) sub-order */ SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info; - j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1); + j->records_read= (sjm->is_sj_scan? sjm->rows : 1); + j->records= (ha_rows) j->records_read; + j->cond_selectivity= 1.0; JOIN_TAB *jt; JOIN_TAB_RANGE *jt_range; if (!(jt= (JOIN_TAB*)join->thd->alloc(sizeof(JOIN_TAB)*sjm->tables)) || @@ -7939,7 +8656,8 @@ get_best_combination(JOIN *join) Save records_read in JOIN_TAB so that select_describe()/etc don't have to access join->best_positions[]. */ - j->records_read= (ha_rows)join->best_positions[tablenr].records_read; + j->records_read= join->best_positions[tablenr].records_read; + j->cond_selectivity= join->best_positions[tablenr].cond_selectivity; join->map2table[j->table->tablenr]= j; /* If we've reached the end of sjm nest, switch back to main sequence */ @@ -7958,7 +8676,7 @@ get_best_combination(JOIN *join) { if (j->bush_children) j= j->bush_children->start; - + used_tables|= j->table->map; if (j->type != JT_CONST && j->type != JT_SYSTEM) { @@ -7978,6 +8696,7 @@ get_best_combination(JOIN *join) join->table_access_tabs= join->join_tab; join->top_table_access_tabs_count= join->top_join_tab_count; + update_depend_map(join); DBUG_RETURN(0); } @@ -8051,12 +8770,13 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, !(key_part_info = (KEY_PART_INFO *) thd->alloc(sizeof(KEY_PART_INFO)* key_parts))) DBUG_RETURN(TRUE); - keyinfo->usable_key_parts= keyinfo->key_parts = key_parts; - keyinfo->ext_key_parts= keyinfo->key_parts; + keyinfo->usable_key_parts= keyinfo->user_defined_key_parts = key_parts; + keyinfo->ext_key_parts= keyinfo->user_defined_key_parts; keyinfo->key_part= key_part_info; keyinfo->key_length=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; keyinfo->flags= HA_GENERATED_KEY; + keyinfo->is_statistics_from_stat_tables= FALSE; keyinfo->name= (char *) "$hj"; keyinfo->rec_per_key= (ulong*) thd->calloc(sizeof(ulong)*key_parts); if (!keyinfo->rec_per_key) @@ -8101,7 +8821,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, keyuse++; } while (keyuse->table == table && keyuse->is_for_hash_join()); - keyinfo->ext_key_parts= keyinfo->key_parts; + keyinfo->ext_key_parts= keyinfo->user_defined_key_parts; keyinfo->ext_key_flags= keyinfo->flags; keyinfo->ext_key_part_map= 0; @@ -8125,7 +8845,7 @@ static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables) table_map local_tables= jtab->emb_sj_nest->nested_join->used_tables | jtab->join->const_table_map | OUTER_REF_TABLE_BIT; - return !test(used_tables & ~local_tables); + return !MY_TEST(used_tables & ~local_tables); } /* @@ -8229,6 +8949,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.null_rejecting= 0; j->ref.disable_cache= FALSE; j->ref.null_ref_part= NO_REF_PART; + j->ref.const_ref_part_map= 0; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; @@ -8259,12 +8980,19 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, !are_tables_local(j, keyuse->val->used_tables())) keyuse++; /* Skip other parts */ - uint maybe_null= test(keyinfo->key_part[i].null_bit); + uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal j->ref.cond_guards[i]= keyuse->cond_guard; if (keyuse->null_rejecting) j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; + /* + Todo: we should remove this check for thd->lex->describe on the next + line. With SHOW EXPLAIN code, EXPLAIN printout code no longer depends + on it. However, removing the check caused change in lots of query + plans! Does the optimizer depend on the contents of + table_ref->key_copy ? If yes, do we produce incorrect EXPLAINs? + */ if (!keyuse->val->used_tables() && !thd->lex->describe) { // Compare against constant store_key_item tmp(thd, @@ -8277,6 +9005,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, if (thd->is_fatal_error) DBUG_RETURN(TRUE); tmp.copy(); + j->ref.const_ref_part_map |= key_part_map(1) << i ; } else *ref_key++= get_store_key(thd, @@ -8302,10 +9031,10 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, ulong key_flags= j->table->actual_key_flags(keyinfo); if (j->type == JT_CONST) j->table->const_table= 1; - else if (!((keyparts == keyinfo->key_parts && + else if (!((keyparts == keyinfo->user_defined_key_parts && ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)) || - (keyparts > keyinfo->key_parts && // true only for extended keys - test(key_flags & HA_EXT_NOSAME) && + (keyparts > keyinfo->user_defined_key_parts && // true only for extended keys + MY_TEST(key_flags & HA_EXT_NOSAME) && keyparts == keyinfo->ext_key_parts)) || null_ref_key) { @@ -8394,6 +9123,7 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) !(parent->join_tab_reexec= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB)))) DBUG_RETURN(TRUE); /* purecov: inspected */ + // psergey-todo: here, save the pointer for original join_tabs. join_tab= parent->join_tab_reexec; table= &parent->table_reexec[0]; parent->table_reexec[0]= temp_table; table_count= top_join_tab_count= 1; @@ -8447,7 +9177,7 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) functions are handled. */ // the temporary table was explicitly requested - DBUG_ASSERT(test(select_options & OPTION_BUFFER_RESULT)); + DBUG_ASSERT(MY_TEST(select_options & OPTION_BUFFER_RESULT)); // the temporary table does not have a grouping expression DBUG_ASSERT(!temp_table->group); } @@ -8813,10 +9543,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* there may be a select without a cond. */ if (join->table_count > 1) cond->update_used_tables(); // Tablenr may have changed - if (join->const_tables == join->table_count && - thd->lex->current_select->master_unit() == - &thd->lex->unit) // not upper level SELECT - join->const_table_map|=RAND_TABLE_BIT; /* Extract expressions that depend on constant tables @@ -8894,7 +9620,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) table_map current_map; i= join->const_tables; for (tab= first_depth_first_tab(join); tab; - tab= next_depth_first_tab(join, tab), i++) + tab= next_depth_first_tab(join, tab)) { bool is_hj; /* @@ -9052,11 +9778,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (tab->table) { tab->table->file->pushed_cond= NULL; - if (((thd->variables.optimizer_switch & - OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) || - (tab->table->file->ha_table_flags() & - HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) && - !first_inner_tab) + if (thd->use_cond_push(tab->table->file) && !first_inner_tab) { COND *push_cond= make_cond_for_table(thd, tmp, current_map, current_map, @@ -9119,9 +9841,14 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) Check again if we should use an index. We could have used an column from a previous table in the index if we are using limit and this is the first table + + (1) - Don't switch the used index if we are using semi-join + LooseScan on this table. Using different index will not + produce the desired ordering and de-duplication. */ if (!tab->table->is_filled_at_execution() && + !tab->loosescan_match_tab && // (1) ((cond && (!tab->keys.is_subset(tab->const_keys) && i > 0)) || (!tab->const_keys.is_clear_all() && i == join->const_tables && join->unit->select_limit_cnt < @@ -9388,6 +10115,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } first_inner_tab= first_inner_tab->first_upper; } + if (!tab->bush_children) + i++; } } DBUG_RETURN(0); @@ -9863,7 +10592,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) if (item->is_null()) DBUG_RETURN(NESTED_LOOP_OK); } - fill_record(thd, table->field, sjm->sjm_table_cols, TRUE, FALSE); + fill_record(thd, table, table->field, sjm->sjm_table_cols, TRUE, FALSE); if (thd->is_error()) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if ((error= table->file->ha_write_tmp_row(table->record[0]))) @@ -10023,7 +10752,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, uint table_index, JOIN_TAB *prev_tab) { - COST_VECT cost; + Cost_estimate cost; uint flags= 0; ha_rows rows= 0; uint bufsz= 4096; @@ -10157,10 +10886,10 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (cache_level == 1) prev_cache= 0; if ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache)) && - ((options & SELECT_DESCRIBE) || !tab->cache->init())) + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; - return (2-test(!prev_cache)); + return (2 - MY_TEST(!prev_cache)); } goto no_join_cache; case JT_SYSTEM: @@ -10171,7 +10900,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, goto no_join_cache; if (tab->ref.is_access_triggered()) goto no_join_cache; - + if (!tab->is_ref_for_hash_join() && !no_bka_cache) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; @@ -10192,10 +10921,10 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (cache_level == 3) prev_cache= 0; if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) && - ((options & SELECT_DESCRIBE) || !tab->cache->init())) + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; - return (4-test(!prev_cache)); + return (4 - MY_TEST(!prev_cache)); } goto no_join_cache; } @@ -10213,8 +10942,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (cache_level == 5) prev_cache= 0; if ((tab->cache= new JOIN_CACHE_BKA(join, tab, flags, prev_cache)) && - ((options & SELECT_DESCRIBE) || !tab->cache->init())) - return (6-test(!prev_cache)); + !tab->cache->init(options & SELECT_DESCRIBE)) + return (6 - MY_TEST(!prev_cache)); goto no_join_cache; } else @@ -10222,10 +10951,10 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (cache_level == 7) prev_cache= 0; if ((tab->cache= new JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) && - ((options & SELECT_DESCRIBE) || !tab->cache->init())) + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->idx_cond_fact_out= FALSE; - return (8-test(!prev_cache)); + return (8 - MY_TEST(!prev_cache)); } goto no_join_cache; } @@ -10320,7 +11049,7 @@ restart: no_jbuf_after, idx, prev_tab); - tab->use_join_cache= test(tab->used_join_cache_level); + tab->use_join_cache= MY_TEST(tab->used_join_cache_level); /* psergey-merge: todo: raise the question that this is really stupid that we can first allocate a join buffer, then decide not to use it and free @@ -10340,6 +11069,86 @@ restart: } } +/** + Remove pushdown conditions that are already checked by the scan phase + of BNL/BNLH joins. + + @note + If the single-table condition for this table will be used by a + blocked join to pre-filter this table's rows, there is no need + to re-check the same single-table condition for each joined record. + + This method removes from JOIN_TAB::select_cond and JOIN_TAB::select::cond + all top-level conjuncts that also appear in in JOIN_TAB::cache_select::cond. +*/ + +void JOIN_TAB::remove_redundant_bnl_scan_conds() +{ + if (!(select_cond && cache_select && cache && + (cache->get_join_alg() == JOIN_CACHE::BNL_JOIN_ALG || + cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG))) + return; + + /* + select->cond is not processed separately. This method assumes it is always + the same as select_cond. + */ + DBUG_ASSERT(!select || !select->cond || + (select->cond == select_cond)); + + if (is_cond_and(select_cond)) + { + List_iterator<Item> pushed_cond_li(*((Item_cond*) select_cond)->argument_list()); + Item *pushed_item; + Item_cond_and *reduced_select_cond= new Item_cond_and; + + if (is_cond_and(cache_select->cond)) + { + List_iterator<Item> scan_cond_li(*((Item_cond*) cache_select->cond)->argument_list()); + Item *scan_item; + while ((pushed_item= pushed_cond_li++)) + { + bool found= false; + scan_cond_li.rewind(); + while ((scan_item= scan_cond_li++)) + { + if (pushed_item->eq(scan_item, 0)) + { + found= true; + break; + } + } + if (!found) + reduced_select_cond->add(pushed_item); + } + } + else + { + while ((pushed_item= pushed_cond_li++)) + { + if (!pushed_item->eq(cache_select->cond, 0)) + reduced_select_cond->add(pushed_item); + } + } + + /* + JOIN_CACHE::check_match uses JOIN_TAB::select->cond instead of + JOIN_TAB::select_cond. set_cond() sets both pointers. + */ + if (reduced_select_cond->argument_list()->is_empty()) + set_cond(NULL); + else if (reduced_select_cond->argument_list()->elements == 1) + set_cond(reduced_select_cond->argument_list()->head()); + else + { + reduced_select_cond->quick_fix_field(); + set_cond(reduced_select_cond); + } + } + else if (select_cond->eq(cache_select->cond, 0)) + set_cond(NULL); +} + /* Plan refinement stage: do various setup things for the executor @@ -10370,7 +11179,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) uint i; DBUG_ENTER("make_join_readinfo"); - bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); + bool statistics= MY_TEST(!(join->select_options & SELECT_DESCRIBE)); bool sorted= 1; join->complex_firstmatch_tables= table_map(0); @@ -10503,10 +11312,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* These init changes read_record */ if (tab->use_quick == 2) { - join->thd->server_status|=SERVER_QUERY_NO_GOOD_INDEX_USED; + join->thd->set_status_no_good_index_used(); tab->read_first_record= join_init_quick_read_record; if (statistics) - status_var_increment(join->thd->status_var.select_range_check_count); + join->thd->inc_status_select_range_check(); } else { @@ -10517,14 +11326,14 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (tab->select && tab->select->quick) { if (statistics) - status_var_increment(join->thd->status_var.select_range_count); + join->thd->inc_status_select_range(); } else { - join->thd->server_status|=SERVER_QUERY_NO_INDEX_USED; + join->thd->set_status_no_index_used(); if (statistics) { - status_var_increment(join->thd->status_var.select_scan_count); + join->thd->inc_status_select_scan(); join->thd->query_plan_flags|= QPLAN_FULL_SCAN; } } @@ -10534,16 +11343,14 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (tab->select && tab->select->quick) { if (statistics) - status_var_increment(join->thd->status_var. - select_full_range_join_count); + join->thd->inc_status_select_full_range_join(); } else { - join->thd->server_status|=SERVER_QUERY_NO_INDEX_USED; + join->thd->set_status_no_index_used(); if (statistics) { - status_var_increment(join->thd->status_var. - select_full_join_count); + join->thd->inc_status_select_full_join(); join->thd->query_plan_flags|= QPLAN_FULL_JOIN; } } @@ -10597,6 +11404,15 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) abort(); /* purecov: end */ } + + tab->remove_redundant_bnl_scan_conds(); + DBUG_EXECUTE("where", + char buff[256]; + String str(buff,sizeof(buff),system_charset_info); + str.length(0); + str.append(tab->table? tab->table->alias.c_ptr() :"<no_table_name>"); + str.append(" final_pushdown_cond"); + print_where(tab->select_cond, str.c_ptr_safe(), QT_ORDINARY);); } uint n_top_tables= join->join_tab_ranges.head()->end - join->join_tab_ranges.head()->start; @@ -10730,13 +11546,15 @@ void JOIN_TAB::cleanup() } else { + TABLE_LIST *tmp= table->pos_in_table_list; end_read_record(&read_record); - table->pos_in_table_list->jtbm_subselect->cleanup(); + tmp->jtbm_subselect->cleanup(); /* The above call freed the materializedd temptable. Set it to NULL so that we don't attempt to touch it if JOIN_TAB::cleanup() is invoked multiple times (it may be) */ + tmp->table= NULL; table=NULL; } DBUG_VOID_RETURN; @@ -10770,7 +11588,7 @@ double JOIN_TAB::scan_time() } else { - found_records= records= table->file->stats.records; + found_records= records= table->stat_records(); read_time= table->file->scan_time(); /* table->quick_condition_rows has already been set to @@ -10781,7 +11599,7 @@ double JOIN_TAB::scan_time() } else { - found_records= records=table->file->stats.records; + found_records= records=table->stat_records(); read_time= found_records ? (double)found_records: 10.0;// TODO:fix this stub res= read_time; } @@ -10797,12 +11615,11 @@ double JOIN_TAB::scan_time() ha_rows JOIN_TAB::get_examined_rows() { - ha_rows examined_rows; + if (select && select->quick && use_quick != 2) + return select->quick->records; - if (select && select->quick) - examined_rows= select->quick->records; - else if (type == JT_NEXT || type == JT_ALL || - type == JT_HASH || type ==JT_HASH_NEXT) + if (type == JT_NEXT || type == JT_ALL || + type == JT_HASH || type ==JT_HASH_NEXT) { if (limit) { @@ -10810,26 +11627,20 @@ ha_rows JOIN_TAB::get_examined_rows() @todo This estimate is wrong, a LIMIT query may examine much more rows than the LIMIT itself. */ - examined_rows= limit; - } - else - { - if (table->is_filled_at_execution()) - examined_rows= records; - else - { - /* - handler->info(HA_STATUS_VARIABLE) has been called in - make_join_statistics() - */ - examined_rows= table->file->stats.records; - } + return limit; } + + if (table->is_filled_at_execution()) + return records; + + /* + handler->info(HA_STATUS_VARIABLE) has been called in + make_join_statistics() + */ + return table->stat_records(); } - else - examined_rows= records_read; - return examined_rows; + return (ha_rows) records_read; } @@ -10853,13 +11664,21 @@ bool JOIN_TAB::preread_init() mysql_handle_single_derived(join->thd->lex, derived, DT_CREATE | DT_FILL)) return TRUE; + preread_init_done= TRUE; if (select && select->quick) select->quick->replace_handler(table->file); + DBUG_EXECUTE_IF("show_explain_probe_join_tab_preread", + if (dbug_user_var_equals_int(join->thd, + "show_explain_probe_select_id", + join->select_lex->select_number)) + dbug_serve_apcs(join->thd, 1); + ); + /* init ftfuns for just initialized derived table */ if (table->fulltext_searched) - init_ftfuncs(join->thd, join->select_lex, test(join->order)); + init_ftfuncs(join->thd, join->select_lex, MY_TEST(join->order)); return FALSE; } @@ -10887,7 +11706,7 @@ bool TABLE_REF::tmp_table_index_lookup_init(THD *thd, bool value, uint skip) { - uint tmp_key_parts= tmp_key->key_parts; + uint tmp_key_parts= tmp_key->user_defined_key_parts; uint i; DBUG_ENTER("TABLE_REF::tmp_table_index_lookup_init"); @@ -10915,12 +11734,12 @@ bool TABLE_REF::tmp_table_index_lookup_init(THD *thd, Item *item= it.next(); DBUG_ASSERT(item); items[i]= item; - 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, /* TIMOUR: 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, @@ -10984,7 +11803,7 @@ bool TABLE_REF::is_access_triggered() a correlated subquery itself, but has subqueries, we can free it fully and also free JOINs of all its subqueries. The exception is a subquery in SELECT list, e.g: @n - SELECT a, (select max(b) from t1) group by c @n + SELECT a, (select MY_MAX(b) from t1) group by c @n This subquery will not be evaluated at first sweep and its value will not be inserted into the temporary table. Instead, it's evaluated when selecting from the temporary table. Therefore, it can't be freed @@ -11067,6 +11886,9 @@ void JOIN::cleanup(bool full) { DBUG_ENTER("JOIN::cleanup"); DBUG_PRINT("enter", ("full %u", (uint) full)); + + if (full) + have_query_plan= QEP_DELETED; if (table) { @@ -11575,7 +12397,8 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, result->send_eof(); // Should be safe } /* Update results for FOUND_ROWS */ - join->thd->limit_found_rows= join->thd->examined_row_count= 0; + join->thd->limit_found_rows= 0; + join->thd->set_examined_row_count(0); DBUG_RETURN(0); } @@ -11615,18 +12438,10 @@ public: { TRASH_FREE(ptr, size); } Item *and_level; - Item_func *cmp_func; - COND_CMP(Item *a,Item_func *b) :and_level(a),cmp_func(b) {} + Item_bool_func2 *cmp_func; + COND_CMP(Item *a,Item_bool_func2 *b) :and_level(a),cmp_func(b) {} }; -#ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION -template class I_List<COND_CMP>; -template class I_List_iterator<COND_CMP>; -template class List<Item_func_match>; -template class List_iterator<Item_func_match>; -#endif - - /** Find the multiple equality predicate containing a field. @@ -11891,7 +12706,7 @@ static bool check_simple_equality(Item *left_item, Item *right_item, if (field_item->cmp_type() == STRING_RESULT) { - CHARSET_INFO *cs= ((Field_str*) field_item->field)->charset(); + CHARSET_INFO *cs= field_item->field->charset(); if (!item) { Item_func_eq *eq_item; @@ -12079,7 +12894,7 @@ static bool check_equality(THD *thd, Item *item, COND_EQUAL *cond_equal, equality predicates that is equivalent to the conjunction. Thus, =(a1,a2,a3) can substitute for ((a1=a3) AND (a2=a3) AND (a2=a1)) as it is equivalent to ((a1=a2) AND (a2=a3)). - The function always makes a substitution of all equality predicates occured + The function always makes a substitution of all equality predicates occurred in a conjuction for a minimal set of multiple equality predicates. This set can be considered as a canonical representation of the sub-conjunction of the equality predicates. @@ -12117,7 +12932,8 @@ static bool check_equality(THD *thd, Item *item, COND_EQUAL *cond_equal, */ static COND *build_equal_items_for_cond(THD *thd, COND *cond, - COND_EQUAL *inherited) + COND_EQUAL *inherited, + bool link_item_fields) { Item_equal *item_equal; COND_EQUAL cond_equal; @@ -12167,6 +12983,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, List_iterator_fast<Item_equal> it(cond_equal.current_level); while ((item_equal= it++)) { + item_equal->set_link_equal_fields(link_item_fields); item_equal->fix_fields(thd, NULL); item_equal->update_used_tables(); set_if_bigger(thd->lex->current_select->max_equal_elems, @@ -12186,7 +13003,8 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, while ((item= li++)) { Item *new_item; - if ((new_item= build_equal_items_for_cond(thd, item, inherited)) != item) + if ((new_item= build_equal_items_for_cond(thd, item, inherited, FALSE)) + != item) { /* This replacement happens only for standalone equalities */ /* @@ -12329,9 +13147,9 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, @endcode Thus, applying equalities from the where condition we basically can get more freedom in performing join operations. - Althogh we don't use this property now, it probably makes sense to use + Although we don't use this property now, it probably makes sense to use it in the future. - @param thd Thread handler + @param thd Thread handler @param cond condition to build the multiple equalities for @param inherited path to all inherited multiple equality items @param join_list list of join tables to which the condition @@ -12340,6 +13158,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, for on expressions @param[out] cond_equal_ref pointer to the structure to place built equalities in + @param link_equal_items equal fields are to be linked @return pointer to the transformed condition containing multiple equalities @@ -12349,14 +13168,15 @@ static COND *build_equal_items(JOIN *join, COND *cond, COND_EQUAL *inherited, List<TABLE_LIST> *join_list, bool ignore_on_conds, - COND_EQUAL **cond_equal_ref) + COND_EQUAL **cond_equal_ref, + bool link_equal_fields) { THD *thd= join->thd; COND_EQUAL *cond_equal= 0; if (cond) { - cond= build_equal_items_for_cond(thd, cond, inherited); + cond= build_equal_items_for_cond(thd, cond, inherited, link_equal_fields); cond->update_used_tables(); if (cond->type() == Item::COND_ITEM && ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) @@ -12487,7 +13307,7 @@ static int compare_fields_by_table_order(Item *field1, if (!cmp) { KEY *key_info= tab->table->key_info + keyno; - for (uint i= 0; i < key_info->key_parts; i++) + for (uint i= 0; i < key_info->user_defined_key_parts; i++) { Field *fld= key_info->key_part[i].field; if (fld->eq(f2->field)) @@ -12670,7 +13490,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, item= NULL; /* Don't produce equality */ } - bool produce_equality= test(item == field_item); + bool produce_equality= MY_TEST(item == field_item); if (!item_const && field_sjm && field_sjm != current_sjm) { /* Entering an SJM nest */ @@ -13017,6 +13837,75 @@ static void update_const_equal_items(COND *cond, JOIN_TAB *tab, bool const_key) } +/** + Check if + WHERE expr=value AND expr=const + can be rewritten as: + WHERE const=value AND expr=const + + @param target - the target operator whose "expr" argument will be + replaced to "const". + @param target_expr - the target's "expr" which will be replaced to "const". + @param target_value - the target's second argument, it will remain unchanged. + @param source - the equality expression ("=" or "<=>") that + can be used to rewrite the "target" part + (under certain conditions, see the code). + @param source_expr - the source's "expr". It should be exactly equal to + the target's "expr" to make condition rewrite possible. + @param source_const - the source's "const" argument, it will be inserted + into "target" instead of "expr". +*/ +static bool +can_change_cond_ref_to_const(Item_bool_func2 *target, + Item *target_expr, Item *target_value, + Item_bool_func2 *source, + Item *source_expr, Item *source_const) +{ + if (!target_expr->eq(source_expr,0) || + target_value == source_const || + target_expr->cmp_context != source_expr->cmp_context) + return false; + if (target_expr->cmp_context == STRING_RESULT) + { + /* + In this example: + SET NAMES utf8 COLLATE utf8_german2_ci; + DROP TABLE IF EXISTS t1; + CREATE TABLE t1 (a CHAR(10) CHARACTER SET utf8); + INSERT INTO t1 VALUES ('o-umlaut'),('oe'); + SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe'; + + the query should return only the row with 'oe'. + It should not return 'o-umlaut', because 'o-umlaut' does not match + the right part of the condition: a='oe' + ('o-umlaut' is not equal to 'oe' in utf8_general_ci, + which is the collation of the field "a"). + + If we change the right part from: + ... AND a='oe' + to + ... AND 'oe' COLLATE utf8_german2_ci='oe' + it will be evalulated to TRUE and removed from the condition, + so the overall query will be simplified to: + + SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci; + + which will erroneously start to return both 'oe' and 'o-umlaut'. + So changing "expr" to "const" is not possible if the effective + collations of "target" and "source" are not exactly the same. + + Note, the code before the fix for MDEV-7152 only checked that + collations of "source_const" and "target_value" are the same. + This was not enough, as the bug report demonstrated. + */ + return + target->compare_collation() == source->compare_collation() && + target_value->collation.collation == source_const->collation.collation; + } + return true; // Non-string comparison +} + + /* change field = field to field = const for each found field = const in the and_level @@ -13025,6 +13914,7 @@ static void update_const_equal_items(COND *cond, JOIN_TAB *tab, bool const_key) static void change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, Item *and_father, Item *cond, + Item_bool_func2 *field_value_owner, Item *field, Item *value) { if (cond->type() == Item::COND_ITEM) @@ -13035,7 +13925,7 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, Item *item; while ((item=li++)) change_cond_ref_to_const(thd, save_list,and_level ? cond : item, item, - field, value); + field_value_owner, field, value); return; } if (cond->eq_cmp_result() == Item::COND_OK) @@ -13047,11 +13937,8 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, Item *right_item= args[1]; Item_func::Functype functype= func->functype(); - if (right_item->eq(field,0) && left_item != value && - right_item->cmp_context == field->cmp_context && - (left_item->result_type() != STRING_RESULT || - value->result_type() != STRING_RESULT || - left_item->collation.collation == value->collation.collation)) + if (can_change_cond_ref_to_const(func, right_item, left_item, + field_value_owner, field, value)) { Item *tmp=value->clone_item(); if (tmp) @@ -13070,11 +13957,8 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, func->set_cmp_func(); } } - else if (left_item->eq(field,0) && right_item != value && - left_item->cmp_context == field->cmp_context && - (right_item->result_type() != STRING_RESULT || - value->result_type() != STRING_RESULT || - right_item->collation.collation == value->collation.collation)) + else if (can_change_cond_ref_to_const(func, left_item, right_item, + field_value_owner, field, value)) { Item *tmp= value->clone_item(); if (tmp) @@ -13123,7 +14007,8 @@ propagate_cond_constants(THD *thd, I_List<COND_CMP> *save_list, Item **args= cond_cmp->cmp_func->arguments(); if (!args[0]->const_item()) change_cond_ref_to_const(thd, &save,cond_cmp->and_level, - cond_cmp->and_level, args[0], args[1]); + cond_cmp->and_level, + cond_cmp->cmp_func, args[0], args[1]); } } } @@ -13145,14 +14030,14 @@ propagate_cond_constants(THD *thd, I_List<COND_CMP> *save_list, resolve_const_item(thd, &args[1], args[0]); func->update_used_tables(); change_cond_ref_to_const(thd, save_list, and_father, and_father, - args[0], args[1]); + func, args[0], args[1]); } else if (left_const) { resolve_const_item(thd, &args[0], args[1]); func->update_used_tables(); change_cond_ref_to_const(thd, save_list, and_father, and_father, - args[1], args[0]); + func, args[1], args[0]); } } } @@ -13288,7 +14173,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, NESTED_JOIN *nested_join; TABLE_LIST *prev_table= 0; List_iterator<TABLE_LIST> li(*join_list); - bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN); + bool straight_join= MY_TEST(join->select_options & SELECT_STRAIGHT_JOIN); DBUG_ENTER("simplify_joins"); /* @@ -13897,9 +14782,10 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, static COND * -optimize_cond(JOIN *join, COND *conds, +optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, bool ignore_on_conds, - Item::cond_result *cond_value, COND_EQUAL **cond_equal) + Item::cond_result *cond_value, COND_EQUAL **cond_equal, + int flags) { THD *thd= join->thd; DBUG_ENTER("optimize_cond"); @@ -13922,9 +14808,10 @@ optimize_cond(JOIN *join, COND *conds, multiple equality contains a constant. */ DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY);); - conds= build_equal_items(join, conds, NULL, join_list, ignore_on_conds, - cond_equal); - DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); + conds= build_equal_items(join, conds, NULL, join_list, + ignore_on_conds, cond_equal, + MY_TEST(flags & OPT_LINK_EQUAL_FIELDS)); + DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); /* change field = field to field = const for each found field = const */ propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds); @@ -14071,20 +14958,9 @@ bool cond_is_datetime_is_null(Item *cond) if (cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) { - Item **args= ((Item_func_isnull*) cond)->arguments(); - if (args[0]->real_item()->type() == Item::FIELD_ITEM) - { - Field *field=((Item_field*) (args[0]->real_item()))->field; - - if (((field->type() == MYSQL_TYPE_DATE) || - (field->type() == MYSQL_TYPE_DATETIME)) && - (field->flags & NOT_NULL_FLAG)) - { - return TRUE; - } - } + return ((Item_func_isnull*) cond)->arg_is_datetime_notnull_field(); } - return FALSE; + return false; } @@ -14553,7 +15429,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) static bool test_if_equality_guarantees_uniqueness(Item *l, Item *r) { - return r->const_item() && + return (r->const_item() || !(r->used_tables() & ~OUTER_REF_TABLE_BIT)) && item_cmp_type(l->cmp_type(), r->cmp_type()) == l->cmp_type() && (l->cmp_type() != STRING_RESULT || l->collation.collation == r->collation.collation); @@ -14719,6 +15595,10 @@ Field *create_tmp_field_from_field(THD *thd, Field *org_field, ((Field_double *) new_field)->not_fixed= TRUE; new_field->vcol_info= 0; new_field->stored_in_db= TRUE; + new_field->cond_selectivity= 1.0; + new_field->next_equal_field= NULL; + new_field->option_list= NULL; + new_field->option_struct= NULL; } return new_field; } @@ -15046,6 +15926,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, case Item::REAL_ITEM: case Item::DECIMAL_ITEM: case Item::STRING_ITEM: + case Item::DATE_ITEM: case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: @@ -15081,17 +15962,20 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps) { uint field_count= table->s->fields; - bitmap_init(&table->def_read_set, (my_bitmap_map*) bitmaps, field_count, + my_bitmap_init(&table->def_read_set, (my_bitmap_map*) bitmaps, field_count, FALSE); - bitmap_init(&table->def_vcol_set, + my_bitmap_init(&table->def_vcol_set, (my_bitmap_map*) (bitmaps+ bitmap_buffer_size(field_count)), field_count, FALSE); - bitmap_init(&table->tmp_set, + my_bitmap_init(&table->tmp_set, (my_bitmap_map*) (bitmaps+ 2*bitmap_buffer_size(field_count)), field_count, FALSE); - bitmap_init(&table->eq_join_set, + my_bitmap_init(&table->eq_join_set, (my_bitmap_map*) (bitmaps+ 3*bitmap_buffer_size(field_count)), field_count, FALSE); + my_bitmap_init(&table->cond_set, + (my_bitmap_map*) (bitmaps+ 4*bitmap_buffer_size(field_count)), + field_count, FALSE); /* write_set and all_set are copies of read_set */ table->def_write_set= table->def_read_set; table->s->all_set= table->def_read_set; @@ -15155,7 +16039,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, KEY *keyinfo; KEY_PART_INFO *key_part_info; Item **copy_func; - ENGINE_COLUMNDEF *recinfo; + TMP_ENGINE_COLUMNDEF *recinfo; /* total_uneven_bit_length is uneven bit length for visible fields hidden_uneven_bit_length is uneven bit length for hidden fields @@ -15169,9 +16053,8 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, ("table_alias: '%s' distinct: %d save_sum_fields: %d " "rows_limit: %lu group: %d", table_alias, (int) distinct, (int) save_sum_fields, - (ulong) rows_limit,test(group))); + (ulong) rows_limit, MY_TEST(group))); - status_var_increment(thd->status_var.created_tmp_tables); thd->query_plan_flags|= QPLAN_TMP_TABLE; if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES)) @@ -15239,7 +16122,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (param->precomputed_group_by) copy_func_count+= param->sum_func_count; - 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), @@ -15257,7 +16140,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, &tmpname, (uint) strlen(path)+1, &group_buff, (group && ! using_unique_constraint ? param->group_length : 0), - &bitmaps, bitmap_buffer_size(field_count)*4, + &bitmaps, bitmap_buffer_size(field_count)*5, NullS)) { if (temp_pool_slot != MY_BIT_NONE) @@ -15303,7 +16186,6 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, 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= param->table_charset; share->primary_key= MAX_KEY; // Indicate no primary key share->keys_for_keyread.init(); @@ -15546,6 +16428,12 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (!table->file) goto err; + if (table->file->set_ha_share_ref(&share->ha_share)) + { + delete table->file; + goto err; + } + if (!using_unique_constraint) reclength+= group_null_items; // null flag is stored separately @@ -15692,7 +16580,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, 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); @@ -15712,18 +16600,21 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, table->group=group; /* Table is grouped by key */ param->group_buff=group_buff; share->keys=1; - share->uniques= test(using_unique_constraint); + share->uniques= MY_TEST(using_unique_constraint); table->key_info= table->s->key_info= keyinfo; table->keys_in_use_for_query.set_bit(0); share->keys_in_use.set_bit(0); keyinfo->key_part=key_part_info; keyinfo->flags=HA_NOSAME | HA_BINARY_PACK_KEY | HA_PACK_KEY; keyinfo->ext_key_flags= keyinfo->flags; - keyinfo->usable_key_parts=keyinfo->key_parts= param->group_parts; - keyinfo->ext_key_parts= keyinfo->key_parts; + keyinfo->usable_key_parts=keyinfo->user_defined_key_parts= param->group_parts; + keyinfo->ext_key_parts= keyinfo->user_defined_key_parts; keyinfo->key_length=0; - keyinfo->rec_per_key=0; + keyinfo->rec_per_key=NULL; + keyinfo->read_stats= NULL; + keyinfo->collected_stats= NULL; keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->is_statistics_from_stat_tables= FALSE; keyinfo->name= (char*) "group_key"; ORDER *cur_group= group; for (; cur_group ; cur_group= cur_group->next, key_part_info++) @@ -15763,7 +16654,8 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (!(cur_group->field= field->new_key_field(thd->mem_root,table, group_buff + - test(maybe_null), + MY_TEST(maybe_null), + key_part_info->length, field->null_ptr, field->null_bit))) goto err; /* purecov: inspected */ @@ -15817,16 +16709,17 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, share->uniques= 1; } null_pack_length-=hidden_null_pack_length; - keyinfo->key_parts= ((field_count-param->hidden_field_count)+ - (share->uniques ? test(null_pack_length) : 0)); - keyinfo->ext_key_parts= keyinfo->key_parts; + keyinfo->user_defined_key_parts= + ((field_count-param->hidden_field_count)+ + (share->uniques ? MY_TEST(null_pack_length) : 0)); + keyinfo->ext_key_parts= keyinfo->user_defined_key_parts; table->distinct= 1; share->keys= 1; if (!(key_part_info= (KEY_PART_INFO*) alloc_root(&table->mem_root, - keyinfo->key_parts * sizeof(KEY_PART_INFO)))) + keyinfo->user_defined_key_parts * sizeof(KEY_PART_INFO)))) goto err; - bzero((void*) key_part_info, keyinfo->key_parts * sizeof(KEY_PART_INFO)); + bzero((void*) key_part_info, keyinfo->user_defined_key_parts * sizeof(KEY_PART_INFO)); table->keys_in_use_for_query.set_bit(0); share->keys_in_use.set_bit(0); table->key_info= table->s->key_info= keyinfo; @@ -15836,6 +16729,10 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, keyinfo->key_length= 0; // Will compute the sum of the parts below. keyinfo->name= (char*) "distinct_key"; keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->is_statistics_from_stat_tables= FALSE; + keyinfo->read_stats= NULL; + keyinfo->collected_stats= NULL; + /* Needed by non-merged semi-joins: SJ-Materialized table must have a valid rec_per_key array, because it participates in join optimization. Since @@ -15845,7 +16742,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, (For table record count, we calculate and set JOIN_TAB::found_records, see get_delayed_table_estimates()). */ - size_t rpk_size= keyinfo->key_parts* sizeof(keyinfo->rec_per_key[0]); + size_t rpk_size= keyinfo->user_defined_key_parts * sizeof(keyinfo->rec_per_key[0]); if (!(keyinfo->rec_per_key= (ulong*) alloc_root(&table->mem_root, rpk_size))) goto err; @@ -16006,7 +16903,7 @@ TABLE *create_virtual_tmp_table(THD *thd, List<Create_field> &field_list) &share, sizeof(*share), &field, (field_count + 1) * sizeof(Field*), &blob_field, (field_count+1) *sizeof(uint), - &bitmaps, bitmap_buffer_size(field_count)*4, + &bitmaps, bitmap_buffer_size(field_count)*5, NullS)) return 0; @@ -16017,7 +16914,6 @@ TABLE *create_virtual_tmp_table(THD *thd, List<Create_field> &field_list) table->temp_pool_slot= MY_BIT_NONE; share->blob_field= blob_field; share->fields= field_count; - share->blob_ptr_size= portable_sizeof_char_ptr; setup_tmp_table_column_bitmaps(table, bitmaps); /* Create all fields and calculate the total length of record */ @@ -16114,19 +17010,23 @@ bool open_tmp_table(TABLE *table) HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { - table->file->print_error(error,MYF(0)); /* purecov: inspected */ - table->db_stat=0; - return(1); + table->file->print_error(error, MYF(0)); /* purecov: inspected */ + table->db_stat= 0; + return 1; } table->db_stat= HA_OPEN_KEYFILE+HA_OPEN_RNDFILE; - (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - table->created= TRUE; - return(0); -} + (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ + if (!table->created) + { + table->created= TRUE; + table->in_use->inc_status_created_tmp_tables(); + } + return 0; +} -#if defined(WITH_ARIA_STORAGE_ENGINE) && defined(USE_ARIA_FOR_TMP_TABLES) +#ifdef USE_ARIA_FOR_TMP_TABLES /* Create internal (MyISAM or Maria) temporary table @@ -16142,12 +17042,12 @@ bool open_tmp_table(TABLE *table) Create an internal emporary table according to passed description. The is assumed to have one unique index or constraint. - The passed array or ENGINE_COLUMNDEF structures must have this form: + The passed array or TMP_ENGINE_COLUMNDEF structures must have this form: 1. 1-byte column (afaiu for 'deleted' flag) (note maybe not 1-byte when there are many nullable columns) 2. Table columns - 3. One free ENGINE_COLUMNDEF element (*recinfo points here) + 3. One free TMP_ENGINE_COLUMNDEF element (*recinfo points here) This function may use the free element to create hash column for unique constraint. @@ -16159,8 +17059,8 @@ bool open_tmp_table(TABLE *table) bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, - ENGINE_COLUMNDEF *start_recinfo, - ENGINE_COLUMNDEF **recinfo, + TMP_ENGINE_COLUMNDEF *start_recinfo, + TMP_ENGINE_COLUMNDEF **recinfo, ulonglong options) { int error; @@ -16174,11 +17074,11 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, { // Get keys for ni_create bool using_unique_constraint=0; HA_KEYSEG *seg= (HA_KEYSEG*) alloc_root(&table->mem_root, - sizeof(*seg) * keyinfo->key_parts); + sizeof(*seg) * keyinfo->user_defined_key_parts); if (!seg) goto err; - bzero(seg, sizeof(*seg) * keyinfo->key_parts); + bzero(seg, sizeof(*seg) * keyinfo->user_defined_key_parts); /* Note that a similar check is performed during subquery_types_allow_materialization. See MDEV-7122 for more details as @@ -16186,7 +17086,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, all tmp_table engines. */ if (keyinfo->key_length > table->file->max_key_length() || - keyinfo->key_parts > table->file->max_key_parts() || + keyinfo->user_defined_key_parts > table->file->max_key_parts() || share->uniques) { if (!share->uniques && !(keyinfo->flags & HA_NOSAME)) @@ -16201,7 +17101,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, share->uniques= 1; using_unique_constraint=1; bzero((char*) &uniquedef,sizeof(uniquedef)); - uniquedef.keysegs=keyinfo->key_parts; + uniquedef.keysegs=keyinfo->user_defined_key_parts; uniquedef.seg=seg; uniquedef.null_are_equal=1; @@ -16217,10 +17117,10 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, /* Create a key */ bzero((char*) &keydef,sizeof(keydef)); keydef.flag= keyinfo->flags & HA_NOSAME; - keydef.keysegs= keyinfo->key_parts; + keydef.keysegs= keyinfo->user_defined_key_parts; keydef.seg= seg; } - for (uint i=0; i < keyinfo->key_parts ; i++,seg++) + for (uint i=0; i < keyinfo->user_defined_key_parts ; i++,seg++) { Field *field=keyinfo->key_part[i].field; seg->flag= 0; @@ -16232,7 +17132,8 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, seg->type= ((keyinfo->key_part[i].key_type & FIELDFLAG_BINARY) ? HA_KEYTYPE_VARBINARY2 : HA_KEYTYPE_VARTEXT2); - seg->bit_start= (uint8)(field->pack_length() - share->blob_ptr_size); + seg->bit_start= (uint8)(field->pack_length() - + portable_sizeof_char_ptr); seg->flag= HA_BLOB_PART; seg->length=0; // Whole blob in unique constraint } @@ -16287,36 +17188,22 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, start_recinfo, share->uniques, &uniquedef, &create_info, - HA_CREATE_TMP_TABLE))) + HA_CREATE_TMP_TABLE | HA_CREATE_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; goto err; } - status_var_increment(table->in_use->status_var.created_tmp_disk_tables); + table->in_use->inc_status_created_tmp_disk_tables(); + table->in_use->inc_status_created_tmp_tables(); table->in_use->query_plan_flags|= QPLAN_TMP_DISK; share->db_record_offset= 1; + table->created= TRUE; DBUG_RETURN(0); err: DBUG_RETURN(1); } - -bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, - ENGINE_COLUMNDEF *start_recinfo, - ENGINE_COLUMNDEF **recinfo, - int error, - bool ignore_last_dupp_key_error, - bool *is_duplicate) -{ - return create_internal_tmp_table_from_heap2(thd, table, - start_recinfo, recinfo, error, - ignore_last_dupp_key_error, - maria_hton, - "converting HEAP to Aria", - is_duplicate); -} - #else /* @@ -16334,12 +17221,12 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, Create an internal emporary table according to passed description. The is assumed to have one unique index or constraint. - The passed array or ENGINE_COLUMNDEF structures must have this form: + The passed array or TMP_ENGINE_COLUMNDEF structures must have this form: 1. 1-byte column (afaiu for 'deleted' flag) (note maybe not 1-byte when there are many nullable columns) 2. Table columns - 3. One free ENGINE_COLUMNDEF element (*recinfo points here) + 3. One free TMP_ENGINE_COLUMNDEF element (*recinfo points here) This function may use the free element to create hash column for unique constraint. @@ -16352,8 +17239,8 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, /* Create internal MyISAM temporary table */ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, - ENGINE_COLUMNDEF *start_recinfo, - ENGINE_COLUMNDEF **recinfo, + TMP_ENGINE_COLUMNDEF *start_recinfo, + TMP_ENGINE_COLUMNDEF **recinfo, ulonglong options) { int error; @@ -16366,11 +17253,11 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, { // Get keys for ni_create bool using_unique_constraint=0; HA_KEYSEG *seg= (HA_KEYSEG*) alloc_root(&table->mem_root, - sizeof(*seg) * keyinfo->key_parts); + sizeof(*seg) * keyinfo->user_defined_key_parts); if (!seg) goto err; - bzero(seg, sizeof(*seg) * keyinfo->key_parts); + bzero(seg, sizeof(*seg) * keyinfo->user_defined_key_parts); /* Note that a similar check is performed during subquery_types_allow_materialization. See MDEV-7122 for more details as @@ -16378,7 +17265,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, all tmp_table engines. */ if (keyinfo->key_length > table->file->max_key_length() || - keyinfo->key_parts > table->file->max_key_parts() || + keyinfo->user_defined_key_parts > table->file->max_key_parts() || share->uniques) { /* Can't create a key; Make a unique constraint instead of a key */ @@ -16386,7 +17273,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, share->uniques= 1; using_unique_constraint=1; bzero((char*) &uniquedef,sizeof(uniquedef)); - uniquedef.keysegs=keyinfo->key_parts; + uniquedef.keysegs=keyinfo->user_defined_key_parts; uniquedef.seg=seg; uniquedef.null_are_equal=1; @@ -16403,10 +17290,10 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, bzero((char*) &keydef,sizeof(keydef)); keydef.flag= ((keyinfo->flags & HA_NOSAME) | HA_BINARY_PACK_KEY | HA_PACK_KEY); - keydef.keysegs= keyinfo->key_parts; + keydef.keysegs= keyinfo->user_defined_key_parts; keydef.seg= seg; } - for (uint i=0; i < keyinfo->key_parts ; i++,seg++) + for (uint i=0; i < keyinfo->user_defined_key_parts ; i++,seg++) { Field *field=keyinfo->key_part[i].field; seg->flag= 0; @@ -16418,7 +17305,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, seg->type= ((keyinfo->key_part[i].key_type & FIELDFLAG_BINARY) ? HA_KEYTYPE_VARBINARY2 : HA_KEYTYPE_VARTEXT2); - seg->bit_start= (uint8)(field->pack_length() - share->blob_ptr_size); + seg->bit_start= (uint8)(field->pack_length() - portable_sizeof_char_ptr); seg->flag= HA_BLOB_PART; seg->length=0; // Whole blob in unique constraint } @@ -16455,7 +17342,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, start_recinfo, share->uniques, &uniquedef, &create_info, - HA_CREATE_TMP_TABLE | + HA_CREATE_TMP_TABLE | HA_CREATE_INTERNAL_TABLE | ((share->db_create_options & HA_OPTION_PACK_RECORD) ? HA_PACK_RECORD : 0) ))) @@ -16464,7 +17351,8 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, table->db_stat=0; goto err; } - status_var_increment(table->in_use->status_var.created_tmp_disk_tables); + table->in_use->inc_status_created_tmp_disk_tables(); + table->in_use->inc_status_created_tmp_tables(); table->in_use->query_plan_flags|= QPLAN_TMP_DISK; share->db_record_offset= 1; table->created= TRUE; @@ -16473,27 +17361,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, DBUG_RETURN(1); } - -/** - If a HEAP table gets full, create a MyISAM table and copy all rows to this -*/ - -bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, - ENGINE_COLUMNDEF *start_recinfo, - ENGINE_COLUMNDEF **recinfo, - int error, - bool ignore_last_dupp_key_error, - bool *is_duplicate) -{ - return create_internal_tmp_table_from_heap2(thd, table, - start_recinfo, recinfo, error, - ignore_last_dupp_key_error, - myisam_hton, - "converting HEAP to MyISAM", - is_duplicate); -} - -#endif /* WITH_MARIA_STORAGE_ENGINE */ +#endif /* USE_ARIA_FOR_TMP_TABLES */ /* @@ -16502,21 +17370,19 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, */ -static bool -create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, - ENGINE_COLUMNDEF *start_recinfo, - ENGINE_COLUMNDEF **recinfo, - int error, - bool ignore_last_dupp_key_error, - handlerton *hton, - const char *proc_info, - bool *is_duplicate) +bool +create_internal_tmp_table_from_heap(THD *thd, TABLE *table, + TMP_ENGINE_COLUMNDEF *start_recinfo, + TMP_ENGINE_COLUMNDEF **recinfo, + int error, + bool ignore_last_dupp_key_error, + bool *is_duplicate) { TABLE new_table; TABLE_SHARE share; const char *save_proc_info; int write_err= 0; - DBUG_ENTER("create_internal_tmp_table_from_heap2"); + DBUG_ENTER("create_internal_tmp_table_from_heap"); if (is_duplicate) *is_duplicate= FALSE; @@ -16533,13 +17399,19 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, new_table= *table; share= *table->s; new_table.s= &share; - new_table.s->db_plugin= ha_lock_engine(thd, hton); + new_table.s->db_plugin= ha_lock_engine(thd, TMP_ENGINE_HTON); if (!(new_table.file= get_new_handler(&share, &new_table.mem_root, new_table.s->db_type()))) DBUG_RETURN(1); // End of memory + if (new_table.file->set_ha_share_ref(&share.ha_share)) + { + delete new_table.file; + DBUG_RETURN(1); + } + save_proc_info=thd->proc_info; - thd_proc_info(thd, proc_info); + THD_STAGE_INFO(thd, stage_converting_heap_to_myisam); new_table.no_rows= table->no_rows; if (create_internal_tmp_table(&new_table, table->key_info, start_recinfo, @@ -16575,7 +17447,7 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, DBUG_EXECUTE_IF("raise_error", write_err= HA_ERR_FOUND_DUPP_KEY ;); if (write_err) goto err; - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); goto err_killed; @@ -16612,8 +17484,8 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, table->file->change_table_ptr(table, table->s); table->use_all_columns(); if (save_proc_info) - thd_proc_info(thd, save_proc_info == copy_to_tmp_table ? - "Copying to tmp table on disk" : save_proc_info); + thd_proc_info(thd, (!strcmp(save_proc_info,"Copying to tmp table") ? + "Copying to tmp table on disk" : save_proc_info)); DBUG_RETURN(0); err: @@ -16642,7 +17514,7 @@ free_tmp_table(THD *thd, TABLE *entry) entry->alias.c_ptr())); save_proc_info=thd->proc_info; - thd_proc_info(thd, "removing tmp table"); + THD_STAGE_INFO(thd, stage_removing_tmp_table); if (entry->file && entry->created) { @@ -16842,6 +17714,14 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) else { DBUG_ASSERT(join->table_count); + + DBUG_EXECUTE_IF("show_explain_probe_do_select", + if (dbug_user_var_equals_int(join->thd, + "show_explain_probe_select_id", + join->select_lex->select_number)) + dbug_serve_apcs(join->thd, 1); + ); + if (join->outer_ref_cond && !join->outer_ref_cond->val_int()) error= NESTED_LOOP_NO_MORE_ROWS; else @@ -16972,7 +17852,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) rc= sub_select(join, join_tab, end_of_records); DBUG_RETURN(rc); } - if (join->thd->killed) + if (join->thd->check_killed()) { /* The user has aborted the execution of the query */ join->thd->send_kill_message(); @@ -17169,7 +18049,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) if (join_tab->on_precond && !join_tab->on_precond->val_int()) rc= NESTED_LOOP_NO_MORE_ROWS; } - join->thd->warning_info->reset_current_row_for_warning(); + join->thd->get_stmt_da()->reset_current_row_for_warning(); if (rc != NESTED_LOOP_NO_MORE_ROWS && (rc= join_tab_execution_startup(join_tab)) < 0) @@ -17262,12 +18142,14 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, DBUG_ENTER("evaluate_join_record"); DBUG_PRINT("enter", ("evaluate_join_record join: %p join_tab: %p" - " cond: %p error: %d", join, join_tab, select_cond, error)); + " cond: %p error: %d alias %s", + join, join_tab, select_cond, error, + join_tab->table->alias.ptr())); if (error > 0 || (join->thd->is_error())) // Fatal error DBUG_RETURN(NESTED_LOOP_ERROR); if (error < 0) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); - if (join->thd->killed) // Aborted by user + if (join->thd->check_killed()) // Aborted by user { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -17278,7 +18160,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, if (select_cond) { - select_cond_result= test(select_cond->val_int()); + select_cond_result= MY_TEST(select_cond->val_int()); /* check for errors evaluating the condition */ if (join->thd->is_error()) @@ -17373,6 +18255,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, if (join_tab->check_weed_out_table && found) { int res= join_tab->check_weed_out_table->sj_weedout_check_row(join->thd); + DBUG_PRINT("info", ("weedout_check: %d", res)); if (res == -1) DBUG_RETURN(NESTED_LOOP_ERROR); else if (res == 1) @@ -17393,15 +18276,15 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, (See above join->return_tab= tab). */ join->examined_rows++; - DBUG_PRINT("counts", ("join->examined_rows++: %lu", - (ulong) join->examined_rows)); + DBUG_PRINT("counts", ("join->examined_rows++: %lu found: %d", + (ulong) join->examined_rows, (int) found)); if (found) { enum enum_nested_loop_state rc; /* A match from join_tab is found for the current partial join. */ rc= (*join_tab->next_select)(join, join_tab+1, 0); - join->thd->warning_info->inc_current_row_for_warning(); + join->thd->get_stmt_da()->inc_current_row_for_warning(); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) DBUG_RETURN(rc); if (return_tab < join->return_tab) @@ -17423,7 +18306,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, } else { - join->thd->warning_info->inc_current_row_for_warning(); + join->thd->get_stmt_da()->inc_current_row_for_warning(); join_tab->read_record.unlock_row(join_tab); } } @@ -17434,7 +18317,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, with the beginning coinciding with the current partial join. */ join->examined_rows++; - join->thd->warning_info->inc_current_row_for_warning(); + join->thd->get_stmt_da()->inc_current_row_for_warning(); join_tab->read_record.unlock_row(join_tab); } DBUG_RETURN(NESTED_LOOP_OK); @@ -17546,13 +18429,8 @@ int report_error(TABLE *table, int error) */ if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT && error != HA_ERR_TABLE_DEF_CHANGED && !table->in_use->killed) - { - push_warning_printf(table->in_use, MYSQL_ERROR::WARN_LEVEL_WARN, error, - "Got error %d when reading table %`s.%`s", - error, table->s->db.str, table->s->table_name.str); sql_print_error("Got error %d when reading table '%s'", error, table->s->path.str); - } table->file->print_error(error,MYF(0)); return 1; } @@ -17615,7 +18493,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) { if ((error=join_read_system(tab))) { // Info for DESCRIBE - tab->info="const row not found"; + tab->info= ET_CONST_ROW_NOT_FOUND; /* Mark for EXPLAIN that the row was not found */ pos->records_read=0.0; pos->ref_depend_map= 0; @@ -17641,7 +18519,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) table->disable_keyread(); if (error) { - tab->info="unique row not found"; + tab->info= ET_UNIQUE_ROW_NOT_FOUND; /* Mark for EXPLAIN that the row was not found */ pos->records_read=0.0; pos->ref_depend_map= 0; @@ -17669,7 +18547,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) (*tab->on_expr_ref)->update_used_tables(); DBUG_ASSERT((*tab->on_expr_ref)->const_item()); #endif - if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0))) + if ((table->null_row= MY_TEST((*tab->on_expr_ref)->val_int() == 0))) mark_as_null_row(table); } if (!table->null_row) @@ -18057,8 +18935,20 @@ int read_first_record_seq(JOIN_TAB *tab) static int test_if_quick_select(JOIN_TAB *tab) { + DBUG_EXECUTE_IF("show_explain_probe_test_if_quick_select", + if (dbug_user_var_equals_int(tab->join->thd, + "show_explain_probe_select_id", + tab->join->select_lex->select_number)) + dbug_serve_apcs(tab->join->thd, 1); + ); + + delete tab->select->quick; tab->select->quick=0; + + if (tab->table->file->inited != handler::NONE) + tab->table->file->ha_index_or_rnd_end(); + return tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, FALSE); @@ -18312,7 +19202,25 @@ end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), join->duplicate_rows++; } } - if (++join->send_records >= join->unit->select_limit_cnt && + + ++join->send_records; + if (join->send_records >= join->unit->select_limit_cnt && + !join->do_send_rows) + { + /* + If filesort is used for sorting, stop after select_limit_cnt+1 + records are read. Because of optimization in some cases it can + provide only select_limit_cnt+1 records. + */ + if (join->order && join->sortorder && + join->filesort_found_rows && + join->select_options & OPTION_FOUND_ROWS) + { + DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT")); + DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT); + } + } + if (join->send_records >= join->unit->select_limit_cnt && join->do_send_rows) { if (join->select_options & OPTION_FOUND_ROWS) @@ -18536,7 +19444,7 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), } } end: - if (join->thd->killed) + if (join->thd->check_killed()) { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -18565,7 +19473,16 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), for (group=table->group ; group ; group=group->next) { Item *item= *group->item; - item->save_org_in_field(group->field); + if (group->fast_field_copier_setup != group->field) + { + DBUG_PRINT("info", ("new setup 0x%lx -> 0x%lx", + (ulong)group->fast_field_copier_setup, + (ulong)group->field)); + group->fast_field_copier_setup= group->field; + group->fast_field_copier_func= + item->setup_fast_field_copier(group->field); + } + item->save_org_in_field(group->field, group->fast_field_copier_func); /* Store in the used key if the field was 0 */ if (item->maybe_null) group->buff[-1]= (char) group->field->is_null(); @@ -18607,7 +19524,7 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), } join->send_records++; end: - if (join->thd->killed) + if (join->thd->check_killed()) { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -18657,7 +19574,7 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } } - if (join->thd->killed) + if (join->thd->check_killed()) { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -18735,7 +19652,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (join->procedure) join->procedure->add(); end: - if (join->thd->killed) + if (join->thd->check_killed()) { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -19247,7 +20164,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, { KEY_PART_INFO *key_part,*key_part_end; key_part=table->key_info[idx].key_part; - key_part_end=key_part+table->key_info[idx].key_parts; + key_part_end=key_part+table->key_info[idx].user_defined_key_parts; key_part_map const_key_parts=table->const_key_parts[idx]; int reverse=0; uint key_parts; @@ -19266,7 +20183,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, for (; const_key_parts & 1 ; const_key_parts>>= 1) key_part++; - if (key_part == key_part_end) + if (key_part >= key_part_end) { /* We are at the end of the key. Check if the engine has the primary @@ -19289,7 +20206,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, (we have to stop as first not continous primary key part) */ for (key_part_end= key_part, - end= key_part+table->key_info[table->s->primary_key].key_parts; + end= key_part+table->key_info[table->s->primary_key].user_defined_key_parts; key_part_end < end; key_part_end++, pk_part_idx++) { /* Found hole in the pk_parts; Abort */ @@ -19307,8 +20224,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, Test if the primary key parts were all const (i.e. there's one row). The sorting doesn't matter. */ - if (key_part == - start+table->key_info[table->s->primary_key].key_parts && + if (key_part == start+table->key_info[table->s->primary_key].user_defined_key_parts && reverse == 0) { key_parts= 0; @@ -19334,7 +20250,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, } if (on_pk_suffix) { - uint used_key_parts_secondary= table->key_info[idx].key_parts; + uint used_key_parts_secondary= table->key_info[idx].user_defined_key_parts; uint used_key_parts_pk= (uint) (key_part - table->key_info[table->s->primary_key].key_part); key_parts= used_key_parts_pk + used_key_parts_secondary; @@ -19387,6 +20303,7 @@ uint find_shortest_key(TABLE *table, const key_map *usable_keys) min_cost= cost; best=nr; } + DBUG_ASSERT(best < MAX_KEY); } } } @@ -19445,7 +20362,7 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, { if (usable_keys->is_set(nr) && table->key_info[nr].key_length < min_length && - table->key_info[nr].key_parts >= ref_key_parts && + table->key_info[nr].user_defined_key_parts >= ref_key_parts && is_subkey(table->key_info[nr].key_part, ref_key_part, ref_key_part_end) && test_if_order_by_key(order, table, nr)) @@ -19503,7 +20420,7 @@ list_contains_unique_index(TABLE *table, KEY_PART_INFO *key_part, *key_part_end; for (key_part=keyinfo->key_part, - key_part_end=key_part+ keyinfo->key_parts; + key_part_end=key_part+ keyinfo->user_defined_key_parts; key_part < key_part_end; key_part++) { @@ -19776,7 +20693,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, uint saved_best_key_parts= 0; int best_key_direction= 0; JOIN *join= tab->join; - ha_rows table_records= table->file->stats.records; + ha_rows table_records= table->stat_records(); test_if_cheaper_ordering(tab, order, table, usable_keys, ref_key, select_limit, @@ -19812,7 +20729,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, order_direction= best_key_direction; /* saved_best_key_parts is actual number of used keyparts found by the - test_if_order_by_key function. It could differ from keyinfo->key_parts, + test_if_order_by_key function. It could differ from keyinfo->user_defined_key_parts, thus we have to restore it in case of desc order as it affects QUICK_SELECT_DESC behaviour. */ @@ -19892,7 +20809,7 @@ check_reverse_order: { tab->ref.key= -1; tab->ref.key_parts= 0; - if (select_limit < table->file->stats.records) + if (select_limit < table->stat_records()) tab->limit= select_limit; table->disable_keyread(); } @@ -20047,6 +20964,8 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, { uint length= 0; ha_rows examined_rows; + ha_rows found_rows; + ha_rows filesort_retval= HA_POS_ERROR; TABLE *table; SQL_SELECT *select; JOIN_TAB *tab; @@ -20135,7 +21054,8 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, goto err; /* purecov: inspected */ table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE), - MYF(MY_WME | MY_ZEROFILL)); + MYF(MY_WME | MY_ZEROFILL| + MY_THREAD_SPECIFIC)); table->status=0; // May be wrong if quick_select if (!tab->preread_init_done && tab->preread_init()) @@ -20179,9 +21099,11 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, if (table->s->tmp_table) table->file->info(HA_STATUS_VARIABLE); // Get record count - table->sort.found_records=filesort(thd, table,join->sortorder, length, - select, filesort_limit, 0, - &examined_rows); + filesort_retval= filesort(thd, table, join->sortorder, length, + select, filesort_limit, 0, + &examined_rows, &found_rows); + table->sort.found_records= filesort_retval; + tab->records= join->select_options & OPTION_FOUND_ROWS ? found_rows : filesort_retval; if (quick_created) { @@ -20199,72 +21121,6 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, *(join->pre_sort_join_tab)= *tab; - /*TODO: here, close the index scan, cancel index-only read. */ - tab->records= table->sort.found_records; // For SQL_CALC_ROWS -#if 0 - /* MariaDB doesn't need the following: */ - if (select) - { - /* - We need to preserve tablesort's output resultset here, because - QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT (called by - SQL_SELECT::cleanup()) may free it assuming it's the result of the quick - select operation that we no longer need. Note that all the other parts of - this data structure are cleaned up when - QUICK_INDEX_MERGE_SELECT::get_next encounters end of data, so the next - SQL_SELECT::cleanup() call changes sort.io_cache alone. - */ - IO_CACHE *tablesort_result_cache; - - tablesort_result_cache= table->sort.io_cache; - table->sort.io_cache= NULL; - - if (select->quick && - select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) - { - tab->filesort_used_loose_index_scan= true; - - QUICK_GROUP_MIN_MAX_SELECT *minmax_quick= - static_cast<QUICK_GROUP_MIN_MAX_SELECT*>(select->quick); - if (minmax_quick->is_agg_distinct()) - tab->filesort_used_loose_index_scan_agg_distinct= true; - } - - /* - If a quick object was created outside of create_sort_index() - that might be reused, then do not call select->cleanup() since - it will delete the quick object. - */ - if (!keep_quick) - { - select->cleanup(); - - // If we deleted the quick object we need to clear quick_keys - table->quick_keys.clear_all(); - table->intersect_keys.clear_all(); - } - else - { - // Need to close the index scan in order to re-use the handler - tab->select->quick->range_end(); - } - - /* - The select object is now ready for the next use. To avoid that - the select object is used when reading the records in sorted - order we set the pointer to it to NULL. The select pointer will - be restored from the saved_select pointer when this select - operation is completed (@see JOIN::exec). This ensures that it - will be re-used when filesort is used by subqueries that are - executed multiple times. - */ - tab->saved_select= tab->select; - tab->select= NULL; - - // Restore the output resultset - table->sort.io_cache= tablesort_result_cache; - } -#endif tab->select=NULL; tab->set_select_cond(NULL, __LINE__); tab->type=JT_ALL; // Read with normal read_record @@ -20275,12 +21131,11 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, goto err; tab->join->examined_rows+=examined_rows; - DBUG_RETURN(table->sort.found_records == HA_POS_ERROR); + DBUG_RETURN(filesort_retval == HA_POS_ERROR); err: DBUG_RETURN(-1); } - void JOIN::clean_pre_sort_join_tab() { //TABLE *table= pre_sort_join_tab->table; @@ -20289,29 +21144,6 @@ void JOIN::clean_pre_sort_join_tab() the table already deleted by st_select_lex_unit::cleanup(). We rely on that fake_select_lex didn't have quick select. */ -#if 0 - if (pre_sort_join_tab->select && pre_sort_join_tab->select->quick) - { - /* - We need to preserve tablesort's output resultset here, because - QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT (called by - SQL_SELECT::cleanup()) may free it assuming it's the result of the quick - select operation that we no longer need. Note that all the other parts of - this data structure are cleaned up when - QUICK_INDEX_MERGE_SELECT::get_next encounters end of data, so the next - SQL_SELECT::cleanup() call changes sort.io_cache alone. - */ - IO_CACHE *tablesort_result_cache; - - tablesort_result_cache= table->sort.io_cache; - table->sort.io_cache= NULL; - pre_sort_join_tab->select->cleanup(); - table->quick_keys.clear_all(); // as far as we cleanup select->quick - table->intersect_keys.clear_all(); - table->sort.io_cache= tablesort_result_cache; - } -#endif - //table->disable_keyread(); // Restore if we used indexes if (pre_sort_join_tab->select && pre_sort_join_tab->select->quick) { pre_sort_join_tab->select->cleanup(); @@ -20435,7 +21267,7 @@ static int remove_dup_with_compare(THD *thd, TABLE *table, Field **first_field, error= file->ha_rnd_next(record); for (;;) { - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); error=0; @@ -20556,7 +21388,7 @@ static int remove_dup_with_hash_index(THD *thd, TABLE *table, for (;;) { uchar *org_key_pos; - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); error=0; @@ -20628,11 +21460,11 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length, count++; if (!sortorder) sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD) * - (max(count, *length) + 1)); + (MY_MAX(count, *length) + 1)); pos= sort= sortorder; if (!pos) - return 0; + DBUG_RETURN(0); for (;order;order=order->next,pos++) { @@ -20664,6 +21496,7 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length, else pos->item= item; pos->reverse=! order->asc; + DBUG_ASSERT(pos->field != NULL || pos->item != NULL); } *length=count; DBUG_RETURN(sort); @@ -20762,7 +21595,7 @@ cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref) ref_pointer_array and all_fields are updated. - @param[in] thd Pointer to current thread structure + @param[in] thd Pointer to current thread structure @param[in,out] ref_pointer_array All select, group and order by fields @param[in] tables List of tables to search in (usually FROM clause) @@ -20811,13 +21644,13 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, order->in_field_list= 1; order->counter= count; order->counter_used= 1; - return FALSE; + return FALSE; } /* Lookup the current GROUP/ORDER field in the SELECT clause. */ select_item= find_item_in_list(order_item, fields, &counter, REPORT_EXCEPT_NOT_FOUND, &resolution); if (!select_item) - return TRUE; /* The item is not unique, or some other error occured. */ + return TRUE; /* The item is not unique, or some other error occurred. */ /* Check whether the resolved field is not ambiguos. */ @@ -20826,7 +21659,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, Item *view_ref= NULL; /* If we have found field not by its alias in select list but by its - original field name, we should additionaly check if we have conflict + original field name, we should additionally check if we have conflict for this name (in case if we would perform lookup in all tables). */ if (resolution == RESOLVED_BEHIND_ALIAS && !order_item->fixed && @@ -20879,7 +21712,8 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, warning so the user knows that the field from the FROM clause overshadows the column reference from the SELECT list. */ - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR, + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_NON_UNIQ_ERROR, ER(ER_NON_UNIQ_ERROR), ((Item_ident*) order_item)->field_name, current_thd->where); @@ -21265,7 +22099,7 @@ test_if_subpart(ORDER *a,ORDER *b) else return 0; } - return test(!b); + return MY_TEST(!b); } /** @@ -21868,7 +22702,7 @@ change_to_use_tmp_fields(THD *thd, Item **ref_pointer_array, We are replacing the argument of Item_func_set_user_var after its value has been read. The argument's null_value should be set by now, so we must set it explicitly for the replacement argument since the null_value - may be read without any preceeding call to val_*(). + may be read without any preceding call to val_*(). */ new_field->update_null_value(); List<Item> list; @@ -22656,162 +23490,359 @@ void JOIN::clear() } } -/** - EXPLAIN handling. - Send a description about what how the select will be done to stdout. +/* + Print an EXPLAIN line with all NULLs and given message in the 'Extra' column */ -static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, - bool distinct,const char *message) +int print_explain_message_line(select_result_sink *result, + uint8 options, + uint select_number, + const char *select_type, + ha_rows *rows, + const char *message) { - List<Item> field_list; - List<Item> item_list; - THD *thd=join->thd; - select_result *result=join->result; Item *item_null= new Item_null(); - CHARSET_INFO *cs= system_charset_info; - int quick_type; - DBUG_ENTER("select_describe"); - DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s", - (ulong)join->select_lex, join->select_lex->type, - message ? message : "NULL")); - /* Don't log this into the slow query log */ - thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED); - join->unit->offset_limit_cnt= 0; + List<Item> item_list; - /* - NOTE: the number/types of items pushed into item_list must be in sync with - EXPLAIN column types as they're "defined" in THD::send_explain_fields() - */ - if (message) - { - item_list.push_back(new Item_int((int32) - join->select_lex->select_number)); - item_list.push_back(new Item_string(join->select_lex->type, - strlen(join->select_lex->type), cs)); - for (uint i=0 ; i < 7; i++) - item_list.push_back(item_null); - if (join->thd->lex->describe & DESCRIBE_PARTITIONS) - item_list.push_back(item_null); - if (join->thd->lex->describe & DESCRIBE_EXTENDED) - item_list.push_back(item_null); + item_list.push_back(new Item_int((int32) select_number)); + item_list.push_back(new Item_string_sys(select_type)); + /* `table` */ + item_list.push_back(item_null); + + /* `partitions` */ + if (options & DESCRIBE_PARTITIONS) + item_list.push_back(item_null); - item_list.push_back(new Item_string(message,strlen(message),cs)); - if (result->send_data(item_list)) - join->error= 1; + /* type, possible_keys, key, key_len, ref */ + for (uint i=0 ; i < 5; i++) + item_list.push_back(item_null); + + /* `rows` */ + if (rows) + { + item_list.push_back(new Item_int(*rows, + MY_INT64_NUM_DECIMAL_DIGITS)); } - else if (join->select_lex == join->unit->fake_select_lex) + else + item_list.push_back(item_null); + + /* `filtered` */ + if (options & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + + /* `Extra` */ + if (message) + item_list.push_back(new Item_string_sys(message)); + else + item_list.push_back(item_null); + + if (result->send_data(item_list)) + return 1; + return 0; +} + + +/* + Make a comma-separated list of possible_keys names and add it into the string +*/ + +void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line) +{ + if (!possible_keys.is_clear_all()) { - /* - here we assume that the query will return at least two rows, so we - show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong - and no filesort will be actually done, but executing all selects in - the UNION to provide precise EXPLAIN information will hardly be - appreciated :) - */ - char table_name_buffer[SAFE_NAME_LEN]; - item_list.empty(); - /* id */ - item_list.push_back(new Item_null); - /* select_type */ - item_list.push_back(new Item_string(join->select_lex->type, - strlen(join->select_lex->type), - cs)); - /* table */ - { - SELECT_LEX *sl= join->unit->first_select(); - uint len= 6, lastop= 0; - memcpy(table_name_buffer, STRING_WITH_LEN("<union")); - for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select()) - { - len+= lastop; - lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len, - "%u,", sl->select_number); - } - if (sl || len + lastop >= NAME_LEN) - { - memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1); - len+= 4; - } - else + uint j; + for (j=0 ; j < table->s->keys ; j++) + { + if (possible_keys.is_set(j)) { - len+= lastop; - table_name_buffer[len - 1]= '>'; // change ',' to '>' + if (line->length()) + line->append(','); + line->append(table->key_info[j].name, + strlen(table->key_info[j].name), + system_charset_info); } - item_list.push_back(new Item_string(table_name_buffer, len, cs)); } - /* partitions */ - if (join->thd->lex->describe & DESCRIBE_PARTITIONS) + } +} + +/* + Print an EXPLAIN output row, based on information provided in the parameters + + @note + Parameters that may have NULL value in EXPLAIN output, should be passed + (char*)NULL. + + @return + 0 - OK + 1 - OOM Error +*/ + +int print_explain_row(select_result_sink *result, + uint8 options, + uint select_number, + const char *select_type, + const char *table_name, + const char *partitions, + enum join_type jtype, + const char *possible_keys, + const char *index, + const char *key_len, + const char *ref, + ha_rows *rows, + const char *extra) +{ + Item *item_null= new Item_null(); + List<Item> item_list; + Item *item; + + item_list.push_back(new Item_int((int32) select_number)); + item_list.push_back(new Item_string_sys(select_type)); + item_list.push_back(new Item_string_sys(table_name)); + if (options & DESCRIBE_PARTITIONS) + { + if (partitions) + { + item_list.push_back(new Item_string_sys(partitions)); + } + else item_list.push_back(item_null); - /* type */ - item_list.push_back(new Item_string(join_type_str[JT_ALL], - strlen(join_type_str[JT_ALL]), - cs)); - /* possible_keys */ - item_list.push_back(item_null); - /* key*/ + } + + const char *jtype_str= join_type_str[jtype]; + item_list.push_back(new Item_string_sys(jtype_str)); + + item= possible_keys? new Item_string_sys(possible_keys) : item_null; + item_list.push_back(item); + + /* 'index */ + item= index ? new Item_string_sys(index) : item_null; + item_list.push_back(item); + + /* 'key_len */ + item= key_len ? new Item_string_sys(key_len) : item_null; + item_list.push_back(item); + + /* 'ref' */ + item= ref ? new Item_string_sys(ref) : item_null; + item_list.push_back(item); + + /* 'rows' */ + if (rows) + { + item_list.push_back(new Item_int(*rows, + MY_INT64_NUM_DECIMAL_DIGITS)); + } + else item_list.push_back(item_null); - /* key_len */ + + /* 'filtered' */ + const double filtered=100.0; + if (options & DESCRIBE_EXTENDED) + item_list.push_back(new Item_float(filtered, 2)); + + /* 'Extra' */ + if (extra) + item_list.push_back(new Item_string_sys(extra)); + else item_list.push_back(item_null); - /* ref */ + + if (result->send_data(item_list)) + return 1; + return 0; +} + + +int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, + SELECT_LEX *select_lex, uint8 explain_flags) +{ + Item *item_null= new Item_null(); + List<Item> item_list; + if (on_the_fly) + select_lex->set_explain_type(on_the_fly); + /* + here we assume that the query will return at least two rows, so we + show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong + and no filesort will be actually done, but executing all selects in + the UNION to provide precise EXPLAIN information will hardly be + appreciated :) + */ + char table_name_buffer[SAFE_NAME_LEN]; + item_list.empty(); + /* id */ + item_list.push_back(new Item_null); + /* select_type */ + item_list.push_back(new Item_string_sys(select_lex->type)); + /* table */ + { + SELECT_LEX *sl= select_lex->master_unit()->first_select(); + uint len= 6, lastop= 0; + memcpy(table_name_buffer, STRING_WITH_LEN("<union")); + for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select()) + { + len+= lastop; + lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len, + "%u,", sl->select_number); + } + if (sl || len + lastop >= NAME_LEN) + { + memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1); + len+= 4; + } + else + { + len+= lastop; + table_name_buffer[len - 1]= '>'; // change ',' to '>' + } + item_list.push_back(new Item_string_sys(table_name_buffer, len)); + } + /* partitions */ + if (explain_flags & DESCRIBE_PARTITIONS) item_list.push_back(item_null); - /* in_rows */ - if (join->thd->lex->describe & DESCRIBE_EXTENDED) - item_list.push_back(item_null); - /* rows */ + /* type */ + item_list.push_back(new Item_string_sys(join_type_str[JT_ALL])); + + /* possible_keys */ + item_list.push_back(item_null); + /* key*/ + item_list.push_back(item_null); + /* key_len */ + item_list.push_back(item_null); + /* ref */ + item_list.push_back(item_null); + /* in_rows */ + if (explain_flags & DESCRIBE_EXTENDED) item_list.push_back(item_null); - /* extra */ - if (join->unit->global_parameters->order_list.first) - item_list.push_back(new Item_string("Using filesort", - 14, cs)); - else - item_list.push_back(new Item_string("", 0, cs)); + /* rows */ + item_list.push_back(item_null); + /* extra */ + if (select_lex->master_unit()->global_parameters->order_list.first) + item_list.push_back(new Item_string_sys("Using filesort", 14)); + else + item_list.push_back(new Item_string_sys("", 0)); + + if (result->send_data(item_list)) + return 1; + return 0; +} + + +/* + Append MRR information from quick select to the given string +*/ + +void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res) +{ + char mrr_str_buf[128]; + mrr_str_buf[0]=0; + int len; + handler *h= quick->head->file; + len= h->multi_range_read_explain_info(quick->mrr_flags, mrr_str_buf, + sizeof(mrr_str_buf)); + if (len > 0) + { + //res->append(STRING_WITH_LEN("; ")); + res->append(mrr_str_buf, len); + } +} + + +/////////////////////////////////////////////////////////////////////////////// +// TODO: join with make_possible_keys_line ? +void append_possible_keys(String *str, TABLE *table, key_map possible_keys) +{ + uint j; + for (j=0 ; j < table->s->keys ; j++) + { + if (possible_keys.is_set(j)) + { + if (str->length()) + str->append(','); + str->append(table->key_info[j].name, + strlen(table->key_info[j].name), + system_charset_info); + } + } +} + + +/* + Save Query Plan Footprint - if (result->send_data(item_list)) - join->error= 1; + @note + Currently, this function may be called multiple times +*/ + +int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, + bool need_order, bool distinct, + const char *message) +{ + Explain_node *UNINIT_VAR(explain_node); + JOIN *join= this; /* Legacy: this code used to be a non-member function */ + THD *thd=join->thd; + const CHARSET_INFO *cs= system_charset_info; + int quick_type; + int error= 0; + DBUG_ENTER("JOIN::save_explain_data_intern"); + DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s", + (ulong)join->select_lex, join->select_lex->type, + message ? message : "NULL")); + DBUG_ASSERT(have_query_plan == QEP_AVAILABLE); + /* Don't log this into the slow query log */ + + if (message) + { + Explain_select *xpl_sel; + explain_node= xpl_sel= new (output->mem_root) Explain_select; + join->select_lex->set_explain_type(true); + + xpl_sel->select_id= join->select_lex->select_number; + xpl_sel->select_type= join->select_lex->type; + xpl_sel->message= message; + /* Setting xpl_sel->message means that all other members are invalid */ + output->add_node(xpl_sel); + } + else if (join->select_lex == join->unit->fake_select_lex) + { + /* Do nothing, Explain_union will create and print fake_select_lex */ } else if (!join->select_lex->master_unit()->derived || join->select_lex->master_unit()->derived->is_materialized_derived()) { + Explain_select *xpl_sel; + explain_node= xpl_sel= new (output->mem_root) Explain_select; table_map used_tables=0; - bool printing_materialize_nest= FALSE; - uint select_id= join->select_lex->select_number; + join->select_lex->set_explain_type(true); + xpl_sel->select_id= join->select_lex->select_number; + xpl_sel->select_type= join->select_lex->type; + + JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab; tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab)) { + uint select_id; if (tab->bush_root_tab) { JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start; select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier(); - printing_materialize_nest= TRUE; } - + else + select_id= join->select_lex->select_number; + TABLE *table=tab->table; TABLE_LIST *table_list= tab->table->pos_in_table_list; - char buff[512]; - char buff1[512], buff2[512], buff3[512], buff4[512]; - char keylen_str_buf[64]; + char buff4[512]; my_bool key_read; - String extra(buff, sizeof(buff),cs); char table_name_buffer[SAFE_NAME_LEN]; - String tmp1(buff1,sizeof(buff1),cs); - String tmp2(buff2,sizeof(buff2),cs); - String tmp3(buff3,sizeof(buff3),cs); String tmp4(buff4,sizeof(buff4),cs); - char hash_key_prefix[]= "#hash#"; KEY *key_info= 0; uint key_len= 0; - bool is_hj= tab->type == JT_HASH || tab->type ==JT_HASH_NEXT; - - extra.length(0); - tmp1.length(0); - tmp2.length(0); - tmp3.length(0); tmp4.length(0); quick_type= -1; + QUICK_SELECT_I *quick= NULL; + JOIN_TAB *saved_join_tab= NULL; /* Don't show eliminated tables */ if (table->map & join->eliminated_tables) @@ -22820,27 +23851,27 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, continue; } - item_list.empty(); - /* id */ - item_list.push_back(new Item_uint((uint32)select_id)); - /* select_type */ - const char* stype= printing_materialize_nest? "MATERIALIZED" : - join->select_lex->type; - item_list.push_back(new Item_string(stype, strlen(stype), cs)); - - if ((tab->type == JT_ALL || tab->type == JT_HASH) && - tab->select && tab->select->quick) + if (join->table_access_tabs == join->join_tab && + tab == (first_top_tab + join->const_tables) && pre_sort_join_tab) { - quick_type= tab->select->quick->get_type(); - if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || - (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) - tab->type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; - else - tab->type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; + saved_join_tab= tab; + tab= pre_sort_join_tab; } + Explain_table_access *eta= new (output->mem_root) Explain_table_access; + xpl_sel->add_table(eta); + eta->key.set(thd->mem_root, NULL, (uint)-1); + eta->quick_info= NULL; + + /* id */ + if (tab->bush_root_tab) + eta->sjm_nest_select_id= select_id; + else + eta->sjm_nest_select_id= 0; + + /* select_type */ + xpl_sel->select_type= join->select_lex->type; + /* table */ if (table->derived_select_number) { @@ -22848,7 +23879,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, "<derived%u>", table->derived_select_number); - item_list.push_back(new Item_string(table_name_buffer, len, cs)); + eta->table_name.copy(table_name_buffer, len, cs); } else if (tab->bush_children) { @@ -22858,60 +23889,54 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, sizeof(table_name_buffer)-1, "<subquery%d>", ctab->emb_sj_nest->sj_subq_pred->get_identifier()); - item_list.push_back(new Item_string(table_name_buffer, len, cs)); + eta->table_name.copy(table_name_buffer, len, cs); } else { TABLE_LIST *real_table= table->pos_in_table_list; - item_list.push_back(new Item_string(real_table->alias, - strlen(real_table->alias), cs)); + eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs); } + /* "partitions" column */ - if (join->thd->lex->describe & DESCRIBE_PARTITIONS) { #ifdef WITH_PARTITION_STORAGE_ENGINE partition_info *part_info; if (!table->derived_select_number && (part_info= table->part_info)) { - Item_string *item_str= new Item_string(cs); - make_used_partitions_str(part_info, &item_str->str_value); - item_list.push_back(item_str); + make_used_partitions_str(part_info, &eta->used_partitions); + eta->used_partitions_set= true; } else - item_list.push_back(item_null); + eta->used_partitions_set= false; #else /* just produce empty column if partitioning is not compiled in */ - item_list.push_back(item_null); + eta->used_partitions_set= false; #endif } + /* "type" column */ - item_list.push_back(new Item_string(join_type_str[tab->type], - strlen(join_type_str[tab->type]), - cs)); - /* Build "possible_keys" value and add it to item_list */ - if (!tab->keys.is_clear_all()) - { - uint j; - for (j=0 ; j < table->s->keys ; j++) - { - if (tab->keys.is_set(j)) - { - if (tmp1.length()) - tmp1.append(','); - tmp1.append(table->key_info[j].name, - strlen(table->key_info[j].name), - system_charset_info); - } - } + enum join_type tab_type= tab->type; + if ((tab->type == JT_ALL || tab->type == JT_HASH) && + tab->select && tab->select->quick && tab->use_quick != 2) + { + quick= tab->select->quick; + quick_type= tab->select->quick->get_type(); + if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || + (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) + tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; + else + tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; } - if (tmp1.length()) - item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs)); - else - item_list.push_back(item_null); + eta->type= tab_type; - /* Build "key", "key_len", and "ref" values and add them to item_list */ - if (tab->type == JT_NEXT) + /* Build "possible_keys" value */ + append_possible_keys(&eta->possible_keys_str, table, tab->keys); + + /* Build "key", "key_len", and "ref" */ + if (tab_type == JT_NEXT) { key_info= table->key_info+tab->index; key_len= key_info->key_length; @@ -22921,56 +23946,55 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, key_info= tab->get_keyinfo_by_key_no(tab->ref.key); key_len= tab->ref.key_length; } - if (key_info) + + /* + In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type + that still have quick selects. + */ + if (tab->select && tab->select->quick && tab_type != JT_CONST) { - register uint length; - if (is_hj) - tmp2.append(hash_key_prefix, strlen(hash_key_prefix), cs); - tmp2.append(key_info->name, strlen(key_info->name), cs); - length= (longlong10_to_str(key_len, keylen_str_buf, 10) - - keylen_str_buf); - tmp3.append(keylen_str_buf, length, cs); - if (tab->ref.key_parts) + eta->quick_info= tab->select->quick->get_explain(thd->mem_root); + } + + if (key_info) /* 'index' or 'ref' access */ + { + eta->key.set(thd->mem_root, key_info->name, key_len); + + if (tab->ref.key_parts && tab_type != JT_FT) { - for (store_key **ref=tab->ref.key_copy ; *ref ; ref++) + store_key **ref=tab->ref.key_copy; + for (uint kp= 0; kp < tab->ref.key_parts; kp++) { if (tmp4.length()) tmp4.append(','); - tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); + + if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map) + tmp4.append("const"); + else + { + tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); + ref++; + } } } } - if (is_hj && tab->type != JT_HASH) + + if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */ { - tmp2.append(':'); - tmp3.append(':'); + eta->hash_next_key.set(thd->mem_root, + table->key_info[tab->index].name, + table->key_info[tab->index].key_length); } - if (tab->type == JT_HASH_NEXT) + + if (key_info) { - register uint length; - key_info= table->key_info+tab->index; - key_len= key_info->key_length; - tmp2.append(key_info->name, strlen(key_info->name), cs); - length= (longlong10_to_str(key_len, keylen_str_buf, 10) - - keylen_str_buf); - tmp3.append(keylen_str_buf, length, cs); - } - if (tab->type != JT_CONST && tab->select && tab->select->quick) - tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3); - if (key_info || (tab->select && tab->select->quick)) - { - if (tmp2.length()) - item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); - else - item_list.push_back(item_null); - if (tmp3.length()) - item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs)); - else - item_list.push_back(item_null); - if (key_info && tab->type != JT_NEXT) - item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs)); + if (key_info && tab_type != JT_NEXT) + { + eta->ref.copy(tmp4); + eta->ref_set= true; + } else - item_list.push_back(item_null); + eta->ref_set= false; } else { @@ -22980,140 +24004,128 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, { const char *tmp_buff; int f_idx; + StringBuffer<64> key_name_buf; if (table_list->has_db_lookup_value) { + /* The "key" has the name of the column referring to the database */ f_idx= table_list->schema_table->idx_field1; tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - tmp2.append(tmp_buff, strlen(tmp_buff), cs); + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); } if (table_list->has_table_lookup_value) { if (table_list->has_db_lookup_value) - tmp2.append(','); + key_name_buf.append(','); + f_idx= table_list->schema_table->idx_field2; tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - tmp2.append(tmp_buff, strlen(tmp_buff), cs); + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); } - if (tmp2.length()) - item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); - else - item_list.push_back(item_null); + + if (key_name_buf.length()) + eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1); } - else - item_list.push_back(item_null); - item_list.push_back(item_null); - item_list.push_back(item_null); + eta->ref_set= false; } - /* Add "rows" field to item_list. */ + /* "rows" */ if (table_list /* SJM bushes don't have table_list */ && table_list->schema_table) { - /* in_rows */ - if (join->thd->lex->describe & DESCRIBE_EXTENDED) - item_list.push_back(item_null); - /* rows */ - item_list.push_back(item_null); + /* I_S tables have rows=extra=NULL */ + eta->rows_set= false; + eta->filtered_set= false; } else { - ha_rows examined_rows= tab->get_examined_rows(); - item_list.push_back(new Item_int((ulonglong) examined_rows, - MY_INT64_NUM_DECIMAL_DIGITS)); + double examined_rows= eta->rows= tab->get_examined_rows(); + eta->rows_set= true; - /* Add "filtered" field to item_list. */ - if (join->thd->lex->describe & DESCRIBE_EXTENDED) + /* "filtered" */ + float f= 0.0; + if (examined_rows) { - float f= 0.0; - if (examined_rows) + double pushdown_cond_selectivity= tab->cond_selectivity; + if (pushdown_cond_selectivity == 1.0) f= (float) (100.0 * tab->records_read / examined_rows); - set_if_smaller(f, 100.0); - item_list.push_back(new Item_float(f, 2)); + else + f= (float) (100.0 * pushdown_cond_selectivity); } + set_if_smaller(f, 100.0); + eta->filtered_set= true; + eta->filtered= f; } - /* Build "Extra" field and add it to item_list. */ + /* Build "Extra" field and save it */ key_read=table->key_read; - if ((tab->type == JT_NEXT || tab->type == JT_CONST) && + if ((tab_type == JT_NEXT || tab_type == JT_CONST) && table->covering_keys.is_set(tab->index)) key_read=1; if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && - !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row) + !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row) key_read=1; if (tab->info) - item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs)); + { + eta->push_extra(tab->info); + } else if (tab->packed_info & TAB_INFO_HAVE_VALUE) { if (tab->packed_info & TAB_INFO_USING_INDEX) - extra.append(STRING_WITH_LEN("; Using index")); + eta->push_extra(ET_USING_INDEX); if (tab->packed_info & TAB_INFO_USING_WHERE) - extra.append(STRING_WITH_LEN("; Using where")); + eta->push_extra(ET_USING_WHERE); if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) - extra.append(STRING_WITH_LEN("; Full scan on NULL key")); - /* Skip initial "; "*/ - const char *str= extra.ptr(); - uint32 len= extra.length(); - if (len) - { - str += 2; - len -= 2; - } - item_list.push_back(new Item_string(str, len, cs)); + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); } else { uint keyno= MAX_KEY; if (tab->ref.key_parts) keyno= tab->ref.key; - else if (tab->select && tab->select->quick) - keyno = tab->select->quick->index; + else if (tab->select && quick) + keyno = quick->index; if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && table->file->pushed_idx_cond) - extra.append(STRING_WITH_LEN("; Using index condition")); + eta->push_extra(ET_USING_INDEX_CONDITION); else if (tab->cache_idx_cond) - extra.append(STRING_WITH_LEN("; Using index condition(BKA)")); + eta->push_extra(ET_USING_INDEX_CONDITION_BKA); if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) { - extra.append(STRING_WITH_LEN("; Using ")); - tab->select->quick->add_info_string(&extra); + eta->push_extra(ET_USING); } if (tab->select) { if (tab->use_quick == 2) { - /* 4 bits per 1 hex digit + terminating '\0' */ - char buf[MAX_KEY / 4 + 1]; - extra.append(STRING_WITH_LEN("; Range checked for each " - "record (index map: 0x")); - extra.append(tab->keys.print(buf)); - extra.append(')'); + eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); + eta->range_checked_map= tab->keys; } - else if (tab->select->cond) + else if (tab->select->cond || + (tab->cache_select && tab->cache_select->cond)) { const COND *pushed_cond= tab->table->file->pushed_cond; - if (((thd->variables.optimizer_switch & - OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) || - (tab->table->file->ha_table_flags() & - HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) && - pushed_cond) + if (thd->use_cond_push(tab->table->file) && pushed_cond) { - extra.append(STRING_WITH_LEN("; Using where with pushed " - "condition")); - if (thd->lex->describe & DESCRIBE_EXTENDED) + eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION); + /* + psergey-todo: what to do? This was useful with NDB only. + + if (explain_flags & DESCRIBE_EXTENDED) { extra.append(STRING_WITH_LEN(": ")); ((COND *)pushed_cond)->print(&extra, QT_ORDINARY); } + */ } else - extra.append(STRING_WITH_LEN("; Using where")); + eta->push_extra(ET_USING_WHERE); } } if (table_list /* SJM bushes don't have table_list */ && @@ -23121,19 +24133,20 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) { if (!table_list->table_open_method) - extra.append(STRING_WITH_LEN("; Skip_open_table")); + eta->push_extra(ET_SKIP_OPEN_TABLE); else if (table_list->table_open_method == OPEN_FRM_ONLY) - extra.append(STRING_WITH_LEN("; Open_frm_only")); + eta->push_extra(ET_OPEN_FRM_ONLY); else - extra.append(STRING_WITH_LEN("; Open_full_table")); + eta->push_extra(ET_OPEN_FULL_TABLE); + /* psergey-note: the following has a bug.*/ if (table_list->has_db_lookup_value && table_list->has_table_lookup_value) - extra.append(STRING_WITH_LEN("; Scanned 0 databases")); + eta->push_extra(ET_SCANNED_0_DATABASES); else if (table_list->has_db_lookup_value || table_list->has_table_lookup_value) - extra.append(STRING_WITH_LEN("; Scanned 1 database")); + eta->push_extra(ET_SCANNED_1_DATABASE); else - extra.append(STRING_WITH_LEN("; Scanned all databases")); + eta->push_extra(ET_SCANNED_ALL_DATABASES); } if (key_read) { @@ -23141,69 +24154,52 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, { QUICK_GROUP_MIN_MAX_SELECT *qgs= (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick; - extra.append(STRING_WITH_LEN("; Using index for group-by")); - qgs->append_loose_scan_type(&extra); + eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY); + eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning(); } else - extra.append(STRING_WITH_LEN("; Using index")); + eta->push_extra(ET_USING_INDEX); } if (table->reginfo.not_exists_optimize) - extra.append(STRING_WITH_LEN("; Not exists")); + eta->push_extra(ET_NOT_EXISTS); - /* - if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE && - !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags & - HA_MRR_USE_DEFAULT_IMPL)) - { - extra.append(STRING_WITH_LEN("; Using MRR")); - } - */ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) { - char mrr_str_buf[128]; - mrr_str_buf[0]=0; - int len; - uint mrr_flags= - ((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags; - len= table->file->multi_range_read_explain_info(mrr_flags, - mrr_str_buf, - sizeof(mrr_str_buf)); - if (len > 0) - { - extra.append(STRING_WITH_LEN("; ")); - extra.append(mrr_str_buf, len); - } + explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick), + &eta->mrr_type); + if (eta->mrr_type.length() > 0) + eta->push_extra(ET_USING_MRR); } if (need_tmp_table) { need_tmp_table=0; - extra.append(STRING_WITH_LEN("; Using temporary")); + xpl_sel->using_temporary= true; } if (need_order) { need_order=0; - extra.append(STRING_WITH_LEN("; Using filesort")); + xpl_sel->using_filesort= true; } if (distinct & test_all_bits(used_tables, join->select_list_used_tables)) - extra.append(STRING_WITH_LEN("; Distinct")); + eta->push_extra(ET_DISTINCT); if (tab->loosescan_match_tab) { - extra.append(STRING_WITH_LEN("; LooseScan")); + eta->push_extra(ET_LOOSESCAN); } if (tab->first_weedout_table) - extra.append(STRING_WITH_LEN("; Start temporary")); + eta->push_extra(ET_START_TEMPORARY); if (tab->check_weed_out_table) - extra.append(STRING_WITH_LEN("; End temporary")); + eta->push_extra(ET_END_TEMPORARY); else if (tab->do_firstmatch) { - if (tab->do_firstmatch == join->join_tab - 1) - extra.append(STRING_WITH_LEN("; FirstMatch")); + if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1) + eta->push_extra(ET_FIRST_MATCH); else { - extra.append(STRING_WITH_LEN("; FirstMatch(")); + eta->push_extra(ET_FIRST_MATCH); TABLE *prev_table=tab->do_firstmatch->table; if (prev_table->derived_select_number) { @@ -23212,11 +24208,10 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, int len= my_snprintf(namebuf, sizeof(namebuf)-1, "<derived%u>", prev_table->derived_select_number); - extra.append(namebuf, len); + eta->firstmatch_table_name.append(namebuf, len); } else - extra.append(prev_table->pos_in_table_list->alias); - extra.append(STRING_WITH_LEN(")")); + eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias); } } @@ -23224,34 +24219,84 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, { if (tab->ref.cond_guards[part]) { - extra.append(STRING_WITH_LEN("; Full scan on NULL key")); + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); break; } } if (tab->cache) { - extra.append(STRING_WITH_LEN("; Using join buffer")); - tab->cache->print_explain_comment(&extra); - } - - /* Skip initial "; "*/ - const char *str= extra.ptr(); - uint32 len= extra.length(); - if (len) - { - str += 2; - len -= 2; + eta->push_extra(ET_USING_JOIN_BUFFER); + tab->cache->save_explain_data(&eta->bka_type); } - item_list.push_back(new Item_string(str, len, cs)); } + if (saved_join_tab) + tab= saved_join_tab; + // For next iteration used_tables|=table->map; - if (result->send_data(item_list)) - join->error= 1; } + output->add_node(xpl_sel); } + + for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit(); + unit; + unit= unit->next_unit()) + { + /* + Display subqueries only if + (1) they are not parts of ON clauses that were eliminated by table + elimination. + (2) they are not merged derived tables + */ + if (!(unit->item && unit->item->eliminated) && // (1) + (!unit->derived || unit->derived->is_materialized_derived())) // (2) + { + explain_node->add_child(unit->first_select()->select_number); + } + } + + if (!error && select_lex->is_top_level_node()) + output->query_plan_ready(); + + + DBUG_RETURN(error); +} + + +/* + This function serves as "shortcut point" for EXPLAIN queries. + + The EXPLAIN statement executes just like its SELECT counterpart would + execute, except that JOIN::exec() will call select_describe() instead of + actually executing the query. + + Inside select_describe(): + - Query plan is updated with latest QEP choices made at the start of + JOIN::exec(). + - the proces of "almost execution" is invoked for the children subqueries. + + Overall, select_describe() is a legacy of old EXPLAIN implementation and + should be removed. +*/ + +static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, + bool distinct,const char *message) +{ + THD *thd=join->thd; + select_result *result=join->result; + DBUG_ENTER("select_describe"); + + /* Update the QPF with latest values of using_temporary, using_filesort */ + Explain_select *explain_sel; + uint select_nr= join->select_lex->select_number; + if ((explain_sel= thd->lex->explain->get_select(select_nr))) + { + explain_sel->using_temporary= need_tmp_table; + explain_sel->using_filesort= need_order; + } + for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit(); unit; unit= unit->next_unit()) @@ -23294,13 +24339,13 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) for (SELECT_LEX *sl= first; sl; sl= sl->next_select()) { - sl->set_explain_type(); + sl->set_explain_type(FALSE); sl->options|= SELECT_DESCRIBE; } if (unit->is_union()) { - unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization + unit->fake_select_lex->select_number= FAKE_SELECT_LEX_ID; // jost for initialization unit->fake_select_lex->type= "UNION RESULT"; unit->fake_select_lex->options|= SELECT_DESCRIBE; if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE))) @@ -23409,6 +24454,7 @@ static void print_join(THD *thd, /* List is reversed => we should reverse it before using */ List_iterator_fast<TABLE_LIST> ti(*tables); TABLE_LIST **table; + DBUG_ENTER("print_join"); /* If the QT_NO_DATA_EXPANSION flag is specified, we print the @@ -23441,13 +24487,13 @@ static void print_join(THD *thd, if (tables_to_print == 0) { str->append(STRING_WITH_LEN("dual")); - return; // all tables were optimized away + DBUG_VOID_RETURN; // all tables were optimized away } ti.rewind(); if (!(table= static_cast<TABLE_LIST **>(thd->alloc(sizeof(TABLE_LIST*) * tables_to_print)))) - return; // out of memory + DBUG_VOID_RETURN; // out of memory TABLE_LIST *tmp, **t= table + (tables_to_print - 1); while ((tmp= ti++)) @@ -23488,6 +24534,7 @@ static void print_join(THD *thd, } print_table_array(thd, eliminated_tables, str, table, table + tables_to_print, query_type); + DBUG_VOID_RETURN; } /** @@ -23613,6 +24660,22 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, append_identifier(thd, str, table_name, table_name_length); cmp_name= table_name; } +#ifdef WITH_PARTITION_STORAGE_ENGINE + if (partition_names && partition_names->elements) + { + int i, num_parts= partition_names->elements; + List_iterator<String> name_it(*(partition_names)); + str->append(STRING_WITH_LEN(" PARTITION (")); + for (i= 1; i <= num_parts; i++) + { + String *name= name_it++; + append_identifier(thd, str, name->c_ptr(), name->length()); + if (i != num_parts) + str->append(','); + } + str->append(')'); + } +#endif /* WITH_PARTITION_STORAGE_ENGINE */ } if (my_strcasecmp(table_alias_charset, cmp_name, alias)) { @@ -23620,7 +24683,7 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, const char *t_alias= alias; str->append(' '); - if (lower_case_table_names== 1) + if (lower_case_table_names == 1) { if (alias && alias[0]) { @@ -23790,6 +24853,12 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) // limit print_limit(thd, str, query_type); + // lock type + if (lock_type == TL_READ_WITH_SHARED_LOCKS) + str->append(" lock in share mode"); + else if (lock_type == TL_WRITE) + str->append(" for update"); + // PROCEDURE unsupported here } @@ -23877,7 +24946,7 @@ void JOIN::save_query_plan(Join_plan_state *save_to) } memcpy((uchar*) save_to->best_positions, (uchar*) best_positions, sizeof(POSITION) * (table_count + 1)); - memset(best_positions, 0, sizeof(POSITION) * (table_count + 1)); + memset((uchar*) best_positions, 0, sizeof(POSITION) * (table_count + 1)); /* Save SJM nests */ List_iterator<TABLE_LIST> it(select_lex->sj_nests); @@ -23959,7 +25028,7 @@ void JOIN::restore_query_plan(Join_plan_state *restore_from) @retval REOPT_NEW_PLAN there is a new plan. @retval REOPT_OLD_PLAN no new improved plan was produced, use the old one. - @retval REOPT_ERROR an irrecovarable error occured during reoptimization. + @retval REOPT_ERROR an irrecovarable error occurred during reoptimization. */ JOIN::enum_reopt_result @@ -23990,7 +25059,8 @@ JOIN::reoptimize(Item *added_where, table_map join_tables, reset_query_plan(); if (!keyuse.buffer && - my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64)) + my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64, + MYF(MY_THREAD_SPECIFIC))) { delete_dynamic(&added_keyuse); return REOPT_ERROR; @@ -24123,9 +25193,9 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, int best_key= -1; bool is_best_covering= FALSE; double fanout= 1; - ha_rows table_records= table->file->stats.records; + ha_rows table_records= table->stat_records(); bool group= join && join->group && order == join->group_list; - ha_rows ref_key_quick_rows= HA_POS_ERROR; + ha_rows refkey_rows_estimate= table->quick_condition_rows; const bool has_limit= (select_limit_arg != HA_POS_ERROR); /* @@ -24151,10 +25221,6 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, else keys= usable_keys; - if (ref_key >= 0 && ref_key != MAX_KEY && - table->covering_keys.is_set(ref_key)) - ref_key_quick_rows= table->quick_rows[ref_key]; - if (join) { uint tablenr= tab - join->join_tab; @@ -24165,6 +25231,22 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, else read_time= table->file->scan_time(); + /* + Calculate the selectivity of the ref_key for REF_ACCESS. For + RANGE_ACCESS we use table->quick_condition_rows. + */ + if (ref_key >= 0 && tab->type == JT_REF) + { + if (table->quick_keys.is_set(ref_key)) + refkey_rows_estimate= table->quick_rows[ref_key]; + else + { + const KEY *ref_keyinfo= table->key_info + ref_key; + refkey_rows_estimate= ref_keyinfo->rec_per_key[tab->ref.key_parts - 1]; + } + set_if_bigger(refkey_rows_estimate, 1); + } + for (nr=0; nr < table->s->keys ; nr++) { int direction; @@ -24205,17 +25287,17 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, if (group) { /* - Used_key_parts can be larger than keyinfo->key_parts + Used_key_parts can be larger than keyinfo->user_defined_key_parts when using a secondary index clustered with a primary key (e.g. as in Innodb). See Bug #28591 for details. */ - uint used_index_parts= keyinfo->key_parts; + uint used_index_parts= keyinfo->user_defined_key_parts; uint used_pk_parts= 0; if (used_key_parts > used_index_parts) used_pk_parts= used_key_parts-used_index_parts; rec_per_key= used_key_parts ? - keyinfo->rec_per_key[used_key_parts-1] : 1; + keyinfo->actual_rec_per_key(used_key_parts-1) : 1; /* Take into account the selectivity of the used pk prefix */ if (used_pk_parts) { @@ -24225,19 +25307,19 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, of the primary key are considered unknown we assume they are equal to 1. */ - if (used_key_parts == pkinfo->key_parts || + if (used_key_parts == pkinfo->user_defined_key_parts || pkinfo->rec_per_key[0] == 0) rec_per_key= 1; if (rec_per_key > 1) { - rec_per_key*= pkinfo->rec_per_key[used_pk_parts-1]; - rec_per_key/= pkinfo->rec_per_key[0]; + rec_per_key*= pkinfo->actual_rec_per_key(used_pk_parts-1); + rec_per_key/= pkinfo->actual_rec_per_key(0); /* The value of rec_per_key for the extended key has to be adjusted accordingly if some components of the secondary key are included in the primary key. */ - for(uint i= 0; i < used_pk_parts; i++) + for(uint i= 1; i < used_pk_parts; i++) { if (pkinfo->key_part[i].field->key_start.is_set(nr)) { @@ -24245,10 +25327,9 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, We presume here that for any index rec_per_key[i] != 0 if rec_per_key[0] != 0. */ - DBUG_ASSERT(pkinfo->rec_per_key[i]); - DBUG_ASSERT(i > 0); - rec_per_key*= pkinfo->rec_per_key[i-1]; - rec_per_key/= pkinfo->rec_per_key[i]; + DBUG_ASSERT(pkinfo->actual_rec_per_key(i)); + rec_per_key*= pkinfo->actual_rec_per_key(i-1); + rec_per_key/= pkinfo->actual_rec_per_key(i); } } } @@ -24282,18 +25363,18 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, with ref_key. Thus, to select first N records we have to scan N/selectivity(ref_key) index entries. selectivity(ref_key) = #scanned_records/#table_records = - table->quick_condition_rows/table_records. + refkey_rows_estimate/table_records. In any case we can't select more than #table_records. - N/(table->quick_condition_rows/table_records) > table_records - <=> N > table->quick_condition_rows. - */ - if (select_limit > table->quick_condition_rows) + N/(refkey_rows_estimate/table_records) > table_records + <=> N > refkey_rows_estimate. + */ + if (select_limit > refkey_rows_estimate) select_limit= table_records; else select_limit= (ha_rows) (select_limit * (double) table_records / - table->quick_condition_rows); - rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1]; + refkey_rows_estimate); + rec_per_key= keyinfo->actual_rec_per_key(keyinfo->user_defined_key_parts-1); set_if_bigger(rec_per_key, 1); /* Here we take into account the fact that rows are @@ -24307,24 +25388,28 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, index entry. */ index_scan_time= select_limit/rec_per_key * - min(rec_per_key, table->file->scan_time()); + MY_MIN(rec_per_key, table->file->scan_time()); if ((ref_key < 0 && (group || table->force_index || is_covering)) || index_scan_time < read_time) { ha_rows quick_records= table_records; + ha_rows refkey_select_limit= (ref_key >= 0 && + table->covering_keys.is_set(ref_key)) ? + refkey_rows_estimate : + HA_POS_ERROR; if ((is_best_covering && !is_covering) || - (is_covering && ref_key_quick_rows < select_limit)) + (is_covering && refkey_select_limit < select_limit)) continue; if (table->quick_keys.is_set(nr)) quick_records= table->quick_rows[nr]; if (best_key < 0 || - (select_limit <= min(quick_records,best_records) ? - keyinfo->key_parts < best_key_parts : + (select_limit <= MY_MIN(quick_records,best_records) ? + keyinfo->user_defined_key_parts < best_key_parts : quick_records < best_records) || (!is_best_covering && is_covering)) { best_key= nr; - best_key_parts= keyinfo->key_parts; + best_key_parts= keyinfo->user_defined_key_parts; if (saved_best_key_parts) *saved_best_key_parts= used_key_parts; best_records= quick_records; @@ -24357,6 +25442,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, @param table Table to find a key @param select Pointer to access/update select->quick (if any) @param limit LIMIT clause parameter + @param [out] scanned_limit How many records we expect to scan + Valid if *need_sort=FALSE. @param [out] need_sort TRUE if filesort needed @param [out] reverse TRUE if the key is reversed again given ORDER (undefined if key == MAX_KEY) @@ -24374,7 +25461,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, - ha_rows limit, bool *need_sort, bool *reverse) + ha_rows limit, ha_rows *scanned_limit, + bool *need_sort, bool *reverse) { if (!order) { @@ -24403,7 +25491,8 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, switch (test_if_order_by_key(order, table, select->quick->index, &used_key_parts)) { case 1: // desired order - *need_sort= FALSE; + *need_sort= FALSE; + *scanned_limit= MY_MIN(limit, select->quick->records); return select->quick->index; case 0: // unacceptable order *need_sort= TRUE; @@ -24416,6 +25505,7 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, { select->set_quick(reverse_quick); *need_sort= FALSE; + *scanned_limit= MY_MIN(limit, select->quick->records); return select->quick->index; } else @@ -24434,7 +25524,7 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, Update quick_condition_rows since single table UPDATE/DELETE procedures don't call make_join_statistics() and leave this variable uninitialized. */ - table->quick_condition_rows= table->file->stats.records; + table->quick_condition_rows= table->stat_records(); int key, direction; if (test_if_cheaper_ordering(NULL, order, table, @@ -24444,6 +25534,7 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, !is_key_used(table, key, table->write_set)) { *need_sort= FALSE; + *scanned_limit= limit; *reverse= (direction < 0); return key; } @@ -24452,6 +25543,78 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, return MAX_KEY; } +/* + Count how much times conditions are true for several first rows of the table + + @param thd thread handle + @param rows_to_read how much rows to check + @param table table which should be checked + @conds conds list of conditions and countars for them + + @return number of really checked rows or 0 in case of error or empty table +*/ + +ulong check_selectivity(THD *thd, + ulong rows_to_read, + TABLE *table, + List<COND_STATISTIC> *conds) +{ + ulong count= 0; + COND_STATISTIC *cond; + List_iterator_fast<COND_STATISTIC> it(*conds); + handler *file= table->file; + uchar *record= table->record[0]; + int error= 0; + DBUG_ENTER("check_selectivity"); + + DBUG_ASSERT(rows_to_read > 0); + while ((cond= it++)) + { + DBUG_ASSERT(cond->cond); + DBUG_ASSERT(cond->cond->used_tables() == table->map); + cond->positive= 0; + } + it.rewind(); + + if (file->ha_rnd_init_with_error(1)) + DBUG_RETURN(0); + do + { + error= file->ha_rnd_next(record); + + if (thd->killed) + { + thd->send_kill_message(); + count= 0; + goto err; + } + if (error) + { + if (error == HA_ERR_RECORD_DELETED) + continue; + if (error == HA_ERR_END_OF_FILE) + break; + goto err; + } + + count++; + while ((cond= it++)) + { + if (cond->cond->val_bool()) + cond->positive++; + } + it.rewind(); + + } while (count < rows_to_read); + + file->ha_rnd_end(); + DBUG_RETURN(count); + +err: + DBUG_PRINT("error", ("error %d", error)); + file->ha_rnd_end(); + DBUG_RETURN(0); +} /** @} (end of group Query_Optimizer) |