diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/item.cc | 101 | ||||
| -rw-r--r-- | sql/item.h | 4 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 11 | ||||
| -rw-r--r-- | sql/opt_range.cc | 57 | ||||
| -rw-r--r-- | sql/sql_select.cc | 344 | ||||
| -rw-r--r-- | sql/sql_select.h | 3 | ||||
| -rw-r--r-- | sql/table.h | 2 |
7 files changed, 389 insertions, 133 deletions
diff --git a/sql/item.cc b/sql/item.cc index a17336ea634..c99c0a9ba6d 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -408,7 +408,7 @@ bool DTCollation::aggregate(DTCollation &dt, bool superset_conversion) Item_field::Item_field(Field *f) :Item_ident(NullS, f->table_name, f->field_name), - item_equal(0), + item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { set_field(f); @@ -419,7 +419,7 @@ Item_field::Item_field(Field *f) Item_field::Item_field(THD *thd, Field *f) :Item_ident(NullS, thd->strdup(f->table_name), thd->strdup(f->field_name)), - item_equal(0), + item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { set_field(f); @@ -432,11 +432,12 @@ Item_field::Item_field(THD *thd, Item_field *item) :Item_ident(thd, item), field(item->field), result_field(item->result_field), + item_equal(item->item_equal), + no_const_subst(item->no_const_subst), have_privileges(item->have_privileges), any_privileges(item->any_privileges) { collation.set(DERIVATION_IMPLICIT); - item_equal= item->item_equal; } void Item_field::set_field(Field *field_par) @@ -1607,7 +1608,101 @@ void Item_field::cleanup() First Item_equal containing the field, if success 0, otherwise */ +Item_equal *Item_field::find_item_equal(COND_EQUAL *cond_equal) +{ + Item_equal *item= 0; + while (cond_equal) + { + List_iterator_fast<Item_equal> li(cond_equal->current_level); + while ((item= li++)) + { + if (item->contains(field)) + return item; + } + /* + The field is not found in any of the multiple equalities + of the current level. Look for it in upper levels + */ + cond_equal= cond_equal->upper_levels; + } + return 0; +} + +/* + Set a pointer to the multiple equality the field reference belongs to (if any) + + SYNOPSIS + equal_fields_propagator() + arg - reference to list of multiple equalities where + the field (this object) is to be looked for + + DESCRIPTION + The function looks for a multiple equality containing the field item + among those referenced by arg. + In the case such equality exists the function does the following. + If the found multiple equality contains a constant, then the field + reference is substituted for this constant, otherwise it sets a pointer + to the multiple equality in the field item. + + NOTES + This function is supposed to be called as a callback parameter in calls + of the transform method. + + RETURN VALUES + pointer to the replacing constant item, if the field item was substituted + pointer to the field item, otherwise. +*/ + +Item *Item_field::equal_fields_propagator(byte *arg) +{ + if (no_const_subst) + return this; + item_equal= find_item_equal((COND_EQUAL *) arg); + Item *item= 0; + if (item_equal) + item= item_equal->get_const(); + if (!item) + item= this; + return item; +} + + +/* + Set a pointer to the multiple equality the field reference belongs to (if any) + + SYNOPSIS + replace_equal_field_processor() + arg - a dummy parameter, is not used here + + DESCRIPTION + The function replaces a pointer to a field in the Item_field object + by a pointer to another field. + The replacement field is taken from the very beginning of + the item_equal list which the Item_field object refers to (belongs to) + If the Item_field object does not refer any Item_equal object, + nothing is done. + + NOTES + This function is supposed to be called as a callback parameter in calls + of the walk method. + + RETURN VALUES + 0 +*/ + +bool Item_field::replace_equal_field_processor(byte *arg) +{ + if (item_equal) + { + Item_field *subst= item_equal->get_first(); + if (!field->eq(subst->field)) + { + field= subst->field; + return 0; + } + } + return 0; } void Item::init_make_field(Send_field *tmp_field, diff --git a/sql/item.h b/sql/item.h index 9826bcb4a5a..c8e274b7205 100644 --- a/sql/item.h +++ b/sql/item.h @@ -457,6 +457,8 @@ protected: void set_field(Field *field); public: Field *field,*result_field; + Item_equal *item_equal; + bool no_const_subst; /* if any_privileges set to TRUE then here real effective privileges will be stored @@ -468,7 +470,7 @@ public: Item_field(const char *db_par,const char *table_name_par, const char *field_name_par) :Item_ident(db_par,table_name_par,field_name_par), - field(0), result_field(0), item_equal(0),} + field(0), result_field(0), item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { collation.set(DERIVATION_IMPLICIT); } // Constructor need to process subselect with temporary tables (see Item) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 1f2e744f714..66af96f671f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -231,6 +231,8 @@ void Item_bool_func2::fix_length_and_dec() conv->collation.set(args[weak]->collation.derivation); conv->fix_fields(thd, 0, &conv); } + if (args[weak]->type() == FIELD_ITEM) + ((Item_field *)args[weak])->no_const_subst= 1; args[weak]= conv ? conv : args[weak]; } } @@ -1956,7 +1958,7 @@ Item_cond::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) char buff[sizeof(char*)]; // Max local vars in function #endif not_null_tables_cache= used_tables_cache= 0; - const_item_cache= 0; + const_item_cache= 1; /* and_table_cache is the value that Item_cond_or() returns for not_null_tables() @@ -1987,7 +1989,7 @@ Item_cond::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) tmp_table_map= item->not_null_tables(); not_null_tables_cache|= tmp_table_map; and_tables_cache&= tmp_table_map; - const_item_cache&= item->const_item(); + const_item_cache&= item->const_item(); with_sum_func= with_sum_func || item->with_sum_func; if (item->maybe_null) maybe_null=1; @@ -2051,7 +2053,7 @@ void Item_cond::split_sum_func(Item **ref_pointer_array, List<Item> &fields) List_iterator<Item> li(list); Item *item; used_tables_cache=0; - const_item_cache=0; + const_item_cache=1; while ((item=li++)) { if (item->with_sum_func && item->type() != SUM_FUNC_ITEM) @@ -2088,7 +2090,7 @@ void Item_cond::update_used_tables() { item->update_used_tables(); used_tables_cache|= item->used_tables(); - const_item_cache&= item->const_item(); + const_item_cache&= item->const_item(); } } @@ -2934,6 +2936,7 @@ void Item_equal::add(Item *c) } Item_func_eq *func= new Item_func_eq(c, const_item); func->set_cmp_func(); + func->quick_fix_field(); cond_false = !(func->val_int()); } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 502a8d51b07..fe34ac7235e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3339,6 +3339,63 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond) } DBUG_RETURN(ftree); + } + default: + if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[0]); + value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : 0; + } + else if (cond_func->have_rev_func() && + cond_func->arguments()[1]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[1]); + value= cond_func->arguments()[0]; + } + else + DBUG_RETURN(0); + } + + /* + If the where condition contains a predicate (ti.field op const), + then not only SELL_TREE for this predicate is built, but + the trees for the results of substitution of ti.field for + each tj.field belonging to the same multiple equality as ti.field + are built as well. + E.g. for WHERE t1.a=t2.a AND t2.a > 10 + a SEL_TREE for t2.a > 10 will be built for quick select from t2 + and + a SEL_TREE for t1.a > 10 will be built for quick select from t1. + */ + + for (uint i= 0; i < cond_func->arg_count; i++) + { + Item *arg= cond_func->arguments()[i]; + if (arg != field_item) + ref_tables|= arg->used_tables(); + } + Field *field= field_item->field; + Item_result cmp_type= field->cmp_type(); + if (!((ref_tables | field->table->map) & param_comp)) + ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type); + Item_equal *item_equal= field_item->item_equal; + if (item_equal) + { + Item_equal_iterator it(*item_equal); + Item_field *item; + while ((item= it++)) + { + Field *f= item->field; + if (field->eq(f)) + continue; + if (!((ref_tables | f->table->map) & param_comp)) + { + tree= get_func_mm_tree(param, cond_func, f, value, cmp_type); + ftree= !ftree ? tree : tree_and(param, ftree, tree); + } + } + } + DBUG_RETURN(ftree); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b3355f6c57c..05df457da1b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -91,8 +91,10 @@ static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables, uint select_options, const char *info, Item *having, Procedure *proc, SELECT_LEX_UNIT *unit); -static COND *build_all_equal_items(COND *cond, - COND_EQUAL *inherited); +static COND *build_equal_items(COND *cond, + COND_EQUAL *inherited, + List<TABLE_LIST> *join_list, + COND_EQUAL **cond_equal_ref); static COND* substitute_for_best_equal_field(COND *cond, COND_EQUAL *cond_equal, void *table_join_idx); @@ -530,17 +532,29 @@ JOIN::optimize() } } #endif - - /* Eliminate NOT operators */ - conds= eliminate_not_funcs(conds); - DBUG_EXECUTE("where", print_where(conds, "after negation elimination");); + SELECT_LEX *sel= thd->lex->current_select; + if (sel->first_cond_optimization) { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - tables->on_expr= eliminate_not_funcs(tables->on_expr); - } + /* + The following code will allocate the new items in a permanent + MEMROOT for prepared statements and stored procedures. + */ + + Item_arena *arena= thd->current_arena, backup; + if (arena->is_conventional()) + arena= 0; // For easier test + else + thd->set_n_backup_item_arena(arena, &backup); + + sel->first_cond_optimization= 0; + + /* Convert all outer joins to inner joins if possible */ + conds= simplify_joins(this, join_list, conds, TRUE); + + sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; + + if (arena) + thd->restore_backup_item_arena(arena, &backup); } /* @@ -550,32 +564,8 @@ JOIN::optimize() that occurs in a function set a pointer to the multiple equality predicate. Substitute a constant instead of this field if the multiple equality contains a constant. - */ - if (conds) - { - conds= build_all_equal_items(conds, NULL); - conds->update_used_tables(); - if (conds->type() == Item::COND_ITEM && - ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC) - cond_equal= &((Item_cond_and*) conds)->cond_equal; - else if (conds->type() == Item::FUNC_ITEM && - ((Item_cond*) conds)->functype() == Item_func::MULT_EQUAL_FUNC) - { - cond_equal= new COND_EQUAL; - cond_equal->current_level.push_back((Item_equal *) conds); - } - } - { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - { - tables->on_expr= build_all_equal_items(tables->on_expr, cond_equal); - tables->on_expr->update_used_tables(); - } - } - } + */ + conds= build_equal_items(conds, NULL, join_list, &cond_equal); conds= optimize_cond(this, conds,&cond_value); if (thd->net.report_error) @@ -669,31 +659,6 @@ JOIN::optimize() if (const_tables && !thd->locked_tables && !(select_options & SELECT_NO_UNLOCK)) mysql_unlock_some_tables(thd, table, const_tables); - /* - Among the equal fields belonging to the same multiple equality - choose the one that is to be retrieved first and substitute - all references to these in where condition for a reference for - the selected field. - */ - if (conds) - { - conds= substitute_for_best_equal_field(conds, cond_equal, map2table); - conds->update_used_tables(); - } - { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - { - tables->on_expr= substitute_for_best_equal_field(tables->on_expr, - cond_equal, - map2table); - tables->on_expr->update_used_tables(); - map2table[tables->table->tablenr]->on_expr= tables->on_expr; - } - } - } if (!conds && outer_join) { /* Handle the case where we have an OUTER JOIN without a WHERE */ @@ -710,6 +675,32 @@ JOIN::optimize() make_outerjoin_info(this); + /* + Among the equal fields belonging to the same multiple equality + choose the one that is to be retrieved first and substitute + all references to these in where condition for a reference for + the selected field. + */ + if (conds) + { + conds= substitute_for_best_equal_field(conds, cond_equal, map2table); + conds->update_used_tables(); + } + /* + Permorm the the optimization on fields evaluation mentioned above + for all on expressions. + */ + for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++) + { + if (*tab->on_expr_ref) + { + *tab->on_expr_ref= substitute_for_best_equal_field(*tab->on_expr_ref, + tab->cond_equal, + map2table); + (*tab->on_expr_ref)->update_used_tables(); + } + } + if (make_join_select(this, select, conds)) { zero_result_cause= @@ -1545,8 +1536,6 @@ JOIN::exec() /* table->keyuse is set in the case there was an original WHERE clause on the table that was optimized away. - table->on_expr tells us that it was a LEFT JOIN and there will be - at least one row generated from the table. */ if (curr_table->select_cond || (curr_table->keyuse && !curr_table->first_inner)) @@ -1646,6 +1635,7 @@ JOIN::cleanup() tmp_table_param.copy_field=0; DBUG_RETURN(tmp_join->cleanup()); } + cond_equal= 0; lock=0; // It's faster to unlock later join_free(1); @@ -1794,7 +1784,7 @@ Cursor::fetch(ulong num_rows) { THD *thd= join->thd; JOIN_TAB *join_tab= join->join_tab + join->const_tables;; - COND *on_expr= join_tab->on_expr; + COND *on_expr= *join_tab->on_expr_ref; COND *select_cond= join_tab->select_cond; READ_RECORD *info= &join_tab->read_record; @@ -2156,7 +2146,8 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, s->dependent= tables->dep_tables; s->key_dependent= 0; - if ((s->on_expr=tables->on_expr)) + s->on_expr_ref= &tables->on_expr; + if (*s->on_expr_ref) { /* s is the only inner table of an outer join */ if (!table->file->records) @@ -2378,7 +2369,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, SQL_SELECT *select; select= make_select(s->table, found_const_table_map, found_const_table_map, - s->on_expr ? s->on_expr : conds, + *s->on_expr_ref ? *s->on_expr_ref : conds, &error); records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); @@ -2396,7 +2387,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, join->const_table_map|= s->table->map; set_position(join,const_count++,s,(KEYUSE*) 0); s->type= JT_CONST; - if (s->on_expr) + if (*s->on_expr_ref) { /* Generate empty row */ s->info= "Impossible ON condition"; @@ -2774,16 +2765,26 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, case Item_func::OPTIMIZE_NONE: break; case Item_func::OPTIMIZE_KEY: - // BETWEEN, IN, NOT + { + // BETWEEN, IN, NE if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM && !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) - add_key_field(key_fields,*and_level,cond_func, - ((Item_field*)(cond_func->key_item()->real_item()))->field, - cond_func->argument_count() == 2 && - cond_func->functype() == Item_func::IN_FUNC, - cond_func->arguments()+1, cond_func->argument_count()-1, - usable_tables); + { + Item **values= cond_func->arguments()+1; + if (cond_func->functype() == Item_func::NE_FUNC && + cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM && + !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT)) + values--; + add_key_equal_fields(key_fields, *and_level, cond_func, + (Item_field*) (cond_func->key_item()->real_item()), + cond_func->argument_count() == 2 && + cond_func->functype() == Item_func::IN_FUNC, + values, + cond_func->argument_count()-1, + usable_tables); + } break; + } case Item_func::OPTIMIZE_OP: { bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC || @@ -3048,9 +3049,9 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, for inner tables in outer joins these keys will be taken into account as well. */ - if (join_tab[i].on_expr) + if (*join_tab[i].on_expr_ref) { - add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr, + add_key_fields(join_tab,&end,&and_level,*join_tab[i].on_expr_ref, join_tab[i].table->map); } else @@ -4654,7 +4655,7 @@ get_best_combination(JOIN *join) form=join->table[tablenr]=j->table; used_tables|= form->map; form->reginfo.join_tab=j; - if (!j->on_expr) + if (!*j->on_expr_ref) form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN if (j->type == JT_CONST) continue; // Handled in make_join_stat.. @@ -4909,7 +4910,7 @@ make_simple_join(JOIN *join,TABLE *tmp_table) join_tab->type= JT_ALL; /* Map through all records */ join_tab->keys.init(~0); /* test everything in quick */ join_tab->info=0; - join_tab->on_expr=0; + join_tab->on_expr_ref=0; join_tab->last_inner= 0; join_tab->first_unmatched= 0; join_tab->ref.key = -1; @@ -4978,7 +4979,7 @@ add_found_match_trig_cond(JOIN_TAB *tab, COND *cond, JOIN_TAB *root_tab) first inner table of the embedding outer join operation, if there is any, through the field t0->first_upper. The on expression for the outer join operation is attached to the - corresponding first inner table through the field t0->on_expr. + corresponding first inner table through the field t0->on_expr_ref. Here ti are structures of the JOIN_TAB type. EXAMPLE @@ -4992,8 +4993,8 @@ add_found_match_trig_cond(JOIN_TAB *tab, COND *cond, JOIN_TAB *root_tab) is selected, the following references will be set; t4->last_inner=[t4], t4->first_inner=[t4], t4->first_upper=[t2] t2->last_inner=[t4], t2->first_inner=t3->first_inner=[t2], - on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to t2->on_expr, - while t3.a=t4.a will be attached to t4->on_expr. + on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to + *t2->on_expr_ref, while t3.a=t4.a will be attached to *t4->on_expr_ref. NOTES The function assumes that the simplification procedure has been @@ -5020,7 +5021,8 @@ make_outerjoin_info(JOIN *join) is in the query above.) */ tab->last_inner= tab->first_inner= tab; - tab->on_expr= tbl->on_expr; + tab->on_expr_ref= &tbl->on_expr; + tab->cond_equal= tbl->cond_equal; if (embedding) tab->first_upper= embedding->nested_join->first_nested; } @@ -5034,7 +5036,8 @@ make_outerjoin_info(JOIN *join) Save reference to it in the nested join structure. */ nested_join->first_nested= tab; - tab->on_expr= embedding->on_expr; + tab->on_expr_ref= &embedding->on_expr; + tab->cond_equal= tbl->cond_equal; if (embedding->embedding) tab->first_upper= embedding->embedding->nested_join->first_nested; } @@ -5069,10 +5072,10 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) for (JOIN_TAB *tab= join->join_tab+join->const_tables; tab < join->join_tab+join->tables ; tab++) { - if (tab->on_expr) + if (*tab->on_expr_ref) { JOIN_TAB *cond_tab= tab->first_inner; - COND *tmp= make_cond_for_table(tab->on_expr, + COND *tmp= make_cond_for_table(*tab->on_expr_ref, join->const_table_map, (table_map) 0); if (!tmp) @@ -5210,7 +5213,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* Join with outer join condition */ COND *orig_cond=sel->cond; - sel->cond= and_conds(sel->cond, tab->on_expr); + sel->cond= and_conds(sel->cond, *tab->on_expr_ref); if (sel->cond && !sel->cond->fixed) sel->cond->fix_fields(join->thd, 0, &sel->cond); if (sel->test_quick_select(join->thd, tab->keys, @@ -5225,7 +5228,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) we have to check isn't it only "impossible ON" instead */ sel->cond=orig_cond; - if (!tab->on_expr || + if (!*tab->on_expr_ref || sel->test_quick_select(join->thd, tab->keys, used_tables & ~ current_map, (join->select_options & @@ -5290,7 +5293,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) Table tab is the last inner table of an outer join. An on expression is always attached to it. */ - COND *on_expr= first_inner_tab->on_expr; + COND *on_expr= *first_inner_tab->on_expr_ref; table_map used_tables= join->const_table_map | OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; @@ -5804,7 +5807,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, bool *simple_order) table_map not_const_tables= ~join->const_table_map; table_map ref; prev_ptr= &first_order; - *simple_order= join->join_tab[join->const_tables].on_expr ? 0 : 1; + *simple_order= *join->join_tab[join->const_tables].on_expr_ref ? 0 : 1; /* NOTE: A variable of not_const_tables ^ first_table; breaks gcc 2.7 */ @@ -6024,7 +6027,7 @@ finish: general case, its own constant for each fields from the multiple equality. But at the same time it would allow us to get rid of constant propagation completely: it would be done by the call - to build_all_equal_items. + to build_equal_items_for_cond. IMPLEMENTATION The implementation does not follow exactly the above rules to @@ -6156,7 +6159,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) bool copyfl; if (field_item->result_type() == STRING_RESULT && - ((Field_str *) field_item)->charset() != + ((Field_str *) field_item->field)->charset() != ((Item_cond *) item)->compare_collation()) return FALSE; @@ -6192,7 +6195,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) Replace all equality predicates in a condition by multiple equality items SYNOPSIS - build_all_equal_items() + build_equal_items_for_cond() cond condition(expression) where to make replacement inherited path to all inherited multiple equality items @@ -6253,7 +6256,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) pointer to the transformed condition */ -static COND *build_all_equal_items(COND *cond, +static COND *build_equal_items_for_cond(COND *cond, COND_EQUAL *inherited) { Item_equal *item_equal; @@ -6315,7 +6318,7 @@ static COND *build_all_equal_items(COND *cond, while((item= li++)) { Item *new_item; - if ((new_item = build_all_equal_items(item, inherited))!= item) + if ((new_item = build_equal_items_for_cond(item, inherited))!= item) { /* This replacement happens only for standalone equalities */ li.replace(new_item); @@ -6352,6 +6355,113 @@ static COND *build_all_equal_items(COND *cond, cond->update_used_tables(); } return cond; +} + +/* + Build multiple equalities for a condition and all on expressions that + inherit these multiple equalities + + SYNOPSIS + build_equal_items() + cond condition to build the multiple equalities for + inherited path to all inherited multiple equality items + join_list list of join tables to which the condition refers to + cond_equal_ref :out pointer to the structure to place built equalities in + + DESCRIPTION + The function first applies the build_equal_items_for_cond function + to build all multiple equalities for condition cond utilizing equalities + referred through the parameter inherited. The extended set of + equalities is returned in the structure referred by the cond_equal_ref + parameter. After this the function calls itself recursively for + all on expressions whose direct references can be found in join_list + and who inherit directly the multiple equalities just having built. + + NOTES + The on expression used in an outer join operation inherits all equalities + from the on expression of the embedding join, if there is any, or + otherwise - from the where condition. + This fact is not obvious, but presumably can be proved. + Consider the following query: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t2.a=t4.a + WHERE t1.a=t2.a; + If the on expression in the query inherits =(t1.a,t2.a), then we + can build the multiple equality =(t1.a,t2.a,t3.a,t4.a) that infers + the equality t3.a=t4.a. Although the on expression + t1.a=t3.a AND t2.a=t4.a AND t3.a=t4.a is not equivalent to the one + in the query the latter can be replaced by the former: the new query + will return the same result set as the original one. + + Interesting that multiple equality =(t1.a,t2.a,t3.a,t4.a) allows us + to use t1.a=t3.a AND t3.a=t4.a under the on condition: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a + WHERE t1.a=t2.a + This query equivalent to: + SELECT * FROM (t1 LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a),t2 + WHERE t1.a=t2.a + Similarly the original query can be rewritten to the query: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t2.a=t4.a AND t3.a=t4.a + WHERE t1.a=t2.a + that is equivalent to: + SELECT * FROM (t2 LEFT JOIN (t3,t4)ON t2.a=t4.a AND t3.a=t4.a), t1 + WHERE t1.a=t2.a + Thus, applying equalities from the where condition we basically + can get more freedom in performing join operations. + Althogh we don't use this property now, it probably makes sense to use + it in the future. + + RETURN + pointer to the transformed condition containing multiple equalities +*/ + +static COND *build_equal_items(COND *cond, + COND_EQUAL *inherited, + List<TABLE_LIST> *join_list, + COND_EQUAL **cond_equal_ref) +{ + COND_EQUAL *cond_equal= 0; + + if (cond) + { + cond= build_equal_items_for_cond(cond, inherited); + cond->update_used_tables(); + if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + cond_equal= &((Item_cond_and*) cond)->cond_equal; + else if (cond->type() == Item::FUNC_ITEM && + ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + cond_equal= new COND_EQUAL; + cond_equal->current_level.push_back((Item_equal *) cond); + } + } + if (cond_equal) + { + cond_equal->upper_levels= inherited; + inherited= cond_equal; + } + *cond_equal_ref= cond_equal; + + if (join_list) + { + TABLE_LIST *table; + List_iterator<TABLE_LIST> li(*join_list); + + while ((table= li++)) + { + if (table->on_expr) + { + List<TABLE_LIST> *join_list= table->nested_join ? + &table->nested_join->join_list : NULL; + table->on_expr= build_equal_items(table->on_expr, + inherited, + join_list, + &table->cond_equal); + } + } + } + + return cond; } /* @@ -6447,6 +6557,11 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, { List<Item> eq_list; Item_func_eq *eq_item= 0; + if (((Item *) item_equal)->const_item() && !item_equal->val_int()) + { + cond= new Item_int((char*) "FALSE",0,1); + return cond; + } Item *item_const= item_equal->get_const(); Item_equal_iterator it(*item_equal); Item *head; @@ -6484,6 +6599,7 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, if (!eq_item) return 0; eq_item->set_cmp_func(); + eq_item->quick_fix_field(); } } @@ -6495,6 +6611,9 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, cond= new Item_cond_and(eq_list); else ((Item_cond *) cond)->add_at_head(&eq_list); + + cond->quick_fix_field(); + cond->update_used_tables(); return cond; } @@ -6589,7 +6708,6 @@ static COND* substitute_for_best_equal_field(COND *cond, return cond; } - /* change field = field to field = const for each found field = const in the and_level @@ -6998,38 +7116,15 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top) } } DBUG_RETURN(conds); +} static COND * optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) { THD *thd= join->thd; - SELECT_LEX *select= thd->lex->current_select;} + SELECT_LEX *select= thd->lex->current_select; DBUG_ENTER("optimize_cond"); - if (select->first_cond_optimization) - { - /* - The following code will allocate the new items in a permanent - MEMROOT for prepared statements and stored procedures. - */ - - Item_arena *arena= thd->current_arena, backup; - if (arena->is_conventional()) - arena= 0; // For easier test - else - thd->set_n_backup_item_arena(arena, &backup); - - select->first_cond_optimization= 0; - - /* Convert all outer joins to inner joins if possible */ - conds= simplify_joins(join, join->join_list, conds, TRUE); - - select->prep_where= conds ? conds->copy_andor_structure(thd) : 0; - - if (arena) - thd->restore_backup_item_arena(arena, &backup); - } - if (!conds) { *cond_value= Item::COND_TRUE; @@ -7047,6 +7142,7 @@ optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) DBUG_EXECUTE("where",print_where(conds,"after const change");); conds= remove_eq_conds(thd, conds, cond_value) ; DBUG_EXECUTE("info",print_where(conds,"after remove");); + } DBUG_RETURN(conds); } @@ -8871,9 +8967,9 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) table->file->extra(HA_EXTRA_NO_KEYREAD); } } - if (tab->on_expr && !table->null_row) + if (*tab->on_expr_ref && !table->null_row) { - if ((table->null_row= test(tab->on_expr->val_int() == 0))) + if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0))) mark_as_null_row(table); } if (!table->null_row) diff --git a/sql/sql_select.h b/sql/sql_select.h index c9cfeb70225..18664624a77 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -91,7 +91,8 @@ typedef struct st_join_table { SQL_SELECT *select; COND *select_cond; QUICK_SELECT_I *quick; - Item *on_expr; /* associated on expression */ + Item **on_expr_ref; /* pointer to the associated on expression */ + COND_EQUAL *cond_equal; /* multiple equalities for the on expression */ st_join_table *first_inner; /* first inner table for including outerjoin */ bool found; /* true after all matches or null complement */ bool not_null_compl;/* true before null complement is added */ diff --git a/sql/table.h b/sql/table.h index 35f6c6803fb..605cd516d9c 100644 --- a/sql/table.h +++ b/sql/table.h @@ -21,6 +21,7 @@ class Item; /* Needed by ORDER */ class GRANT_TABLE; class st_select_lex_unit; class st_select_lex; +class COND_EQUAL; /* Order clause list element */ @@ -209,6 +210,7 @@ typedef struct st_table_list char *db, *alias, *real_name; char *option; /* Used by cache index */ Item *on_expr; /* Used with outer join */ + COND_EQUAL *cond_equal; /* Used with outer join */ struct st_table_list *natural_join; /* natural join on this table*/ /* ... join ... USE INDEX ... IGNORE INDEX */ List<String> *use_index, *ignore_index; |