bzr commit into mysql-trunk branch (roy.lyseng:3359) Bug#11822517

[Roy Lyseng]

#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:roy.lyseng@stripped

 3359 Roy Lyseng	2011-04-06
      Bug#11822517: Refactor call interface of choose_plan()
      
      Part 2 - Change meaning of greedy_search() argument 'remaining_tables'.
      
      The purpose of this patch is to make the interface of greedy_search()
      and related functions simpler.
      It is also a preparatory step for a simpler test for dependencies when
      doing semi join materialization together with outer join (WL#5561).
      
      The meaning of the argument remaining_tables to greedy_search() is changed:
      Before, it meant all tables not added to the current join plan,
      regardless of whether the tables were to be optimized or not.
      Now, it means all tables remaining to be added.
      
      The difference is important when making a plan for a materialized
      semi join nest. Before, remaining_tables included also the tables
      belonging to the query, but not part of the semi join nest being
      optimized. This makes for simpler implementation of
      best_extension_by_limited_search() and easier maintenance.
      
      However, best_access_path() requires that information about all tables
      not yet added to the plan be available. We implement this by adding
      a member variable excluded_tables in the Optimize_join_tables class,
      which contains the set of all tables in the query block that are not
      being optimized in the current operation.
      
      sql/sql_select.cc
        New member variable excluded_tables is added to Optimize_join_tables.
      
        In choose_table_order(), join_tables is calculated as the set of
        tables to be optimized.
      
        In best_extension_by_limited_search(), use of allowed_tables is
        eliminated, and logic has been slightly simplified.

    modified:
      sql/sql_select.cc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-04-06 11:56:55 +0000
+++ b/sql/sql_select.cc	2011-04-06 13:27:04 +0000
@@ -284,7 +284,10 @@ public:
                                         thd->variables.optimizer_search_depth)),
     prune_level(thd->variables.optimizer_prune_level),
     thd(thd), join(join),
-    cur_embedding_map(0)
+    cur_embedding_map(0),
+    excluded_tables(sjm_nest ?
+                    join->all_table_map & ~sjm_nest->sj_inner_tables :
+                    0)
   {
     join->emb_sjm_nest= sjm_nest;
   }
@@ -312,6 +315,16 @@ private:
   */
   nested_join_map cur_embedding_map;
   /**
+    When calculating a plan for a materialized semi join nest,
+    best_access_plan() needs to know not only the remaining tables within the
+    semi join nest, but also all tables outside of this nest, because there may
+    be key references between the semi join nest and the outside tables
+    that should not be considered when materializing the semi join nest.
+    @c excluded_tables tracks these tables.
+  */
+  const table_map excluded_tables;
+
+  /**
     @todo: Add remaining Join optimization state members here,
     ie emb_sjm_nest, positions, cur_sj_inner_tables.
     This is not yet possible because (some of them) are accessed through
@@ -7648,7 +7661,7 @@ bool Optimize_table_order::choose_table_
        tables from this semi-join as first
     */
     jtab_sort_func= join_tab_cmp_embedded_first;
-    join_tables= join->all_table_map & ~join->const_table_map;
+    join_tables= join->emb_sjm_nest->sj_inner_tables;
     /*
       Inner tables of semi join nest are not allowed to be identified as
       const tables.
@@ -8334,9 +8347,6 @@ bool Optimize_table_order::best_extensio
   DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time,
                                 "part_plan"););
 
-  table_map allowed_tables= ~(table_map)0;
-  if (join->emb_sjm_nest)
-    allowed_tables= join->emb_sjm_nest->sj_inner_tables;
   const bool has_sj=
     !(join->select_lex->sj_nests.is_empty() || join->emb_sjm_nest);
 
@@ -8358,7 +8368,6 @@ bool Optimize_table_order::best_extensio
     swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
 
     if ((remaining_tables & real_table_bit) && 
-        (allowed_tables & real_table_bit) &&
         !(remaining_tables & s->dependent) && 
         (!idx || !check_interleaving_with_nj(s)))
     {
@@ -8367,7 +8376,7 @@ bool Optimize_table_order::best_extensio
 
       /* Find the best access method from 's' to the current partial plan */
       POSITION loose_scan_pos;
-      best_access_path(join, s, remaining_tables,
+      best_access_path(join, s, excluded_tables | remaining_tables,
                        idx, false, record_count, 
                        position, &loose_scan_pos);
 
@@ -8439,8 +8448,7 @@ bool Optimize_table_order::best_extensio
         }
       }
 
-      if ((current_search_depth > 1) &&
-          (remaining_tables & ~real_table_bit) & allowed_tables )
+      if ((current_search_depth > 1) && (remaining_tables & ~real_table_bit))
       {
         /* Explore more best extensions of plan */
         if (best_extension_by_limited_search(remaining_tables & ~real_table_bit,


Attachment: [text/bzr-bundle] bzr/[e-mail]