| List: | Commits | « Previous MessageNext Message » | |
| From: | Sergey Petrunia | Date: | March 24 2007 1:43am |
| Subject: | bk commit into 5.2 tree (sergefp:1.2456) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.2 repository of psergey. When psergey does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-03-24 03:43:48+03:00, sergefp@stripped +19 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. mysql-test/r/subselect_sj.result@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +173 -0 New BitKeeper file ``mysql-test/r/subselect_sj.result'' mysql-test/r/subselect_sj.result@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +0 -0 mysql-test/t/subselect_sj.test@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +76 -0 New BitKeeper file ``mysql-test/t/subselect_sj.test'' mysql-test/t/subselect_sj.test@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +0 -0 sql/item.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +32 -1 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +25 -1 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_cmpfunc.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +52 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_cmpfunc.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +2 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_func.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +38 -1 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_func.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +1 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_row.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +12 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_row.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +1 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_subselect.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +5 -2 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/item_subselect.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +17 -2 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/mysql_priv.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +1 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/sql_base.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +5 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/sql_class.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +2 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/sql_select.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +846 -24 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/sql_select.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +14 -2 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/sql_test.cc@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +32 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. sql/table.h@stripped, 2007-03-24 03:43:41+03:00, sergefp@stripped +3 -0 WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" - Tables are pulled out; if all tables are pulled out to parent select then the EXPLAIN or query itself will produce correct result - (PSes are expected to be broken) this is an intermediate (won't push) cumulative (all WL code is here) commit. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: sergefp # Host: pylon.mylan # Root: /home/psergey/mysql-5.2-subq-r4-cp --- 1.186/sql/item.cc 2007-03-24 03:43:58 +03:00 +++ 1.187/sql/item.cc 2007-03-24 03:43:58 +03:00 @@ -1984,6 +1984,13 @@ } +void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref) +{ + if (new_parent == depended_from) + depended_from= NULL; +} + + Item *Item_field::get_tmp_table_item(THD *thd) { Item_field *new_item= new Item_field(thd, this); @@ -3930,7 +3937,6 @@ return TRUE; } - Item *Item_field::safe_charset_converter(CHARSET_INFO *tocs) { no_const_subst= 1; @@ -5587,6 +5593,31 @@ table_name= outer_field->table_name; return Item_direct_ref::fix_fields(thd, reference); } + + +void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref) +{ + if (depended_from == new_parent) + *ref= outer_field; + + /* + explain extended select * from ot1, ot2 where ot2.a in (select it1.b from it1, it2 where it1.a=ot1.c ) + "it1.a=ot1.c" + ... + item_field(ot1.c).name_resolution_context == parent_select_lex.name_resolution_contex + ... + (gdb) p ((Item*)subq_lex->where->list->first->next->info)->args[1] + $15 = (Item_outer_ref *) 0x8fa4518 + (gdb) p ((Item*)subq_lex->where->list->first->next->info)->args[1]->context + $16 = (Name_resolution_context *) 0x8fa2bac + (gdb) p *(((Item*)subq_lex->where->list->first->next->info)->args[1]->ref) + $19 = (Item_field *) 0x8fa36d0 + (gdb) p (*(((Item*)subq_lex->where->list->first->next->info)->args[1]->ref))->context + $20 = (Name_resolution_context *) 0x8fa2bac + (gdb) p (*(((Item*)subq_lex->where->list->first->next->info)->args[1]->ref))->field->table->pos_in_table_list->select_lex ; + */ +} + /* Compare two view column references for equality. --- 1.206/sql/item.h 2007-03-24 03:43:58 +03:00 +++ 1.207/sql/item.h 2007-03-24 03:43:58 +03:00 @@ -443,7 +443,8 @@ typedef void (*Cond_traverser) (const Item *item, void *arg); -class Item { +class Item +{ Item(const Item &); /* Prevent use of these */ void operator=(Item &); public: @@ -521,6 +522,27 @@ Field *make_string_field(TABLE *table); virtual bool fix_fields(THD *, Item **); /* + Fix after some tables has been pulled out. Basically re-calculate all + attributes that are dependent on the tables. + */ + virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {}; + + /* + Fix Item::with_subselect after we've replaced a subquery with + Item_int(1). + psergey-todo: + - Item_sum doesn't seem to have this set correctly? + - Q: subquery can be 'substituted' with non-subquery item. Why didn't + this function exist before? (because 'substituion' is done at + fix_fields() right before the with_subselect attribute is calculated? + TODO: is it *really* not possible to perform actions like this using + ::walk or ::compile? + A2: We don't really remove subqueries from depths of arbitrary trees. + The subquery is reachable from the root of expression via + Item_cond_and's. + */ + virtual void fix_after_subq_removal() {}; + /* should be used in case where we are sure that we do not need complete fix_fields() procedure. */ @@ -1351,6 +1373,7 @@ bool send(Protocol *protocol, String *str_arg); void reset_field(Field *f); bool fix_fields(THD *, Item **); + void fix_after_pullout(st_select_lex *new_parent, Item **ref); void make_field(Send_field *tmp_field); int save_in_field(Field *field,bool no_conversions); void save_org_in_field(Field *field); @@ -2088,6 +2111,7 @@ outer_field->save_org_in_field(result_field); } bool fix_fields(THD *, Item **); + void fix_after_pullout(st_select_lex *new_parent, Item **ref); table_map used_tables() const { return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT; --- 1.248/sql/item_cmpfunc.cc 2007-03-24 03:43:58 +03:00 +++ 1.249/sql/item_cmpfunc.cc 2007-03-24 03:43:58 +03:00 @@ -2795,11 +2795,15 @@ DBUG_ASSERT(fixed == 0); List_iterator<Item> li(list); Item *item; + void *orig_thd_marker= thd->thd_marker; #ifndef EMBEDDED_LIBRARY char buff[sizeof(char*)]; // Max local vars in function #endif not_null_tables_cache= used_tables_cache= 0; const_item_cache= 1; + + if (functype() == COND_OR_FUNC) + thd->thd_marker= 0; /* and_table_cache is the value that Item_cond_or() returns for not_null_tables() @@ -2858,9 +2862,57 @@ maybe_null=1; } thd->lex->current_select->cond_count+= list.elements; + thd->thd_marker= orig_thd_marker; fix_length_and_dec(); fixed= 1; return FALSE; +} + + +void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref) +{ + List_iterator<Item> li(list); + Item *item; + + used_tables_cache=0; + const_item_cache=1; + + and_tables_cache= ~(table_map) 0; // Here and below we do as fix_fields does + not_null_tables_cache= 0; + + while ((item=li++)) + { + table_map tmp_table_map; + item->fix_after_pullout(new_parent, li.ref()); + item= *li.ref(); + used_tables_cache|= item->used_tables(); + const_item_cache&= item->const_item(); + + if (item->const_item()) + and_tables_cache= (table_map) 0; + else + { + tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + and_tables_cache&= tmp_table_map; + const_item_cache= FALSE; + } + } +} + +void Item_cond_and::fix_after_subq_removal() +{ + with_subselect= 0; + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (item->with_subselect) + { + with_subselect= 1; + break; + } + } } bool Item_cond::walk(Item_processor processor, bool walk_subquery, byte *arg) --- 1.151/sql/item_cmpfunc.h 2007-03-24 03:43:58 +03:00 +++ 1.152/sql/item_cmpfunc.h 2007-03-24 03:43:58 +03:00 @@ -1284,6 +1284,7 @@ bool add(Item *item) { return list.push_back(item); } void add_at_head(List<Item> *nlist) { list.prepand(nlist); } bool fix_fields(THD *, Item **ref); + void fix_after_pullout(st_select_lex *new_parent, Item **ref); enum Type type() const { return COND_ITEM; } List<Item>* argument_list() { return &list; } @@ -1474,6 +1475,7 @@ return item; } Item *neg_transformer(THD *thd); + void fix_after_subq_removal(); }; class Item_cond_or :public Item_cond --- 1.357/sql/item_func.cc 2007-03-24 03:43:58 +03:00 +++ 1.358/sql/item_func.cc 2007-03-24 03:43:58 +03:00 @@ -141,10 +141,11 @@ { DBUG_ASSERT(fixed == 0); Item **arg,**arg_end; + void *save_thd_marker= thd->thd_marker; #ifndef EMBEDDED_LIBRARY // Avoid compiler warning char buff[STACK_BUFF_ALLOC]; // Max argument in function #endif - + thd->thd_marker= 0; used_tables_cache= not_null_tables_cache= 0; const_item_cache=1; @@ -190,7 +191,43 @@ if (thd->net.report_error) // An error inside fix_length_and_dec occured return TRUE; fixed= 1; + thd->thd_marker= save_thd_marker; return FALSE; +} + + +void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref) +{ + Item **arg,**arg_end; + + used_tables_cache= not_null_tables_cache= 0; + const_item_cache=1; + + if (arg_count) + { + for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++) + { + (*arg)->fix_after_pullout(new_parent, arg); + Item *item= *arg; + + used_tables_cache|= item->used_tables(); + not_null_tables_cache|= item->not_null_tables(); + const_item_cache&= item->const_item(); + + /* + psergey-todo: Check if update of maybe_null is necessary: + + On one hand, if we look at an expression in a global context of + parent's join, it may become NULLable because the semi-join is + within a nested outer join. + + On the other hand, there is no reason to ever evaluate this + expression outside of the outer join. And within an outer join, + whatever was NOT NULL inside the subquery remains NOT NULL within + the semi-join. + */ + } + } } --- 1.161/sql/item_func.h 2007-03-24 03:43:58 +03:00 +++ 1.162/sql/item_func.h 2007-03-24 03:43:58 +03:00 @@ -116,6 +116,7 @@ // Constructor used for Item_cond_and/or (see Item comment) Item_func(THD *thd, Item_func *item); bool fix_fields(THD *, Item **ref); + void fix_after_pullout(st_select_lex *new_parent, Item **ref); table_map used_tables() const; table_map not_null_tables() const; void update_used_tables(); --- 1.486/sql/mysql_priv.h 2007-03-24 03:43:58 +03:00 +++ 1.487/sql/mysql_priv.h 2007-03-24 03:43:58 +03:00 @@ -1464,6 +1464,7 @@ void TEST_filesort(SORT_FIELD *sortorder,uint s_length); void print_plan(JOIN* join,uint idx, double record_count, double read_time, double current_read_time, const char *info); +void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array); #endif void mysql_print_status(); /* key.cc */ --- 1.376/sql/sql_base.cc 2007-03-24 03:43:58 +03:00 +++ 1.377/sql/sql_base.cc 2007-03-24 03:43:58 +03:00 @@ -5874,6 +5874,7 @@ SELECT_LEX *select_lex= thd->lex->current_select; Query_arena *arena= thd->stmt_arena, backup; TABLE_LIST *table= NULL; // For HP compilers + void *save_thd_marker= thd->thd_marker; /* it_is_update set to TRUE when tables of primary SELECT_LEX (SELECT_LEX which belong to LEX, i.e. most up SELECT) will be updated by @@ -5901,6 +5902,7 @@ goto err_no_arena; } + thd->thd_marker= (void*)1; if (*conds) { thd->where="where clause"; @@ -5908,6 +5910,7 @@ (*conds)->check_cols(1)) goto err_no_arena; } + thd->thd_marker= save_thd_marker; /* Apply fix_fields() to all ON clauses at all levels of nesting, @@ -5923,6 +5926,7 @@ if (embedded->on_expr) { /* Make a join an a expression */ + thd->thd_marker= (void*)embedded; thd->where="on clause"; if (!embedded->on_expr->fixed && embedded->on_expr->fix_fields(thd, &embedded->on_expr) || @@ -5947,6 +5951,7 @@ } } } + thd->thd_marker= save_thd_marker; if (!thd->stmt_arena->is_conventional()) { --- 1.341/sql/sql_class.h 2007-03-24 03:43:58 +03:00 +++ 1.342/sql/sql_class.h 2007-03-24 03:43:58 +03:00 @@ -954,6 +954,8 @@ /* container for handler's private per-connection data */ void *ha_data[MAX_HA]; + /* psergey-subq: place to store various things */ + void *thd_marker; #ifndef MYSQL_CLIENT int binlog_setup_trx_data(); --- 1.494/sql/sql_select.cc 2007-03-24 03:43:58 +03:00 +++ 1.495/sql/sql_select.cc 2007-03-24 03:43:58 +03:00 @@ -99,7 +99,7 @@ COND_EQUAL *cond_equal, void *table_join_idx); static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, - COND *conds, bool top); + COND *conds, bool top, bool in_sj); static bool check_interleaving_with_nj(JOIN_TAB *last, JOIN_TAB *next); static void restore_prev_nj_state(JOIN_TAB *last); static void reset_nj_counters(List<TABLE_LIST> *join_list); @@ -235,6 +235,7 @@ { SELECT_LEX_UNIT *unit= &lex->unit; unit->set_limit(unit->global_parameters); + thd->thd_marker= 0; //psergey-todo: find a better place for this /* 'options' of mysql_select will be set in JOIN, as far as JOIN for every PS/SP execution new, we will not need reset this flag if @@ -331,6 +332,7 @@ return res; } +#define MAGIC_IN_WHERE_TOP_LEVEL 10 /* Function to setup clauses without sum functions */ @@ -444,11 +446,50 @@ if ((subselect= select_lex->master_unit()->item)) { Item_subselect::trans_res res; - if ((res= subselect->select_transformer(this)) != - Item_subselect::RES_OK) + bool do_delay= TRUE; + /* + psergey: + Subquery is a candidate for conversion into semi-join nest if + 1. It is an IN/=ANY subquery + 2. It is a single SELECT (no UNIONs) + 3. Subquery does not have GROUP BY or ORDER BY + 4. Subquery does not use aggregate functions or HAVING + + 5. Subquery predicate is at the AND-top-level of ON/WHERE clause + + (*): A single table doesn't have a JOIN (TODO: decide if we + handle this? If yes, provision to switch to multi-table + delete?) + */ + if (subselect->substype() == Item_subselect::IN_SUBS && // 1 + !select_lex->master_unit()->first_select()->next_select() && // 2 + !select_lex->group_list.elements && !order && // 3 + !having && !select_lex->with_sum_func && // 4 + thd->thd_marker && // 5 + select_lex->outer_select()->join && // (*) + do_delay) + { + fprintf(stderr, "subq is an sj candidate\n"); + Item_in_subselect *in_subs= (Item_in_subselect*)subselect; + // fix the left col + if (!in_subs->left_expr->fixed && + in_subs->left_expr->fix_fields(thd, &in_subs->left_expr)); + + //mark it for further processing + select_lex->outer_select()->join->sj_subselects.append(in_subs); + in_subs->expr_join_nest= (TABLE_LIST*)thd->thd_marker; + in_subs->delay_fix_fields= TRUE; + } + else { - select_lex->fix_prepare_information(thd, &conds, &having); - DBUG_RETURN((res == Item_subselect::RES_ERROR)); + fprintf(stderr, "subq is not an sj candidate\n"); + + if ((res= subselect->select_transformer(this)) != + Item_subselect::RES_OK) + { + select_lex->fix_prepare_information(thd, &conds, &having); + DBUG_RETURN((res == Item_subselect::RES_ERROR)); + } } } } @@ -720,7 +761,7 @@ sel->first_cond_optimization= 0; /* Convert all outer joins to inner joins if possible */ - conds= simplify_joins(this, join_list, conds, TRUE); + conds= simplify_joins(this, join_list, conds, TRUE, FALSE); build_bitmap_for_nested_joins(join_list, 0); sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; @@ -2042,6 +2083,201 @@ DBUG_RETURN(error); } + + +////////////////////////////////////////////////////////////////////////////// +// psergey-dbug-dump +////////////////////////////////////////////////////////////////////////////// +template <class T> class GraphDumper +{ +public: + // Record given TABLE_LIST for dumping. + void dbug_dump_also(T *tbl) + { + if (!tbl) + return; + // check if we've already scheduled and/or dumped the element + for (int i= 0; i < last; i++) + { + if (dbg_tables_to_dump[i] == tbl) + { + return; + } + } + // schedule the element to be dumped + dbg_tables_to_dump[last++]= tbl; + } + + bool get_next(T **elem) + { + if (first < last) + { + *elem= dbg_tables_to_dump[first++]; + return TRUE; + } + return FALSE; + } + + void reset() + { + first= last= 0; + } + + T *dbg_tables_to_dump[1000]; + int first; // First undumped table + int last; // Last undumped element +}; + +GraphDumper<TABLE_LIST> dbug_tbl_graph; + +GraphDumper<List<TABLE_LIST> > dbug_tbl_lists; + +FILE *out; + + +/* + - Dump one TABLE_LIST and its outgoing edges + - Schedule stuff seen along these edges for dumping +*/ + +void dump_table_list(TABLE_LIST *tbl) +{ + // Dump the node + fprintf(out, "\"%p\" [\n", tbl); + fprintf(out, " label = \"%p|", tbl); + fprintf(out, "alias=%s|", tbl->alias? tbl->alias : "NULL"); + fprintf(out, "<next_leaf>next_leaf=%p|", tbl->next_leaf); + fprintf(out, "<next_local>next_local=%p|", tbl->next_local); + fprintf(out, "<next_global>next_global=%p|", tbl->next_global); + fprintf(out, "<embedding>embedding=%p", tbl->embedding); + + if (tbl->nested_join) + fprintf(out, "|<nested_j>nested_j=%p", tbl->nested_join); + if (tbl->join_list) + fprintf(out, "|<join_list>join_list=%p", tbl->join_list); + if (tbl->on_expr) + fprintf(out, "|<on_expr>on_expr=%p", tbl->on_expr); + fprintf(out, "\"\n"); + fprintf(out, " shape = \"record\"\n];\n\n"); + + if (tbl->next_leaf) + { + fprintf(out, "\n\"%p\":next_leaf -> \"%p\"[ color = \"#000000\" ];\n", + tbl, tbl->next_leaf); + dbug_tbl_graph.dbug_dump_also(tbl->next_leaf); + } + if (tbl->next_local) + { + fprintf(out, "\n\"%p\":next_local -> \"%p\"[ color = \"#404040\" ];\n", + tbl, tbl->next_local); + dbug_tbl_graph.dbug_dump_also(tbl->next_local); + } + if (tbl->next_global) + { + fprintf(out, "\n\"%p\":next_global -> \"%p\"[ color = \"#808080\" ];\n", + tbl, tbl->next_global); + dbug_tbl_graph.dbug_dump_also(tbl->next_global); + } + + + if (tbl->embedding) + { + fprintf(out, "\n\"%p\":embedding -> \"%p\"[ color = \"#FF0000\" ];\n", + tbl, tbl->embedding); + dbug_tbl_graph.dbug_dump_also(tbl->embedding); + } + + if (tbl->join_list) + { + fprintf(out, "\n\"%p\":join_list -> \"%p\"[ color = \"#0000FF\" ];\n", + tbl, tbl->join_list); + dbug_tbl_lists.dbug_dump_also(tbl->join_list); + } +} + +void dump_list(List<TABLE_LIST> *list) +{ +//top_join_list + fprintf(out, "\"%p\" [\n", list); + fprintf(out, " bgcolor = \"\""); + fprintf(out, " label = \"L %p\"", list); + fprintf(out, " shape = \"record\"\n];\n\n"); +} + +void dump_TABLE_LIST_struct(st_select_lex *select_lex, TABLE_LIST *first_leaf) +{ + DBUG_ENTER("dump_TABLE_LIST_struct"); + char filename[500]; + int no = 0; + do { + sprintf(filename, "tlist_tree%.3d.g", no); + out = fopen(filename, "rt"); + if (out) + { + //file exists, try next + fclose(out); + } + no++; + } while (out); + + //ok, file doesn't not exist, try opening + out = fopen(filename, "wt"); + + if (!out) + { + DBUG_PRINT("tree_dump", ("Failed to create output file")); + DBUG_VOID_RETURN; + } + + DBUG_PRINT("tree_dump", ("dumping tree to %s", filename)); + + fputs("digraph g {\n", out); + fputs("graph [", out); + fputs(" rankdir = \"LR\"", out); + fputs("];", out); + + dbug_tbl_graph.reset(); + dump_table_list(first_leaf); + TABLE_LIST *tbl; + while (dbug_tbl_graph.get_next(&tbl)) + { + dump_table_list(tbl); + } + + List<TABLE_LIST> *plist; + dbug_tbl_lists.dbug_dump_also(&select_lex->top_join_list); + while (dbug_tbl_lists.get_next(&plist)) + { + dump_list(plist); + } + + fprintf(out, " { rank = same; "); + for (TABLE_LIST *tl=first_leaf; tl; tl= tl->next_leaf) + fprintf(out, " \"%p\"; ", tl); + fprintf(out, "};\n"); + fputs("}", out); + fclose(out); + + char filename2[500]; + filename[strlen(filename) - 1] = 0; + filename[strlen(filename) - 1] = 0; + + sprintf(filename2, "%s.query", filename); + out = fopen(filename2, "wt"); + if (out) + { + fprintf(out, "%s", current_thd->query); + fclose(out); + } + DBUG_VOID_RETURN; +} + + +////////////////////////////////////////////////////////////////////////////// +// psergey-dbug-dump ends +////////////////////////////////////////////////////////////////////////////// + + /* An entry point to single-unit select (a select without UNION). @@ -2146,6 +2382,19 @@ } } + //psergey-subq: + if (!unit->item) + { + //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables); + /* We're not in a subquery predicate */ + if (join->fix_subqueries()) + { + thd->net.report_error= 1; + goto err; + } + //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables); + } + if ((err= join->optimize())) { goto err; // 1 @@ -2188,11 +2437,542 @@ DBUG_RETURN(join->error); } + +int subq_sj_candidate_cmp(Item_in_subselect* const *el1, + Item_in_subselect * const *el2) +{ + return ((*el1)->sort_by < (*el2)->sort_by) ? 1 : + ( ((*el1)->sort_by == (*el2)->sort_by)? 0 : -1); +} + + +inline Item * and_items(Item* cond, Item *item) +{ + return (cond? (new Item_cond_and(cond, item)) : item); +} + + +static TABLE_LIST *alloc_join_nest(THD *thd) +{ + TABLE_LIST *tbl; + if (!(tbl= (TABLE_LIST*) thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST))+ + sizeof(NESTED_JOIN)))) + return NULL; + tbl->nested_join= (NESTED_JOIN*) ((byte*)tbl + + ALIGN_SIZE(sizeof(TABLE_LIST))); + return tbl; +} + + +void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> tlist) +{ + List_iterator<TABLE_LIST> it(tlist); + TABLE_LIST *table; + while ((table= it++)) + { + if (table->on_expr) + table->on_expr->fix_after_pullout(new_parent, &table->on_expr); + if (table->nested_join) + fix_list_after_tbl_changes(new_parent, table->nested_join->join_list); + } +} + + +/* + Convert a subquery predicate into semi-join nest + + SYNOPSIS + //psergey-todo: +*/ + +bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) +{ + SELECT_LEX *parent_lex= parent_join->select_lex; + TABLE_LIST *emb_tbl_nest= NULL; + List<TABLE_LIST> *emb_join_list= &parent_lex->top_join_list; + DBUG_ENTER("convert_subq_to_sj"); + /* + 1. Find out where to put the predicate into. + Note: for "t1 LEFT JOIN t2" this will be t2, a leaf. + */ + if ((void*)subq_pred->expr_join_nest != (void*)1) + { + if (subq_pred->expr_join_nest->nested_join) + { + /* + We're dealing with + + ... [LEFT] JOIN ( ... ) ON (subquery AND whatever) ... + + The sj-nest will be inserted into the brackets nest. + */ + emb_tbl_nest= subq_pred->expr_join_nest; + emb_join_list= &emb_tbl_nest->nested_join->join_list; + } + else if (!subq_pred->expr_join_nest->outer_join) + { + /* + We're dealing with + + ... INNER JOIN tblX ON (subquery AND whatever) ... + + The sj-nest will be tblX's "sibling", i.e. another child of its + parent. This is ok because tblX is joined as an inner join. + */ + emb_tbl_nest= subq_pred->expr_join_nest->embedding; + if (emb_tbl_nest) + emb_join_list= &emb_tbl_nest->nested_join->join_list; + } + else if (!subq_pred->expr_join_nest->nested_join) + { + TABLE_LIST *outer_tbl= subq_pred->expr_join_nest; + TABLE_LIST *wrap_nest; + /* + We're dealing with + + ... LEFT JOIN tbl ON (on_expr AND subq_pred) ... + + we'll need to convert it into: + + ... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ... + | | + |<----- wrap_nest ---->| + + Q: other subqueries may be pointing to this element. What to do? + A1: simple solution: copy *subq_pred->expr_join_nest= *parent_nest. + But we'll need to fix other pointers. + A2: Another way: have TABLE_LIST::next_ptr so the following + subqueries know the table has been nested. + A3: changes in the TABLE_LIST::outer_join will make everything work + automatically. + */ + if (!(wrap_nest= alloc_join_nest(parent_join->thd))) + DBUG_RETURN(TRUE); + wrap_nest->embedding= outer_tbl->embedding; + wrap_nest->join_list= outer_tbl->join_list; + wrap_nest->alias= (char*) "(sj-wrap)"; + + wrap_nest->nested_join->join_list.empty(); + wrap_nest->nested_join->join_list.push_back(outer_tbl); + + outer_tbl->embedding= wrap_nest; + outer_tbl->join_list= &wrap_nest->nested_join->join_list; + + /* + wrap_nest will take place of outer_tbl, so move the outer join flag + and on_expr + */ + wrap_nest->outer_join= outer_tbl->outer_join; + outer_tbl->outer_join= 0; + + wrap_nest->on_expr= outer_tbl->on_expr; + outer_tbl->on_expr= NULL; + + List_iterator<TABLE_LIST> li(*wrap_nest->join_list); + TABLE_LIST *tbl; + while ((tbl= li++)) + { + if (tbl == outer_tbl) + { + li.replace(wrap_nest); + break; + } + } + /* + Ok now wrap_nest 'contains' outer_tbl and we're ready to add the + semi-join nest into it + */ + emb_join_list= &wrap_nest->nested_join->join_list; + emb_tbl_nest= wrap_nest; + } + } + + TABLE_LIST *sj_nest; + NESTED_JOIN *nested_join; + if (!(sj_nest= alloc_join_nest(parent_join->thd))) + DBUG_RETURN(TRUE); + nested_join= sj_nest->nested_join; + + sj_nest->join_list= emb_join_list; + sj_nest->embedding= emb_tbl_nest; + sj_nest->alias= (char*) "(sj-nest)"; + /* Nests do not participate in those 'chains', so: */ + /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/ + emb_join_list->push_back(sj_nest); + + /* + nested_join->used_tables and nested_join->not_null_tables are + initialized in simplify_joins(). + */ + + /* + 2. Walk through subquery's top list and set 'embedding' to point to the + sj-nest. + */ + st_select_lex *subq_lex= subq_pred->unit->first_select(); + nested_join->join_list.empty(); + List_iterator_fast<TABLE_LIST> li(subq_lex->top_join_list); + TABLE_LIST *tl, *last_leaf; + while ((tl= li++)) + { + tl->embedding= sj_nest; + tl->join_list= &nested_join->join_list; + nested_join->join_list.push_back(tl); + } + + /* + Reconnect the next_leaf chain. + TODO: Do we have to put subquery's tables at the end of the chain? + Inserting them at the beginning would be a bit faster. + NOTE: We actually insert them at the front! That's because the order is + reversed in this list. + */ + for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf); + tl->next_leaf= subq_lex->leaf_tables; + last_leaf= tl; + + /* + Same as above for next_local chain + (a theory: a next_local chain always starts with ::leaf_tables + because view's tables are inserted after the view) + */ + for (tl= parent_lex->leaf_tables; tl->next_local; tl= tl->next_local); + tl->next_local= subq_lex->leaf_tables; + + /* A theory: no need to re-connect the next_global chain */ + + /* 3. Remove the original subquery predicate from the WHERE/ON */ + *(subq_pred->ref_ptr)= new Item_int(1); + /*TODO: also reset the 'with_subselect' there. */ + + /* n. Adjust the parent_join->tables counter */ + uint table_no= parent_join->tables; + /* n. Walk through child's tables and adjust table->map */ + for (tl= subq_lex->leaf_tables; tl; tl= tl->next_leaf, table_no++) + { + tl->table->tablenr= table_no; + tl->table->map= ((table_map)1) << table_no; + } + parent_join->tables += subq_lex->join->tables; + + //TODO: check again the hypothesis that we don't have to update + // table->maybe_null + + /* + Put the subquery's WHERE into semi-join's sj_on_expr + Add the subquery-induced equalities too. + */ + sj_nest->sj_on_expr= subq_lex->where; + if (subq_pred->left_expr->cols() == 1) + { + Item *item_eq= + new Item_func_eq(subq_pred->left_expr, + subq_lex->ref_pointer_array[0]); + sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); + } + else + { + for (uint i= 0; i < subq_pred->left_expr->cols(); i++) + { + Item *item_eq= + new Item_func_eq(subq_pred->left_expr->element_index(i), + subq_lex->ref_pointer_array[i]); + sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq); + } + } + + /* + Walk through sj nest's WHERE and ON expressions and call + item->fix_table_changes() for all items. + */ + sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); + fix_list_after_tbl_changes(parent_lex, sj_nest->nested_join->join_list); + + + /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */ + subq_lex->master_unit()->exclude_level(); + + DBUG_EXECUTE("where",print_where(sj_nest->sj_on_expr,"SJ-EXPR");); + /* Inject sj_on_expr into the parent's WHERE or ON */ + if (emb_tbl_nest) + { + emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr, + sj_nest->sj_on_expr); + } + else + { + /* Inject into the WHERE */ + parent_join->conds= parent_join->select_lex->where= + and_items(parent_join->conds, sj_nest->sj_on_expr); + } + + DBUG_RETURN(FALSE); +} + + +/* + Walk through semi-join conversion candidates and either convert or fix them + + SYNOPSIS + JOIN::fix_subqueries() + + RETURN + FALSE OK + TRUE Error +*/ + +bool JOIN::fix_subqueries() +{ + DBUG_ENTER("JOIN::fix_subqueries"); + Item_in_subselect **in_subq; + Item_in_subselect **in_subq_end; + + if (sj_subselects.elements() == 0) + DBUG_RETURN(FALSE); + + /* 1. Fix children subqueries */ + for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back(); + in_subq != in_subq_end; in_subq++) + { + JOIN *child_join= (*in_subq)->unit->first_select()->join; + child_join->outer_tables = child_join->tables; + if (child_join->fix_subqueries()) + DBUG_RETURN(TRUE); + (*in_subq)->sort_by= (*in_subq)->is_correlated * MAX_TABLES + + child_join->outer_tables; + } + + //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables); + /* + 2. Pick which subqueries to convert: + sort the subquery array + - prefer correlated subqueries over uncorrelated; + - prefer subqueries that have greater number of outer tables; + */ + sj_subselects.sort(subq_sj_candidate_cmp); + // #tables-in-parent-query + #tables-in-subquery < MAX_TABLES + bool do_converts= TRUE; + for (in_subq= sj_subselects.front(); + in_subq != in_subq_end && + tables + ((*in_subq)->sort_by % MAX_TABLES) < MAX_TABLES; + in_subq++) + { + if (!do_converts) + break; + if (convert_subq_to_sj(this, *in_subq)) + DBUG_RETURN(TRUE); + } + + /* 3. Finalize those we didn't convert */ + for (; in_subq!= in_subq_end; in_subq++) + { + JOIN *child_join= (*in_subq)->unit->first_select()->join; + Item_subselect::trans_res res; + (*in_subq)->delay_fix_fields= FALSE; + (*in_subq)->changed= 0; + (*in_subq)->fixed= 0; + res= (*in_subq)->select_transformer(child_join); + if (res == Item_subselect::RES_ERROR) + DBUG_RETURN(TRUE); + + *((*in_subq)->ref_ptr)= (*in_subq)->substitution; + (*in_subq)->changed= 1; + (*in_subq)->fixed= 1; + if (!(*in_subq)->substitution->fixed && + (*in_subq)->substitution->fix_fields(thd, (*in_subq)->ref_ptr)) + DBUG_RETURN(TRUE); + + //if ((*in_subq)->fix_fields(thd, (*in_subq)->ref_ptr)) + // DBUG_RETURN(TRUE); + } + DBUG_RETURN(FALSE); +} + + +/* + Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate + + RETURN + TRUE - There exists an eq_ref candidate + FALSE - Otherwise + + psergey-todo: check again if it is feasible to factor common parts with + const table search +*/ + +bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables) +{ + KEYUSE *keyuse= table->reginfo.join_tab->keyuse; + uint key, part; + + if (keyuse) + { + while (1) /* For each key */ + { + key= keyuse->key; + KEY *keyinfo= table->key_info + key; + key_part_map bound_parts= 0; + if ((keyinfo->flags & (HA_NOSAME | HA_END_SPACE_KEY)) == HA_NOSAME) + { + do // For all key parts + { + do + { // For all ways to read the keypart + /* check if this is "t.keypart = expr(outer_tables) */ + if (!(keyuse->used_tables & sj_inner_tables) && + !(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)) + { + bound_parts |= 1 << keyuse->keypart; + } + keyuse++; + } while (keyuse->key == key && keyuse->keypart == part && + keyuse->table == table); + } while (keyuse->key == key && keyuse->table == table); + + if (bound_parts == PREV_BITS(uint, keyinfo->key_parts)) + return TRUE; + if (keyuse->table != table) + return FALSE; + } + else + { + do + { + keyuse++; + if (keyuse->table != table) + return FALSE; + } + while (keyuse->key == key); + } + } + } + return FALSE; +} + +/* + Flatten subquery semi-join nests + + SYNOPSIS + psergey: flatten_subq_semi_joins() + + + DESCRIPTION + Try to pull tables out of semi-join nests. Pull out as many as we can. + + When this function is called, the join may have several semi-join nests + (possibly within different semi-join nests), but it is guaranteed that + one semi-join nest does not contain another. + + Q: How do we 'pull out'? + A: Just decrement the counter and check at the end. + (we won't be able to use this in the future as we'll need for nested + semi-joins to remain) + + Q2: How can we pull out a nested outer join? we can't split it right? + + RETURN + 0 - Ok, eliminated all semi-join nests. + 1 - Failed to remove at least one nest. +*/ + +int flatten_subq_semi_joins(JOIN *join) +{ + DBUG_ENTER("flatten_subq_semi_joins"); + TABLE_LIST **sj_nest, **sj_nest_end; + + /* + For WL#3740, we don't need to do the real pull-out. We can just check if + we could have pulled out or not. + */ + + for (sj_nest= join->sj_nests.front(), sj_nest_end= join->sj_nests.back(); + sj_nest != sj_nest_end; sj_nest++) + { + // Action #1: Mark the constant tables to be pulled out + table_map pulled_tables= 0; + + /* + 1. Remove the constant tables out semi-join nests + (Q: do we need this? A: yes. But maybe we should go other way and + accumulate outer_tables_map ??) + */ + List_iterator<TABLE_LIST> child_li((*sj_nest)->nested_join->join_list); + TABLE_LIST *tbl; + + while ((tbl= child_li++)) + { + if (tbl->table->map & join->const_table_map) + { + /* + Note: the below may be excessive if we only consider goals of + WL#3740. + */ + (*sj_nest)->nested_join->used_tables &= ~tbl->table->map; + pulled_tables |= tbl->table->map; + child_li.remove(); + } + } + + /* + Action #2: Find which tables we can pull out based on + update_ref_and_keys() data. Note that pulling one table out can allow + us to pull out some other tables too. + */ + bool pulled_a_table; + do + { + pulled_a_table= FALSE; + child_li.rewind(); + while ((tbl= child_li++)) + { + if (find_eq_ref_candidate(tbl->table, (*sj_nest)->nested_join->used_tables)) + { + pulled_a_table= TRUE; + child_li.remove(); + (*sj_nest)->nested_join->used_tables &= ~tbl->table->map; + tbl->embedding= (*sj_nest)->embedding; + (*sj_nest)->join_list->push_back(tbl); + } + } + } while (pulled_a_table); + + if ((*sj_nest)->nested_join->join_list.is_empty()) + { + fprintf(stderr, "subq nest removed\n"); + /* + Remove also subquery's nest from its parent list. This is needed at + least for EXPLAIN EXTENDED. + */ + List_iterator<TABLE_LIST> parent_it(*(*sj_nest)->join_list); + TABLE_LIST *tbl; + while ((tbl= parent_it++)) + { + if (tbl == (*sj_nest)) + { + parent_it.remove(); + break; + } + } + } + else + { + /* + We're not capable of executing semi-join nests yet, report query + failure + */ + join->thd->net.report_error= 1; + DBUG_RETURN(1); + } + } + DBUG_RETURN(0); +} + /***************************************************************************** Create JOIN_TABS, make a guess about the table types, Approximate how many records will be used in each table *****************************************************************************/ + static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, TABLE *table, const key_map *keys,ha_rows limit) @@ -2320,7 +3100,7 @@ s->embedding_map|= embedding->nested_join->nj_map; continue; } - if (embedding) + if (embedding && !(embedding->sj_on_expr && ! embedding->embedding)) { /* s belongs to a nested join, maybe to several embedded joins */ s->embedding_map= 0; @@ -2548,7 +3328,7 @@ } } } while (join->const_table_map & found_ref && ref_changed); - + /* Update info on indexes that can be used for search lookups as reading const tables may has added new sargable predicates. @@ -2569,6 +3349,10 @@ } } + //psergey: + if (flatten_subq_semi_joins(join)) + DBUG_RETURN(TRUE); + /* Calc how many (possible) matched records in each table */ for (s=stat ; s < stat_end ; s++) @@ -2679,7 +3463,7 @@ Field *field; Item *val; // May be empty if diff constant uint level; - uint optimize; + uint optimize; // KEY_OPTIMIZE_* bool eq_func; /* If true, the condition this struct represents will not be satisfied @@ -2689,10 +3473,6 @@ bool *cond_guard; /* See KEYUSE::cond_guard */ } KEY_FIELD; -/* Values in optimize */ -#define KEY_OPTIMIZE_EXISTS 1 -#define KEY_OPTIMIZE_REF_OR_NULL 2 - /* Merge new key definitions to old ones, remove those not used in both @@ -3406,20 +4186,34 @@ SARGABLE_PARAM **sargables) { List_iterator<TABLE_LIST> li(nested_join_table->nested_join->join_list); + List_iterator<TABLE_LIST> li2(nested_join_table->nested_join->join_list); + bool have_another = FALSE; table_map tables= 0; TABLE_LIST *table; DBUG_ASSERT(nested_join_table->nested_join); - while ((table= li++)) + while ((table= li++) || (have_another && (li=li2, have_another=FALSE, + (table= li++)))) { if (table->nested_join) - add_key_fields_for_nj(join, table, end, and_level, sargables); + { + if (!table->on_expr) + { + /* It's a semi-join nest. Walk into it as if it wasn't a nest */ + have_another= TRUE; + li2= li; + li= List_iterator<TABLE_LIST>(table->nested_join->join_list); + } + else + add_key_fields_for_nj(join, table, end, and_level, sargables); + } else if (!table->on_expr) tables |= table->table->map; } - add_key_fields(join, end, and_level, nested_join_table->on_expr, tables, - sargables); + if (nested_join_table->on_expr) + add_key_fields(join, end, and_level, nested_join_table->on_expr, tables, + sargables); } @@ -3528,6 +4322,8 @@ TABLE_LIST *table; while ((table= li++)) { + // psergey: the below is wrong: we do want to process ON expressions + // for outer joins within inner joins! if (table->nested_join) add_key_fields_for_nj(join_tab->join, table, &end, &and_level, sargables); @@ -3599,6 +4395,7 @@ VOID(set_dynamic(keyuse,(gptr) &key_end,i)); keyuse->elements=i; } + DBUG_EXECUTE("opt", print_keyuse_array(keyuse);); return FALSE; } @@ -4151,7 +4948,7 @@ This is because table scans uses index and we would not win anything by using a table scan. - A word for word translation of the below if-statement in psergey's + A word for word translation of the below if-statement in sergefp's understanding: we check if we should use table scan if: (1) The found 'ref' access produces more records than a table scan (or index scan, or quick select), or 'ref' is more expensive than @@ -8127,10 +8924,22 @@ RETURN VALUE The new condition, if success 0, otherwise + + psergey-todo: this should + - remove semi-joins nested one within another. + (semi-join within nested-outer-join within semi-join must be removed + too) + + - Q: Take into account sj_on_expr for outer-to-inner conversions + A: sj_on_expr is within ON expressions, do we need any special treatments? + + - Do the "prep_on_expr= on_expr" (what's that for btw?) + */ static COND * -simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top) +simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, + bool in_sj) { TABLE_LIST *table; NESTED_JOIN *nested_join; @@ -8165,14 +8974,15 @@ the corresponding on expression is added to E. */ expr= simplify_joins(join, &nested_join->join_list, - expr, FALSE); + expr, FALSE, in_sj || table->sj_on_expr); table->on_expr= expr; if (!table->prep_on_expr) table->prep_on_expr= expr->copy_andor_structure(join->thd); } nested_join->used_tables= (table_map) 0; nested_join->not_null_tables=(table_map) 0; - conds= simplify_joins(join, &nested_join->join_list, conds, top); + conds= simplify_joins(join, &nested_join->join_list, conds, top, + in_sj || table->sj_on_expr); used_tables= nested_join->used_tables; not_null_tables= nested_join->not_null_tables; } @@ -8200,7 +9010,7 @@ table->outer_join= 0; if (table->on_expr) { - /* Add on expression to the where condition. */ + /* Add ON expression to the WHERE or upper-level ON condition. */ if (conds) { conds= and_conds(conds, table->on_expr); @@ -8254,6 +9064,7 @@ we still make the inner tables dependent on the outer tables. It would be enough to set dependency only on one outer table for them. Yet this is really a rare case. + psergey-q: how does this work for RIGHT JOIN ? */ if (!(prev_table->on_expr->used_tables() & ~prev_used_tables)) prev_table->dep_tables|= used_tables; @@ -8262,12 +9073,23 @@ prev_table= table; } - /* Flatten nested joins that can be flattened. */ + /* + Flatten nested joins that can be flattened. + no ON expression and not a semi-join => can be flattened. + */ li.rewind(); while ((table= li++)) { nested_join= table->nested_join; - if (nested_join && !table->on_expr) + if (table->sj_on_expr && !in_sj) + { + /* + If this is a semi-join that is not contained within another semi-join, + leave it intact (otherwise it is flattened) + */ + join->sj_nests.append(table); + } + else if (nested_join && !table->on_expr) { TABLE_LIST *tbl; List_iterator<TABLE_LIST> it(nested_join->join_list); --- 1.117/sql/sql_select.h 2007-03-24 03:43:58 +03:00 +++ 1.118/sql/sql_select.h 2007-03-24 03:43:58 +03:00 @@ -23,11 +23,16 @@ #include "procedure.h" #include <myisam.h> +/* Values in optimize */ +#define KEY_OPTIMIZE_EXISTS 1 +#define KEY_OPTIMIZE_REF_OR_NULL 2 + typedef struct keyuse_t { TABLE *table; Item *val; /* or value if no field */ table_map used_tables; - uint key, keypart, optimize; + uint key, keypart; + uint optimize; // 0, or KEY_OPTIMIZE_* key_part_map keypart_map; ha_rows ref_table_rows; /* @@ -258,7 +263,9 @@ JOIN_TAB **map2table; // mapping between table indexes and JOIN_TABs JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution TABLE **table,**all_tables,*sort_by_table; - uint tables,const_tables; + uint tables; /* Number of tables in the join */ + uint outer_tables; /* Number of tables that are not inside semijoin */ + uint const_tables; uint send_group_parts; bool sort_and_group,first_record,full_join,group, no_field_update; bool do_send_rows; @@ -357,6 +364,10 @@ bool union_part; // this subselect is part of union bool optimized; // flag to avoid double optimization in EXPLAIN + + //psergey-todo: Those two leak memory atm. Pre-alloc on pool? + Dynamic_array<Item_in_subselect*> sj_subselects; + Dynamic_array<TABLE_LIST*> sj_nests; /* storage for caching buffers allocated during query execution. @@ -441,6 +452,7 @@ int destroy(); void restore_tmp(); bool alloc_func_list(); + bool fix_subqueries(); bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, bool before_group_by, bool recompute= FALSE); --- 1.51/sql/sql_test.cc 2007-03-24 03:43:58 +03:00 +++ 1.52/sql/sql_test.cc 2007-03-24 03:43:58 +03:00 @@ -207,6 +207,38 @@ } +void print_keyuse(KEYUSE *keyuse) +{ + char buff[256]; + char buf2[64]; + const char *fieldname; + String str(buff,(uint32) sizeof(buff), system_charset_info); + str.length(0); + keyuse->val->print(&str); + str.append('\0'); + fieldname= keyuse->table->key_info[keyuse->key].key_part[keyuse->keypart].field->field_name; + longlong2str(keyuse->used_tables, buf2, 16); + DBUG_LOCK_FILE; + fprintf(DBUG_FILE, "KEYUSE: %s.%s=%s optimize= %d used_tables=%s " + "ref_table_rows= %lu keypart_map= %0lx\n", + keyuse->table->alias, fieldname, str.ptr(), + keyuse->optimize, buf2, (ulong)keyuse->ref_table_rows, + keyuse->keypart_map); + DBUG_UNLOCK_FILE; + //key_part_map keypart_map; --?? there can be several? +} + + +void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array) +{ + DBUG_LOCK_FILE; + fprintf(DBUG_FILE, "KEYUSE array (%d elements)\n", keyuse_array->elements); + DBUG_UNLOCK_FILE; + for(uint i=0; i < keyuse_array->elements; i++) + print_keyuse((KEYUSE*)dynamic_array_ptr(keyuse_array, i)); +} + + /* Print the current state during query optimization. --- 1.161/sql/table.h 2007-03-24 03:43:58 +03:00 +++ 1.162/sql/table.h 2007-03-24 03:43:58 +03:00 @@ -659,6 +659,8 @@ (TABLE_LIST::natural_join != NULL) - JOIN ... USING (TABLE_LIST::join_using_fields != NULL) + - semi-join + ; */ typedef struct st_table_list @@ -675,6 +677,7 @@ char *db, *alias, *table_name, *schema_table_name; char *option; /* Used by cache index */ Item *on_expr; /* Used with outer join */ + Item *sj_on_expr; /* The structure of ON expression presented in the member above can be changed during certain optimizations. This member --- 1.35/sql/item_row.cc 2007-03-24 03:43:58 +03:00 +++ 1.36/sql/item_row.cc 2007-03-24 03:43:58 +03:00 @@ -119,6 +119,18 @@ } } +void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref) +{ + used_tables_cache= 0; + const_item_cache= 1; + for (uint i= 0; i < arg_count; i++) + { + items[i]->fix_after_pullout(new_parent, &items[i]); + used_tables_cache|= items[i]->used_tables(); + const_item_cache&= items[i]->const_item(); + } +} + bool Item_row::check_cols(uint c) { if (c != arg_count) --- 1.24/sql/item_row.h 2007-03-24 03:43:58 +03:00 +++ 1.25/sql/item_row.h 2007-03-24 03:43:58 +03:00 @@ -59,6 +59,7 @@ return 0; }; bool fix_fields(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *new_parent, Item **ref); void cleanup(); void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields); table_map used_tables() const { return used_tables_cache; }; --- 1.146/sql/item_subselect.cc 2007-03-24 03:43:58 +03:00 +++ 1.147/sql/item_subselect.cc 2007-03-24 03:43:58 +03:00 @@ -612,7 +612,7 @@ Item_in_subselect::Item_in_subselect(Item * left_exp, st_select_lex *select_lex): Item_exists_subselect(), optimizer(0), transformed(0), - pushed_cond_guards(NULL), upper_item(0) + pushed_cond_guards(NULL), delay_fix_fields(FALSE), upper_item(0) { DBUG_ENTER("Item_in_subselect::Item_in_subselect"); left_expr= left_exp; @@ -1519,7 +1519,10 @@ bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref) { bool result = 0; - + ref_ptr= ref; + //if (delay_fix_fields) + // return 0; + if (thd_arg->lex->view_prepare_mode && left_expr && !left_expr->fixed) result = left_expr->fix_fields(thd_arg, &left_expr); --- 1.89/sql/item_subselect.h 2007-03-24 03:43:58 +03:00 +++ 1.90/sql/item_subselect.h 2007-03-24 03:43:58 +03:00 @@ -31,13 +31,15 @@ class Item_subselect :public Item_result_field { my_bool value_assigned; /* value already assigned to subselect */ -protected: +public: /* thread handler, will be assigned in fix_fields only */ THD *thd; /* substitution instead of subselect in case of optimization */ Item *substitution; /* unit of subquery */ +public: st_select_lex_unit *unit; +protected: /* engine that perform execution of subselect (single select or union) */ subselect_engine *engine; /* old engine if engine was changed */ @@ -238,8 +240,9 @@ class Item_in_subselect :public Item_exists_subselect { -protected: +public: Item *left_expr; +protected: /* expr & optimizer used in subselect rewriting to store Item for all JOIN in UNION @@ -252,6 +255,18 @@ public: /* Used to trigger on/off conditions that were pushed down to subselect */ bool *pushed_cond_guards; + + //psergey: + int sort_by; + bool delay_fix_fields; + + /* + Location of the subquery predicate. It is either + - pointer to join nest if the subquery predicate is in the ON expression + - (TABLE_LIST*)1 if the predicate is in the WHERE. + */ + TABLE_LIST *expr_join_nest; + Item **ref_ptr; bool *get_cond_guard(int i) { --- New file --- +++ mysql-test/r/subselect_sj.result 07/03/24 03:43:41 drop table if exists t0, t1, t2, t10, t11, t12; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); insert into t1 values (0,0),(1,1),(2,2); create table t2 as select * from t1; create table t11(a int, b int); create table t10 (pk int, a int, primary key(pk)); insert into t10 select a,a from t0; create table t12 like t10; insert into t12 select * from t10; Flattened because of dependency, t10=func(t1) explain select * from t1 where a in (select pk from t10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index select * from t1 where a in (select pk from t10); a b 0 0 1 1 2 2 A confluent case of dependency explain select * from t1 where a in (select a from t10 where pk=12); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t10 const PRIMARY PRIMARY 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where select * from t1 where a in (select a from t10 where pk=12); a b An empty table inside explain select * from t1 where a in (select a from t11); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t11 system NULL NULL NULL NULL 0 const row not found 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where select * from t1 where a in (select a from t11); a b explain select * from t1 where a in (select pk from t10) and b in (select pk from t10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index select * from t1 where a in (select pk from t10) and b in (select pk from t10); a b 0 0 1 1 2 2 flattening a nested subquery explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); a b 0 0 1 1 2 2 flattening subquery w/ several tables explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t12` join `test`.`t10` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`)) subqueries within outer joins go into ON expr. explain extended select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.a 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t1` left join (`test`.`t10` join `test`.`t2` `A` join `test`.`t2` `B`) on((((`test`.`A`.`a` = `test`.`t1`.`a`) and 1) and (`test`.`B`.`a` = `test`.`t10`.`pk`))) where 1 t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)" explain extended select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join (`test`.`t10` join `test`.`t2`) on((((`test`.`t2`.`a` = `test`.`t1`.`a`) and 1) and (`test`.`t2`.`a` = `test`.`t10`.`pk`))) where 1 we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19, t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29, t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39, t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49 where s00.a in ( select m00.a from t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09, t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY s01 ALL NULL NULL NULL NULL 3 1 PRIMARY s02 ALL NULL NULL NULL NULL 3 1 PRIMARY s03 ALL NULL NULL NULL NULL 3 1 PRIMARY s04 ALL NULL NULL NULL NULL 3 1 PRIMARY s05 ALL NULL NULL NULL NULL 3 1 PRIMARY s06 ALL NULL NULL NULL NULL 3 1 PRIMARY s07 ALL NULL NULL NULL NULL 3 1 PRIMARY s08 ALL NULL NULL NULL NULL 3 1 PRIMARY s09 ALL NULL NULL NULL NULL 3 1 PRIMARY s10 ALL NULL NULL NULL NULL 3 1 PRIMARY s11 ALL NULL NULL NULL NULL 3 1 PRIMARY s12 ALL NULL NULL NULL NULL 3 1 PRIMARY s13 ALL NULL NULL NULL NULL 3 1 PRIMARY s14 ALL NULL NULL NULL NULL 3 1 PRIMARY s15 ALL NULL NULL NULL NULL 3 1 PRIMARY s16 ALL NULL NULL NULL NULL 3 1 PRIMARY s17 ALL NULL NULL NULL NULL 3 1 PRIMARY s18 ALL NULL NULL NULL NULL 3 1 PRIMARY s19 ALL NULL NULL NULL NULL 3 1 PRIMARY s20 ALL NULL NULL NULL NULL 3 1 PRIMARY s21 ALL NULL NULL NULL NULL 3 1 PRIMARY s22 ALL NULL NULL NULL NULL 3 1 PRIMARY s23 ALL NULL NULL NULL NULL 3 1 PRIMARY s24 ALL NULL NULL NULL NULL 3 1 PRIMARY s25 ALL NULL NULL NULL NULL 3 1 PRIMARY s26 ALL NULL NULL NULL NULL 3 1 PRIMARY s27 ALL NULL NULL NULL NULL 3 1 PRIMARY s28 ALL NULL NULL NULL NULL 3 1 PRIMARY s29 ALL NULL NULL NULL NULL 3 1 PRIMARY s30 ALL NULL NULL NULL NULL 3 1 PRIMARY s31 ALL NULL NULL NULL NULL 3 1 PRIMARY s32 ALL NULL NULL NULL NULL 3 1 PRIMARY s33 ALL NULL NULL NULL NULL 3 1 PRIMARY s34 ALL NULL NULL NULL NULL 3 1 PRIMARY s35 ALL NULL NULL NULL NULL 3 1 PRIMARY s36 ALL NULL NULL NULL NULL 3 1 PRIMARY s37 ALL NULL NULL NULL NULL 3 1 PRIMARY s38 ALL NULL NULL NULL NULL 3 1 PRIMARY s39 ALL NULL NULL NULL NULL 3 1 PRIMARY s40 ALL NULL NULL NULL NULL 3 1 PRIMARY s41 ALL NULL NULL NULL NULL 3 1 PRIMARY s42 ALL NULL NULL NULL NULL 3 1 PRIMARY s43 ALL NULL NULL NULL NULL 3 1 PRIMARY s44 ALL NULL NULL NULL NULL 3 1 PRIMARY s45 ALL NULL NULL NULL NULL 3 1 PRIMARY s46 ALL NULL NULL NULL NULL 3 1 PRIMARY s47 ALL NULL NULL NULL NULL 3 1 PRIMARY s48 ALL NULL NULL NULL NULL 3 1 PRIMARY s49 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B; The join order must be t10, t1: explain select * from t1 where a in (select pk from t10 where pk<3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 1 PRIMARY t1 ALL NULL NULL NULL NULL 103 Using where --- New file --- +++ mysql-test/t/subselect_sj.test 07/03/24 03:43:41 # # Nested Loops semi-join subquery evaluation tests # --disable_warnings drop table if exists t0, t1, t2, t10, t11, t12; --enable_warnings # # 1. Subqueries that are converted into semi-joins # create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); insert into t1 values (0,0),(1,1),(2,2); create table t2 as select * from t1; create table t11(a int, b int); create table t10 (pk int, a int, primary key(pk)); insert into t10 select a,a from t0; create table t12 like t10; insert into t12 select * from t10; --echo Flattened because of dependency, t10=func(t1) explain select * from t1 where a in (select pk from t10); select * from t1 where a in (select pk from t10); --echo A confluent case of dependency explain select * from t1 where a in (select a from t10 where pk=12); select * from t1 where a in (select a from t10 where pk=12); --echo An empty table inside explain select * from t1 where a in (select a from t11); select * from t1 where a in (select a from t11); explain select * from t1 where a in (select pk from t10) and b in (select pk from t10); select * from t1 where a in (select pk from t10) and b in (select pk from t10); --echo flattening a nested subquery explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12)); --echo flattening subquery w/ several tables explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a); --echo subqueries within outer joins go into ON expr. explain extended select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10)); --echo t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)" explain extended select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)); --echo we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19, t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29, t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39, t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49 where s00.a in ( select m00.a from t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09, t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19 ); # Try I2O orders insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B; --echo The join order must be t10, t1: explain select * from t1 where a in (select pk from t10 where pk<3);
| Thread | ||
|---|---|---|
| • bk commit into 5.2 tree (sergefp:1.2456) | Sergey Petrunia | 24 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.313 sec. using MySQL 5.1.14-beta-log