1 files changed, 520 insertions, 61 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index ba674743724..7d361263548 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -29,6 +29,7 @@
 #pragma implementation				// gcc: Class implementation
 #endif
 
+#include <my_global.h>
 #include "sql_priv.h"
 /*
   It is necessary to include set_var.h instead of item.h because there
@@ -43,6 +44,9 @@
 
 double get_post_group_estimate(JOIN* join, double join_op_rows);
 
+const char *exists_outer_expr_name= "<exists outer expr>";
+
+int check_and_do_in_subquery_rewrites(JOIN *join);
 
 Item_subselect::Item_subselect():
   Item_result_field(), value_assigned(0), own_engine(0), thd(0), old_engine(0), 
@@ -83,15 +87,24 @@ void Item_subselect::init(st_select_lex *select_lex,
 
   if (unit->item)
   {
-    /*
-      Item can be changed in JOIN::prepare while engine in JOIN::optimize
-      => we do not copy old_engine here
-    */
     engine= unit->item->engine;
-    own_engine= FALSE;
     parsing_place= unit->item->parsing_place;
-    thd->change_item_tree((Item**)&unit->item, this);
-    engine->change_result(this, result, TRUE);
+    if (unit->item->substype() == EXISTS_SUBS &&
+        ((Item_exists_subselect *)unit->item)->exists_transformed)
+    {
+      /* it is permanent transformation of EXISTS to IN */
+      unit->item= this;
+      engine->change_result(this, result, FALSE);
+    }
+    else
+    {
+      /*
+        Item can be changed in JOIN::prepare while engine in JOIN::optimize
+        => we do not copy old_engine here
+      */
+      thd->change_item_tree((Item**)&unit->item, this);
+      engine->change_result(this, result, TRUE);
+    }
   }
   else
   {
@@ -462,7 +475,7 @@ public:
 void Item_subselect::recalc_used_tables(st_select_lex *new_parent, 
                                         bool after_pullout)
 {
-  List_iterator<Ref_to_outside> it(upper_refs);
+  List_iterator_fast<Ref_to_outside> it(upper_refs);
   Ref_to_outside *upper;
   DBUG_ENTER("recalc_used_tables");
   
@@ -675,9 +688,12 @@ bool Item_subselect::exec()
 
 void Item_subselect::get_cache_parameters(List<Item> &parameters)
 {
-  Collect_deps_prm prm= {&parameters,
-    unit->first_select()->nest_level_base,
-    unit->first_select()->nest_level};
+  Collect_deps_prm prm= {&parameters,      // parameters
+    unit->first_select()->nest_level_base, // nest_level_base
+    0,                                     // count
+    unit->first_select()->nest_level,      // nest_level
+    TRUE                                   // collect
+  };
   walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm);
 }
 
@@ -1077,7 +1093,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     {
       char warn_buff[MYSQL_ERRMSG_SIZE];
       sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
-      push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+      push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
 		   ER_SELECT_REDUCED, warn_buff);
     }
     substitution= select_lex->item_list.head();
@@ -1317,10 +1333,10 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
 
 
 Item_exists_subselect::Item_exists_subselect(st_select_lex *select_lex):
-  Item_subselect()
+  Item_subselect(), upper_not(NULL), abort_on_null(0),
+  emb_on_expr_nest(NULL), optimizer(0), exists_transformed(0)
 {
   DBUG_ENTER("Item_exists_subselect::Item_exists_subselect");
-  bool val_bool();
   init(select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
   null_value= FALSE; //can't be NULL
@@ -1354,21 +1370,19 @@ bool Item_in_subselect::test_limit(st_select_lex_unit *unit_arg)
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  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), 
-  is_flattenable_semijoin(FALSE),
-  is_registered_semijoin(FALSE), 
+  Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
+  in_strategy(SUBS_NOT_TRANSFORMED),
+  pushed_cond_guards(NULL), is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE),
+  is_flattenable_semijoin(FALSE), is_registered_semijoin(FALSE),
   upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
