1 files changed, 67 insertions, 23 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index d3fdda04520..46a900616ec 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -26,8 +26,6 @@ static int subq_sj_candidate_cmp(Item_in_subselect* const *el1,
                                  Item_in_subselect* const *el2);
 static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred);
 static TABLE_LIST *alloc_join_nest(THD *thd);
-static 
-void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist);
 static uint get_tmp_table_rec_length(List<Item> &items);
 static double get_tmp_table_lookup_cost(THD *thd, double row_count,
                                         uint row_size);
@@ -179,9 +177,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         !join->having && !select_lex->with_sum_func &&                // 4
         thd->thd_marker.emb_on_expr_nest &&                           // 5
         select_lex->outer_select()->join &&                           // 6
-        parent_unit->first_select()->leaf_tables &&                   // 7
+        parent_unit->first_select()->leaf_tables.elements &&          // 7
         !in_subs->in_strategy &&                                      // 8
-        select_lex->outer_select()->leaf_tables &&                    // 9
+        select_lex->outer_select()->leaf_tables.elements &&           // 9
         !((join->select_options |                                     // 10
            select_lex->outer_select()->join->select_options)          // 10
           & SELECT_STRAIGHT_JOIN))                                    // 10
@@ -242,9 +240,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         */
         if (!(in_subs->in_strategy & SUBS_MATERIALIZATION && 
               !select_lex->is_part_of_union() &&                            // 1
-              parent_unit->first_select()->leaf_tables &&                   // 2
+              parent_unit->first_select()->leaf_tables.elements &&          // 2
               thd->lex->sql_command == SQLCOM_SELECT &&                     // *
-              select_lex->outer_select()->leaf_tables &&                    // 2A
+              select_lex->outer_select()->leaf_tables.elements &&           // 2A
               subquery_types_allow_materialization(in_subs) &&
               // psergey-todo: duplicated_subselect_card_check: where it's done?
               (in_subs->is_top_level_item() ||                               //3
@@ -401,6 +399,26 @@ bool convert_max_min_subquery(JOIN *join)
 }
 
 
+bool check_for_outer_joins(List<TABLE_LIST> *join_list)
+{
+  TABLE_LIST *table;
+  NESTED_JOIN *nested_join;
+  List_iterator<TABLE_LIST> li(*join_list);
+  while ((table= li++))
+  {
+    if ((nested_join= table->nested_join))
+    {
+      if (check_for_outer_joins(&nested_join->join_list))
+        return TRUE;
+    }
+    
+    if (table->outer_join)
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
 /*
   Convert semi-join subquery predicates into semi-join join nests
 
@@ -454,11 +472,25 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
   Item_in_subselect **in_subq;
   Item_in_subselect **in_subq_end;
   THD *thd= join->thd;
+  List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables);
   DBUG_ENTER("convert_join_subqueries_to_semijoins");
 
   if (join->sj_subselects.elements() == 0)
     DBUG_RETURN(FALSE);
 
+  for (in_subq= join->sj_subselects.front(), 
+       in_subq_end= join->sj_subselects.back(); 
+       in_subq != in_subq_end; 
+       in_subq++)
+  {
+    SELECT_LEX *subq_sel= (*in_subq)->get_select_lex();
+    if (subq_sel->handle_derived(thd->lex, DT_OPTIMIZE))
+      DBUG_RETURN(1);
+    if (subq_sel->handle_derived(thd->lex, DT_MERGE))
+      DBUG_RETURN(TRUE);
+    subq_sel->update_used_tables();
+  }
+
   /* First, convert child join's subqueries. We proceed bottom-up here */
   for (in_subq= join->sj_subselects.front(), 
        in_subq_end= join->sj_subselects.back(); 
@@ -485,16 +517,24 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
   
   // Temporary measure: disable semi-joins when they are together with outer
   // joins.
-  for (TABLE_LIST *tbl= join->select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)
+  /*
+  while ((tbl= ti++))
   {
     TABLE_LIST *embedding= tbl->embedding;
-    if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr && 
-                                            !embedding->embedding)))
+    if (tbl->on_expr ||
+        (embedding && embedding->outer_join && 
+         !(embedding->sj_on_expr && !embedding->embedding)))
     {
       in_subq= join->sj_subselects.front();
       arena= thd->activate_stmt_arena_if_needed(&backup);
       goto skip_conversion;
     }
+  }*/
+  if (check_for_outer_joins(join->join_list))
+  {
+    in_subq= join->sj_subselects.front();
+    arena= thd->activate_stmt_arena_if_needed(&backup);
+    goto skip_conversion;
   }
 
   //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
@@ -808,7 +848,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
   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;
+  TABLE_LIST *tl;
   while ((tl= li++))
   {
     tl->embedding= sj_nest;
@@ -823,17 +863,15 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     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;
+  parent_lex->leaf_tables.concat(&subq_lex->leaf_tables);
 
   /*
     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;
+  for (tl= parent_lex->leaf_tables.head(); tl->next_local; tl= tl->next_local) ;
+  tl->next_local= subq_lex->leaf_tables.head();
 
   /* A theory: no need to re-connect the next_global chain */
 
@@ -846,7 +884,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
   /* 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++)
+  List_iterator_fast<TABLE_LIST> si(subq_lex->leaf_tables);
+  while ((tl= si++))
   {
     tl->table->tablenr= table_no;
     tl->table->map= ((table_map)1) << table_no;
@@ -856,6 +895,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
          emb && emb->select_lex == old_sl;
          emb= emb->embedding)
       emb->select_lex= parent_join->select_lex;
+    table_no++;
   }
   parent_join->tables += subq_lex->join->tables;
 
@@ -948,7 +988,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     */
     save_lex= thd->lex->current_select;
     thd->lex->current_select=parent_join->select_lex;
-    parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
+    if (!parent_join->conds->fixed)
+      parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
     thd->lex->current_select=save_lex;
     parent_join->select_lex->where= parent_join->conds;
   }
@@ -976,7 +1017,6 @@ static TABLE_LIST *alloc_join_nest(THD *thd)
 }
 
 
-static
 void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
 {
   List_iterator<TABLE_LIST> it(*tlist);
@@ -1545,6 +1585,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables,
   TABLE_LIST *emb_sj_nest;
   POSITION *pos= join->positions + idx;
   remaining_tables &= ~new_join_tab->table->map;
+  bool disable_jbuf= join->thd->variables.join_cache_level == 0;
 
   pos->prefix_cost.convert_from_cost(*current_read_time);
   pos->prefix_record_count= *current_record_count;
@@ -1710,7 +1751,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables,
       optimize_wo_join_buffering(join, pos->first_loosescan_table, idx,
                                  remaining_tables, 
                                  TRUE,  //first_alt
-                                 pos->first_loosescan_table + n_tables,
+                                 disable_jbuf ? join->tables :
+                                   pos->first_loosescan_table + n_tables,
                                  current_record_count,
                                  current_read_time);
       /*
@@ -1849,8 +1891,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables,
     /* Need to re-run best-access-path as we prefix_rec_count has changed */
     for (i= first_tab + mat_info->tables; i <= idx; i++)
     {
-      best_access_path(join, join->positions[i].table, rem_tables, i, FALSE,
-                       prefix_rec_count, &curpos, &dummy);
+      best_access_path(join, join->positions[i].table, rem_tables, i,
+                       disable_jbuf, prefix_rec_count, &curpos, &dummy);
       prefix_rec_count *= curpos.records_read;
       prefix_cost += curpos.read_time;
     }
@@ -2149,6 +2191,7 @@ at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab,
 void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
 {
   uint table_count=join->tables;
+  bool disable_jbuf= join->thd->variables.join_cache_level == 0;
   uint tablenr;
   table_map remaining_tables= 0;
   table_map handled_tabs= 0;
@@ -2210,8 +2253,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
       join->cur_sj_inner_tables= 0;
       for (i= first + sjm->tables; i <= tablenr; i++)
       {
-        best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
-                         prefix_rec_count, join->best_positions + i, &dummy);
+        best_access_path(join, join->best_positions[i].table, rem_tables, i,
+                         disable_jbuf, prefix_rec_count,
+                         join->best_positions + i, &dummy);
         prefix_rec_count *= join->best_positions[i].records_read;
         rem_tables &= ~join->best_positions[i].table->table->map;
       }