Fixed the bugs mdev-12670 and mdev-12675.

The code that blocked conversion of a IN subselect pedicate to a semi-join
if it occurred in the ON expression of an outer join did not do it correctly.
As a result, the conversion was blocked for IN subselect predicates
encountered in ON expressions of INNER joins or in WHERE conditions
of mergeable views / derived tables. This patch fixes this problem.

3 files changed, 86 insertions, 7 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 78dcfc4215c..12337ec2b1c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1366,7 +1366,7 @@ Item_in_subselect::Item_in_subselect(Item * left_exp,
   Item_exists_subselect(), 
   left_expr_cache(0), first_execution(TRUE), in_strategy(SUBS_NOT_TRANSFORMED),
   optimizer(0), pushed_cond_guards(NULL), emb_on_expr_nest(NULL),
-  is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE), 
+  do_not_convert_to_sj(FALSE), is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE), 
   is_flattenable_semijoin(FALSE),
   is_registered_semijoin(FALSE), 
   upper_item(0)
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 2f166c83e8f..75822ff8c6b 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -466,6 +466,8 @@ public:
       NULL              - for all other locations
   */
   TABLE_LIST *emb_on_expr_nest;
+  /* May be TRUE only for the candidates to semi-join conversion */ 
+  bool do_not_convert_to_sj; 
   /*
     Types of left_expr and subquery's select list allow to perform subquery
     materialization. Currently, we set this to FALSE when it as well could
@@ -554,9 +556,9 @@ public:
   Item_in_subselect()
     :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
      abort_on_null(0), in_strategy(SUBS_NOT_TRANSFORMED), optimizer(0),
-    pushed_cond_guards(NULL), func(NULL), emb_on_expr_nest(NULL), 
-    is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE),
-    upper_item(0)
+    pushed_cond_guards(NULL), func(NULL), emb_on_expr_nest(NULL),
+    do_not_convert_to_sj(FALSE), is_jtbm_merged(FALSE),
+    is_jtbm_const_tab(FALSE), upper_item(0)
     {}
   void cleanup();
   subs_type substype() { return IN_SUBS; }
@@ -617,6 +619,8 @@ public:
     emb_on_expr_nest= embedding;
   }
 
+  void block_conversion_to_sj () { do_not_convert_to_sj= TRUE; }
+
   bool test_strategy(uchar strategy)
   { return test(in_strategy & strategy); }
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 5137d8a0986..645afa6744a 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -678,6 +678,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         !((join->select_options |                                     // 10
            select_lex->outer_select()->join->select_options)          // 10
           & SELECT_STRAIGHT_JOIN))                                    // 10
+ 
     {
       DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
 
@@ -1000,6 +1001,25 @@ bool check_for_outer_joins(List<TABLE_LIST> *join_list)
 }
 
 
+void find_and_block_conversion_to_sj(Item *to_find,
+				     List_iterator_fast<Item_in_subselect> &li)
+{
+  if (to_find->type() != Item::SUBSELECT_ITEM ||
+      ((Item_subselect *) to_find)->substype() != Item_subselect::IN_SUBS)
+    return;
+  Item_in_subselect *in_subq;
+  li.rewind();
+  while ((in_subq= li++))
+  {
+    if (in_subq == to_find)
+    {
+      in_subq->block_conversion_to_sj();
+      return;
+    }
+  }
+}
+
+
 /*
   Convert semi-join subquery predicates into semi-join join nests
 
@@ -1052,7 +1072,6 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
   Query_arena *arena, backup;
   Item_in_subselect *in_subq;
   THD *thd= join->thd;
-  List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables);
   DBUG_ENTER("convert_join_subqueries_to_semijoins");
 
   if (join->select_lex->sj_subselects.is_empty())
@@ -1070,6 +1089,60 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
     subq_sel->update_used_tables();
   }
 
+  /* 
+    Check all candidates to semi-join conversion that occur
+    in ON expressions of outer join. Set the flag blocking
+    this conversion for them.
+  */
+  TABLE_LIST *tbl;
+  List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables);
+  while ((tbl= ti++))
+  {
+    TABLE_LIST *embedded;
+    TABLE_LIST *embedding= tbl;
+    do
+    {
+      embedded= embedding;
+      if (test(embedded->outer_join))
+      {
+	Item *cond= embedded->on_expr;
+        if (!cond)
+          ;
+        else if (cond->type() != Item::COND_ITEM)
+          find_and_block_conversion_to_sj(cond, li);
+        else if (((Item_cond*) cond)->functype() ==
+	         Item_func::COND_AND_FUNC)
+	{
+          Item *item;
+          List_iterator<Item> it(*(((Item_cond*) cond)->argument_list()));
+          while ((item= it++))
+	  {
+	    find_and_block_conversion_to_sj(item, li);
+          }
+	}
+      }
+      embedding= embedded->embedding;
+    }
+    while (embedding &&
+           embedding->nested_join->join_list.head() == embedded);
+  }
+
+  /* 
+    Block conversion to semi-joins for those candidates that
+    are encountered in the WHERE condition of the multi-table view
+    with CHECK OPTION if this view is used in UPDATE/DELETE.
+    (This limitation can be, probably, easily lifted.) 
+  */  
+  li.rewind();
+  while ((in_subq= li++))
+  {
+    if (in_subq->emb_on_expr_nest != NO_JOIN_NEST &&
+        in_subq->emb_on_expr_nest->effective_with_check)
+    {
+      in_subq->block_conversion_to_sj();
+    }
+  }
+      
   li.rewind();
   /* First, convert child join's subqueries. We proceed bottom-up here */
   while ((in_subq= li++)) 
@@ -1088,8 +1161,10 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
 
     if (convert_join_subqueries_to_semijoins(child_join))
       DBUG_RETURN(TRUE);
+
+
     in_subq->sj_convert_priority= 
-      test(in_subq->emb_on_expr_nest != NO_JOIN_NEST) * MAX_TABLES * 2 +
+      test(in_subq->do_not_convert_to_sj) * MAX_TABLES * 2 +
       in_subq->is_correlated * MAX_TABLES + child_join->outer_tables;
   }
   
@@ -1122,7 +1197,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
     bool remove_item= TRUE;
 
     /* Stop processing if we've reached a subquery that's attached to the ON clause */
-    if (in_subq->emb_on_expr_nest != NO_JOIN_NEST)
+    if (in_subq->do_not_convert_to_sj)
       break;
 
     if (in_subq->is_flattenable_semijoin)