diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 1845 |
1 files changed, 1197 insertions, 648 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8644acefad8..f88a14c3312 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -29,11 +29,30 @@ #pragma implementation // gcc: Class implementation #endif -#include "mysql_priv.h" +#include "sql_priv.h" +#include "unireg.h" #include "sql_select.h" -#include "sql_cursor.h" +#include "sql_cache.h" // query_cache_* +#include "sql_table.h" // primary_key_name +#include "probes_mysql.h" +#include "key.h" // key_copy, key_cmp, key_cmp_if_same +#include "lock.h" // mysql_unlock_some_tables, + // mysql_unlock_read_tables +#include "sql_show.h" // append_identifier +#include "sql_base.h" // setup_wild, setup_fields, fill_record +#include "sql_parse.h" // check_stack_overrun +#include "sql_partition.h" // make_used_partitions_str +#include "sql_acl.h" // *_ACL +#include "sql_test.h" // print_where, print_keyuse_array, + // print_sjm, print_plan, TEST_join +#include "records.h" // init_read_record, end_read_record +#include "filesort.h" // filesort_free_buffers +#include "sql_union.h" // mysql_union #include "opt_subselect.h" +#include "log_slow.h" +#include "sql_derived.h" +#include "debug_sync.h" // DEBUG_SYNC #include <m_ctype.h> #include <my_bit.h> #include <hash.h> @@ -76,9 +95,11 @@ static bool best_extension_by_limited_search(JOIN *join, double read_time, uint depth, uint prune_level); static uint determine_search_depth(JOIN* join); +C_MODE_START static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2); static int join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2); static int join_tab_cmp_embedded_first(const void *emb, const void* ptr1, const void *ptr2); +C_MODE_END /* TODO: 'find_best' is here only temporarily until 'greedy_search' is tested and approved. @@ -126,32 +147,26 @@ 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, - Item::cond_result *cond_value, + Item::cond_result *cond_value, COND_EQUAL **cond_equal); -static bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); +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 *); static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table, Procedure *proc); -static enum_nested_loop_state -evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, - int error); +static enum_nested_loop_state evaluate_join_record(JOIN *, JOIN_TAB *, int); static enum_nested_loop_state evaluate_null_complemented_join_record(JOIN *join, JOIN_TAB *join_tab); static enum_nested_loop_state end_send(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); -enum_nested_loop_state -end_send_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); static enum_nested_loop_state end_write(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); static enum_nested_loop_state end_update(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); static enum_nested_loop_state end_unique_update(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); -enum_nested_loop_state -end_write_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records); static int test_if_group_changed(List<Cached_item> &list); static int join_read_const_table(JOIN_TAB *tab, POSITION *pos); @@ -191,6 +206,14 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); +static bool test_if_cheaper_ordering(const JOIN_TAB *tab, + ORDER *order, TABLE *table, + key_map usable_keys, int key, + ha_rows select_limit, + int *new_key, int *new_key_direction, + ha_rows *new_select_limit, + uint *new_used_key_parts= NULL, + uint *saved_best_key_parts= NULL); static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order, ha_rows select_limit, bool no_changes, const key_map *map); @@ -235,6 +258,7 @@ static void update_tmptable_sum_func(Item_sum **func,TABLE *tmp_table); static void copy_sum_funcs(Item_sum **func_ptr, Item_sum **end); static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab); static bool setup_sum_funcs(THD *thd, Item_sum **func_ptr); +static bool prepare_sum_aggregators(Item_sum **func_ptr, bool need_distinct); static bool init_sum_functions(Item_sum **func, Item_sum **end); static bool update_sum_func(Item_sum **func); static void select_describe(JOIN *join, bool need_tmp_table,bool need_order, @@ -258,6 +282,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, bool res; register SELECT_LEX *select_lex = &lex->select_lex; DBUG_ENTER("handle_select"); + MYSQL_SELECT_START(thd->query()); if (select_lex->master_unit()->is_union() || select_lex->master_unit()->fake_select_lex) @@ -281,7 +306,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, select_lex->group_list.first, select_lex->having, lex->proc_list.first, - select_lex->options | thd->options | + select_lex->options | thd->variables.option_bits | setup_tables_done_option, result, unit, select_lex); } @@ -289,8 +314,24 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, thd->is_error())); res|= thd->is_error(); if (unlikely(res)) - result->abort(); + result->abort_result_set(); + if (thd->killed == ABORT_QUERY) + { + /* + 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, + ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT, + ER(ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT), + thd->accessed_rows_and_keys, + thd->lex->limit_rows_examined->val_uint()); + thd->killed= NOT_KILLED; + } + /* Disable LIMIT ROWS EXAMINED after query execution. */ + thd->lex->limit_rows_examined_cnt= ULONGLONG_MAX; + MYSQL_SELECT_DONE((int) res, (ulong) thd->limit_found_rows); DBUG_RETURN(res); } @@ -327,7 +368,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, function is, in turn, aggregated in the query block where the outer field was resolved or some query nested therein, then the Item_direct_ref class should be used. Also it should be used if we are - grouping by a subquery containing the outer field. + grouping by a subquery that references this outer field. The resolution is done here and not at the fix_fields() stage as it can be done only after aggregate functions are fixed and pulled up to @@ -603,7 +644,7 @@ JOIN::prepare(Item ***rref_pointer_array, */ if (select_lex->master_unit()->item && // 1) select_lex->first_cond_optimization && // 2) - !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)) // 3) + !thd->lex->is_view_context_analysis()) // 3) { remove_redundant_subquery_clauses(select_lex); } @@ -904,16 +945,19 @@ JOIN::optimize() { ulonglong select_opts_for_readinfo; uint no_jbuf_after; - DBUG_ENTER("JOIN::optimize"); + do_send_rows = (unit->select_limit_cnt) ? 1 : 0; // to prevent double initialization on EXPLAIN if (optimized) DBUG_RETURN(0); optimized= 1; + DEBUG_SYNC(thd, "before_join_optimize"); + thd_proc_info(thd, "optimizing"); set_allowed_join_cache_types(); + need_distinct= TRUE; /* Run optimize phase for all derived tables/views used in this SELECT. */ if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) @@ -1189,7 +1233,7 @@ JOIN::optimize() error= 0; goto setup_subq_exit; } - if (!(thd->options & OPTION_BIG_SELECTS) && + if (!(thd->variables.option_bits & OPTION_BIG_SELECTS) && best_read > (double) thd->variables.max_join_size && !(select_options & SELECT_DESCRIBE)) { /* purecov: inspected */ @@ -1197,7 +1241,7 @@ JOIN::optimize() error= -1; DBUG_RETURN(1); } - if (const_tables && !thd->locked_tables && + if (const_tables && !thd->locked_tables_mode && !(select_options & SELECT_NO_UNLOCK)) mysql_unlock_some_tables(thd, table, const_tables); if (!conds && outer_join) @@ -1315,6 +1359,9 @@ JOIN::optimize() conds=new Item_int((longlong) 0,1); // Always false } + /* Cache constant expressions in WHERE, HAVING, ON clauses. */ + cache_const_exprs(); + if (make_join_select(this, select, conds)) { zero_result_cause= @@ -1507,7 +1554,11 @@ JOIN::optimize() if (test_if_subpart(group_list, order) || (!group_list && tmp_table_param.sum_func_count)) + { order=0; + if (is_indexed_agg_distinct(this, NULL)) + sort_and_group= 0; + } // Can't use sort on head table if using join buffering if (full_join || hash_join) @@ -1539,11 +1590,11 @@ JOIN::optimize() */ no_jbuf_after= 1 ? table_count : make_join_orderinfo(this); + // Don't use join buffering when we use MATCH select_opts_for_readinfo= (select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) | (select_lex->ftfunc_list->elements ? SELECT_NO_JOIN_CACHE : 0); - // No cache for MATCH == 'Don't use join buffering when we use MATCH'. if (make_join_readinfo(this, select_opts_for_readinfo, no_jbuf_after)) DBUG_RETURN(1); @@ -1656,7 +1707,14 @@ JOIN::optimize() join_tab element of the plan for its access method. */ if (join_tab->is_using_loose_index_scan()) + { tmp_table_param.precomputed_group_by= TRUE; + if (join_tab->is_using_agg_loose_index_scan()) + { + need_distinct= FALSE; + tmp_table_param.precomputed_group_by= FALSE; + } + } error= 0; @@ -1693,6 +1751,19 @@ int JOIN::init_execution() DBUG_ASSERT(!(select_options & SELECT_DESCRIBE)); initialized= true; + /* + Enable LIMIT ROWS EXAMINED during query execution if: + (1) This JOIN is the outermost query (not a subquery or derived table) + This ensures that the limit is enabled when actual execution begins, and + not if a subquery is evaluated during optimization of the outer query. + (2) This JOIN is not the result of a UNION. In this case do not apply the + limit in order to produce the partial query result stored in the + UNION temp table. + */ + if (!select_lex->outer_select() && // (1) + select_lex != select_lex->master_unit()->fake_select_lex) // (2) + thd->lex->set_limit_rows_examined(); + /* Create a tmp table if distinct or if the sort is too complicated */ if (need_tmp) { @@ -1719,15 +1790,10 @@ int JOIN::init_execution() if (!(exec_tmp_table1= create_tmp_table(thd, &tmp_table_param, all_fields, - tmp_group, - group_list ? 0 : select_distinct, + tmp_group, group_list ? 0 : select_distinct, group_list && simple_group, - select_options, - tmp_rows_limit, - (char *) ""))) - { + select_options, tmp_rows_limit, ""))) DBUG_RETURN(1); - } /* We don't have to store rows in temp table that doesn't match HAVING if: @@ -1752,6 +1818,7 @@ int JOIN::init_execution() HA_POS_ERROR, HA_POS_ERROR, FALSE) || alloc_group_fields(this, group_list) || make_sum_func_list(all_fields, fields_list, 1) || + prepare_sum_aggregators(sum_funcs, need_distinct) || setup_sum_funcs(thd, sum_funcs)) { DBUG_RETURN(1); @@ -1761,6 +1828,7 @@ int JOIN::init_execution() else { if (make_sum_func_list(all_fields, fields_list, 0) || + prepare_sum_aggregators(sum_funcs, need_distinct) || setup_sum_funcs(thd, sum_funcs)) { DBUG_RETURN(1); @@ -2114,8 +2182,8 @@ JOIN::exec() (zero_result_cause?zero_result_cause:"No tables used")); else { - if (result->send_fields(*columns_list, - Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) + if (result->send_result_set_metadata(*columns_list, + Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) { DBUG_VOID_RETURN; } @@ -2422,8 +2490,7 @@ JOIN::exec() curr_join->select_distinct && !curr_join->group_list, 1, curr_join->select_options, - HA_POS_ERROR, - (char *) ""))) + HA_POS_ERROR, ""))) DBUG_VOID_RETURN; curr_join->exec_tmp_table2= exec_tmp_table2; } @@ -2460,7 +2527,9 @@ JOIN::exec() } } if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list, - 1, TRUE)) + 1, TRUE) || + prepare_sum_aggregators(curr_join->sum_funcs, + !curr_join->join_tab->is_using_agg_loose_index_scan())) DBUG_VOID_RETURN; curr_join->group_list= 0; if (!curr_join->sort_and_group && @@ -2576,13 +2645,17 @@ JOIN::exec() if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list, 1, TRUE) || + prepare_sum_aggregators(curr_join->sum_funcs, + !curr_join->join_tab || + !curr_join->join_tab-> + is_using_agg_loose_index_scan()) || setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) || thd->is_fatal_error) DBUG_VOID_RETURN; } if (curr_join->group_list || curr_join->order) { - DBUG_PRINT("info",("Sorting for send_fields")); + DBUG_PRINT("info",("Sorting for send_result_set_metadata")); thd_proc_info(thd, "Sorting result"); /* If we have already done the group, add HAVING to sorted table */ if (curr_join->tmp_having && ! curr_join->group_list && @@ -2713,35 +2786,13 @@ JOIN::exec() curr_join->fields= curr_fields_list; curr_join->procedure= procedure; - if (is_top_level_join() && thd->cursor && table_count != const_tables) - { - /* - We are here if this is JOIN::exec for the last select of the main unit - and the client requested to open a cursor. - We check that not all tables are constant because this case is not - handled by do_select() separately, and this case is not implemented - for cursors yet. - */ - DBUG_ASSERT(error == 0); - /* - curr_join is used only for reusable joins - that is, - to perform SELECT for each outer row (like in subselects). - This join is main, so we know for sure that curr_join == join. - */ - DBUG_ASSERT(curr_join == this); - /* Open cursor for the last join sweep */ - error= thd->cursor->open(this); - } - else - { - thd_proc_info(thd, "Sending data"); - DBUG_PRINT("info", ("%s", thd->proc_info)); - result->send_fields((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; - } + thd_proc_info(thd, "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; /* Accumulate the counts from all join iterations of all join parts. */ thd->examined_row_count+= curr_join->examined_rows; @@ -2962,16 +3013,6 @@ mysql_select(THD *thd, Item ***rref_pointer_array, join->exec(); - if (thd->cursor && thd->cursor->is_open()) - { - /* - A cursor was opened for the last sweep in exec(). - We are here only if this is mysql_select for top-level SELECT_LEX_UNIT - and there were no error. - */ - free_join= 0; - } - if (thd->lex->describe & DESCRIBE_EXTENDED) { select_lex->where= join->conds_history; @@ -3000,9 +3041,7 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, { int error; DBUG_ENTER("get_quick_record_count"); -#ifndef EMBEDDED_LIBRARY // Avoid compiler warning uchar buff[STACK_BUFF_ALLOC]; -#endif if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) DBUG_RETURN(0); // Fatal error flag is set if (select) @@ -3300,7 +3339,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, goto error; // Fatal error } else + { found_const_table_map|= s->table->map; + s->table->pos_in_table_list->optimized_away= TRUE; + } } } @@ -3427,11 +3469,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, The effect of this is that we don't do const substitution for such tables. */ - if (eq_part.is_prefix(table->key_info[key].key_parts) && + KEY *keyinfo= table->key_info + key; + uint key_parts= table->actual_n_key_parts(keyinfo); + if (eq_part.is_prefix(key_parts) && !table->fulltext_searched && (!embedding || (embedding->sj_on_expr && !embedding->embedding))) { - if (table->key_info[key].flags & HA_NOSAME) + if (table->actual_key_flags(keyinfo) & HA_NOSAME) { if (const_ref == eq_part && !has_expensive_keyparts && @@ -4001,14 +4045,14 @@ add_key_field(JOIN *join, (*sargables)->arg_value= value; (*sargables)->num_values= num_values; } + if (!eq_func) // eq_func is NEVER true when num_values > 1 + return; /* We can't use indexes when comparing a string index to a number or two strings if the effective collation of the operation differ from the field collation. */ - if (!eq_func) - return; if (field->cmp_type() == STRING_RESULT) { @@ -4465,7 +4509,8 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field) if (form->key_info[key].flags & (HA_FULLTEXT | HA_SPATIAL)) continue; // ToDo: ft-keys in non-ft queries. SerG - uint key_parts= (uint) form->key_info[key].key_parts; + KEY *keyinfo= form->key_info+key; + uint key_parts= form->actual_n_key_parts(keyinfo); for (uint part=0 ; part < key_parts ; part++) { if (field->eq(form->key_info[key].key_part[part].field)) @@ -4512,19 +4557,19 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, cond_func=(Item_func_match *)cond; else if (func->arg_count == 2) { - Item *arg0= func->arguments()[0], - *arg1= func->arguments()[1]; + Item *arg0=(Item *)(func->arguments()[0]), + *arg1=(Item *)(func->arguments()[1]); if (arg1->const_item() && arg1->cols() == 1 && arg0->type() == Item::FUNC_ITEM && ((Item_func *) arg0)->functype() == Item_func::FT_FUNC && ((functype == Item_func::GE_FUNC && arg1->val_real() > 0) || - (functype == Item_func::GT_FUNC && arg1->val_real() >= 0))) + (functype == Item_func::GT_FUNC && arg1->val_real() >=0))) cond_func= (Item_func_match *) arg0; else if (arg0->const_item() && arg0->cols() == 1 && arg1->type() == Item::FUNC_ITEM && ((Item_func *) arg1)->functype() == Item_func::FT_FUNC && ((functype == Item_func::LE_FUNC && arg0->val_real() > 0) || - (functype == Item_func::LT_FUNC && arg0->val_real() >= 0))) + (functype == Item_func::LT_FUNC && arg0->val_real() >=0))) cond_func= (Item_func_match *) arg1; } } @@ -4850,7 +4895,7 @@ static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse, save_pos++; } i= (uint) (save_pos-(KEYUSE*) keyuse->buffer); - VOID(set_dynamic(keyuse,(uchar*) &key_end,i)); + (void) set_dynamic(keyuse,(uchar*) &key_end,i); keyuse->elements= i; return FALSE; @@ -4903,6 +4948,82 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array) /** + Check for the presence of AGGFN(DISTINCT a) queries that may be subject + to loose index scan. + + + Check if the query is a subject to AGGFN(DISTINCT) using loose index scan + (QUICK_GROUP_MIN_MAX_SELECT). + Optionally (if out_args is supplied) will push the arguments of + AGGFN(DISTINCT) to the list + + @param join the join to check + @param[out] out_args list of aggregate function arguments + @return does the query qualify for indexed AGGFN(DISTINCT) + @retval true it does + @retval false AGGFN(DISTINCT) must apply distinct in it. +*/ + +bool +is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args) +{ + Item_sum **sum_item_ptr; + bool result= false; + + if (join->table_count != 1 || /* reference more than 1 table */ + join->select_distinct || /* or a DISTINCT */ + join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */ + return false; + + if (join->make_sum_func_list(join->all_fields, join->fields_list, true)) + return false; + + for (sum_item_ptr= join->sum_funcs; *sum_item_ptr; sum_item_ptr++) + { + Item_sum *sum_item= *sum_item_ptr; + Item *expr; + /* aggregate is not AGGFN(DISTINCT) or more than 1 argument to it */ + switch (sum_item->sum_func()) + { + case Item_sum::MIN_FUNC: + case Item_sum::MAX_FUNC: + continue; + case Item_sum::COUNT_DISTINCT_FUNC: + break; + case Item_sum::AVG_DISTINCT_FUNC: + case Item_sum::SUM_DISTINCT_FUNC: + if (sum_item->get_arg_count() == 1) + break; + /* fall through */ + default: return false; + } + /* + We arrive here for every COUNT(DISTINCT),AVG(DISTINCT) or SUM(DISTINCT). + Collect the arguments of the aggregate functions to a list. + We don't worry about duplicates as these will be sorted out later in + get_best_group_min_max + */ + for (uint i= 0; i < sum_item->get_arg_count(); i++) + { + expr= sum_item->get_arg(i); + /* The AGGFN(DISTINCT) arg is not an attribute? */ + if (expr->real_item()->type() != Item::FIELD_ITEM) + return false; + + /* + If we came to this point the AGGFN(DISTINCT) loose index scan + optimization is applicable + */ + if (out_args) + out_args->push_back((Item_field *) expr->real_item()); + result= true; + } + } + return result; +} + + +/** Discover the indexes that can be used for GROUP BY or DISTINCT queries. If the query has a GROUP BY clause, find all indexes that contain all @@ -4944,6 +5065,10 @@ add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab) item->walk(&Item::collect_item_field_processor, 0, (uchar*) &indexed_fields); } + else if (is_indexed_agg_distinct(join, &indexed_fields)) + { + join->sort_and_group= 1; + } else return; @@ -5097,6 +5222,8 @@ best_access_path(JOIN *join, for (keyuse=s->keyuse ; keyuse->table == table ;) { KEY *keyinfo; + ulong key_flags; + uint key_parts; key_part_map found_part= 0; table_map found_ref= 0; uint key= keyuse->key; @@ -5123,6 +5250,8 @@ best_access_path(JOIN *join, } keyinfo= table->key_info+key; + key_parts= table->actual_n_key_parts(keyinfo); + key_flags= table->actual_key_flags(keyinfo); /* Calculate how many key segments of the current key we can use */ start_key= keyuse; @@ -5201,11 +5330,11 @@ best_access_path(JOIN *join, loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part); /* Check if we found full key */ - if (found_part == PREV_BITS(uint,keyinfo->key_parts) && + if (found_part == PREV_BITS(uint, key_parts) && !ref_or_null_part) { /* use eq key */ max_key_part= (uint) ~0; - if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) + if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) { tmp = prev_record_reads(join->positions, idx, found_ref); records=1.0; @@ -5241,7 +5370,8 @@ best_access_path(JOIN *join, } else { - if (!(records=keyinfo->rec_per_key[keyinfo->key_parts-1])) + uint key_parts= table->actual_n_key_parts(keyinfo); + if (!(records=keyinfo->rec_per_key[key_parts-1])) { /* Prefer longer keys */ records= ((double) s->records / (double) rec * @@ -5448,7 +5578,6 @@ best_access_path(JOIN *join, tmp= best_time; // Do nothing } - DBUG_ASSERT(tmp > 0 || record_count == 0); tmp += s->startup_cost; loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ @@ -6297,7 +6426,7 @@ greedy_search(JOIN *join, on exit. */ bool is_interleave_error __attribute__((unused))= - check_interleaving_with_nj(best_table); + check_interleaving_with_nj (best_table); /* This has been already checked by best_extension_by_limited_search */ DBUG_ASSERT(!is_interleave_error); @@ -7512,6 +7641,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, key_parts))) DBUG_RETURN(TRUE); keyinfo->usable_key_parts= keyinfo->key_parts = key_parts; + keyinfo->ext_key_parts= keyinfo->key_parts; keyinfo->key_part= key_part_info; keyinfo->key_length=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; @@ -7556,6 +7686,10 @@ 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_flags= keyinfo->flags; + keyinfo->ext_key_part_map= 0; + join_tab->hj_key= keyinfo; DBUG_RETURN(FALSE); @@ -7750,8 +7884,11 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, DBUG_RETURN(0); if (j->type == JT_CONST) j->table->const_table= 1; - else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) != HA_NOSAME) || - keyparts != keyinfo->key_parts || null_ref_key) + else if (((j->table->actual_key_flags(keyinfo) & + (HA_NOSAME | HA_NULL_PART_KEY)) + != HA_NOSAME) || + keyparts != j->table->actual_n_key_parts(keyinfo) || + null_ref_key) { /* Must read with repeat */ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; @@ -8508,7 +8645,11 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (tab->table) { tab->table->file->pushed_cond= NULL; - if (thd->variables.engine_condition_pushdown && !first_inner_tab) + 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) { COND *push_cond= make_cond_for_table(thd, tmp, current_map, current_map, @@ -8538,11 +8679,12 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* Use quick key read if it's a constant and it's not used with key reading */ - if ((tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF - && tab->type != JT_FT && - ((tab->type != JT_REF && tab->type != JT_CONST) || - (uint) tab->ref.key == tab->quick->index)) || is_hj) - { + if ((tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF && + tab->type != JT_FT && + ((tab->type != JT_CONST && tab->type != JT_REF) || + (uint) tab->ref.key == tab->quick->index)) || is_hj) + { + DBUG_ASSERT(tab->quick->is_valid()); sel->quick=tab->quick; // Use value from get_quick_... sel->quick_keys.clear_all(); sel->needed_reg.clear_all(); @@ -9986,12 +10128,14 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) { join->need_tmp= 1; join->simple_order= join->simple_group= 0; - if (sort_by_tab->type == JT_NEXT) + if (sort_by_tab->type == JT_NEXT && + !sort_by_tab->table->covering_keys.is_set(sort_by_tab->index)) { sort_by_tab->type= JT_ALL; sort_by_tab->read_first_record= join_init_read_record; } - else if (sort_by_tab->type == JT_HASH_NEXT) + else if (sort_by_tab->type == JT_HASH_NEXT && + !sort_by_tab->table->covering_keys.is_set(sort_by_tab->index)) { sort_by_tab->type= JT_HASH; sort_by_tab->read_first_record= join_init_read_record; @@ -10327,7 +10471,7 @@ void JOIN::join_free() We are not using tables anymore Unlock all tables. We may be in an INSERT .... SELECT statement. */ - if (can_unlock && lock && thd->lock && + if (can_unlock && lock && thd->lock && ! thd->locked_tables_mode && !(select_options & SELECT_NO_UNLOCK) && !select_lex->subquery_in_having && (select_lex == (thd->lex->unit.fake_select_lex ? @@ -10371,18 +10515,19 @@ void JOIN::cleanup(bool full) */ if (table_count > const_tables) // Test for not-const tables { - free_io_cache(table[const_tables]); - filesort_free_buffers(table[const_tables],full); + JOIN_TAB *first_tab= first_top_level_tab(this, WITHOUT_CONST_TABLES); + if (first_tab->table) + { + free_io_cache(first_tab->table); + filesort_free_buffers(first_tab->table, full); + } } if (full) { for (tab= first_linear_tab(this, WITH_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) - { tab->cleanup(); - } - table= 0; } else { @@ -10671,8 +10816,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, *simple_order=0; else { - Item *comp_item=0; - if (cond && const_expression_in_where(cond,order->item[0], &comp_item)) + if (cond && const_expression_in_where(cond,order->item[0])) { DBUG_PRINT("info",("removing: %s", order->item[0]->full_name())); continue; @@ -10702,6 +10846,46 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, } +/** + Filter out ORDER items those are equal to constants in WHERE + + This function is a limited version of remove_const() for use + with non-JOIN statements (i.e. single-table UPDATE and DELETE). + + + @param order Linked list of ORDER BY arguments + @param cond WHERE expression + + @return pointer to new filtered ORDER list or NULL if whole list eliminated + + @note + This function overwrites input order list. +*/ + +ORDER *simple_remove_const(ORDER *order, COND *where) +{ + if (!order || !where) + return order; + + ORDER *first= NULL, *prev= NULL; + for (; order; order= order->next) + { + DBUG_ASSERT(!order->item[0]->with_sum_func); // should never happen + if (!const_expression_in_where(where, order->item[0])) + { + if (!first) + first= order; + if (prev) + prev->next= order; + prev= order; + } + } + if (prev) + prev->next= NULL; + return first; +} + + static int return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, List<Item> &fields, bool send_row, ulonglong select_options, @@ -10746,7 +10930,7 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, if (having && having->val_int() == 0) send_row=0; } - if (!(result->send_fields(fields, + if (!(result->send_result_set_metadata(fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))) { bool send_error= FALSE; @@ -11064,7 +11248,7 @@ static bool check_simple_equality(Item *left_item, Item *right_item, { bool copyfl; - if (field_item->result_type() == STRING_RESULT) + if (field_item->cmp_type() == STRING_RESULT) { CHARSET_INFO *cs= ((Field_str*) field_item->field)->charset(); if (!item) @@ -11515,8 +11699,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, pointer to the transformed condition containing multiple equalities */ -static COND *build_equal_items(THD *thd, COND *cond, - COND_EQUAL *inherited, +static COND *build_equal_items(THD *thd, COND *cond, COND_EQUAL *inherited, List<TABLE_LIST> *join_list, COND_EQUAL **cond_equal_ref) { @@ -12141,7 +12324,6 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, left_item->collation.collation == value->collation.collation)) { Item *tmp=value->clone_item(); - if (tmp) { tmp->collation.set(right_item->collation); @@ -12165,7 +12347,6 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list, right_item->collation.collation == value->collation.collation)) { Item *tmp= value->clone_item(); - if (tmp) { tmp->collation.set(left_item->collation); @@ -13014,18 +13195,26 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, /** - Remove const and eq items. + Handles the recursive job remove_eq_conds() - @return - Return new item, or NULL if no condition @n - cond_value is set to according: - - COND_OK : query is possible (field = constant) - - COND_TRUE : always true ( 1 = 1 ) - - COND_FALSE : always false ( 1 = 2 ) + Remove const and eq items. Return new item, or NULL if no condition + cond_value is set to according: + COND_OK query is possible (field = constant) + COND_TRUE always true ( 1 = 1 ) + COND_FALSE always false ( 1 = 2 ) + + SYNOPSIS + internal_remove_eq_conds() + thd THD environment + cond the condition to handle + cond_value the resulting value of the condition + + RETURN + *COND with the simplified condition */ -COND * -remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) +static COND * +internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) { if (cond->type() == Item::COND_ITEM) { @@ -13039,12 +13228,12 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) Item *item; while ((item=li++)) { - Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value); + Item *new_item=internal_remove_eq_conds(thd, item, &tmp_cond_value); if (!new_item) li.remove(); else if (item != new_item) { - VOID(li.replace(new_item)); + (void) li.replace(new_item); should_fix_fields=1; } if (*cond_value == Item::COND_UNDEF) @@ -13088,6 +13277,102 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) else if (cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) { + Item_func_isnull *func=(Item_func_isnull*) cond; + Item **args= func->arguments(); + if (args[0]->type() == Item::FIELD_ITEM) + { + Field *field=((Item_field*) args[0])->field; + /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ + /* + See BUG#12594011 + Documentation says that + SELECT datetime_notnull d FROM t1 WHERE d IS NULL + shall return rows where d=='0000-00-00' + + Thus, for DATE and DATETIME columns defined as NOT NULL, + "date_notnull IS NULL" has to be modified to + "date_notnull IS NULL OR date_notnull == 0" (if outer join) + "date_notnull == 0" (otherwise) + + */ + if (((field->type() == MYSQL_TYPE_DATE) || + (field->type() == MYSQL_TYPE_DATETIME)) && + (field->flags & NOT_NULL_FLAG)) + { + Item *item0= new(thd->mem_root) Item_int((longlong)0, 1); + Item *eq_cond= new(thd->mem_root) Item_func_eq(args[0], item0); + if (!eq_cond) + return cond; + + if (field->table->pos_in_table_list->outer_join) + { + // outer join: transform "col IS NULL" to "col IS NULL or col=0" + Item *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond); + if (!or_cond) + return cond; + cond= or_cond; + } + else + { + // not outer join: transform "col IS NULL" to "col=0" + cond= eq_cond; + } + + cond->fix_fields(thd, &cond); + } + } + if (cond->const_item() && !cond->is_expensive()) + { + *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; + return (COND*) 0; + } + } + else if (cond->const_item() && !cond->is_expensive()) + { + *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; + return (COND*) 0; + } + else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK) + { // boolan compare function + Item *left_item= ((Item_func*) cond)->arguments()[0]; + Item *right_item= ((Item_func*) cond)->arguments()[1]; + if (left_item->eq(right_item,1)) + { + if (!left_item->maybe_null || + ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC) + return (COND*) 0; // Compare of identical items + } + } + *cond_value=Item::COND_OK; + return cond; // Point at next and level +} + +/** + Remove const and eq items. Return new item, or NULL if no condition + cond_value is set to according: + COND_OK query is possible (field = constant) + COND_TRUE always true ( 1 = 1 ) + COND_FALSE always false ( 1 = 2 ) + + SYNPOSIS + remove_eq_conds() + thd THD environment + cond the condition to handle + cond_value the resulting value of the condition + + NOTES + calls the inner_remove_eq_conds to check all the tree reqursively + + RETURN + *COND with the simplified condition +*/ + +COND * +remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) +{ + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) + { /* Handles this special case for some ODBC applications: The are requesting the row that was just updated with a auto_increment @@ -13104,12 +13389,12 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) { Field *field=((Item_field*) args[0])->field; if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null && - (thd->options & OPTION_AUTO_IS_NULL) && + (thd->variables.option_bits & OPTION_AUTO_IS_NULL) && (thd->first_successful_insert_id_in_prev_stmt > 0 && thd->substitute_null_with_insert_id)) { #ifdef HAVE_QUERY_CACHE - query_cache_abort(&thd->net); + query_cache_abort(&thd->query_cache_tls); #endif COND *new_cond; if ((new_cond= new Item_func_eq(args[0], @@ -13130,53 +13415,17 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) clear for next row */ thd->substitute_null_with_insert_id= FALSE; - } - /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ - else if (((field->type() == MYSQL_TYPE_DATE) || - (field->type() == MYSQL_TYPE_DATETIME)) && - (field->flags & NOT_NULL_FLAG) && - !field->table->maybe_null) - { - COND *new_cond; - if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2)))) - { - cond=new_cond; - /* - Item_func_eq can't be fixed after creation so we do not check - cond->fixed, also it do not need tables so we use 0 as second - argument. - */ - cond->fix_fields(thd, &cond); - } + + *cond_value= Item::COND_OK; + return cond; } } - if (cond->const_item() && !cond->is_expensive()) - { - *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; - return (COND*) 0; - } } - else if (cond->const_item() && !cond->is_expensive()) - { - *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; - return (COND*) 0; - } - else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK) - { // boolan compare function - Item *left_item= ((Item_func*) cond)->arguments()[0]; - Item *right_item= ((Item_func*) cond)->arguments()[1]; - if (left_item->eq(right_item,1)) - { - if (!left_item->maybe_null || - ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC) - return (COND*) 0; // Compare of identical items - } - } - *cond_value=Item::COND_OK; - return cond; // Point at next and level + return internal_remove_eq_conds(thd, cond, cond_value); // Scan all the condition } -/** + +/* Check if equality can be used in removing components of GROUP BY/DISTINCT @param l the left comparison argument (a field if any) @@ -13208,13 +13457,50 @@ test_if_equality_guarantees_uniqueness(Item *l, Item *r) l->collation.collation == r->collation.collation); } -/** - Return TRUE if the item is a const value in all the WHERE clause. + +/* + Return TRUE if i1 and i2 (if any) are equal items, + or if i1 is a wrapper item around the f2 field. */ -static bool -const_expression_in_where(COND *cond, Item *comp_item, Item **const_item) +static bool equal(Item *i1, Item *i2, Field *f2) +{ + DBUG_ASSERT((i2 == NULL) ^ (f2 == NULL)); + + if (i2 != NULL) + return i1->eq(i2, 1); + else if (i1->type() == Item::FIELD_ITEM) + return f2->eq(((Item_field *) i1)->field); + else + return FALSE; +} + + +/** + Test if a field or an item is equal to a constant value in WHERE + + @param cond WHERE clause expression + @param comp_item Item to find in WHERE expression + (if comp_field != NULL) + @param comp_field Field to find in WHERE expression + (if comp_item != NULL) + @param[out] const_item intermediate arg, set to Item pointer to NULL + + @return TRUE if the field is a constant value in WHERE + + @note + comp_item and comp_field parameters are mutually exclusive. +*/ +bool +const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field, + Item **const_item) { + DBUG_ASSERT((comp_item == NULL) ^ (comp_field == NULL)); + + Item *intermediate= NULL; + if (const_item == NULL) + const_item= &intermediate; + if (cond->type() == Item::COND_ITEM) { bool and_level= (((Item_cond*) cond)->functype() @@ -13223,7 +13509,8 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item) Item *item; while ((item=li++)) { - bool res=const_expression_in_where(item, comp_item, const_item); + bool res=const_expression_in_where(item, comp_item, comp_field, + const_item); if (res) // Is a const value { if (and_level) @@ -13235,14 +13522,14 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item) return and_level ? 0 : 1; } else if (cond->eq_cmp_result() != Item::COND_OK) - { // boolan compare function + { // boolean compare function Item_func* func= (Item_func*) cond; if (func->functype() != Item_func::EQUAL_FUNC && func->functype() != Item_func::EQ_FUNC) return 0; Item *left_item= ((Item_func*) cond)->arguments()[0]; Item *right_item= ((Item_func*) cond)->arguments()[1]; - if (left_item->eq(comp_item,1)) + if (equal(left_item, comp_item, comp_field)) { if (test_if_equality_guarantees_uniqueness (left_item, right_item)) { @@ -13252,7 +13539,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item) return 1; } } - else if (right_item->eq(comp_item,1)) + else if (equal(right_item, comp_item, comp_field)) { if (test_if_equality_guarantees_uniqueness (right_item, left_item)) { @@ -13266,6 +13553,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item) return 0; } + /**************************************************************************** Create internal temporary table ****************************************************************************/ @@ -13519,7 +13807,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item_sum *item_sum=(Item_sum*) item; result= item_sum->create_tmp_field(group, table, convert_blob_length); if (!result) - thd->fatal_error(); + my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR)); return result; } case Item::FIELD_ITEM: @@ -13684,7 +13972,7 @@ void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps) Create a temp table according to a field list. Given field pointers are changed to point at tmp_table for - send_fields. The table object is self contained: it's + send_result_set_metadata. The table object is self contained: it's allocated in its own memory root, as well as Field objects created for table columns. This function will replace Item_sum items in 'fields' list with @@ -13710,7 +13998,7 @@ TABLE * create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, - char *table_alias, bool do_not_open) + const char *table_alias, bool do_not_open) { MEM_ROOT *mem_root_save, own_root; TABLE *table; @@ -13876,6 +14164,8 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, share->primary_key= MAX_KEY; // Indicate no primary key share->keys_for_keyread.init(); share->keys_in_use.init(); + if (param->schema_table) + share->db= INFORMATION_SCHEMA_NAME; /* Calculate which type of fields we will store in the temporary table */ @@ -14088,10 +14378,10 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, /* If result table is small; use a heap */ /* future: storage engine selection can be made dynamic? */ - if (blob_count || using_unique_constraint || - (select_options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) == - OPTION_BIG_TABLES || (select_options & TMP_TABLE_FORCE_MYISAM) || - !thd->variables.tmp_table_size) + if (blob_count || using_unique_constraint + || (thd->variables.big_tables && !(select_options & SELECT_SMALL_RESULT)) + || (select_options & TMP_TABLE_FORCE_MYISAM) + || thd->variables.tmp_table_size == 0) { share->db_plugin= ha_lock_engine(0, TMP_ENGINE_HTON); table->file= get_new_handler(share, &table->mem_root, @@ -14297,7 +14587,9 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, 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->key_length=0; keyinfo->rec_per_key=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; @@ -14321,7 +14613,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? 0 : FIELDFLAG_BINARY; - + key_part_info->key_part_flag= 0; if (!using_unique_constraint) { cur_group->buff=(char*) group_buff; @@ -14396,6 +14688,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, 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; table->distinct= 1; share->keys= 1; if (!(key_part_info= (KEY_PART_INFO*) @@ -14408,6 +14701,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, table->key_info= table->s->key_info= keyinfo; keyinfo->key_part=key_part_info; keyinfo->flags=HA_NOSAME | HA_NULL_ARE_EQUAL | HA_BINARY_PACK_KEY | HA_PACK_KEY; + keyinfo->ext_key_flags= keyinfo->flags; keyinfo->key_length= 0; // Will compute the sum of the parts below. keyinfo->name= (char*) "distinct_key"; keyinfo->algorithm= HA_KEY_ALG_UNDEF; @@ -14503,7 +14797,8 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (share->db_type() == TMP_ENGINE_HTON) { if (create_internal_tmp_table(table, param->keyinfo, param->start_recinfo, - ¶m->recinfo, select_options)) + ¶m->recinfo, select_options, + thd->variables.big_tables)) goto err; } if (open_tmp_table(table)) @@ -14575,6 +14870,7 @@ TABLE *create_virtual_tmp_table(THD *thd, List<Create_field> &field_list) bzero(share, sizeof(*share)); table->field= field; table->s= share; + table->temp_pool_slot= MY_BIT_NONE; share->blob_field= blob_field; share->fields= field_count; share->blob_ptr_size= portable_sizeof_char_ptr; @@ -14642,6 +14938,18 @@ TABLE *create_virtual_tmp_table(THD *thd, List<Create_field> &field_list) null_bit= 1; } } + if (cur_field->type() == MYSQL_TYPE_BIT && + cur_field->key_type() == HA_KEYTYPE_BIT) + { + /* This is a Field_bit since key_type is HA_KEYTYPE_BIT */ + static_cast<Field_bit*>(cur_field)->set_bit_ptr(null_pos, null_bit); + null_bit+= cur_field->field_length & 7; + if (null_bit > 7) + { + null_pos++; + null_bit-= 8; + } + } cur_field->reset(); field_pos+= cur_field->pack_length(); @@ -14673,7 +14981,7 @@ bool open_tmp_table(TABLE *table) } -#if defined(WITH_ARIA_STORAGE_ENGINE) && defined(USE_MARIA_FOR_TMP_TABLES) +#if defined(WITH_ARIA_STORAGE_ENGINE) && defined(USE_ARIA_FOR_TMP_TABLES) /* Create internal (MyISAM or Maria) temporary table @@ -14709,7 +15017,7 @@ bool open_tmp_table(TABLE *table) bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, ENGINE_COLUMNDEF *start_recinfo, ENGINE_COLUMNDEF **recinfo, - ulonglong options) + ulonglong options, my_bool big_tables) { int error; MARIA_KEYDEF keydef; @@ -14802,8 +15110,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, } bzero((char*) &create_info,sizeof(create_info)); - if ((options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) == - OPTION_BIG_TABLES) + if (big_tables && !(options & SELECT_SMALL_RESULT)) create_info.data_file_length= ~(ulonglong) 0; /* @@ -14893,7 +15200,7 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, ENGINE_COLUMNDEF *start_recinfo, ENGINE_COLUMNDEF **recinfo, - ulonglong options) + ulonglong options, my_bool big_tables) { int error; MI_KEYDEF keydef; @@ -14980,8 +15287,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, MI_CREATE_INFO create_info; bzero((char*) &create_info,sizeof(create_info)); - if ((options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) == - OPTION_BIG_TABLES) + if (big_tables && !(options & SELECT_SMALL_RESULT)) create_info.data_file_length= ~(ulonglong) 0; if ((error=mi_create(share->table_name.str, share->keys, &keydef, @@ -15053,8 +15359,7 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, We don't want this error to be converted to a warning, e.g. in case of INSERT IGNORE ... SELECT. */ - thd->fatal_error(); - table->file->print_error(error,MYF(0)); + table->file->print_error(error, MYF(ME_FATALERROR)); DBUG_RETURN(1); } new_table= *table; @@ -15072,7 +15377,8 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, if (create_internal_tmp_table(&new_table, table->key_info, start_recinfo, recinfo, thd->lex->select_lex.options | - thd->options)) + thd->variables.option_bits, + thd->variables.big_tables)) goto err2; if (open_tmp_table(&new_table)) goto err1; @@ -15165,6 +15471,7 @@ free_tmp_table(THD *thd, TABLE *entry) if (entry->file && entry->created) { + entry->file->ha_index_or_rnd_end(); if (entry->db_stat) entry->file->ha_drop_table(entry->s->table_name.str); else @@ -15181,6 +15488,7 @@ free_tmp_table(THD *thd, TABLE *entry) bitmap_lock_clear_bit(&temp_pool, entry->temp_pool_slot); plugin_unlock(0, entry->s->db_plugin); + entry->alias.free(); free_root(&own_root, MYF(0)); /* the table is allocated in its own root */ thd_proc_info(thd, save_proc_info); @@ -15293,7 +15601,7 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) if (table) { - VOID(table->file->extra(HA_EXTRA_WRITE_CACHE)); + (void) table->file->extra(HA_EXTRA_WRITE_CACHE); empty_record(table); if (table->group && join->tmp_table_param.sum_func_count && table->s->keys && !table->file->inited) @@ -15337,7 +15645,6 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) so we don't touch it here. */ join->examined_rows++; - join->thd->row_count++; DBUG_ASSERT(join->examined_rows <= 1); } else if (join->send_row_on_empty_set()) @@ -15349,6 +15656,13 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) rc= join->result->send_data(*columns_list) > 0; } } + /* + An error can happen when evaluating the conds + (the join condition and piece of where clause + relevant to this join table). + */ + if (join->thd->is_error()) + error= NESTED_LOOP_ERROR; } else { @@ -15357,34 +15671,15 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) error= NESTED_LOOP_NO_MORE_ROWS; else error= sub_select(join,join_tab,0); - if (error == NESTED_LOOP_OK || error == NESTED_LOOP_NO_MORE_ROWS) + if ((error == NESTED_LOOP_OK || error == NESTED_LOOP_NO_MORE_ROWS) && + join->thd->killed != ABORT_QUERY) error= sub_select(join,join_tab,1); if (error == NESTED_LOOP_QUERY_LIMIT) error= NESTED_LOOP_OK; /* select_limit used */ } - if (error == NESTED_LOOP_NO_MORE_ROWS) + if (error == NESTED_LOOP_NO_MORE_ROWS || join->thd->killed == ABORT_QUERY) error= NESTED_LOOP_OK; - if (table == NULL) // If sending data to client - { - /* - The following will unlock all cursors if the command wasn't an - update command - */ - join->join_free(); // Unlock all cursors - } - if (error == NESTED_LOOP_OK) - { - /* - Sic: this branch works even if rc != 0, e.g. when - send_data above returns an error. - */ - if (table == NULL && join->result->send_eof()) // If sending data to client - rc= 1; // Don't send error - DBUG_PRINT("info",("%ld records output", (long) join->send_records)); - } - else - rc= -1; if (table) { int tmp, new_errno= 0; @@ -15401,6 +15696,29 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) if (new_errno) table->file->print_error(new_errno,MYF(0)); } + else + { + /* + The following will unlock all cursors if the command wasn't an + update command + */ + join->join_free(); // Unlock all cursors + } + if (error == NESTED_LOOP_OK) + { + /* + Sic: this branch works even if rc != 0, e.g. when + send_data above returns an error. + */ + if (!table) // If sending data to client + { + if (join->result->send_eof()) + rc= 1; // Don't send error + } + DBUG_PRINT("info",("%ld records output", (long) join->send_records)); + } + else + rc= -1; #ifndef DBUG_OFF if (rc) { @@ -15653,52 +15971,38 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) if (!join_tab->preread_init_done && join_tab->preread_init()) DBUG_RETURN(NESTED_LOOP_ERROR); - if (join->resume_nested_loop) - { - /* If not the last table, plunge down the nested loop */ - if (join_tab < join->join_tab + join->top_join_tab_count - 1) - rc= (*join_tab->next_select)(join, join_tab + 1, 0); - else - { - join->resume_nested_loop= FALSE; - rc= NESTED_LOOP_OK; - } - } - else - { - join->return_tab= join_tab; + join->return_tab= join_tab; - if (join_tab->last_inner) - { - /* join_tab is the first inner table for an outer join operation. */ + if (join_tab->last_inner) + { + /* join_tab is the first inner table for an outer join operation. */ - /* Set initial state of guard variables for this table.*/ - join_tab->found=0; - join_tab->not_null_compl= 1; + /* Set initial state of guard variables for this table.*/ + join_tab->found=0; + join_tab->not_null_compl= 1; - /* Set first_unmatched for the last inner table of this group */ - join_tab->last_inner->first_unmatched= join_tab; - if (join_tab->on_precond && !join_tab->on_precond->val_int()) - rc= NESTED_LOOP_NO_MORE_ROWS; - } - join->thd->row_count= 0; - - if (rc != NESTED_LOOP_NO_MORE_ROWS && - (rc= join_tab_execution_startup(join_tab)) < 0) - DBUG_RETURN(rc); + /* Set first_unmatched for the last inner table of this group */ + join_tab->last_inner->first_unmatched= join_tab; + 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(); - if (join_tab->loosescan_match_tab) - join_tab->loosescan_match_tab->found_match= FALSE; + if (rc != NESTED_LOOP_NO_MORE_ROWS && + (rc= join_tab_execution_startup(join_tab)) < 0) + DBUG_RETURN(rc); + + if (join_tab->loosescan_match_tab) + join_tab->loosescan_match_tab->found_match= FALSE; - if (rc != NESTED_LOOP_NO_MORE_ROWS) - { - error= (*join_tab->read_first_record)(join_tab); - if (join_tab->keep_current_rowid) - join_tab->table->file->position(join_tab->table->record[0]); - rc= evaluate_join_record(join, join_tab, error); - } + if (rc != NESTED_LOOP_NO_MORE_ROWS) + { + error= (*join_tab->read_first_record)(join_tab); + if (join_tab->keep_current_rowid) + join_tab->table->file->position(join_tab->table->record[0]); + rc= evaluate_join_record(join, join_tab, error); } - + /* Note: psergey has added the 2nd part of the following condition; the change should probably be made in 5.1, too. @@ -15826,14 +16130,26 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, will be re-evaluated again. It could be fixed, but, probably, it's not worth doing now. */ + /* + not_exists_optimize has been created from a + select_cond containing 'is_null'. This 'is_null' + predicate is still present on any 'tab' with + 'not_exists_optimize'. Furthermore, the usual rules + for condition guards also applies for + 'not_exists_optimize' -> When 'is_null==false' we + know all cond. guards are open and we can apply + the 'not_exists_optimize'. + */ + DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize && + !tab->select_cond)); + if (tab->select_cond && !tab->select_cond->val_int()) { /* The condition attached to table tab is false */ + if (tab == join_tab) { found= 0; - if (tab->table->reginfo.not_exists_optimize) - DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); } else { @@ -15842,10 +16158,21 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, not to the last table of the current nest level. */ join->return_tab= tab; - if (tab->table->reginfo.not_exists_optimize) - DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); - else - DBUG_RETURN(NESTED_LOOP_OK); + } + + if (tab->table->reginfo.not_exists_optimize) + { + /* + When not_exists_optimize is set: No need to further + explore more rows of 'tab' for this partial result. + Any found 'tab' matches are known to evaluate to 'false'. + Returning .._NO_MORE_ROWS will skip rem. 'tab' rows. + */ + DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); + } + else if (tab != join_tab) + { + DBUG_RETURN(NESTED_LOOP_OK); } } } @@ -15885,7 +16212,6 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, (See above join->return_tab= tab). */ join->examined_rows++; - join->thd->row_count++; DBUG_PRINT("counts", ("join->examined_rows++: %lu", (ulong) join->examined_rows)); @@ -15894,6 +16220,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, 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(); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) DBUG_RETURN(rc); if (return_tab < join->return_tab) @@ -15910,7 +16237,10 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); } else + { + join->thd->warning_info->inc_current_row_for_warning(); join_tab->read_record.unlock_row(join_tab); + } } else { @@ -15919,7 +16249,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, with the beginning coinciding with the current partial join. */ join->examined_rows++; - join->thd->row_count++; + join->thd->warning_info->inc_current_row_for_warning(); join_tab->read_record.unlock_row(join_tab); } DBUG_RETURN(NESTED_LOOP_OK); @@ -16031,7 +16361,7 @@ int report_error(TABLE *table, int error) */ if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT) { - push_warning_printf(table->in_use, MYSQL_ERROR::WARN_LEVEL_ERROR, error, + 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'", @@ -16092,7 +16422,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) /* Mark for EXPLAIN that the row was not found */ pos->records_read=0.0; pos->ref_depend_map= 0; - if (!table->maybe_null || error > 0) + if (!table->pos_in_table_list->outer_join || error > 0) DBUG_RETURN(error); } /* @@ -16118,7 +16448,7 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) /* Mark for EXPLAIN that the row was not found */ pos->records_read=0.0; pos->ref_depend_map= 0; - if (!table->maybe_null || error > 0) + if (!table->pos_in_table_list->outer_join || error > 0) DBUG_RETURN(error); } } @@ -16488,9 +16818,8 @@ join_init_quick_read_record(JOIN_TAB *tab) } -int init_read_record_seq(JOIN_TAB *tab) +int read_first_record_seq(JOIN_TAB *tab) { - tab->read_record.read_record= rr_sequential; if (tab->read_record.table->file->ha_rnd_init_with_error(1)) return 1; return (*tab->read_record.read_record)(&tab->read_record); @@ -16631,11 +16960,6 @@ join_ft_read_first(JOIN_TAB *tab) table->file->print_error(error, MYF(0)); /* purecov: inspected */ return(1); /* purecov: inspected */ } -#if NOT_USED_YET - /* as ft-key doesn't use store_key's, see also FT_SELECT::init() */ - if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref)) - return -1; -#endif table->file->ft_init(); if ((error= table->file->ha_ft_read(table->record[0]))) @@ -16722,6 +17046,12 @@ end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_ENTER("end_send"); if (!end_of_records) { + if (join->table_count && + join->join_tab->is_using_loose_index_scan()) + { + /* Copy non-aggregated fields when loose index scan is used. */ + copy_fields(&join->tmp_table_param); + } if (join->having && join->having->val_int() == 0) DBUG_RETURN(NESTED_LOOP_OK); // Didn't match having if (join->procedure) @@ -16894,7 +17224,7 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (end_of_records) DBUG_RETURN(NESTED_LOOP_OK); join->first_record=1; - VOID(test_if_group_changed(join->group_fields)); + (void) test_if_group_changed(join->group_fields); } if (idx < (int) join->send_group_parts) { @@ -16926,33 +17256,12 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), TABLE *table=join->tmp_table; DBUG_ENTER("end_write"); - if (join->thd->killed) // Aborted by user - { - join->thd->send_kill_message(); - DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ - } if (!end_of_records) { copy_fields(&join->tmp_table_param); if (copy_funcs(join->tmp_table_param.items_to_copy, join->thd)) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ -#ifdef TO_BE_DELETED - if (!table->uniques) // If not unique handling - { - /* Copy null values from group to row */ - ORDER *group; - for (group=table->group ; group ; group=group->next) - { - Item *item= *group->item; - if (item->maybe_null) - { - Field *field=item->get_tmp_table_field(); - field->ptr[-1]= (uchar) (field->is_null() ? 1 : 0); - } - } - } -#endif if (!join->having || join->having->val_int()) { int error; @@ -16975,11 +17284,15 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT); join->do_send_rows=0; join->unit->select_limit_cnt = HA_POS_ERROR; - DBUG_RETURN(NESTED_LOOP_OK); } } } end: + if (join->thd->killed) + { + join->thd->send_kill_message(); + DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ + } DBUG_RETURN(NESTED_LOOP_OK); } @@ -16997,11 +17310,6 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (end_of_records) DBUG_RETURN(NESTED_LOOP_OK); - if (join->thd->killed) // Aborted by user - { - join->thd->send_kill_message(); - DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ - } join->found_records++; copy_fields(&join->tmp_table_param); // Groups are copied twice. @@ -17027,7 +17335,7 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), table->file->print_error(error,MYF(0)); /* purecov: inspected */ DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } - DBUG_RETURN(NESTED_LOOP_OK); + goto end; } /* @@ -17062,6 +17370,12 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), join->join_tab[join->top_join_tab_count-1].next_select=end_unique_update; } join->send_records++; +end: + if (join->thd->killed) + { + join->thd->send_kill_message(); + DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ + } DBUG_RETURN(NESTED_LOOP_OK); } @@ -17078,11 +17392,6 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (end_of_records) DBUG_RETURN(NESTED_LOOP_OK); - if (join->thd->killed) // Aborted by user - { - join->thd->send_kill_message(); - DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ - } init_tmptable_sum_functions(join->sum_funcs); copy_fields(&join->tmp_table_param); // Groups are copied twice. @@ -17112,6 +17421,11 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } } + if (join->thd->killed) + { + join->thd->send_kill_message(); + DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ + } DBUG_RETURN(NESTED_LOOP_OK); } @@ -17125,11 +17439,6 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), int idx= -1; DBUG_ENTER("end_write_group"); - if (join->thd->killed) - { // Aborted by user - join->thd->send_kill_message(); - DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ - } if (!join->first_record || end_of_records || (idx=test_if_group_changed(join->group_fields)) >= 0) { @@ -17163,15 +17472,15 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_ERROR); } if (end_of_records) - DBUG_RETURN(NESTED_LOOP_OK); + goto end; } } else { if (end_of_records) - DBUG_RETURN(NESTED_LOOP_OK); + goto end; join->first_record=1; - VOID(test_if_group_changed(join->group_fields)); + (void) test_if_group_changed(join->group_fields); } if (idx < (int) join->send_group_parts) { @@ -17182,13 +17491,19 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_ERROR); if (join->procedure) join->procedure->add(); - DBUG_RETURN(NESTED_LOOP_OK); + goto end; } } if (update_sum_func(join->sum_funcs)) DBUG_RETURN(NESTED_LOOP_ERROR); if (join->procedure) join->procedure->add(); +end: + if (join->thd->killed) + { + join->thd->send_kill_message(); + DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ + } DBUG_RETURN(NESTED_LOOP_OK); } @@ -17525,7 +17840,7 @@ make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, new_cond->argument_list()->push_back(fix); } /* - Item_cond_and do not need fix_fields for execution, its parameters + Item_cond_or do not need fix_fields for execution, its parameters are fixed or do not need fix_fields, too */ new_cond->quick_fix_field(); @@ -17638,7 +17953,8 @@ part_of_refkey(TABLE *table,Field *field) @param order Sort order @param table Table to sort @param idx Index to check - @param used_key_parts Return value for used key parts. + @param used_key_parts [out] NULL by default, otherwise return value for + used key parts. @note @@ -17657,13 +17973,14 @@ part_of_refkey(TABLE *table,Field *field) */ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, - uint *used_key_parts) + uint *used_key_parts= NULL) { 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_map const_key_parts=table->const_key_parts[idx]; int reverse=0; + uint key_parts; my_bool on_pk_suffix= FALSE; DBUG_ENTER("test_if_order_by_key"); @@ -17704,15 +18021,16 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, */ if (key_part == key_part_end && reverse == 0) { - *used_key_parts= 0; - DBUG_RETURN(1); + key_parts= 0; + reverse= 1; + goto ok; } } else DBUG_RETURN(0); } - if (key_part->field != field) + if (key_part->field != field || !field->part_of_sortkey.is_set(idx)) DBUG_RETURN(0); /* set flag to 1 if we can use read-next on key, else to -1 */ @@ -17728,7 +18046,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, uint used_key_parts_secondary= table->key_info[idx].key_parts; uint used_key_parts_pk= (uint) (key_part - table->key_info[table->s->primary_key].key_part); - *used_key_parts= used_key_parts_pk + used_key_parts_secondary; + key_parts= used_key_parts_pk + used_key_parts_secondary; if (reverse == -1 && (!(table->file->index_flags(idx, used_key_parts_secondary - 1, 1) & @@ -17739,11 +18057,14 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, } else { - *used_key_parts= (uint) (key_part - table->key_info[idx].key_part); + key_parts= (uint) (key_part - table->key_info[idx].key_part); if (reverse == -1 && - !(table->file->index_flags(idx, *used_key_parts-1, 1) & HA_READ_PREV)) + !(table->file->index_flags(idx, key_parts-1, 1) & HA_READ_PREV)) reverse= 0; // Index can't be used } +ok: + if (used_key_parts != NULL) + *used_key_parts= key_parts; DBUG_RETURN(reverse); } @@ -17826,7 +18147,6 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, uint nr; uint min_length= (uint) ~0; uint best= MAX_KEY; - uint not_used; KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part; KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts; @@ -17837,7 +18157,7 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, table->key_info[nr].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, ¬_used)) + test_if_order_by_key(order, table, nr)) { min_length= table->key_info[nr].key_length; best= nr; @@ -17997,11 +18317,11 @@ find_field_in_item_list (Field *field, void *data) */ static bool -test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit_arg, +test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, bool no_changes, const key_map *map) { int ref_key; - uint ref_key_parts; + uint UNINIT_VAR(ref_key_parts); int order_direction= 0; uint used_key_parts= 0; TABLE *table=tab->table; @@ -18012,12 +18332,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit_arg, bool orig_cond_saved= false; int best_key= -1; bool changed_key= false; - ha_rows best_select_limit; DBUG_ENTER("test_if_skip_sort_order"); - LINT_INIT(ref_key_parts); - LINT_INIT(best_select_limit); - /* Check that we are always called with first non-const table */ DBUG_ASSERT(tab == tab->join->join_tab + tab->join->const_tables); @@ -18162,229 +18478,17 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit_arg, goto check_reverse_order; } { - /* - Check whether there is an index compatible with the given order - usage of which is cheaper than usage of the ref_key index (ref_key>=0) - or a table scan. - It may be the case if ORDER/GROUP BY is used with LIMIT. - */ - uint nr; - key_map keys; - uint best_key_parts; + uint UNINIT_VAR(best_key_parts); uint saved_best_key_parts= 0; - int best_key_direction; - ha_rows best_records; - double read_time; - bool is_best_covering= FALSE; - double fanout= 1; + int best_key_direction= 0; JOIN *join= tab->join; - uint tablenr= tab - join->join_tab; ha_rows table_records= table->file->stats.records; - bool group= join->group && order == join->group_list; - ha_rows ref_key_quick_rows= HA_POS_ERROR; - LINT_INIT(best_key_parts); - LINT_INIT(best_key_direction); - LINT_INIT(best_records); - /* - If not used with LIMIT, only use keys if the whole query can be - resolved with a key; This is because filesort() is usually faster than - retrieving all rows through an index. - */ - if (select_limit_arg >= table_records) - { - keys= *table->file->keys_to_use_for_scanning(); - keys.merge(table->covering_keys); - - /* - We are adding here also the index specified in FORCE INDEX clause, - if any. - This is to allow users to use index in ORDER BY. - */ - if (table->force_index) - keys.merge(group ? table->keys_in_use_for_group_by : - table->keys_in_use_for_order_by); - keys.intersect(usable_keys); - } - else - keys= usable_keys; - - if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) - ref_key_quick_rows= table->quick_rows[ref_key]; - - read_time= join->best_positions[tablenr].read_time; - for (uint i= tablenr+1; i < join->table_count; i++) - fanout*= join->best_positions[i].records_read; // fanout is always >= 1 - - for (nr=0; nr < table->s->keys ; nr++) - { - int direction; - ha_rows select_limit= select_limit_arg; - - if (keys.is_set(nr) && - (direction= test_if_order_by_key(order, table, nr, &used_key_parts))) - { - /* - At this point we are sure that ref_key is a non-ordering - key (where "ordering key" is a key that will return rows - in the order required by ORDER BY). - */ - DBUG_ASSERT (ref_key != (int) nr); - - bool is_covering= (table->covering_keys.is_set(nr) || - (table->file->index_flags(nr, 0, 1) & - HA_CLUSTERED_INDEX)); - - /* - Don't use an index scan with ORDER BY without limit. - For GROUP BY without limit always use index scan - if there is a suitable index. - Why we hold to this asymmetry hardly can be explained - rationally. It's easy to demonstrate that using - temporary table + filesort could be cheaper for grouping - queries too. - */ - if (is_covering || - select_limit != HA_POS_ERROR || - (ref_key < 0 && (group || table->force_index))) - { - double rec_per_key; - double index_scan_time; - KEY *keyinfo= tab->table->key_info+nr; - if (select_limit == HA_POS_ERROR) - select_limit= table_records; - if (group) - { - /* - Used_key_parts can be larger than keyinfo->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_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; - /* Take into account the selectivity of the used pk prefix */ - if (used_pk_parts) - { - KEY *pkinfo=tab->table->key_info+table->s->primary_key; - /* - If the values of of records per key for the prefixes - of the primary key are considered unknown we assume - they are equal to 1. - */ - if (used_key_parts == pkinfo->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]; - /* - 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++) - { - if (pkinfo->key_part[i].field->key_start.is_set(nr)) - { - /* - 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]); - rec_per_key*= pkinfo->rec_per_key[i-1]; - rec_per_key/= pkinfo->rec_per_key[i]; - } - } - } - } - set_if_bigger(rec_per_key, 1); - /* - With a grouping query each group containing on average - rec_per_key records produces only one row that will - be included into the result set. - */ - if (select_limit > table_records/rec_per_key) - select_limit= table_records; - else - select_limit= (ha_rows) (select_limit*rec_per_key); - } /* group */ - - /* - If tab=tk is not the last joined table tn then to get first - L records from the result set we can expect to retrieve - only L/fanout(tk,tn) where fanout(tk,tn) says how many - rows in the record set on average will match each row tk. - Usually our estimates for fanouts are too pessimistic. - So the estimate for L/fanout(tk,tn) will be too optimistic - and as result we'll choose an index scan when using ref/range - access + filesort will be cheaper. - */ - select_limit= (ha_rows) (select_limit < fanout ? - 1 : select_limit/fanout); - /* - We assume that each of the tested indexes is not correlated - 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. - 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) - 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]; - set_if_bigger(rec_per_key, 1); - /* - Here we take into account the fact that rows are - accessed in sequences rec_per_key records in each. - Rows in such a sequence are supposed to be ordered - by rowid/primary key. When reading the data - in a sequence we'll touch not more pages than the - table file contains. - TODO. Use the formula for a disk sweep sequential access - to calculate the cost of accessing data rows for one - index entry. - */ - index_scan_time= select_limit/rec_per_key * - 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; - if ((is_best_covering && !is_covering) || - (is_covering && ref_key_quick_rows < 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 : - quick_records < best_records) || - (!is_best_covering && is_covering)) - { - best_key= nr; - best_key_parts= keyinfo->key_parts; - saved_best_key_parts= used_key_parts; - best_records= quick_records; - is_best_covering= is_covering; - best_key_direction= direction; - best_select_limit= select_limit; - } - } - } - } - } + test_if_cheaper_ordering(tab, order, table, usable_keys, + ref_key, select_limit, + &best_key, &best_key_direction, + &select_limit, &best_key_parts, + &saved_best_key_parts); /* filesort() and join cache are usually faster than reading in @@ -18392,7 +18496,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit_arg, index is clustered key. */ if (best_key < 0 || - ((select_limit_arg >= table_records) && + ((select_limit >= table_records) && (tab->type == JT_ALL && tab->join->table_count > tab->join->const_tables + 1) && !(table->file->index_flags(best_key, 0, 1) & HA_CLUSTERED_INDEX))) @@ -18494,8 +18598,8 @@ check_reverse_order: { tab->ref.key= -1; tab->ref.key_parts= 0; - if (best_select_limit < table->file->stats.records) - tab->limit= best_select_limit; + if (select_limit < table->file->stats.records) + tab->limit= select_limit; } } else if (tab->type != JT_ALL) @@ -18533,22 +18637,16 @@ check_reverse_order: { if (select && select->quick) { - QUICK_SELECT_DESC *tmp; - bool error= FALSE; - /* ORDER BY range_key DESC */ - tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick), - used_key_parts, &error); - if (tmp && select->quick == save_quick) - save_quick= 0; // ::QUICK_SELECT_DESC consumed it - - if (!tmp || error) + QUICK_SELECT_I *tmp= select->quick->make_reverse(used_key_parts); + if (!tmp) { - delete tmp; tab->limit= 0; goto use_filesort; // Reverse sort failed -> filesort } - select->quick= tmp; + if (select->quick == save_quick) + save_quick= 0; // make_reverse() consumed it + select->set_quick(tmp); } else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL && tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts) @@ -18608,7 +18706,7 @@ use_filesort: SYNOPSIS create_sort_index() thd Thread handler - tab Table to sort (in join structure) + join Join with table to sort order How table should be sorted filesort_limit Max number of rows that needs to be sorted select_limit Max number of rows in final output @@ -18618,8 +18716,8 @@ use_filesort: IMPLEMENTATION - - If there is an index that can be used, 'tab' is modified to use - this index. + - If there is an index that can be used, the first non-const join_tab in + 'join' is modified to use this index. - If no index, create with filesort() an index file that can be used to retrieve rows in order (should be done with 'read_record'). The sorted data is stored in tab->table and will be freed when calling @@ -18752,49 +18850,6 @@ err: DBUG_RETURN(-1); } -#ifdef NOT_YET -/** - Add the HAVING criteria to table->select. -*/ - -static bool fix_having(JOIN *join, Item **having) -{ - (*having)->update_used_tables(); // Some tables may have been const - JOIN_TAB *table=&join->join_tab[join->const_tables]; - table_map used_tables= join->const_table_map | table->table->map; - - DBUG_EXECUTE("where",print_where(*having,"having", QT_ORDINARY);); - Item* sort_table_cond= make_cond_for_table(join->thd, *having, used_tables, - used_tables, MAX_TABLES, - FALSE, FALSE); - if (sort_table_cond) - { - if (!table->select) - if (!(table->select=new SQL_SELECT)) - return 1; - if (!table->select->cond) - table->select->cond=sort_table_cond; - else // This should never happen - if (!(table->select->cond= new Item_cond_and(table->select->cond, - sort_table_cond)) || - table->select->cond->fix_fields(join->thd, &table->select->cond)) - return 1; - table->set_select_cond(table->select->cond, __LINE__); - table->select_cond->top_level_item(); - DBUG_EXECUTE("where",print_where(table->select_cond, - "select and having", - QT_ORDINARY);); - *having= make_cond_for_table(join->thd, *having, - ~ (table_map) 0,~used_tables, - MAX_TABLES, FALSE, FALSE); - DBUG_EXECUTE("where", - print_where(*having,"having after make_cond", QT_ORDINARY);); - } - return 0; -} -#endif - - /***************************************************************************** Remove duplicates from tmp table This should be recoded to add a unique index to the table and remove @@ -18841,6 +18896,7 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having) ulong reclength,offset; uint field_count; THD *thd= join->thd; + DBUG_ENTER("remove_duplicates"); entry->reginfo.lock_type=TL_WRITE; @@ -18866,6 +18922,13 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having) offset(entry->record[0]) : 0); reclength=entry->s->reclength-offset; + /* + Disable LIMIT ROWS EXAMINED in order to avoid interrupting prematurely + duplicate removal, and produce a possibly incomplete query result. + */ + thd->lex->limit_rows_examined_cnt= ULONGLONG_MAX; + if (thd->killed == ABORT_QUERY) + thd->killed= NOT_KILLED; free_io_cache(entry); // Safety entry->file->info(HA_STATUS_VARIABLE); if (entry->s->db_type() == heap_hton || @@ -18879,6 +18942,8 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having) error=remove_dup_with_compare(join->thd, entry, first_field, offset, having); + if (join->select_lex != join->select_lex->master_unit()->fake_select_lex) + thd->lex->set_limit_rows_examined(); free_blobs(first_field); DBUG_RETURN(error); } @@ -19021,10 +19086,10 @@ static int remove_dup_with_hash_index(THD *thd, TABLE *table, extra_length= ALIGN_SIZE(key_length)-key_length; } - if (hash_init(&hash, &my_charset_bin, (uint) file->stats.records, 0, - key_length, (hash_get_key) 0, 0, 0)) + if (my_hash_init(&hash, &my_charset_bin, (uint) file->stats.records, 0, + key_length, (my_hash_get_key) 0, 0, 0)) { - my_free((char*) key_buffer,MYF(0)); + my_free(key_buffer); DBUG_RETURN(1); } @@ -19065,7 +19130,7 @@ static int remove_dup_with_hash_index(THD *thd, TABLE *table, key_pos+= *field_length++; } /* Check if it exists before */ - if (hash_search(&hash, org_key_pos, key_length)) + if (my_hash_search(&hash, org_key_pos, key_length)) { /* Duplicated found ; Remove the row */ if ((error= file->ha_delete_row(record))) @@ -19078,15 +19143,15 @@ static int remove_dup_with_hash_index(THD *thd, TABLE *table, } key_pos+=extra_length; } - my_free((char*) key_buffer,MYF(0)); - hash_free(&hash); + my_free(key_buffer); + my_hash_free(&hash); file->extra(HA_EXTRA_NO_CACHE); (void) file->ha_rnd_end(); DBUG_RETURN(0); err: - my_free((char*) key_buffer,MYF(0)); - hash_free(&hash); + my_free(key_buffer); + my_hash_free(&hash); file->extra(HA_EXTRA_NO_CACHE); (void) file->ha_rnd_end(); if (error) @@ -19134,7 +19199,6 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length, } - /* eq_ref: Create the lookup key and check if it is the same as saved key @@ -19556,7 +19620,7 @@ setup_new_fields(THD *thd, List<Item> &fields, optimize away 'order by'. */ -static ORDER * +ORDER * create_distinct_group(THD *thd, Item **ref_pointer_array, ORDER *order_list, List<Item> &fields, List<Item> &all_fields, @@ -19822,8 +19886,7 @@ calc_group_buffer(JOIN *join,ORDER *group) default: /* This case should never be choosen */ DBUG_ASSERT(0); - my_error(ER_OUT_OF_RESOURCES, MYF(0)); - join->thd->fatal_error(); + my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR)); } } parts++; @@ -19947,7 +20010,7 @@ test_if_group_changed(List<Cached_item> &list) Only FIELD_ITEM:s and FUNC_ITEM:s needs to be saved between groups. Change old item_field to use a new field with points at saved fieldvalue - This function is only called before use of send_fields. + This function is only called before use of send_result_set_metadata. @param thd THD pointer @param param temporary table parameters @@ -19978,7 +20041,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, Item *pos; List_iterator_fast<Item> li(all_fields); Copy_field *copy= NULL; - IF_DBUG(Copy_field *copy_start); + Copy_field *copy_start __attribute__((unused)); res_selected_fields.empty(); res_all_fields.empty(); List_iterator_fast<Item> itr(res_all_fields); @@ -19991,7 +20054,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, goto err2; param->copy_funcs.empty(); - IF_DBUG(copy_start= copy); + copy_start= copy; for (i= 0; (pos= li++); i++) { Field *field; @@ -20187,7 +20250,7 @@ bool JOIN::alloc_func_list() Initialize 'sum_funcs' array with all Item_sum objects. @param field_list All items - @param send_fields Items in select list + @param send_result_set_metadata Items in select list @param before_group_by Set to 1 if this is called before GROUP BY handling @param recompute Set to TRUE if sum_funcs must be recomputed @@ -20197,7 +20260,7 @@ bool JOIN::alloc_func_list() 1 error */ -bool JOIN::make_sum_func_list(List<Item> &field_list, List<Item> &send_fields, +bool JOIN::make_sum_func_list(List<Item> &field_list, List<Item> &send_result_set_metadata, bool before_group_by, bool recompute) { List_iterator_fast<Item> it(field_list); @@ -20219,7 +20282,7 @@ bool JOIN::make_sum_func_list(List<Item> &field_list, List<Item> &send_fields, if (before_group_by && rollup.state == ROLLUP::STATE_INITED) { rollup.state= ROLLUP::STATE_READY; - if (rollup_make_fields(field_list, send_fields, &func)) + if (rollup_make_fields(field_list, send_result_set_metadata, &func)) DBUG_RETURN(TRUE); // Should never happen } else if (rollup.state == ROLLUP::STATE_NONE) @@ -20395,7 +20458,22 @@ static bool setup_sum_funcs(THD *thd, Item_sum **func_ptr) DBUG_ENTER("setup_sum_funcs"); while ((func= *(func_ptr++))) { - if (func->setup(thd)) + if (func->aggregator_setup(thd)) + DBUG_RETURN(TRUE); + } + DBUG_RETURN(FALSE); +} + + +static bool prepare_sum_aggregators(Item_sum **func_ptr, bool need_distinct) +{ + Item_sum *func; + DBUG_ENTER("prepare_sum_aggregators"); + while ((func= *(func_ptr++))) + { + if (func->set_aggregator(need_distinct && func->has_with_distinct() ? + Aggregator::DISTINCT_AGGREGATOR : + Aggregator::SIMPLE_AGGREGATOR)) DBUG_RETURN(TRUE); } DBUG_RETURN(FALSE); @@ -20439,13 +20517,13 @@ init_sum_functions(Item_sum **func_ptr, Item_sum **end_ptr) { for (; func_ptr != end_ptr ;func_ptr++) { - if ((*func_ptr)->reset()) + if ((*func_ptr)->reset_and_add()) return 1; } /* If rollup, calculate the upper sum levels */ for ( ; *func_ptr ; func_ptr++) { - if ((*func_ptr)->add()) + if ((*func_ptr)->aggregator_add()) return 1; } return 0; @@ -20457,7 +20535,7 @@ update_sum_func(Item_sum **func_ptr) { Item_sum *func; for (; (func= (Item_sum*) *func_ptr) ; func_ptr++) - if (func->add()) + if (func->aggregator_add()) return 1; return 0; } @@ -21340,6 +21418,11 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, length= (longlong10_to_str(key_len, keylen_str_buf, 10) - keylen_str_buf); tmp3.append(keylen_str_buf, length, cs); +/*<<<<<<< TREE + } + if ((is_hj || tab->type==JT_RANGE || tab->type == JT_INDEX_MERGE) && + tab->select && tab->select->quick) +=======*/ } if (tab->type != JT_CONST && tab->select && tab->select->quick) tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3); @@ -21509,7 +21592,11 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, { const COND *pushed_cond= tab->table->file->pushed_cond; - if (thd->variables.engine_condition_pushdown && 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) { extra.append(STRING_WITH_LEN("; Using where with pushed " "condition")); @@ -21545,7 +21632,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, if (key_read) { if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + 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); + } else extra.append(STRING_WITH_LEN("; Using index")); } @@ -21722,7 +21814,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) first->group_list.first, first->having, thd->lex->proc_list.first, - first->options | thd->options | SELECT_DESCRIBE, + first->options | thd->variables.option_bits | SELECT_DESCRIBE, result, unit, first); } DBUG_RETURN(res || thd->is_error()); @@ -21751,6 +21843,8 @@ static void print_table_array(THD *thd, (curr->nested_join && !(curr->nested_join->used_tables & ~eliminated_tables)))) { + /* as of 5.5, print_join doesnt put eliminated elements into array */ + DBUG_ASSERT(0); continue; } @@ -21776,6 +21870,21 @@ static void print_table_array(THD *thd, } +/* + Check if the passed table is + - a base table which was eliminated, or + - a join nest which only contained eliminated tables (and so was eliminated, + too) +*/ + +static bool is_eliminated_table(table_map eliminated_tables, TABLE_LIST *tbl) +{ + return eliminated_tables && + ((tbl->table && (tbl->table->map & eliminated_tables)) || + (tbl->nested_join && !(tbl->nested_join->used_tables & + ~eliminated_tables))); +} + /** Print joins from the FROM clause. @@ -21793,13 +21902,36 @@ 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= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) * - tables->elements); - if (table == 0) + TABLE_LIST **table; + uint non_const_tables= 0; + + for (TABLE_LIST *t= ti++; t ; t= ti++) + { + /* + See comment in print_table_array() about the second part of the + condition + */ + if (!t->optimized_away && !is_eliminated_table(eliminated_tables, t)) + non_const_tables++; + } + if (!non_const_tables) + { + str->append(STRING_WITH_LEN("dual")); + return; // all tables were optimized away + } + ti.rewind(); + + if (!(table= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) * + non_const_tables))) return; // out of memory - for (TABLE_LIST **t= table + (tables->elements - 1); t >= table; t--) - *t= ti++; + TABLE_LIST *tmp, **t= table + (non_const_tables - 1); + while ((tmp= ti++)) + { + if (tmp->optimized_away || is_eliminated_table(eliminated_tables, tmp)) + continue; + *t--= tmp; + } DBUG_ASSERT(tables->elements >= 1); /* @@ -21816,7 +21948,7 @@ static void print_join(THD *thd, */ if ((*table)->sj_inner_tables) { - TABLE_LIST **end= table + tables->elements; + TABLE_LIST **end= table + non_const_tables; for (TABLE_LIST **t2= table; t2!=end; t2++) { if (!(*t2)->sj_inner_tables) @@ -21829,7 +21961,7 @@ static void print_join(THD *thd, } } print_table_array(thd, eliminated_tables, str, table, - table + tables->elements, query_type); + table + non_const_tables, query_type); } /** @@ -21999,8 +22131,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) /* First add options */ if (options & SELECT_STRAIGHT_JOIN) str->append(STRING_WITH_LEN("straight_join ")); - if ((thd->lex->lock_option == TL_READ_HIGH_PRIORITY) && - (this == &thd->lex->select_lex)) + if (options & SELECT_HIGH_PRIORITY) str->append(STRING_WITH_LEN("high_priority ")); if (options & SELECT_DISTINCT) str->append(STRING_WITH_LEN("distinct ")); @@ -22371,5 +22502,423 @@ JOIN::reoptimize(Item *added_where, table_map join_tables, /** + Cache constant expressions in WHERE, HAVING, ON conditions. +*/ + +void JOIN::cache_const_exprs() +{ + bool cache_flag= FALSE; + bool *analyzer_arg= &cache_flag; + + /* No need in cache if all tables are constant. */ + if (const_tables == table_count) + return; + + if (conds) + conds->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg, + &Item::cache_const_expr_transformer, (uchar *)&cache_flag); + cache_flag= FALSE; + if (having) + having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg, + &Item::cache_const_expr_transformer, (uchar *)&cache_flag); + + for (JOIN_TAB *tab= first_depth_first_tab(this); tab; + tab= next_depth_first_tab(this, tab)) + { + if (*tab->on_expr_ref) + { + cache_flag= FALSE; + (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer, + (uchar **)&analyzer_arg, + &Item::cache_const_expr_transformer, + (uchar *)&cache_flag); + } + } +} + + +/** + Find a cheaper access key than a given @a key + + @param tab NULL or JOIN_TAB of the accessed table + @param order Linked list of ORDER BY arguments + @param table Table if tab == NULL or tab->table + @param usable_keys Key map to find a cheaper key in + @param ref_key + * 0 <= key < MAX_KEY - key number (hint) to start the search + * -1 - no key number provided + @param select_limit LIMIT value + @param [out] new_key Key number if success, otherwise undefined + @param [out] new_key_direction Return -1 (reverse) or +1 if success, + otherwise undefined + @param [out] new_select_limit Return adjusted LIMIT + @param [out] new_used_key_parts NULL by default, otherwise return number + of new_key prefix columns if success + or undefined if the function fails + @param [out] saved_best_key_parts NULL by default, otherwise preserve the + value for further use in QUICK_SELECT_DESC + + @note + This function takes into account table->quick_condition_rows statistic + (that is calculated by the make_join_statistics function). + However, single table procedures such as mysql_update() and mysql_delete() + never call make_join_statistics, so they have to update it manually + (@see get_index_for_order()). +*/ + +static bool +test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, + key_map usable_keys, int ref_key, + ha_rows select_limit_arg, + int *new_key, int *new_key_direction, + ha_rows *new_select_limit, uint *new_used_key_parts, + uint *saved_best_key_parts) +{ + DBUG_ENTER("test_if_cheaper_ordering"); + /* + Check whether there is an index compatible with the given order + usage of which is cheaper than usage of the ref_key index (ref_key>=0) + or a table scan. + It may be the case if ORDER/GROUP BY is used with LIMIT. + */ + ha_rows best_select_limit= HA_POS_ERROR; + JOIN *join= tab ? tab->join : NULL; + uint nr; + key_map keys; + uint best_key_parts= 0; + int best_key_direction= 0; + ha_rows best_records= 0; + double read_time; + int best_key= -1; + bool is_best_covering= FALSE; + double fanout= 1; + ha_rows table_records= table->file->stats.records; + bool group= join && join->group && order == join->group_list; + ha_rows ref_key_quick_rows= HA_POS_ERROR; + + /* + If not used with LIMIT, only use keys if the whole query can be + resolved with a key; This is because filesort() is usually faster than + retrieving all rows through an index. + */ + if (select_limit_arg >= table_records) + { + keys= *table->file->keys_to_use_for_scanning(); + keys.merge(table->covering_keys); + + /* + We are adding here also the index specified in FORCE INDEX clause, + if any. + This is to allow users to use index in ORDER BY. + */ + if (table->force_index) + keys.merge(group ? table->keys_in_use_for_group_by : + table->keys_in_use_for_order_by); + keys.intersect(usable_keys); + } + else + keys= usable_keys; + + if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) + ref_key_quick_rows= table->quick_rows[ref_key]; + + if (join) + { + uint tablenr= tab - join->join_tab; + read_time= join->best_positions[tablenr].read_time; + for (uint i= tablenr+1; i < join->table_count; i++) + fanout*= join->best_positions[i].records_read; // fanout is always >= 1 + } + else + read_time= table->file->scan_time(); + + for (nr=0; nr < table->s->keys ; nr++) + { + int direction; + ha_rows select_limit= select_limit_arg; + uint used_key_parts; + + if (keys.is_set(nr) && + (direction= test_if_order_by_key(order, table, nr, &used_key_parts))) + { + /* + At this point we are sure that ref_key is a non-ordering + key (where "ordering key" is a key that will return rows + in the order required by ORDER BY). + */ + DBUG_ASSERT (ref_key != (int) nr); + + bool is_covering= (table->covering_keys.is_set(nr) || + (table->file->index_flags(nr, 0, 1) & + HA_CLUSTERED_INDEX)); + /* + Don't use an index scan with ORDER BY without limit. + For GROUP BY without limit always use index scan + if there is a suitable index. + Why we hold to this asymmetry hardly can be explained + rationally. It's easy to demonstrate that using + temporary table + filesort could be cheaper for grouping + queries too. + */ + if (is_covering || + select_limit != HA_POS_ERROR || + (ref_key < 0 && (group || table->force_index))) + { + double rec_per_key; + double index_scan_time; + KEY *keyinfo= table->key_info+nr; + if (select_limit == HA_POS_ERROR) + select_limit= table_records; + if (group) + { + /* + Used_key_parts can be larger than keyinfo->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_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; + /* Take into account the selectivity of the used pk prefix */ + if (used_pk_parts) + { + KEY *pkinfo=tab->table->key_info+table->s->primary_key; + /* + If the values of of records per key for the prefixes + of the primary key are considered unknown we assume + they are equal to 1. + */ + if (used_key_parts == pkinfo->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]; + /* + 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++) + { + if (pkinfo->key_part[i].field->key_start.is_set(nr)) + { + /* + 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]); + rec_per_key*= pkinfo->rec_per_key[i-1]; + rec_per_key/= pkinfo->rec_per_key[i]; + } + } + } + } + set_if_bigger(rec_per_key, 1); + /* + With a grouping query each group containing on average + rec_per_key records produces only one row that will + be included into the result set. + */ + if (select_limit > table_records/rec_per_key) + select_limit= table_records; + else + select_limit= (ha_rows) (select_limit*rec_per_key); + } /* group */ + + /* + If tab=tk is not the last joined table tn then to get first + L records from the result set we can expect to retrieve + only L/fanout(tk,tn) where fanout(tk,tn) says how many + rows in the record set on average will match each row tk. + Usually our estimates for fanouts are too pessimistic. + So the estimate for L/fanout(tk,tn) will be too optimistic + and as result we'll choose an index scan when using ref/range + access + filesort will be cheaper. + */ + select_limit= (ha_rows) (select_limit < fanout ? + 1 : select_limit/fanout); + /* + We assume that each of the tested indexes is not correlated + 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. + 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) + 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]; + set_if_bigger(rec_per_key, 1); + /* + Here we take into account the fact that rows are + accessed in sequences rec_per_key records in each. + Rows in such a sequence are supposed to be ordered + by rowid/primary key. When reading the data + in a sequence we'll touch not more pages than the + table file contains. + TODO. Use the formula for a disk sweep sequential access + to calculate the cost of accessing data rows for one + index entry. + */ + index_scan_time= select_limit/rec_per_key * + 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; + if ((is_best_covering && !is_covering) || + (is_covering && ref_key_quick_rows < 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 : + quick_records < best_records) || + (!is_best_covering && is_covering)) + { + best_key= nr; + best_key_parts= keyinfo->key_parts; + if (saved_best_key_parts) + *saved_best_key_parts= used_key_parts; + best_records= quick_records; + is_best_covering= is_covering; + best_key_direction= direction; + best_select_limit= select_limit; + } + } + } + } + } + + if (best_key < 0 || best_key == ref_key) + DBUG_RETURN(FALSE); + + *new_key= best_key; + *new_key_direction= best_key_direction; + *new_select_limit= best_select_limit; + if (new_used_key_parts != NULL) + *new_used_key_parts= best_key_parts; + + DBUG_RETURN(TRUE); +} + + +/** + Find a key to apply single table UPDATE/DELETE by a given ORDER + + @param order Linked list of ORDER BY arguments + @param table Table to find a key + @param select Pointer to access/update select->quick (if any) + @param limit LIMIT clause parameter + @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) + + @return + - MAX_KEY if no key found (need_sort == TRUE) + - MAX_KEY if quick select result order is OK (need_sort == FALSE) + - key number (either index scan or quick select) (need_sort == FALSE) + + @note + Side effects: + - may deallocate or deallocate and replace select->quick; + - may set table->quick_condition_rows and table->quick_rows[...] + to table->file->stats.records. +*/ + +uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, + ha_rows limit, bool *need_sort, bool *reverse) +{ + if (!order) + { + *need_sort= FALSE; + if (select && select->quick) + return select->quick->index; // index or MAX_KEY, use quick select as is + else + return table->file->key_used_on_scan; // MAX_KEY or index for some engines + } + + if (!is_simple_order(order)) // just to cut further expensive checks + { + *need_sort= TRUE; + return MAX_KEY; + } + + if (select && select->quick) + { + if (select->quick->index == MAX_KEY) + { + *need_sort= TRUE; + return MAX_KEY; + } + + uint used_key_parts; + switch (test_if_order_by_key(order, table, select->quick->index, + &used_key_parts)) { + case 1: // desired order + *need_sort= FALSE; + return select->quick->index; + case 0: // unacceptable order + *need_sort= TRUE; + return MAX_KEY; + case -1: // desired order, but opposite direction + { + QUICK_SELECT_I *reverse_quick; + if ((reverse_quick= + select->quick->make_reverse(used_key_parts))) + { + select->set_quick(reverse_quick); + *need_sort= FALSE; + return select->quick->index; + } + else + { + *need_sort= TRUE; + return MAX_KEY; + } + } + } + DBUG_ASSERT(0); + } + else if (limit != HA_POS_ERROR) + { // check if some index scan & LIMIT is more efficient than filesort + + /* + 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; + + int key, direction; + if (test_if_cheaper_ordering(NULL, order, table, + table->keys_in_use_for_order_by, -1, + limit, + &key, &direction, &limit) && + !is_key_used(table, key, table->write_set)) + { + *need_sort= FALSE; + *reverse= (direction < 0); + return key; + } + } + *need_sort= TRUE; + return MAX_KEY; +} + + +/** @} (end of group Query_Optimizer) */ |