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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3890 to 3891) WL#5561 | Roy Lyseng | 22 Mar |