+  DBUG_PRINT("info", ("in_strategy: %u", (uint)in_strategy));
   left_expr_orig= left_expr= left_exp;
   func= &eq_creator;
   init(select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
   maybe_null= 1;
-  abort_on_null= 0;
   reset();
   //if test_limit will fail then error will be reported to client
   test_limit(select_lex->master_unit());
@@ -1755,7 +1769,7 @@ Item_in_subselect::single_value_transformer(JOIN *join)
     {
       char warn_buff[MYSQL_ERRMSG_SIZE];
       sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
-      push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+      push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
                    ER_SELECT_REDUCED, warn_buff);
     }
     DBUG_RETURN(false);
@@ -1773,8 +1787,7 @@ Item_in_subselect::single_value_transformer(JOIN *join)
     SELECT_LEX *current= thd->lex->current_select;
 
     thd->lex->current_select= current->return_after_parsing();
-    //optimizer never use Item **ref => we can pass 0 as parameter
-    if (!optimizer || optimizer->fix_left(thd, 0))
+    if (!optimizer || optimizer->fix_left(thd))
     {
       thd->lex->current_select= current;
       DBUG_RETURN(true);
@@ -1951,7 +1964,7 @@ bool Item_allany_subselect::is_maxmin_applicable(JOIN *join)
     WHERE condition.
   */
   return (abort_on_null || (upper_item && upper_item->is_top_level_item())) &&
-      !join->select_lex->master_unit()->uncacheable && !func->eqne_op();
+      !(join->select_lex->master_unit()->uncacheable & ~UNCACHEABLE_EXPLAIN) && !func->eqne_op();
 }
 
 
@@ -2153,8 +2166,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
 
     SELECT_LEX *current= thd->lex->current_select;
     thd->lex->current_select= current->return_after_parsing();
-    //optimizer never use Item **ref => we can pass 0 as parameter
-    if (!optimizer || optimizer->fix_left(thd, 0))
+    if (!optimizer || optimizer->fix_left(thd))
     {
       thd->lex->current_select= current;
       DBUG_RETURN(true);
@@ -2334,7 +2346,7 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join,
                                          ref_pointer_array+i,
                                          (char *)"<no matter>",
                                          (char *)"<list ref>"));
-      if (!abort_on_null)
+      if (!abort_on_null && select_lex->ref_pointer_array[i]->maybe_null)
       {
         Item *having_col_item=
           new Item_is_not_null_test(this,
@@ -2353,10 +2365,6 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join,
                                            (char *)"<no matter>",
                                            (char *)"<list ref>"));
         item= new Item_cond_or(item, item_isnull);
-        /* 
-          TODO: why we create the above for cases where the right part
-                cant be NULL?
-        */
         if (left_expr->element_index(i)->maybe_null)
         {
           if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
@@ -2367,6 +2375,11 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join,
         }
         *having_item= and_items(*having_item, having_col_item);
       }
+      if (!abort_on_null && left_expr->element_index(i)->maybe_null)
+      {
+        if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
+          DBUG_RETURN(true);
+      }
       *where_item= and_items(*where_item, item);
     }
   }
@@ -2397,6 +2410,12 @@ Item_in_subselect::select_transformer(JOIN *join)
   return select_in_like_transformer(join);
 }
 
