At file:///home/psergey/dev/maria-5.3-subqueries-r12/ ------------------------------------------------------------ revno: 2795 revision-id: psergey@askmonty.org-20100510152819-782x0h73kolavb90 parent: psergey@askmonty.org-20100425082352-ili66ufb1v35ooer committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r12 timestamp: Mon 2010-05-10 19:28:19 +0400 message: Subquery optimizations: - Better comments - Use more appropriate return types for functions - Provide handling where it was missing. === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-06 21:29:09 +0000 +++ b/sql/item_subselect.cc 2010-05-10 15:28:19 +0000 @@ -188,7 +188,7 @@ { // all transformation is done (used by prepared statements) changed= 1; - inside_first_fix_fields= FALSE; + inside_first_fix_fields= FALSE; // all transformation is done (used by prepared statements) @@ -217,13 +217,13 @@ if (!(*ref)->fixed) res= (*ref)->fix_fields(thd, ref); goto end; -//psergey-merge: done_first_fix_fields= FALSE; + } // Is it one field subselect? if (engine->cols() > max_columns) { my_error(ER_OPERAND_COLUMNS, MYF(0), 1); -//psergey-merge: done_first_fix_fields= FALSE; + goto end; } fix_length_and_dec(); @@ -241,6 +241,7 @@ end: done_first_fix_fields= FALSE; + inside_first_fix_fields= FALSE; thd->where= save_where; return res; } === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-04-25 08:23:52 +0000 +++ b/sql/opt_subselect.cc 2010-05-10 15:28:19 +0000 @@ -16,7 +16,6 @@ #include <my_bit.h> -// Our own: static bool subquery_types_allow_materialization(Item_in_subselect *in_subs); static bool replace_where_subcondition(JOIN *join, Item **expr, @@ -52,17 +51,22 @@ /* Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them + SYNOPSIS + check_and_do_in_subquery_rewrites() + join Subquery's join + DESCRIPTION Check if we need to do - - subquery->semi-join rewrite + - subquery -> mergeable semi-join rewrite - if the subquery can be handled with materialization - 'substitution' rewrite for table-less subqueries like "(select 1)" - - and mark appropriately + - IN->EXISTS rewrite + and, depending on the rewrite, either do it, or record it to be done at a + later phase. RETURN - 0 - OK - -1 - Some sort of query error + 0 - OK + Other - Some sort of query error */ int check_and_do_in_subquery_rewrites(JOIN *join) @@ -387,7 +391,7 @@ TODO: what about delaying that rewrite until here? */ if (!item->convert_to_semi_join) - { + { //psergey-jtbm-fix: this branch is always taken?? replace_me= item->optimizer; } @@ -1019,6 +1023,20 @@ } +/* + Convert subquery predicate into non-mergeable semi-join nest. + + TODO: + why does this do IN-EXISTS conversion? Can't we unify it with mergeable + semi-joins? currently, convert_subq_to_sj() cannot fail to convert (unless + fatal errors) + + + RETURN + FALSE - Ok + TRUE - Fatal error +*/ + static bool convert_subq_to_jtbm(JOIN *parent_join, Item_in_subselect *subq_pred, bool *remove_item) @@ -1058,24 +1076,15 @@ /* jtbm->next_leaf= jtbm->next_local= jtbm->next_global == NULL*/ emb_join_list->push_back(jtbm); - /* Inject ourselves into next-leaf list */ /* - JTBM: Inject us into next_leaf and lext_local chains.. - so that make_join_statistics et al find us. - */ - - /* - 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. + Inject the jtbm table into TABLE_LIST::next_leaf list, so that + make_join_statistics() and co. can find it. */ for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf) ; tl->next_leaf= jtbm; /* - Same as above for next_local chain + Same as above for TABLE_LIST::next_local chain (a theory: a next_local chain always starts with ::leaf_tables because view's tables are inserted after the view) */ @@ -1540,7 +1549,7 @@ return len; } -//psergey-todo: is the below a kind of table elimination?? + /* Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate @@ -1557,6 +1566,8 @@ Check again if it is feasible to factor common parts with constant table search + Also check if it's feasible to factor common parts with table elimination + RETURN TRUE - There exists an eq_ref(outer-tables) candidate FALSE - Otherwise @@ -3744,7 +3755,7 @@ } -int do_jtbm_materialization_if_needed(JOIN_TAB *tab) +bool do_jtbm_materialization_if_needed(JOIN_TAB *tab) { Item_in_subselect *in_subs; if (tab->table->pos_in_table_list && @@ -3761,9 +3772,9 @@ hash_sj_engine->is_materialized= TRUE; if (hash_sj_engine->materialize_join->error || tab->join->thd->is_fatal_error) - return 1; + return TRUE; } } - return 0; + return FALSE; } === modified file 'sql/opt_subselect.h' --- a/sql/opt_subselect.h 2010-04-25 08:23:52 +0000 +++ b/sql/opt_subselect.h 2010-05-10 15:28:19 +0000 @@ -370,5 +370,5 @@ void get_temptable_params(Item_in_subselect *item, ha_rows *out_rows, ha_rows *scan_time); -int do_jtbm_materialization_if_needed(JOIN_TAB *tab); +bool do_jtbm_materialization_if_needed(JOIN_TAB *tab); === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-04-05 20:16:45 +0000 +++ b/sql/sql_join_cache.cc 2010-05-10 15:28:19 +0000 @@ -33,8 +33,6 @@ #define NO_MORE_RECORDS_IN_BUFFER (uint)(-1) -int do_jtbm_materialization_if_needed(JOIN_TAB *tab); - /***************************************************************************** * Join cache module ******************************************************************************/ @@ -1780,8 +1778,11 @@ /* Start retrieving all records of the joined table */ - //jtbm-todo: error handling! - do_jtbm_materialization_if_needed(join_tab); + if (do_jtbm_materialization_if_needed(join_tab)) + { + rc= NESTED_LOOP_ERROR; + goto finish; + } if ((error= join_init_read_record(join_tab))) { === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-04-25 08:23:52 +0000 +++ b/sql/sql_select.cc 2010-05-10 15:28:19 +0000 @@ -12858,8 +12858,9 @@ } join->thd->row_count= 0; - //jtbm-todo: error handling! - do_jtbm_materialization_if_needed(join_tab); + if (do_jtbm_materialization_if_needed(join_tab)) + DBUG_RETURN(NESTED_LOOP_ERROR); + error= (*join_tab->read_first_record)(join_tab); if (join_tab->keep_current_rowid)