diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 324 |
1 files changed, 237 insertions, 87 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 81f510a4563..0abac42eb73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1,4 +1,4 @@ -/* Copyright (c) 2000, 2012 Oracle and/or its affiliates. +/* Copyright (c) 2000, 2013 Oracle and/or its affiliates. Copyright (c) 2009, 2013 Monty Program Ab. This program is free software; you can redistribute it and/or modify @@ -130,9 +130,10 @@ static int return_zero_rows(JOIN *join, select_result *res, List<Item> &fields, bool send_row, ulonglong select_options, const char *info, Item *having, List<Item> &all_fields); -static COND *build_equal_items(THD *thd, COND *cond, +static COND *build_equal_items(JOIN *join, COND *cond, COND_EQUAL *inherited, List<TABLE_LIST> *join_list, + bool ignore_on_conds, COND_EQUAL **cond_equal_ref); static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, COND *cond, @@ -148,7 +149,8 @@ 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, + bool ignore_on_conds, + Item::cond_result *cond_value, COND_EQUAL **cond_equal); bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); static bool create_internal_tmp_table_from_heap2(THD *, TABLE *, @@ -607,16 +609,16 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, uint *reserved) { int res; - nesting_map save_allow_sum_func=thd->lex->allow_sum_func ; + st_select_lex *const select= thd->lex->current_select; + nesting_map save_allow_sum_func= thd->lex->allow_sum_func; /* Need to save the value, so we can turn off only any new non_agg_field_used additions coming from the WHERE */ - const bool saved_non_agg_field_used= - thd->lex->current_select->non_agg_field_used(); + const bool saved_non_agg_field_used= select->non_agg_field_used(); DBUG_ENTER("setup_without_group"); - thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level); + thd->lex->allow_sum_func&= ~((nesting_map)1 << select->nest_level); res= setup_conds(thd, tables, leaves, conds); if (thd->lex->current_select->first_cond_optimization) { @@ -627,12 +629,12 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, } /* it's not wrong to have non-aggregated columns in a WHERE */ - thd->lex->current_select->set_non_agg_field_used(saved_non_agg_field_used); + select->set_non_agg_field_used(saved_non_agg_field_used); - thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level; + thd->lex->allow_sum_func|= (nesting_map)1 << select->nest_level; res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields, order); - thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level); + thd->lex->allow_sum_func&= ~((nesting_map)1 << select->nest_level); res= res || setup_group(thd, ref_pointer_array, tables, fields, all_fields, group, hidden_group_fields); thd->lex->allow_sum_func= save_allow_sum_func; @@ -781,7 +783,7 @@ JOIN::prepare(Item ***rref_pointer_array, { nesting_map save_allow_sum_func= thd->lex->allow_sum_func; thd->where="having clause"; - thd->lex->allow_sum_func|= 1 << select_lex_arg->nest_level; + thd->lex->allow_sum_func|= (nesting_map)1 << select_lex_arg->nest_level; select_lex->having_fix_field= 1; /* Wrap alone field in HAVING clause in case it will be outer field of subquery @@ -1155,7 +1157,8 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1); - conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal); + conds= optimize_cond(this, conds, join_list, FALSE, + &cond_value, &cond_equal); if (thd->is_error()) { @@ -1165,7 +1168,9 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S } { - having= optimize_cond(this, having, join_list, &having_value, &having_equal); + having= optimize_cond(this, having, join_list, TRUE, + &having_value, &having_equal); + if (thd->is_error()) { error= 1; @@ -2312,6 +2317,7 @@ void JOIN::exec_inner() { List<Item> *columns_list= &fields_list; int tmp_error; + DBUG_ENTER("JOIN::exec"); const bool has_group_by= this->group; @@ -4183,7 +4189,7 @@ add_key_field(JOIN *join, !(field->table->pos_in_table_list->is_materialized_derived() && field->table->created)) || (field->table->pos_in_table_list->is_materialized_derived() && - !field->table->created))) + !field->table->created && !(field->flags & BLOB_FLAG)))) { optimize= KEY_OPTIMIZE_EQ; } @@ -5160,6 +5166,7 @@ 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. @@ -5486,6 +5493,8 @@ best_access_path(JOIN *join, 2. we won't get two ref-or-null's */ if (!(remaining_tables & keyuse->used_tables) && + s->access_from_tables_is_allowed(keyuse->used_tables, + join->sjm_lookup_tables) && !(ref_or_null_part && (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL))) { @@ -5606,7 +5615,8 @@ best_access_path(JOIN *join, in ReuseRangeEstimateForRef-3. */ if (table->quick_keys.is_set(key) && - (const_part & ((1 << table->quick_key_parts[key])-1)) == + (const_part & + (((key_part_map)1 << table->quick_key_parts[key])-1)) == (((key_part_map)1 << table->quick_key_parts[key])-1) && table->quick_n_ranges[key] == 1 && records > (double) table->quick_rows[key]) @@ -5770,7 +5780,8 @@ best_access_path(JOIN *join, */ if (table->quick_keys.is_set(key) && table->quick_key_parts[key] <= max_key_part && - const_part & (1 << table->quick_key_parts[key]) && + const_part & + ((key_part_map)1 << table->quick_key_parts[key]) && table->quick_n_ranges[key] == 1 + test(ref_or_null_part & const_part) && records > (double) table->quick_rows[key]) @@ -8024,7 +8035,9 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, */ do { - if (!(~used_tables & keyuse->used_tables)) + if (!(~used_tables & keyuse->used_tables) && + j->access_from_tables_is_allowed(keyuse->used_tables, + join->sjm_lookup_tables)) { if (are_tables_local(j, keyuse->val->used_tables())) { @@ -8093,7 +8106,9 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, uint i; for (i=0 ; i < keyparts ; keyuse++,i++) { - while (((~used_tables) & keyuse->used_tables) || + while (((~used_tables) & keyuse->used_tables) || + !j->access_from_tables_is_allowed(keyuse->used_tables, + join->sjm_lookup_tables) || keyuse->keypart == NO_KEYPART || (keyuse->keypart != (is_hash_join_key_no(key) ? @@ -8105,7 +8120,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.items[i]=keyuse->val; // Save for cond removal j->ref.cond_guards[i]= keyuse->cond_guard; if (keyuse->null_rejecting) - j->ref.null_rejecting |= 1 << i; + j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; /* Todo: we should remove this check for thd->lex->describe on the next @@ -8152,20 +8167,17 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, ulong key_flags= j->table->actual_key_flags(keyinfo); if (j->type == JT_CONST) j->table->const_table= 1; - else if (((key_flags & (HA_NOSAME | HA_NULL_PART_KEY))!= HA_NOSAME) || - keyparts != j->table->actual_n_key_parts(keyinfo) || - null_ref_key) - { - if (test(key_flags & HA_EXT_NOSAME) && keyparts == keyinfo->ext_key_parts && - !null_ref_key) - j->type= JT_EQ_REF; - else - { - /* Must read with repeat */ - j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; - j->ref.null_ref_key= null_ref_key; - j->ref.null_ref_part= null_ref_part; - } + else if (!((keyparts == keyinfo->key_parts && + ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)) || + (keyparts > keyinfo->key_parts && // true only for extended keys + test(key_flags & HA_EXT_NOSAME) && + keyparts == keyinfo->ext_key_parts)) || + null_ref_key) + { + /* Must read with repeat */ + j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; + j->ref.null_ref_key= null_ref_key; + j->ref.null_ref_part= null_ref_part; } else if (keyuse_uses_no_tables) { @@ -8316,9 +8328,9 @@ inline void add_cond_and_fix(THD *thd, Item **e1, Item *e2) Item *res; if ((res= new Item_cond_and(*e1, e2))) { - *e1= res; res->fix_fields(thd, 0); res->update_used_tables(); + *e1= res; } } else @@ -8390,7 +8402,7 @@ static void add_not_null_conds(JOIN *join) { for (uint keypart= 0; keypart < tab->ref.key_parts; keypart++) { - if (tab->ref.null_rejecting & (1 << keypart)) + if (tab->ref.null_rejecting & ((key_part_map)1 << keypart)) { Item *item= tab->ref.items[keypart]; Item *notnull; @@ -10580,7 +10592,6 @@ bool JOIN_TAB::preread_init() } - /** Build a TABLE_REF structure for index lookup in the temporary table @@ -11832,7 +11843,9 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, item_equal->n_field_items()); } - ((Item_cond_and*)cond)->cond_equal= cond_equal; + ((Item_cond_and*)cond)->cond_equal.copy(cond_equal); + cond_equal.current_level= + ((Item_cond_and*)cond)->cond_equal.current_level; inherited= &(((Item_cond_and*)cond)->cond_equal); } /* @@ -11887,6 +11900,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, item_equal->update_used_tables(); set_if_bigger(thd->lex->current_select->max_equal_elems, item_equal->n_field_items()); + item_equal->upper_levels= inherited; return item_equal; } @@ -11909,7 +11923,8 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, set_if_bigger(thd->lex->current_select->max_equal_elems, item_equal->n_field_items()); } - and_cond->cond_equal= cond_equal; + and_cond->cond_equal.copy(cond_equal); + cond_equal.current_level= and_cond->cond_equal.current_level; args->concat((List<Item> *)&cond_equal.current_level); return and_cond; @@ -11991,6 +12006,8 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, @param inherited path to all inherited multiple equality items @param join_list list of join tables to which the condition refers to + @ignore_on_conds TRUE <-> do not build multiple equalities + for on expressions @param[out] cond_equal_ref pointer to the structure to place built equalities in @@ -11998,10 +12015,13 @@ 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(JOIN *join, COND *cond, + COND_EQUAL *inherited, List<TABLE_LIST> *join_list, + bool ignore_on_conds, COND_EQUAL **cond_equal_ref) { + THD *thd= join->thd; COND_EQUAL *cond_equal= 0; if (cond) @@ -12026,7 +12046,7 @@ static COND *build_equal_items(THD *thd, COND *cond, COND_EQUAL *inherited, } *cond_equal_ref= cond_equal; - if (join_list) + if (join_list && !ignore_on_conds) { TABLE_LIST *table; List_iterator<TABLE_LIST> li(*join_list); @@ -12041,8 +12061,8 @@ static COND *build_equal_items(THD *thd, COND *cond, COND_EQUAL *inherited, We can modify table->on_expr because its old value will be restored before re-execution of PS/SP. */ - table->on_expr= build_equal_items(thd, table->on_expr, inherited, - nested_join_list, + table->on_expr= build_equal_items(join, table->on_expr, inherited, + nested_join_list, ignore_on_conds, &table->cond_equal); } } @@ -12239,11 +12259,16 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, Item *item_const= item_equal->get_const(); Item_equal_fields_iterator it(*item_equal); Item *head; - DBUG_ASSERT(!cond || cond->type() == Item::COND_ITEM); - TABLE_LIST *current_sjm= NULL; Item *current_sjm_head= NULL; + DBUG_ASSERT(!cond || + cond->type() == Item::INT_ITEM || + (cond->type() == Item::FUNC_ITEM && + ((Item_func *) cond)->functype() == Item_func::EQ_FUNC) || + (cond->type() == Item::COND_ITEM && + ((Item_func *) cond)->functype() == Item_func::COND_AND_FUNC)); + /* Pick the "head" item: the constant one or the first in the join order (if the first in the join order happends to be inside an SJM nest, that's @@ -12318,8 +12343,8 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, if (produce_equality) { - if (eq_item) - eq_list.push_back(eq_item); + if (eq_item && eq_list.push_back(eq_item)) + return 0; /* If we're inside an SJM-nest (current_sjm!=NULL), and the multi-equality @@ -12343,31 +12368,61 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, current_sjm= field_sjm; } - if (!cond) + /* + We have produced zero, one, or more pair-wise equalities eq_i. We want to + return an expression in form: + + cond AND eq_1 AND eq_2 AND eq_3 AND ... + + 'cond' is a parameter for this function, which may be NULL, an Item_int(1), + or an Item_func_eq or an Item_cond_and. + + We want to return a well-formed condition: no nested Item_cond_and objects, + or Item_cond_and with a single child: + - if 'cond' is an Item_cond_and, we add eq_i as its tail + - if 'cond' is Item_int(1), we return eq_i + - otherwise, we create our own Item_cond_and and put 'cond' at the front of + it. + - if we have only one condition to return, we don't create an Item_cond_and + */ + + if (eq_item && eq_list.push_back(eq_item)) + return 0; + COND *res= 0; + switch (eq_list.elements) + { + case 0: + res= cond ? cond : new Item_int((longlong) 1, 1); + break; + case 1: + if (!cond || cond->type() == Item::INT_ITEM) + res= eq_item; + break; + default: + break; + } + if (!res) { - if (eq_list.is_empty()) + if (cond) { - if (eq_item) - return eq_item; - return new Item_int((longlong) 1, 1); + if (cond->type() == Item::COND_ITEM) + { + res= cond; + ((Item_cond *) res)->add_at_end(&eq_list); + } + else if (eq_list.push_front(cond)) + return 0; } - /* eq_item is always set if list is not empty */ - DBUG_ASSERT(eq_item); - eq_list.push_back(eq_item); - if (!(cond= new Item_cond_and(eq_list))) - return 0; // Error - } - else + } + if (!res) + res= new Item_cond_and(eq_list); + if (res) { - if (eq_item) - eq_list.push_back(eq_item); - if (!eq_list.is_empty()) - ((Item_cond *) cond)->add_at_head(&eq_list); + res->quick_fix_field(); + res->update_used_tables(); } - cond->quick_fix_field(); - cond->update_used_tables(); - - return cond; + + return res; } @@ -12470,31 +12525,68 @@ static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, if (and_level) { + COND *eq_cond= 0; List_iterator_fast<Item_equal> it(cond_equal->current_level); + bool false_eq_cond= FALSE; while ((item_equal= it++)) { - cond= eliminate_item_equal(cond, cond_equal->upper_levels, item_equal); - // This occurs when eliminate_item_equal() founds that cond is - // always false and substitutes it with Item_int 0. - // Due to this, value of item_equal will be 0, so just return it. - if (!cond) - return org_cond; // Error - if (cond->type() != Item::COND_ITEM) + eq_cond= eliminate_item_equal(eq_cond, cond_equal->upper_levels, + item_equal); + if (!eq_cond) + { + eq_cond= 0; + break; + } + else if (eq_cond->type() == Item::INT_ITEM && !eq_cond->val_bool()) + { + /* + This occurs when eliminate_item_equal() founds that cond is + always false and substitutes it with Item_int 0. + Due to this, value of item_equal will be 0, so just return it. + */ + cond= eq_cond; + false_eq_cond= TRUE; break; + } } - } - if (cond->type() == Item::COND_ITEM && - !((Item_cond*)cond)->argument_list()->elements) - cond= new Item_int((int32)cond->val_bool()); - + if (eq_cond && !false_eq_cond) + { + /* Insert the generated equalities before all other conditions */ + if (eq_cond->type() == Item::COND_ITEM) + ((Item_cond *) cond)->add_at_head( + ((Item_cond *) eq_cond)->argument_list()); + else + { + if (cond_list->is_empty()) + cond= eq_cond; + else + { + /* Do not add an equality condition if it's always true */ + if (eq_cond->type() != Item::INT_ITEM && + cond_list->push_front(eq_cond)) + eq_cond= 0; + } + } + } + if (!eq_cond) + { + /* + We are out of memory doing the transformation. + This is a fatal error now. However we bail out by returning the + original condition that we had before we started the transformation. + */ + cond_list->concat((List<Item> *) &cond_equal->current_level); + } + } } else if (cond->type() == Item::FUNC_ITEM && ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) { item_equal= (Item_equal *) cond; item_equal->sort(&compare_fields_by_table_order, table_join_idx); + cond_equal= item_equal->upper_levels; if (cond_equal && cond_equal->current_level.head() == item_equal) - cond_equal= 0; + cond_equal= cond_equal->upper_levels; cond= eliminate_item_equal(0, cond_equal, item_equal); return cond ? cond : org_cond; } @@ -13453,7 +13545,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, static COND * -optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, +optimize_cond(JOIN *join, COND *conds, + List<TABLE_LIST> *join_list, bool ignore_on_conds, Item::cond_result *cond_value, COND_EQUAL **cond_equal) { THD *thd= join->thd; @@ -13462,7 +13555,9 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, if (!conds) { *cond_value= Item::COND_TRUE; - build_equal_items(join->thd, NULL, NULL, join_list, cond_equal); + if (!ignore_on_conds) + build_equal_items(join, NULL, NULL, join_list, ignore_on_conds, + cond_equal); } else { @@ -13475,7 +13570,8 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, multiple equality contains a constant. */ DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY);); - conds= build_equal_items(join->thd, conds, NULL, join_list, cond_equal); + conds= build_equal_items(join, conds, NULL, join_list, ignore_on_conds, + cond_equal); DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); /* change field = field to field = const for each found field = const */ @@ -13531,7 +13627,61 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) li.remove(); else if (item != new_item) { - (void) li.replace(new_item); + if (and_level) + { + /* + Take a special care of multiple equality predicates + that may be part of 'cond' and 'new_item'. + Those multiple equalities that have common members + must be merged. + */ + Item_cond_and *cond_and= (Item_cond_and *) cond; + List<Item_equal> *cond_equal_items= + &cond_and->cond_equal.current_level; + List<Item> *cond_and_list= cond_and->argument_list(); + + if (new_item->type() == Item::COND_ITEM && + ((Item_cond*) new_item)->functype() == Item_func::COND_AND_FUNC) + { + Item_cond_and *new_item_and= (Item_cond_and *) new_item; + List<Item_equal> *new_item_equal_items= + &new_item_and->cond_equal.current_level; + List<Item> *new_item_and_list= new_item_and->argument_list(); + cond_and_list->disjoin((List<Item>*) cond_equal_items); + new_item_and_list->disjoin((List<Item>*) new_item_equal_items); + Item_equal *equal_item; + List_iterator<Item_equal> it(*new_item_equal_items); + while ((equal_item= it++)) + { + equal_item->merge_into_list(cond_equal_items); + } + if (new_item_and_list->is_empty()) + li.remove(); + else + li.replace(*new_item_and_list); + cond_and_list->concat((List<Item>*) cond_equal_items); + } + else if (new_item->type() == Item::FUNC_ITEM && + ((Item_cond*) new_item)->functype() == + Item_func::MULT_EQUAL_FUNC) + { + cond_and_list->disjoin((List<Item>*) cond_equal_items); + ((Item_equal *) new_item)->merge_into_list(cond_equal_items); + li.remove(); + cond_and_list->concat((List<Item>*) cond_equal_items); + } + else + li.replace(new_item); + } + else + { + if (new_item->type() == Item::COND_ITEM && + ((Item_cond*) new_item)->functype() == + ((Item_cond*) cond)->functype()) + li.replace(*((Item_cond*) new_item)->argument_list()); + else + li.replace(new_item); + } should_fix_fields=1; } if (*cond_value == Item::COND_UNDEF) @@ -14110,6 +14260,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, } case Item::FIELD_ITEM: case Item::DEFAULT_VALUE_ITEM: + case Item::INSERT_VALUE_ITEM: { Item_field *field= (Item_field*) item; bool orig_modify= modify_item; @@ -14788,11 +14939,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, } else { - recinfo->null_bit= 1 << (null_count & 7); + recinfo->null_bit= (uint8)1 << (null_count & 7); recinfo->null_pos= null_count/8; } field->move_field(pos,null_flags+null_count/8, - 1 << (null_count & 7)); + (uint8)1 << (null_count & 7)); null_count++; } else @@ -15246,7 +15397,7 @@ TABLE *create_virtual_tmp_table(THD *thd, List<Create_field> &field_list) { cur_field->move_field(field_pos, (uchar*) null_pos, null_bit); null_bit<<= 1; - if (null_bit == (1 << 8)) + if (null_bit == (uint)1 << 8) { ++null_pos; null_bit= 1; @@ -19306,7 +19457,6 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, *(join->pre_sort_join_tab)= *tab; - tab->select=NULL; tab->set_select_cond(NULL, __LINE__); tab->type=JT_ALL; // Read with normal read_record |