+bool
+Item_exists_subselect::select_transformer(JOIN *join)
+{
+  return select_prepare_to_be_in();
+}
+
 
 /**
   Create the predicates needed to transform an IN/ALL/ANY subselect into a
@@ -2532,6 +2551,433 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg)
 }
 
 
+/*
+  If this select can potentially be converted by EXISTS->IN conversion, wrap it
+  in an Item_in_optimizer object. Final decision whether to do the conversion
+  is done at a later phase.
+*/
+
+bool Item_exists_subselect::select_prepare_to_be_in()
+{
+  bool trans_res= FALSE;
+  DBUG_ENTER("Item_exists_subselect::select_prepare_to_be_in");
+  if (!optimizer &&
+      thd->lex->sql_command == SQLCOM_SELECT &&
+      !unit->first_select()->is_part_of_union() &&
+      optimizer_flag(thd, OPTIMIZER_SWITCH_EXISTS_TO_IN) &&
+      (is_top_level_item() ||
+       (upper_not && upper_not->is_top_level_item())))
+  {
+    Query_arena *arena, backup;
+    bool result;
+    arena= thd->activate_stmt_arena_if_needed(&backup);
+    result= (!(optimizer= new Item_in_optimizer(new Item_int(1), this)));
+    if (arena)
+      thd->restore_active_arena(arena, &backup);
+    if (result)
+      trans_res= TRUE;
+    else
+      substitution= optimizer;
+  }
+  DBUG_RETURN(trans_res);
+}
+
+/**
+  Check if 'func' is an equality in form "inner_table.column = outer_expr"
+
+  @param func              Expression to check
+  @param local_field  OUT  Return "inner_table.column" here
+  @param outer_expr   OUT  Return outer_expr here
+
+  @return true - 'func' is an Equality.
+*/
+
+static bool check_equality_for_exist2in(Item_func *func,
+                                        Item_ident **local_field,
+                                        Item **outer_exp)
+{
+  Item **args;
+  if (func->functype() != Item_func::EQ_FUNC)
+    return FALSE;
+  DBUG_ASSERT(func->arg_count == 2);
+  args= func->arguments();
+  if (args[0]->real_type() == Item::FIELD_ITEM &&
+      args[0]->all_used_tables() != OUTER_REF_TABLE_BIT &&
+      args[1]->all_used_tables() == OUTER_REF_TABLE_BIT)
+  {
+    /* It is Item_field or Item_direct_view_ref) */
+    DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM ||
+                args[0]->type() == Item::REF_ITEM);
+    *local_field= (Item_ident *)args[0];
+    *outer_exp= args[1];
+    return TRUE;
+  }
+  else if (args[1]->real_type() == Item::FIELD_ITEM &&
+           args[1]->all_used_tables() != OUTER_REF_TABLE_BIT &&
+           args[0]->all_used_tables() == OUTER_REF_TABLE_BIT)
+  {
+    /* It is Item_field or Item_direct_view_ref) */
+    DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM ||
+                args[0]->type() == Item::REF_ITEM);
+    *local_field= (Item_ident *)args[1];
+    *outer_exp= args[0];
+    return TRUE;
+  }
+
+  return FALSE;
+}
+
+typedef struct st_eq_field_outer
+{
+  Item **eq_ref;
+  Item_ident *local_field;
+  Item *outer_exp;
+} EQ_FIELD_OUTER;
+
+
+/**
+  Check if 'conds' is a set of AND-ed outer_expr=inner_table.col equalities
+
+  @detail
+    Check if 'conds' has form
+
+    outer1=inner_tbl1.col1 AND ... AND outer2=inner_tbl1.col2 AND remainder_cond
+
+  @param  conds    Condition to be checked
+  @parm   result   Array to collect EQ_FIELD_OUTER elements describing
+                   inner-vs-outer equalities the function has found.
+  @return
+    false - some inner-vs-outer equalities were found
+    true  - otherwise.
+*/
+
+static bool find_inner_outer_equalities(Item **conds,
+                                        Dynamic_array<EQ_FIELD_OUTER> &result)
+{
+  bool found=  FALSE;
+  EQ_FIELD_OUTER element;
+  if (is_cond_and(*conds))
+  {
+    List_iterator<Item> li(*((Item_cond*)*conds)->argument_list());
+    Item *item;
+    while ((item= li++))
+    {
+      if (item->type() == Item::FUNC_ITEM &&
+          check_equality_for_exist2in((Item_func *)item,
+                                      &element.local_field,
+                                      &element.outer_exp))
+      {
+        found= TRUE;
+        element.eq_ref= li.ref();
+        if (result.append(element))
+          goto alloc_err;
+      }
+    }
+  }
+  else if ((*conds)->type() == Item::FUNC_ITEM &&
+           check_equality_for_exist2in((Item_func *)*conds,
+                                       &element.local_field,
+                                       &element.outer_exp))
+  {
+    found= TRUE;
+    element.eq_ref= conds;
+    if (result.append(element))
+      goto alloc_err;
+  }
+
+  return !found;
+alloc_err:
+  return TRUE;
+}
+
+/**
+  Converts EXISTS subquery to IN subquery if it is possible and has sense
+
+  @param opt_arg         Pointer on THD
+
+  @return TRUE in case of error and FALSE otherwise.
+*/
+
+bool Item_exists_subselect::exists2in_processor(uchar *opt_arg)
+{
+  THD *thd= (THD *)opt_arg;
+  SELECT_LEX *first_select=unit->first_select(), *save_select;
+  JOIN *join= first_select->join;
+  Item **eq_ref= NULL;
+  Item_ident *local_field= NULL;
+  Item *outer_exp= NULL;
+  Item *left_exp= NULL; Item_in_subselect *in_subs;
+  Query_arena *arena= NULL, backup;
+  int res= FALSE;
+  List<Item> outer;
+  Dynamic_array<EQ_FIELD_OUTER> eqs(5, 5);
+  bool will_be_correlated;
+  DBUG_ENTER("Item_exists_subselect::exists2in_processor");
+
+  if (!optimizer ||
+      !optimizer_flag(thd, OPTIMIZER_SWITCH_EXISTS_TO_IN) ||
+      (!is_top_level_item() && (!upper_not ||
+                                !upper_not->is_top_level_item())) ||
+      first_select->is_part_of_union() ||
+      first_select->group_list.elements ||
+      first_select->order_list.elements ||
+      join->having ||
+      first_select->with_sum_func ||
+      !first_select->leaf_tables.elements||
+      !join->conds)
+    DBUG_RETURN(FALSE);
+
+  DBUG_ASSERT(first_select->order_list.elements == 0 &&
+              first_select->group_list.elements == 0 &&
+              first_select->having == NULL);
+
+  if (find_inner_outer_equalities(&join->conds, eqs))
+    DBUG_RETURN(FALSE);
+
+  DBUG_ASSERT(eqs.elements() != 0);
+
+  save_select= thd->lex->current_select;
+  thd->lex->current_select= first_select;
+
+  /* check that the subquery has only dependencies we are going pull out */
+  {
+    List<Item> unused;
+    Collect_deps_prm prm= {&unused,          // parameters
+      unit->first_select()->nest_level_base, // nest_level_base
+      0,                                     // count
+      unit->first_select()->nest_level,      // nest_level
+      FALSE                                  // collect
+    };
+    walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm);
+    DBUG_ASSERT(prm.count > 0);
+    DBUG_ASSERT(prm.count >= (uint)eqs.elements());
+    will_be_correlated= prm.count > (uint)eqs.elements();
+    if (upper_not && will_be_correlated)
+      goto out;
+  }
+
+  if ((uint)eqs.elements() > (first_select->item_list.elements +
+                              first_select->select_n_reserved))
+    goto out;
+  /* It is simple query */
+  DBUG_ASSERT(first_select->join->all_fields.elements ==
+              first_select->item_list.elements);
+
+  arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  while (first_select->item_list.elements > (uint)eqs.elements())
+  {
+    first_select->item_list.pop();
+    first_select->join->all_fields.elements--;
+  }
+  {
+    List_iterator<Item> it(first_select->item_list);
+
+    for (uint i= 0; i < (uint)eqs.elements(); i++)
+    {
+      Item *item= it++;
+      eq_ref= eqs.at(i).eq_ref;
+      local_field= eqs.at(i).local_field;
+      outer_exp= eqs.at(i).outer_exp;
+      /* Add the field to the SELECT_LIST */
+      if (item)
+        it.replace(local_field);
+      else
+      {
+        first_select->item_list.push_back(local_field);
+        first_select->join->all_fields.elements++;
+      }
+      first_select->ref_pointer_array[i]= (Item *)local_field;
+
+      /* remove the parts from condition */
+      if (!upper_not || !local_field->maybe_null)
+        *eq_ref= new Item_int(1);
+      else
+      {
+        *eq_ref= new Item_func_isnotnull(
+          new Item_field(thd,
+                         ((Item_field*)(local_field->real_item()))->context,
+                         ((Item_field*)(local_field->real_item()))->field));
+        if((*eq_ref)->fix_fields(thd, (Item **)eq_ref))
+        {
+          res= TRUE;
+          goto out;
+        }
+      }
+      outer_exp->fix_after_pullout(unit->outer_select(), &outer_exp);
+      outer_exp->update_used_tables();
+      outer.push_back(outer_exp);
+    }
+  }
+
+  join->conds->update_used_tables();
+
+  /* make IN SUBQUERY and put outer_exp as left part */
+  if (eqs.elements() == 1)
+    left_exp= outer_exp;
+  else
+  {
+    if (!(left_exp= new Item_row(outer)))
+    {
+      res= TRUE;
+      goto out;
+    }
+  }
+
+  /* make EXISTS->IN permanet (see Item_subselect::init()) */
+  set_exists_transformed();
+
+  first_select->select_limit= NULL;
+  if (!(in_subs= new Item_in_subselect(left_exp, first_select)))
+  {
+    res= TRUE;
+    goto out;
+  }
+  in_subs->set_exists_transformed();
+  optimizer->arguments()[0]= left_exp;
+  optimizer->arguments()[1]= in_subs;
+  in_subs->optimizer= optimizer;
+  DBUG_ASSERT(is_top_level_item() ||
+              (upper_not && upper_not->is_top_level_item()));
+  in_subs->top_level_item();
+  {
+    SELECT_LEX *current= thd->lex->current_select;
+    optimizer->reset_cache(); // renew cache, and we will not keep it
+    thd->lex->current_select= unit->outer_select();
+    DBUG_ASSERT(optimizer);
+    if (optimizer->fix_left(thd))
+    {
+      res= TRUE;
+      /*
+        We should not restore thd->lex->current_select because it will be
+        reset on exit from this procedure
+      */
+      goto out;
+    }
+    /*
+      As far as  Item_ref_in_optimizer do not substitute itself on fix_fields
+      we can use same item for all selects.
+    */
+    in_subs->expr= new Item_direct_ref(&first_select->context,
+                                       (Item**)optimizer->get_cache(),
+                                       (char *)"<no matter>",
+                                       (char *)in_left_expr_name);
+    if (in_subs->fix_fields(thd, optimizer->arguments() + 1))
+    {
+      res= TRUE;
+      /*
+        We should not restore thd->lex->current_select because it will be
+        reset on exit from this procedure
+      */
+      goto out;
+    }
+    {
+      /* Move dependence list */
+      List_iterator_fast<Ref_to_outside> it(upper_refs);
+      Ref_to_outside *upper;
+      while ((upper= it++))
+      {
+        uint i;
+        for (i= 0; i < (uint)eqs.elements(); i++)
+          if (eqs.at(i).outer_exp->
+              walk(&Item::find_item_processor, TRUE, (uchar*)upper->item))
+            break;
+        if (i == (uint)eqs.elements() &&
+            (in_subs->upper_refs.push_back(upper, thd->stmt_arena->mem_root)))
+          goto out;
+      }
+    }
+    in_subs->update_used_tables();
+    /*
+      The engine of the subquery is fixed so above fix_fields() is not
+      complete and should be fixed
+    */
+    in_subs->upper_refs= upper_refs;
+    upper_refs.empty();
+    thd->lex->current_select= current;
+  }
+
+  DBUG_ASSERT(unit->item == in_subs);
+  DBUG_ASSERT(join == first_select->join);
+  /*
+    Fix dependency info
+  */
+  in_subs->is_correlated= will_be_correlated;
+  if (!will_be_correlated)
+  {
+    first_select->uncacheable&= ~UNCACHEABLE_DEPENDENT_GENERATED;
+    unit->uncacheable&= ~UNCACHEABLE_DEPENDENT_GENERATED;
+  }
+  /*
+    set possible optimization strategies
+  */
+  in_subs->emb_on_expr_nest= emb_on_expr_nest;
+  res= check_and_do_in_subquery_rewrites(join);
+  first_select->join->prepare_stage2();
+
+  first_select->fix_prepare_information(thd, &join->conds, &join->having);
+
+  if (upper_not)
+  {
+    Item *exp;
+    if (eqs.elements() == 1)
+    {
+      exp= (optimizer->arguments()[0]->maybe_null ?
+            (Item*)
+            new Item_cond_and(
+              new Item_func_isnotnull(
+                new Item_direct_ref(&unit->outer_select()->context,
+                                    optimizer->arguments(),
+                                    (char *)"<no matter>",
+                                    (char *)exists_outer_expr_name)),
+              optimizer) :
+            (Item *)optimizer);
+    }
+    else
+    {
+      List<Item> *and_list= new List<Item>;
+      if (!and_list)
+      {
+        res= TRUE;
+        goto out;
+      }
+      for (size_t i= 0; i < eqs.elements(); i++)
+      {
+        if (optimizer->arguments()[0]->maybe_null)
+        {
+          and_list->
+            push_front(
+              new Item_func_isnotnull(
+                new Item_direct_ref(&unit->outer_select()->context,
+                                    optimizer->arguments()[0]->addr(i),
+                                    (char *)"<no matter>",
+                                    (char *)exists_outer_expr_name)));
+        }
+      }
+      if (and_list->elements > 0)
+      {
+        and_list->push_front(optimizer);
+        exp= new Item_cond_and(*and_list);
+      }
+      else
+        exp= optimizer;
+    }
+    upper_not->arguments()[0]= exp;
+    if (!exp->fixed && exp->fix_fields(thd, upper_not->arguments()))
+    {
+      res= TRUE;
+      goto out;
+    }
+  }
+
+out:
+  thd->lex->current_select= save_select;
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  DBUG_RETURN(res);
+}
+
+
 /**
   Prepare IN/ALL/ANY/SOME subquery transformation and call the appropriate
   transformation function.
@@ -2592,7 +3038,7 @@ Item_in_subselect::select_in_like_transformer(JOIN *join)
   }
 
   thd->lex->current_select= current->return_after_parsing();
-  result= optimizer->fix_left(thd, optimizer->arguments());
+  result= optimizer->fix_left(thd);
   thd->lex->current_select= current;
 
   if (changed)
@@ -2646,15 +3092,24 @@ void Item_in_subselect::print(String *str, enum_query_type query_type)
   Item_subselect::print(str, query_type);
 }
 
+bool Item_exists_subselect::fix_fields(THD *thd_arg, Item **ref)
+{
+  DBUG_ENTER("Item_exists_subselect::fix_fields");
+  if (exists_transformed)
+    DBUG_RETURN( !( (*ref)= new Item_int(1)));
+  DBUG_RETURN(Item_subselect::fix_fields(thd_arg, ref));
+}
+
 
 bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
 {
   uint outer_cols_num;
   List<Item> *inner_cols;
   char const *save_where= thd->where;
+  DBUG_ENTER("Item_in_subselect::fix_fields");
 
   if (test_strategy(SUBS_SEMI_JOIN))
-    return !( (*ref)= new Item_int(1));
+    DBUG_RETURN( !( (*ref)= new Item_int(1)) );
 
   thd->where= "IN/ALL/ANY subquery";
   /*
@@ -2706,15 +3161,15 @@ bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
       left_expr->fix_fields(thd_arg, &left_expr))
     goto err;
   else
-    if (Item_subselect::fix_fields(thd_arg, ref))
-      goto err;
+  if (Item_subselect::fix_fields(thd_arg, ref))
+    goto err;
   fixed= TRUE;
   thd->where= save_where;
-  return FALSE;
+  DBUG_RETURN(FALSE);
 
 err:
   thd->where= save_where;
-  return TRUE;
+  DBUG_RETURN(TRUE);
 }
 
 
@@ -2947,7 +3402,7 @@ bool subselect_union_engine::is_executed() const
 bool subselect_union_engine::no_rows()
 {
   /* Check if we got any rows when reading UNION result from temp. table: */
