From: Roy Lyseng Date: March 22 2012 11:54am Subject: bzr push into mysql-trunk branch (roy.lyseng:3890 to 3891) WL#5561 List-Archive: http://lists.mysql.com/commits/143280 Message-Id: <20120322115429.9AD1B211@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3891 Roy Lyseng 2012-03-22 WL#5561 - Enable semi-join transformation with outer join queries Third worklog commit, after Guilhem's review. sql/sql_optimizer.cc Deleted some unused code, forgotten in previous commit. sql/sql_planner.cc Updated check_interleaving_with_nj() according to review comments. sql/sql_select.cc Typos fixed. sql/table.h Adjusted some comments. modified: sql/sql_optimizer.cc sql/sql_planner.cc sql/sql_select.cc sql/table.h 3890 Roy Lyseng 2012-03-21 WL#5561 - Enable semi-join transformation with outer join queries Second worklog commit, after Guilhem's review. mysql-test/include/subquery_sj.inc Added a test case to demonstrate impact of table extension when setting up duplicate weedout for tables involved in outer join. mysql-test/r/subquery_all.result mysql-test/r/subquery_all_bka.result mysql-test/r/subquery_all_bka_nixbnl.result Changed plan due to modifications to nested join advance and backout. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result New results for the added test case. sql/item_func.cc Change a return to an old-style assert in Item_func::fix_fields. sql/sql_executor.cc Added a const modifier in do_sj_dups_weedout(). Changed a comment in evaluate_null_complemented_join_record(). sql/sql_optimizer.cc In optimize_semijoin_nests_for_materialization(), updated code according to review comments. sql/sql_planner.cc Optimize_table_order::check_interleaving_with_nj() and Optimize_table_order::backout_nj_sj_state() are updated and simplified according to review comments. sql/sql_select.cc Table extension code in duplicate weedout changed according to review comments (setup_semijoin_dups_elimination()). Comment added in setup_join_buffering(), according to review comments. sql/table.h Changed comments of new members according to review comments. Removed is_fully_covered() function, as it was barely used. modified: mysql-test/include/subquery_sj.inc mysql-test/r/subquery_all.result mysql-test/r/subquery_all_bka.result mysql-test/r/subquery_all_bka_nixbnl.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result sql/item_func.cc sql/sql_executor.cc sql/sql_optimizer.cc sql/sql_planner.cc sql/sql_select.cc sql/table.h === modified file 'sql/sql_optimizer.cc' --- a/sql/sql_optimizer.cc 2012-03-21 11:22:13 +0000 +++ b/sql/sql_optimizer.cc 2012-03-22 11:53:41 +0000 @@ -3496,42 +3496,6 @@ void semijoin_types_allow_materializatio sj_nest->nested_join->sjm.lookup_allowed= !blobs_involved; DBUG_PRINT("info",("semijoin_types_allow_materialization: ok, allowed")); -#if defined(UNUSED) - // @todo: When sub_select_sjm() can handle outer joins, - // enable the lines marked SJM-DISABLED and disable the lines - // marked SJM-ENABLED. - // Note also next todo a few lines below. - //if (sj_nest->nested_join->sjm.scan_allowed) SJM-DISABLED - if (sj_nest->nested_join->sjm.scan_allowed || // SJM-ENABLED - sj_nest->nested_join->sjm.lookup_allowed) // SJM-ENABLED - { - /* - If the semi-join contains dependencies to outer tables, - materialize-scan strategy cannot be used. - */ - JOIN_TAB *jt; - for (jt= join->join_tab + join->const_tables; - jt < join->join_tab + join->tables; - jt++) - { - if ((jt->table->map & sj_nest->sj_inner_tables) && - (jt->dependent & ~sj_nest->sj_inner_tables)) - break; - } - //if (jt < join->join_tab + join->tables) SJM-DISABLED - // sj_nest->nested_join->sjm.scan_allowed= false; SJM-DISABLED - if (jt < join->join_tab + join->tables) // SJM-ENABLED - { // SJM-ENABLED - sj_nest->nested_join->sjm.scan_allowed= false; // SJM-ENABLED - sj_nest->nested_join->sjm.lookup_allowed= false; // SJM-ENABLED - } // SJM-ENABLED - } - - DBUG_PRINT("info",("semijoin_types_allow_materialization: %s", - sj_nest->nested_join->sjm.scan_allowed || - sj_nest->nested_join->sjm.lookup_allowed ? - "ok, allowed" : "not allowed")); -#endif DBUG_VOID_RETURN; } === modified file 'sql/sql_planner.cc' --- a/sql/sql_planner.cc 2012-03-21 11:22:13 +0000 +++ b/sql/sql_planner.cc 2012-03-22 11:53:41 +0000 @@ -2751,7 +2751,7 @@ bool Optimize_table_order::check_interle Do update counters for "pairs of brackets" that we've left (marked as X,Y,Z in the above picture) */ - for (;next_emb; next_emb= next_emb->embedding) + for (; next_emb != emb_sjm_nest; next_emb= next_emb->embedding) { // Ignore join nests that are not outer joins. if (!next_emb->nested_join->nj_map) === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-03-21 11:22:13 +0000 +++ b/sql/sql_select.cc 2012-03-22 11:53:41 +0000 @@ -542,7 +542,7 @@ static bool setup_semijoin_dups_eliminat and the last duplicate weedout table is one of the inner tables of the outer join. In this case, we must assure that all the inner tables of the - outer join is part of the duplicate weedout operation. + outer join are part of the duplicate weedout operation. This is to assure that NULL-extension for inner tables of an outer join is performed before duplicate elimination is performed, otherwise we will have extra NULL-extended rows being output, which @@ -2281,7 +2281,7 @@ static bool setup_join_buffering(JOIN_TA The following code eliminates use of join buffering when there is an outer join operation and first match semi-join strategy is used, because: - Outer join needs a "match flag" to track what a row should be + Outer join needs a "match flag" to track that a row should be NULL-complemented, such flag being attached to first inner table's cache (tracks whether the cached row from outer table got a match, in which case no NULL-complemented row is needed). === modified file 'sql/table.h' --- a/sql/table.h 2012-03-21 11:22:13 +0000 +++ b/sql/table.h 2012-03-22 11:53:41 +0000 @@ -48,9 +48,8 @@ class Field; class Field_temporal_with_date_and_time; /* - Used to identify NESTED_JOIN structures within a join (applicable only to - structures that have not been simplified away and embed more the one - element) + Used to identify NESTED_JOIN structures within a join (applicable to + structures representing outer joins that have not been simplified away). */ typedef ulonglong nested_join_map; @@ -2127,7 +2126,7 @@ typedef struct st_nested_join /** Used to count tables in the nested join in 2 isolated places: 1. In make_outerjoin_info(). - 2. check_interleaving_with_nj/restore_prev_nj_state (these are called + 2. check_interleaving_with_nj/backout_nj_state (these are called by the join optimizer. Before each use the counters are zeroed by reset_nj_counters. */ No bundle (reason: useless for push emails).