diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 604 |
1 files changed, 393 insertions, 211 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 171e9a7f7a5..34c61dc2c2b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1,5 +1,5 @@ -/* Copyright (c) 2000, 2010 Oracle and/or its affiliates. All rights reserved. - Copyright (c) 2009-2010 Monty Program Ab +/* Copyright (c) 2000, 2011 Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2009-2011 Monty Program Ab This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -51,6 +51,7 @@ #include "opt_subselect.h" #include "log_slow.h" +#include "debug_sync.h" // DEBUG_SYNC #include <m_ctype.h> #include <my_bit.h> #include <hash.h> @@ -309,57 +310,60 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, } -/* +/** Fix fields referenced from inner selects. - SYNOPSIS - fix_inner_refs() - thd Thread handle - all_fields List of all fields used in select - select Current select - ref_pointer_array Array of references to Items used in current select - group_list GROUP BY list (is NULL by default) + @param thd Thread handle + @param all_fields List of all fields used in select + @param select Current select + @param ref_pointer_array Array of references to Items used in current select + @param group_list GROUP BY list (is NULL by default) - DESCRIPTION - The function serves 3 purposes - adds fields referenced from inner - selects to the current select list, resolves which class to use - to access referenced item (Item_ref of Item_direct_ref) and fixes - references (Item_ref objects) to these fields. + @details + The function serves 3 purposes + + - adds fields referenced from inner query blocks to the current select list - If a field isn't already in the select list and the ref_pointer_array + - Decides which class to use to reference the items (Item_ref or + Item_direct_ref) + + - fixes references (Item_ref objects) to these fields. + + If a field isn't already on the select list and the ref_pointer_array is provided then it is added to the all_fields list and the pointer to it is saved in the ref_pointer_array. The class to access the outer field is determined by the following rules: - 1. If the outer field isn't used under an aggregate function - then the Item_ref class should be used. - 2. If the outer field is used under an aggregate function and this - function is aggregated in the select where the outer field was - resolved or in some more inner select then the Item_direct_ref - class should be used. - It used used also if we are grouping by a subquery that refers - this outer field. + + -#. If the outer field isn't used under an aggregate function then the + Item_ref class should be used. + + -#. If the outer field is used under an aggregate function and this + 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 that references this outer field. + The resolution is done here and not at the fix_fields() stage as - it can be done only after sum functions are fixed and pulled up to - selects where they are have to be aggregated. + it can be done only after aggregate functions are fixed and pulled up to + selects where they are to be aggregated. + When the class is chosen it substitutes the original field in the Item_outer_ref object. After this we proceed with fixing references (Item_outer_ref objects) to this field from inner subqueries. - RETURN - TRUE an error occured - FALSE ok -*/ + @return Status + @retval true An error occured. + @retval false OK. + */ bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, Item **ref_pointer_array) { Item_outer_ref *ref; - bool res= FALSE; - bool direct_ref= FALSE; /* Mark the references from the inner_refs_list that are occurred in @@ -376,6 +380,7 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, while ((ref= ref_it++)) { + bool direct_ref= false; Item *item= ref->outer_ref; Item **item_ref= ref->ref; Item_ref *new_ref; @@ -434,7 +439,7 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, return TRUE; thd->used_tables|= item->used_tables(); } - return res; + return false; } /** @@ -765,6 +770,7 @@ JOIN::optimize() if (optimized) DBUG_RETURN(0); optimized= 1; + DEBUG_SYNC(thd, "before_join_optimize"); thd_proc_info(thd, "optimizing"); @@ -903,7 +909,7 @@ JOIN::optimize() If all items were resolved by opt_sum_query, there is no need to open any tables. */ - if ((res=opt_sum_query(select_lex->leaf_tables, all_fields, conds))) + if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) { if (res == HA_ERR_KEY_NOT_FOUND) { @@ -1727,6 +1733,10 @@ JOIN::reinit() while ((item= it++)) item->restore_to_before_no_rows_in_result(); } + + if (!(select_options & SELECT_DESCRIBE)) + init_ftfuncs(thd, select_lex, test(order)); + DBUG_RETURN(0); } @@ -1938,7 +1948,11 @@ JOIN::exec() JOIN_TAB *first_tab= curr_join->join_tab + curr_join->const_tables; first_tab->sorted= test(first_tab->loosescan_match_tab); } - if ((tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table, 0))) + + Procedure *save_proc= curr_join->procedure; + tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table, 0); + curr_join->procedure= save_proc; + if (tmp_error) { error= tmp_error; DBUG_VOID_RETURN; @@ -2504,6 +2518,13 @@ mysql_select(THD *thd, Item ***rref_pointer_array, { DBUG_RETURN(TRUE); } + /* + Original join tabs might be overwritten at first + subselect execution. So we need to restore them. + */ + Item_subselect *subselect= select_lex->master_unit()->item; + if (subselect && subselect->is_uncacheable() && join->reinit()) + DBUG_RETURN(TRUE); } else { @@ -3499,26 +3520,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond, eq_func is NEVER true when num_values > 1 */ if (!eq_func) - { - /* - Additional optimization: if we're processing - "t.key BETWEEN c1 AND c1" then proceed as if we were processing - "t.key = c1". - TODO: This is a very limited fix. A more generic fix is possible. - There are 2 options: - A) Make equality propagation code be able to handle BETWEEN - (including cases like t1.key BETWEEN t2.key AND t3.key) - B) Make range optimizer to infer additional "t.key = c" equalities - and use them in equality propagation process (see details in - OptimizerKBAndTodo) - */ - if ((cond->functype() != Item_func::BETWEEN) || - ((Item_func_between*) cond)->negated || - !value[0]->eq(value[1], field->binary())) - return; - eq_func= TRUE; - } - + return; if (field->result_type() == STRING_RESULT) { if ((*value)->result_type() != STRING_RESULT) @@ -3741,9 +3743,65 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, case Item_func::OPTIMIZE_KEY: { Item **values; - // BETWEEN, IN, NE - if (is_local_field (cond_func->key_item()) && - !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) + /* + Build list of possible keys for 'a BETWEEN low AND high'. + It is handled similar to the equivalent condition + 'a >= low AND a <= high': + */ + if (cond_func->functype() == Item_func::BETWEEN) + { + Item_field *field_item; + bool equal_func= FALSE; + uint num_values= 2; + values= cond_func->arguments(); + + bool binary_cmp= (values[0]->real_item()->type() == Item::FIELD_ITEM) + ? ((Item_field*)values[0]->real_item())->field->binary() + : TRUE; + + /* + Additional optimization: If 'low = high': + Handle as if the condition was "t.key = low". + */ + if (!((Item_func_between*)cond_func)->negated && + values[1]->eq(values[2], binary_cmp)) + { + equal_func= TRUE; + num_values= 1; + } + + /* + Append keys for 'field <cmp> value[]' if the + condition is of the form:: + '<field> BETWEEN value[1] AND value[2]' + */ + if (is_local_field (values[0])) + { + field_item= (Item_field *) (values[0]->real_item()); + add_key_equal_fields(key_fields, *and_level, cond_func, + field_item, equal_func, &values[1], + num_values, usable_tables, sargables); + } + /* + Append keys for 'value[0] <cmp> field' if the + condition is of the form: + 'value[0] BETWEEN field1 AND field2' + */ + for (uint i= 1; i <= num_values; i++) + { + if (is_local_field (values[i])) + { + field_item= (Item_field *) (values[i]->real_item()); + add_key_equal_fields(key_fields, *and_level, cond_func, + field_item, equal_func, values, + 1, usable_tables, sargables); + } + } + } // if ( ... Item_func::BETWEEN) + + // IN, NE + else if (is_local_field (cond_func->key_item()) && + !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) { values= cond_func->arguments()+1; if (cond_func->functype() == Item_func::NE_FUNC && @@ -3757,21 +3815,6 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, cond_func->argument_count()-1, usable_tables, sargables); } - if (cond_func->functype() == Item_func::BETWEEN) - { - values= cond_func->arguments(); - for (uint i= 1 ; i < cond_func->argument_count() ; i++) - { - Item_field *field_item; - if (is_local_field (cond_func->arguments()[i])) - { - field_item= (Item_field *) (cond_func->arguments()[i]->real_item()); - add_key_equal_fields(key_fields, *and_level, cond_func, - field_item, 0, values, 1, usable_tables, - sargables); - } - } - } break; } case Item_func::OPTIMIZE_OP: @@ -4239,8 +4282,12 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, continue; } -#ifdef HAVE_valgrind - /* Valgrind complains about overlapped memcpy when save_pos==use. */ +#if defined(__GNUC__) && !MY_GNUC_PREREQ(4,4) + /* + Old gcc used a memcpy(), which is undefined if save_pos==use: + http://gcc.gnu.org/bugzilla/show_bug.cgi?id=19410 + http://gcc.gnu.org/bugzilla/show_bug.cgi?id=39480 + */ if (save_pos != use) #endif *save_pos= *use; @@ -6894,9 +6941,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) tab->set_select_cond(tmp, __LINE__); /* Push condition to storage engine if this is enabled and the condition is not guarded */ - tab->table->file->pushed_cond= NULL; - if ((thd->variables.optimizer_switch & - OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) && !first_inner_tab) + if (tab->table && (thd->variables.optimizer_switch & + OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) && + !first_inner_tab) { COND *push_cond= make_cond_for_table(tmp, current_map, current_map, FALSE); @@ -6920,10 +6967,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 || - (uint) tab->ref.key == tab->quick->index)) - { + 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)) + { + 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(); @@ -10670,24 +10719,41 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) Field *field=((Item_field*) args[0])->field; /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ /* - datetime_field IS NULL has to be modified to - datetime_field == 0 + 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) && !field->table->maybe_null) + (field->flags & NOT_NULL_FLAG)) { - 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); - } + 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()) @@ -11235,7 +11301,12 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, convert_blob_length); if (orig_type == Item::REF_ITEM && orig_modify) ((Item_ref*)orig_item)->set_result_field(result); - if (field->field->eq_def(result)) + /* + Fields that are used as arguments to the DEFAULT() function already have + their data pointers set to the default value during name resulotion. See + Item_default_value::fix_fields. + */ + if (orig_type != Item::DEFAULT_VALUE_ITEM && field->field->eq_def(result)) *default_field= field->field; return result; } @@ -11939,6 +12010,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; @@ -13462,7 +13534,6 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, condition is true => a match is found. */ bool found= 1; - bool use_not_exists_opt= 0; while (join_tab->first_unmatched && found) { /* @@ -13478,17 +13549,29 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, first_unmatched->found= 1; for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++) { - if (tab->table->reginfo.not_exists_optimize) - use_not_exists_opt= 1; /* Check all predicates that has just been activated. */ /* Actually all predicates non-guarded by first_unmatched->found 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; else @@ -13498,6 +13581,20 @@ 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) + { + /* + 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); } } @@ -13512,8 +13609,6 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, join_tab->first_unmatched= first_unmatched; } - if (use_not_exists_opt) - DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); JOIN_TAB *return_tab= join->return_tab; join_tab->found_match= TRUE; @@ -13727,7 +13822,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); } /* @@ -13753,7 +13848,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); } } @@ -14342,10 +14437,14 @@ end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), } if (join->having && join->having->val_int() == 0) DBUG_RETURN(NESTED_LOOP_OK); // Didn't match having - error=0; if (join->procedure) - error=join->procedure->send_row(join->procedure_fields_list); - else if (join->do_send_rows) + { + if (join->procedure->send_row(join->procedure_fields_list)) + DBUG_RETURN(NESTED_LOOP_ERROR); + DBUG_RETURN(NESTED_LOOP_OK); + } + error=0; + if (join->do_send_rows) error=join->result->send_data(*join->fields); if (error) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ @@ -14841,6 +14940,42 @@ bool test_if_ref(Item *root_cond, Item_field *left_item,Item *right_item) return 0; // keep test } +/** + Extract a condition that can be checked after reading given table + + @param cond Condition to analyze + @param tables Tables for which "current field values" are available + @param used_table Table that we're extracting the condition for (may + also include PSEUDO_TABLE_BITS, and may be zero) + @param exclude_expensive_cond Do not push expensive conditions + + @retval <>NULL Generated condition + @retval =NULL Already checked, OR error + + @details + Extract the condition that can be checked after reading the table + specified in 'used_table', given that current-field values for tables + specified in 'tables' bitmap are available. + If 'used_table' is 0 + - extract conditions for all tables in 'tables'. + - extract conditions are unrelated to any tables + in the same query block/level(i.e. conditions + which have used_tables == 0). + + The function assumes that + - Constant parts of the condition has already been checked. + - Condition that could be checked for tables in 'tables' has already + been checked. + + The function takes into account that some parts of the condition are + guaranteed to be true by employed 'ref' access methods (the code that + does this is located at the end, search down for "EQ_FUNC"). + + @note + Make sure to keep the implementations of make_cond_for_table() and + make_cond_after_sjm() synchronized. + make_cond_for_info_schema() uses similar algorithm as well. +*/ /* @@ -15060,7 +15195,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(); @@ -15384,6 +15519,8 @@ static bool list_contains_unique_index(TABLE *table, bool (*find_func) (Field *, void *), void *data) { + if (table->pos_in_table_list->outer_join) + return 0; for (uint keynr= 0; keynr < table->s->keys; keynr++) { if (keynr == table->s->primary_key || @@ -15397,7 +15534,7 @@ list_contains_unique_index(TABLE *table, key_part < key_part_end; key_part++) { - if (key_part->field->maybe_null() || + if (key_part->field->real_maybe_null() || !find_func(key_part->field, data)) break; } @@ -15503,13 +15640,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, { int ref_key; uint ref_key_parts; - int order_direction; + int order_direction= 0; uint used_key_parts; TABLE *table=tab->table; SQL_SELECT *select=tab->select; key_map usable_keys; QUICK_SELECT_I *save_quick= 0; COND *orig_select_cond= 0; + int best_key= -1; + DBUG_ENTER("test_if_skip_sort_order"); LINT_INIT(ref_key_parts); @@ -15619,6 +15758,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, new_ref_key_map.clear_all(); // Force the creation of quick select new_ref_key_map.set_bit(new_ref_key); // only for new_ref_key. + select->quick= 0; if (select->test_quick_select(tab->join->thd, new_ref_key_map, 0, (tab->join->select_options & OPTION_FOUND_ROWS) ? @@ -15640,8 +15780,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, { uint UNINIT_VAR(best_key_parts); uint saved_best_key_parts= 0; - int UNINIT_VAR(best_key_direction); - int best_key= -1; + int best_key_direction= 0; JOIN *join= tab->join; ha_rows table_records= table->file->stats.records; @@ -15661,84 +15800,21 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, tab->join->tables > tab->join->const_tables + 1) && ((unsigned) best_key != table->s->primary_key || !table->file->primary_key_is_clustered())) - DBUG_RETURN(0); + goto use_filesort; if (best_key >= 0) { - bool quick_created= FALSE; if (table->quick_keys.is_set(best_key) && best_key != ref_key) { key_map map; map.clear_all(); // Force the creation of quick select map.set_bit(best_key); // only best_key. - quick_created= - select->test_quick_select(join->thd, map, 0, - join->select_options & OPTION_FOUND_ROWS ? - HA_POS_ERROR : - join->unit->select_limit_cnt, - TRUE, FALSE) > 0; - } - if (!no_changes) - { - /* - If ref_key used index tree reading only ('Using index' in EXPLAIN), - and best_key doesn't, then revert the decision. - */ - if (!table->covering_keys.is_set(best_key)) - table->disable_keyread(); - if (!quick_created) - { - tab->index= best_key; - tab->read_first_record= best_key_direction > 0 ? - join_read_first:join_read_last; - tab->type=JT_NEXT; // Read with index_first(), index_next() - if (select && select->quick) - { - delete select->quick; - select->quick= 0; - } - if (table->covering_keys.is_set(best_key) && ! table->key_read) - table->enable_keyread(); - if (tab->pre_idx_push_select_cond) - { - COND *tmp_cond= tab->pre_idx_push_select_cond; - if (orig_select_cond) - { - tmp_cond= and_conds(tmp_cond, orig_select_cond); - tmp_cond->quick_fix_field(); - } - tab->set_cond(tmp_cond); - /* orig_select_cond was merged, no need to restore original one. */ - orig_select_cond= 0; - } - table->file->ha_index_or_rnd_end(); - if (join->select_options & SELECT_DESCRIBE) - { - tab->ref.key= -1; - tab->ref.key_parts= 0; - if (select_limit < table_records) - tab->limit= select_limit; - } - } - else if (tab->type != JT_ALL) - { - /* - We're about to use a quick access to the table. - We need to change the access method so as the quick access - method is actually used. - */ - DBUG_ASSERT(tab->select->quick); - tab->type=JT_ALL; - tab->use_quick=1; - tab->ref.key= -1; - tab->ref.key_parts=0; // Don't use ref key. - tab->read_first_record= join_init_read_record; - if (tab->is_using_loose_index_scan()) - join->tmp_table_param.precomputed_group_by= TRUE; - /* - TODO: update the number of records in join->best_positions[tablenr] - */ - } + select->quick= 0; + select->test_quick_select(join->thd, map, 0, + join->select_options & OPTION_FOUND_ROWS ? + HA_POS_ERROR : + join->unit->select_limit_cnt, + TRUE, FALSE) > 0; } order_direction= best_key_direction; /* @@ -15751,10 +15827,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, saved_best_key_parts : best_key_parts; } else - goto use_filesort; + goto use_filesort; } check_reverse_order: + DBUG_ASSERT(order_direction != 0); + if (order_direction == -1) // If ORDER BY ... DESC { if (select && select->quick) @@ -15763,9 +15841,10 @@ check_reverse_order: Don't reverse the sort order, if it's already done. (In some cases test_if_order_by_key() can be called multiple times */ - if (!select->quick->reverse_sorted()) + if (select->quick->reverse_sorted()) + goto skipped_filesort; + else { - QUICK_SELECT_I *tmp; int quick_type= select->quick->get_type(); if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || @@ -15773,40 +15852,141 @@ check_reverse_order: quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) { tab->limit= 0; - select->quick= save_quick; - goto use_filesort; // Use filesort + goto use_filesort; // Use filesort } - - /* ORDER BY range_key DESC */ - tmp= select->quick->make_reverse(used_key_parts); - if (!tmp) - { - select->quick= save_quick; - tab->limit= 0; - goto use_filesort; // Reverse sort not supported - } - 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) + } + + /* + Update query plan with access pattern for doing + ordered access according to what we have decided + above. + */ + if (!no_changes) // We are allowed to update QEP + { + if (best_key >= 0) { - /* - SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC + bool quick_created= + (select && select->quick && select->quick!=save_quick); - Use a traversal function that starts by reading the last row - with key part (A) and then traverse the index backwards. + /* + If ref_key used index tree reading only ('Using index' in EXPLAIN), + and best_key doesn't, then revert the decision. */ - tab->read_first_record= join_read_last_key; - tab->read_record.read_record= join_read_prev_same; + if (!table->covering_keys.is_set(best_key)) + table->disable_keyread(); + if (!quick_created) + { + if (select) // Throw any existing quick select + select->quick= 0; // Cleanup either reset to save_quick, + // or 'delete save_quick' + tab->index= best_key; + tab->read_first_record= order_direction > 0 ? + join_read_first:join_read_last; + tab->type=JT_NEXT; // Read with index_first(), index_next() + + if (table->covering_keys.is_set(best_key) && ! table->key_read) + table->enable_keyread(); + if (tab->pre_idx_push_select_cond) + { + COND *tmp_cond= tab->pre_idx_push_select_cond; + if (orig_select_cond) + { + tmp_cond= and_conds(tmp_cond, orig_select_cond); + tmp_cond->quick_fix_field(); + } + tab->set_cond(tmp_cond); + /* orig_select_cond was merged, no need to restore original one. */ + orig_select_cond= 0; + } + table->file->ha_index_or_rnd_end(); + if (tab->join->select_options & SELECT_DESCRIBE) + { + tab->ref.key= -1; + tab->ref.key_parts= 0; + if (select_limit < table->file->stats.records) + tab->limit= select_limit; + } + } + else if (tab->type != JT_ALL) + { + /* + We're about to use a quick access to the table. + We need to change the access method so as the quick access + method is actually used. + */ + DBUG_ASSERT(tab->select->quick); + tab->type=JT_ALL; + tab->use_quick=1; + tab->ref.key= -1; + tab->ref.key_parts=0; // Don't use ref key. + tab->read_first_record= join_init_read_record; + if (tab->is_using_loose_index_scan()) + tab->join->tmp_table_param.precomputed_group_by= TRUE; + /* + TODO: update the number of records in join->best_positions[tablenr] + */ + } + } // best_key >= 0 + + if (order_direction == -1) // If ORDER BY ... DESC + { + if (select && select->quick) + { + /* ORDER BY range_key DESC */ + QUICK_SELECT_I *tmp= select->quick->make_reverse(used_key_parts); + if (!tmp) + { + tab->limit= 0; + goto use_filesort; // Reverse sort failed -> filesort + } + 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) + { + /* + SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC + + Use a traversal function that starts by reading the last row + with key part (A) and then traverse the index backwards. + */ + tab->read_first_record= join_read_last_key; + tab->read_record.read_record= join_read_prev_same; + } } + else if (select && select->quick) + select->quick->need_sorted_output(); + + } // QEP has been modified + + /* + Cleanup: + We may have both a 'select->quick' and 'save_quick' (original) + at this point. Delete the one that we wan't use. + */ + +skipped_filesort: + // Keep current (ordered) select->quick + if (select && save_quick != select->quick) + { + delete save_quick; + save_quick= NULL; } - else if (select && select->quick) - select->quick->need_sorted_output(); if (orig_select_cond) tab->set_cond(orig_select_cond); DBUG_RETURN(1); + use_filesort: + // Restore original save_quick + if (select && select->quick != save_quick) + { + delete select->quick; + select->quick= save_quick; + } if (orig_select_cond) tab->set_cond(orig_select_cond); DBUG_RETURN(0); @@ -15819,7 +15999,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 @@ -15829,8 +16009,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 @@ -16966,6 +17146,8 @@ calc_group_buffer(JOIN *join,ORDER *group) { key_length+= 8; } + else if (type == MYSQL_TYPE_BLOB) + key_length+= MAX_BLOB_WIDTH; // Can't be used as a key else { /* @@ -17612,7 +17794,7 @@ 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 */ |