-  return test(!unit->fake_select_lex->join->send_records);
+  return MY_TEST(!unit->fake_select_lex->join->send_records);
 }
 
 
@@ -3784,7 +4239,7 @@ void subselect_uniquesubquery_engine::print(String *str)
 {
   KEY *key_info= tab->table->key_info + tab->ref.key;
   str->append(STRING_WITH_LEN("<primary_index_lookup>("));
-  for (uint i= 0; i < key_info->key_parts; i++)
+  for (uint i= 0; i < key_info->user_defined_key_parts; i++)
     tab->ref.items[i]->print(str);
   str->append(STRING_WITH_LEN(" in "));
   str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
@@ -3842,6 +4297,7 @@ subselect_single_select_engine::change_result(Item_subselect *si,
                                               select_result_interceptor *res,
                                               bool temp)
 {
+  DBUG_ENTER("subselect_single_select_engine::change_result");
   item= si;
   if (temp)
   {
@@ -3862,7 +4318,7 @@ subselect_single_select_engine::change_result(Item_subselect *si,
     that would not require a lot of extra code that would be harder to manage
     than the current code.
   */
-  return select_lex->join->change_result(res);
+  DBUG_RETURN(select_lex->join->change_result(res));
 }
 
 
@@ -4230,13 +4686,13 @@ ulonglong subselect_hash_sj_engine::rowid_merge_buff_size(
 */
 
 static my_bool
-bitmap_init_memroot(MY_BITMAP *map, uint n_bits, MEM_ROOT *mem_root)
+my_bitmap_init_memroot(MY_BITMAP *map, uint n_bits, MEM_ROOT *mem_root)
 {
   my_bitmap_map *bitmap_buf;
 
   if (!(bitmap_buf= (my_bitmap_map*) alloc_root(mem_root,
                                                 bitmap_buffer_size(n_bits))) ||
-      bitmap_init(map, bitmap_buf, n_bits, FALSE))
+      my_bitmap_init(map, bitmap_buf, n_bits, FALSE))
     return TRUE;
   bitmap_clear_all(map);
   return FALSE;
@@ -4274,9 +4730,9 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns, uint subquery_id)
 
   DBUG_ENTER("subselect_hash_sj_engine::init");
 
-  if (bitmap_init_memroot(&non_null_key_parts, tmp_columns->elements,
+  if (my_bitmap_init_memroot(&non_null_key_parts, tmp_columns->elements,
                             thd->mem_root) ||
-      bitmap_init_memroot(&partial_match_key_parts, tmp_columns->elements,
+      my_bitmap_init_memroot(&partial_match_key_parts, tmp_columns->elements,
                             thd->mem_root))
     DBUG_RETURN(TRUE);
 
@@ -4341,7 +4797,8 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns, uint subquery_id)
     DBUG_ASSERT(
       tmp_table->s->uniques ||
       tmp_table->key_info->key_length >= tmp_table->file->max_key_length() ||
-      tmp_table->key_info->key_parts > tmp_table->file->max_key_parts());
+      tmp_table->key_info->user_defined_key_parts >
+      tmp_table->file->max_key_parts());
     free_tmp_table(thd, tmp_table);
     tmp_table= NULL;
     delete result;
@@ -4355,7 +4812,7 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns, uint subquery_id)
   */
   DBUG_ASSERT(tmp_table->s->keys == 1 &&
               ((Item_in_subselect *) item)->left_expr->cols() ==
-              tmp_table->key_info->key_parts);
+              tmp_table->key_info->user_defined_key_parts);
 
   if (make_semi_join_conds() ||
       /* A unique_engine is used both for complete and partial matching. */
@@ -4598,7 +5055,7 @@ double get_fanout_with_deps(JOIN *join, table_map tset)
         !tab->emb_sj_nest && 
         tab->records_read != 0)
     {
-      fanout *= rows2double(tab->records_read);
+      fanout *= tab->records_read;
     }
   } 
   return fanout;
@@ -4785,8 +5242,8 @@ int subselect_hash_sj_engine::exec()
   /* The subquery should be optimized, and materialized only once. */
   DBUG_ASSERT(materialize_join->optimized && !is_materialized);
   materialize_join->exec();
-  if ((res= test(materialize_join->error || thd->is_fatal_error ||
-                 thd->is_error())))
+  if ((res= MY_TEST(materialize_join->error || thd->is_fatal_error ||
+                    thd->is_error())))
     goto err;
 
   /*
@@ -4869,7 +5326,7 @@ int subselect_hash_sj_engine::exec()
       count_pm_keys= count_partial_match_columns - count_null_only_columns +
                      (nn_key_parts ? 1 : 0);
 
-    choose_partial_match_strategy(test(nn_key_parts),
+    choose_partial_match_strategy(MY_TEST(nn_key_parts),
                                   has_covering_null_row,
                                   &partial_match_key_parts);
     DBUG_ASSERT(strategy == PARTIAL_MATCH_MERGE ||
@@ -4997,7 +5454,7 @@ Ordered_key::Ordered_key(uint keyid_arg, TABLE *tbl_arg, Item *search_key_arg,
 Ordered_key::~Ordered_key()
 {
   my_free(key_buff);
-  bitmap_free(&null_key);
+  my_bitmap_free(&null_key);
 }
 
 
@@ -5098,7 +5555,7 @@ bool Ordered_key::alloc_keys_buffers()
   DBUG_ASSERT(key_buff_elements > 0);
 
   if (!(key_buff= (rownum_t*) my_malloc((size_t)(key_buff_elements * 
-    sizeof(rownum_t)), MYF(MY_WME))))
+    sizeof(rownum_t)), MYF(MY_WME | MY_THREAD_SPECIFIC))))
     return TRUE;
 
   /*
@@ -5107,7 +5564,7 @@ bool Ordered_key::alloc_keys_buffers()
     lookup offset.
   */
   /* Notice that max_null_row is max array index, we need count, so +1. */
-  if (bitmap_init(&null_key, NULL, (uint)(max_null_row + 1), FALSE))
+  if (my_bitmap_init(&null_key, NULL, (uint)(max_null_row + 1), FALSE))
     return TRUE;
 
   cur_key_idx= HA_POS_ERROR;
@@ -5129,7 +5586,8 @@ int
 Ordered_key::cmp_keys_by_row_data(ha_rows a, ha_rows b)
 {
   uchar *rowid_a, *rowid_b;
-  int error, cmp_res;
+  int __attribute__((unused)) error;
+  int cmp_res;
   /* The length in bytes of the rowids (positions) of tmp_table. */
   uint rowid_length= tbl->file->ref_length;
 
@@ -5225,7 +5683,8 @@ int Ordered_key::cmp_key_with_search_key(rownum_t row_num)
   /* The length in bytes of the rowids (positions) of tmp_table. */
   uint rowid_length= tbl->file->ref_length;
   uchar *cur_rowid= row_num_to_rowid + row_num * rowid_length;
-  int error, cmp_res;
+  int __attribute__((unused)) error;
+  int cmp_res;
 
   if ((error= tbl->file->ha_rnd_pos(tbl->record[0], cur_rowid)))
   {
@@ -5523,7 +5982,7 @@ subselect_rowid_merge_engine::init(MY_BITMAP *non_null_key_parts,
       !(null_bitmaps= (MY_BITMAP**) thd->alloc(merge_keys_count *
                                                sizeof(MY_BITMAP*))) ||
       !(row_num_to_rowid= (uchar*) my_malloc((size_t)(row_count * rowid_length),
-        MYF(MY_WME))))
+        MYF(MY_WME | MY_THREAD_SPECIFIC))))
     return TRUE;
 
   /* Create the only non-NULL key if there is any. */
@@ -5544,8 +6003,8 @@ subselect_rowid_merge_engine::init(MY_BITMAP *non_null_key_parts,
   */
   if (!has_covering_null_columns)
   {
-    if (bitmap_init_memroot(&matching_keys, merge_keys_count, thd->mem_root) ||
-        bitmap_init_memroot(&matching_outer_cols, merge_keys_count, thd->mem_root))
+    if (my_bitmap_init_memroot(&matching_keys, merge_keys_count, thd->mem_root) ||
+        my_bitmap_init_memroot(&matching_outer_cols, merge_keys_count, thd->mem_root))
       return TRUE;
 
     /*
@@ -5842,7 +6301,7 @@ bool subselect_rowid_merge_engine::partial_match()
     Do not add the non_null_key, since it was already processed above.
   */
   bitmap_clear_all(&matching_outer_cols);
-  for (uint i= test(non_null_key); i < merge_keys_count; i++)
+  for (uint i= MY_TEST(non_null_key); i < merge_keys_count; i++)
   {
     DBUG_ASSERT(merge_keys[i]->get_column_count() == 1);
     if (merge_keys[i]->get_search_key(0)->null_value)
@@ -5859,7 +6318,7 @@ bool subselect_rowid_merge_engine::partial_match()
     nullable columns (above we guarantee there is a match for the non-null
     coumns), the result is UNKNOWN.
   */
-  if (count_nulls_in_search_key == merge_keys_count - test(non_null_key))
+  if (count_nulls_in_search_key == merge_keys_count - MY_TEST(non_null_key))
   {
     res= TRUE;
     goto end;