BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:

- Make the code produce correct result: use an array of triggers to turn on/off equalities for each
  compared column. Also turn on/off optimizations based on those equalities.
- Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between
  ref/unique_subquery/index_subquery and ALL access.
- index_subquery engine now has HAVING clause when it is needed, and it is
  displayed in EXPLAIN EXTENDED
- Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
// bk trigger note: this commit refers to BUG#24127


mysql-test/r/ndb_subquery.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect2.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect3.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Testcases
mysql-test/t/subselect3.test:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Testcases
sql/item_cmpfunc.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - For row-based IN subqueries, use one flag per each column. Set the flags appropriately before
    running the subquery.
sql/item_cmpfunc.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
   - Added Item_func_trig_cond::get_triv_var()
sql/item_subselect.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
    anymore - now Item_subselect owns the pushed down predicates guard flags.
  - A correct set of conditional predicates is now pushed into row-based IN 
    subquery.
  - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
    results), and it is shown in EXPLAIN EXTENDED
sql/item_subselect.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
    anymore - now Item_subselect owns the pushed down predicates guard flags.
  - A correct set of conditional predicates is now pushed into row-based IN 
    subquery.
  - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
    results), and it is shown in EXPLAIN EXTENDED
sql/mysql_priv.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Added "in_having_cond" special Item name
sql/mysqld.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Added "in_having_cond" special Item name
sql/sql_lex.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
sql/sql_select.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Make "ref" analyzer be able to work with conditional equalities
  - Fix subquery optimization code to match the changes in what kinds of 
    conditions are pushed down into subqueries 
  - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
sql/sql_select.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Make "ref" analyzer be able to work with conditional equalities
  - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)

9 files changed, 422 insertions, 211 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 936ae04e93d..ed7ee4b96f4 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -813,11 +813,35 @@ longlong Item_in_optimizer::val_int()
           We disable the predicates we've pushed down into subselect, run the
           subselect and see if it has produced any rows.
         */
-        ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE;
-        longlong tmp= args[1]->val_bool_result();
-        result_for_null_param= null_value= 
-          !((Item_in_subselect*)args[1])->engine->no_rows();
-        ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE;
+        Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; 
+        if (cache->cols() == 1)
+        {
+          item_subs->set_cond_guard_var(0, FALSE);
+          longlong tmp= args[1]->val_bool_result();
+          result_for_null_param= null_value= !item_subs->engine->no_rows();
+          item_subs->set_cond_guard_var(0, TRUE);
+        }
+        else
+        {
+          uint i;
+          uint ncols= cache->cols();
+          /*
+            Turn off the predicates that are based on column compares for
+            which the left part is currently NULL
+          */
+          for (i= 0; i < ncols; i++)
+          {
+            if (cache->el(i)->null_value)
+              item_subs->set_cond_guard_var(i, FALSE);
+          }
+          
+          longlong tmp= args[1]->val_bool_result();
+          result_for_null_param= null_value= !item_subs->engine->no_rows();
+          
+          /* Turn all predicates back on */
+          for (i= 0; i < ncols; i++)
+            item_subs->set_cond_guard_var(i, TRUE);
+        }
       }
     }
     return 0;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7bdc90adcee..af98e51c55d 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -311,6 +311,7 @@ public:
   enum Functype functype() const { return TRIG_COND_FUNC; };
   const char *func_name() const { return "trigcond"; };
   bool const_item() const { return FALSE; }
+  bool *get_trig_var() { return trig_var; }
 };
 
 class Item_func_not_all :public Item_func_not
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e475634a005..7e6a5614c27 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -191,16 +191,16 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
   return res;
 }
 
-bool Item_subselect::exec(bool full_scan)
+bool Item_subselect::exec()
 {
   int res;
 
-  res= engine->exec(full_scan);
+  res= engine->exec();
 
   if (engine_changed)
   {
     engine_changed= 0;
-    return exec(full_scan);
+    return exec();
   }
   return (res);
 }
@@ -448,13 +448,13 @@ bool Item_singlerow_subselect::null_inside()
 
 void Item_singlerow_subselect::bring_value()
 {
-  exec(FALSE);
+  exec();
 }
 
 double Item_singlerow_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_real();
@@ -469,7 +469,7 @@ double Item_singlerow_subselect::val_real()
 longlong Item_singlerow_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_int();
@@ -483,7 +483,7 @@ longlong Item_singlerow_subselect::val_int()
 
 String *Item_singlerow_subselect::val_str(String *str)
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_str(str);
@@ -498,7 +498,7 @@ String *Item_singlerow_subselect::val_str(String *str)
 
 my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_decimal(decimal_value);
@@ -513,7 +513,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
 
 bool Item_singlerow_subselect::val_bool()
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_bool();
@@ -565,7 +565,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
   Item_exists_subselect(), optimizer(0), transformed(0),
-  enable_pushed_conds(TRUE), upper_item(0)
+  pushed_cond_guards(NULL), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -610,7 +610,7 @@ void Item_exists_subselect::fix_length_and_dec()
 double Item_exists_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -621,7 +621,7 @@ double Item_exists_subselect::val_real()
 longlong Item_exists_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -632,7 +632,7 @@ longlong Item_exists_subselect::val_int()
 String *Item_exists_subselect::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -645,7 +645,7 @@ String *Item_exists_subselect::val_str(String *str)
 my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -658,7 +658,7 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
 bool Item_exists_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -676,7 +676,7 @@ double Item_in_subselect::val_real()
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -697,7 +697,7 @@ longlong Item_in_subselect::val_int()
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -718,7 +718,7 @@ String *Item_in_subselect::val_str(String *str)
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -738,7 +738,7 @@ bool Item_in_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -758,7 +758,7 @@ my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value)
   DBUG_ASSERT(0);
   null_value= 0;
   DBUG_ASSERT(fixed == 1);
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -948,19 +948,14 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
   }
+  if (!abort_on_null && left_expr->maybe_null)
+  {
+    if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool))))
+      DBUG_RETURN(RES_ERROR);
+    pushed_cond_guards[0]= TRUE;
+  }
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
-  /*
-    Add the left part of a subselect to a WHERE or HAVING clause of
-    the right part, e.g. 
-    
-       SELECT 1 IN (SELECT a FROM t1)  =>
-       
-       SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1)
-       
-    HAVING is used only if the right part contains a SUM function, a GROUP
-    BY or a HAVING clause.
-  */
   if (join->having || select_lex->with_sum_func ||
       select_lex->group_list.elements)
   {
@@ -976,9 +971,9 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     {
       /* 
         We can encounter "NULL IN (SELECT ...)". Wrap the added condition
-        within a trigger.
+        within a trig_cond.
       */
-      item= new Item_func_trig_cond(item, &enable_pushed_conds);
+      item= new Item_func_trig_cond(item, get_cond_guard(0));
     }
     
     /*
@@ -987,6 +982,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       argument (reference) to fix_fields()
     */
     select_lex->having= join->having= and_items(join->having, item);
+    if (join->having == item)
+      item->name= (char*)in_having_cond;
     select_lex->having_fix_field= 1;
     /*
       we do not check join->having->fixed, because Item_and (from and_items)
@@ -1017,7 +1014,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
         if (left_expr->maybe_null)
         {
           if (!(having= new Item_func_trig_cond(having,
-                                                &enable_pushed_conds)))
+                                                get_cond_guard(0))))
             DBUG_RETURN(RES_ERROR);
         }
 	/*
@@ -1025,6 +1022,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	  we can assign select_lex->having here, and pass 0 as last
 	  argument (reference) to fix_fields()
 	*/
+        having->name= (char*)in_having_cond;
 	select_lex->having= join->having= having;
 	select_lex->having_fix_field= 1;
         /*
@@ -1040,16 +1038,21 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 			       new Item_func_isnull(orig_item));
       }
       /* 
-        If we may encounter NULL IN (SELECT ...) and care between NULL and
-        FALSE, wrap it in a trigger.
+        If we may encounter NULL IN (SELECT ...) and care whether subquery
+        result is NULL or FALSE, wrap condition in a trig_cond.
       */
       if (!abort_on_null && left_expr->maybe_null)
       {
-        if (!(item= new Item_func_trig_cond(item, &enable_pushed_conds)))
+        if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
           DBUG_RETURN(RES_ERROR);
       }
-
+      /*
+        TODO: figure out why the following is done here in 
+        single_value_transformer but there is no corresponding action in
+        row_value_transformer?
+      */
       item->name= (char *)in_additional_cond;
+
       /*
 	AND can't be changed during fix_fields()
 	we can assign select_lex->having here, and pass 0 as last
@@ -1083,11 +1086,13 @@ Item_in_subselect::single_value_transformer(JOIN *join,
         if (!abort_on_null && left_expr->maybe_null)
         {
           if (!(new_having= new Item_func_trig_cond(new_having,
-                                                    &enable_pushed_conds)))
+                                                    get_cond_guard(0))))
             DBUG_RETURN(RES_ERROR);
         }
+        new_having->name= (char*)in_having_cond;
 	select_lex->having= join->having= new_having;
 	select_lex->having_fix_field= 1;
+        
         /*
           we do not check join->having->fixed, because comparison function
           (from func->create) can't be fixed after creation
@@ -1157,6 +1162,15 @@ Item_in_subselect::row_value_transformer(JOIN *join)
 
     thd->lex->current_select= current;
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
+
+    if (!abort_on_null && left_expr->maybe_null)
+    {
+      if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) *
+                                                        left_expr->cols())))
+        DBUG_RETURN(RES_ERROR);
+      for (uint i= 0; i < cols_num; i++)
+        pushed_cond_guards[i]= TRUE;
+    }
   }
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
@@ -1173,6 +1187,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                                 is_not_null_test(v3))
       where is_not_null_test used to register nulls in case if we have
       not found matching to return correct NULL value
+      TODO: say here explicitly if the order of AND parts matters or not.
     */
     Item *item_having_part2= 0;
     for (uint i= 0; i < cols_num; i++)
@@ -1201,21 +1216,28 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                                       (char *)"<no matter>",
                                       (char *)"<list ref>")
                             );
-      having_item=
-        and_items(having_item,
-                  new Item_cond_or(item_eq, item_isnull));
-      item_having_part2=
-        and_items(item_having_part2,
-                  new
-                  Item_is_not_null_test(this,
-                                        new
-                                        Item_ref(&select_lex->context,
-                                                 select_lex->
-                                                 ref_pointer_array + i,
-                                                 (char *)"<no matter>",
-                                                 (char *)"<list ref>")
-                                       )
-                 );
+      Item *col_item= new Item_cond_or(item_eq, item_isnull);
+      if (!abort_on_null && left_expr->el(i)->maybe_null)
+      {
+        if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i))))
+          DBUG_RETURN(RES_ERROR);
+      }
+      having_item= and_items(having_item, col_item);
+      
+      Item *item_nnull_test= 
+         new Item_is_not_null_test(this,
+                                   new Item_ref(&select_lex->context,
+                                                select_lex->
+                                                ref_pointer_array + i,
+                                                (char *)"<no matter>",
+                                                (char *)"<list ref>"));
+      if (!abort_on_null && left_expr->el(i)->maybe_null)
+      {
+        if (!(item_nnull_test= 
+              new Item_func_trig_cond(item_nnull_test, get_cond_guard(i))))
+          DBUG_RETURN(RES_ERROR);
+      }
+      item_having_part2= and_items(item_having_part2, item_nnull_test);
       item_having_part2->top_level_item();
     }
     having_item= and_items(having_item, item_having_part2);
@@ -1264,18 +1286,15 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                         );
       if (!abort_on_null)
       {
-        having_item=
-          and_items(having_item,
-                    new
-                    Item_is_not_null_test(this,
-                                          new
-                                          Item_ref(&select_lex->context,
-                                                   select_lex->
-                                                   ref_pointer_array + i,
-                                                   (char *)"<no matter>",
-                                                   (char *)"<list ref>")
-                                         )
-                  );
+        Item *having_col_item=
+          new Item_is_not_null_test(this,
+                                    new
+                                    Item_ref(&select_lex->context, 
+                                             select_lex->ref_pointer_array + i,
+                                             (char *)"<no matter>",
+                                             (char *)"<list ref>"));
+        
+        
         item_isnull= new
           Item_func_isnull(new
                            Item_direct_ref(&select_lex->context,
@@ -1284,14 +1303,23 @@ Item_in_subselect::row_value_transformer(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->el(i)->maybe_null)
+        {
+          if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
+            DBUG_RETURN(RES_ERROR);
+          if (!(having_col_item= 
+                  new Item_func_trig_cond(having_col_item, get_cond_guard(i))))
+            DBUG_RETURN(RES_ERROR);
+        }
+        having_item= and_items(having_item, having_col_item);
       }
-
       where_item= and_items(where_item, item);
     }
-    if (where_item)
-      where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds);
     /*
       AND can't be changed during fix_fields()
       we can assign select_lex->where here, and pass 0 as last
@@ -1305,9 +1333,9 @@ Item_in_subselect::row_value_transformer(JOIN *join)
   if (having_item)
   {
     bool res;
-    having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds);
-
     select_lex->having= join->having= and_items(join->having, having_item);
+    if (having_item == select_lex->having)
+      having_item->name= (char*)in_having_cond;
     select_lex->having->top_level_item();
     /*
       AND can't be changed during fix_fields()
@@ -1693,7 +1721,7 @@ int  init_read_record_seq(JOIN_TAB *tab);
 int join_read_always_key_or_null(JOIN_TAB *tab);
 int join_read_next_same_or_null(READ_RECORD *info);
 
-int subselect_single_select_engine::exec(bool full_scan)
+int subselect_single_select_engine::exec()
 {
   DBUG_ENTER("subselect_single_select_engine::exec");
   char const *save_where= thd->where;
@@ -1731,9 +1759,13 @@ int subselect_single_select_engine::exec(bool full_scan)
   if (!executed)
   {
     item->reset_value_registration();
-    if (full_scan)
+    bool have_changed_access= FALSE;
+    JOIN_TAB *changed_tabs[MAX_TABLES];
+    JOIN_TAB **last_changed_tab= changed_tabs;
+    if (item->have_guarded_conds())
     {
       /*
+        For at least one of the pushed predicates the following is true:
         We should not apply optimizations based on the condition that was
         pushed down into the subquery. Those optimizations are ref[_or_null]
         acceses. Change them to be full table scans.
@@ -1741,32 +1773,36 @@ int subselect_single_select_engine::exec(bool full_scan)
       for (uint i=join->const_tables ; i < join->tables ; i++)
       {
         JOIN_TAB *tab=join->join_tab+i;
-        if (tab->keyuse && tab->keyuse->outer_ref)
+        if (tab && tab->keyuse)
         {
-          tab->read_first_record= init_read_record_seq;
-          tab->read_record.record= tab->table->record[0];
-          tab->read_record.thd= join->thd;
-          tab->read_record.ref_length= tab->table->file->ref_length;
+          for (uint i= 0; i < tab->ref.key_parts; i++)
+          {
+            bool *cond_guard= tab->ref.cond_guards[i];
+            if (cond_guard && !*cond_guard)
+            {
+              /* Change the access method to full table scan */
+              tab->read_first_record= init_read_record_seq;
+              tab->read_record.record= tab->table->record[0];
+              tab->read_record.thd= join->thd;
+              tab->read_record.ref_length= tab->table->file->ref_length;
+              *(last_changed_tab++)= tab;
+              break;
+            }
+          }
         }
       }
     }
     
     join->exec();
 
-    if (full_scan)
+    /* Enable the optimizations back */
+    for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
     {
-      /* Enable the optimizations back */
-      for (uint i=join->const_tables ; i < join->tables ; i++)
-      {
-        JOIN_TAB *tab=join->join_tab+i;
-        if (tab->keyuse && tab->keyuse->outer_ref)
-        {
-          tab->read_record.record= 0;
-          tab->read_record.ref_length= 0;
-          tab->read_first_record= join_read_always_key_or_null;
-          tab->read_record.read_record= join_read_next_same_or_null;
-        }
-      }
+      JOIN_TAB *tab= *ptab;
+      tab->read_record.record= 0;
+      tab->read_record.ref_length= 0;
+      tab->read_first_record= join_read_always_key_or_null;
+      tab->read_record.read_record= join_read_next_same_or_null;
     }
     executed= 1;
     thd->where= save_where;
@@ -1778,13 +1814,9 @@ int subselect_single_select_engine::exec(bool full_scan)
   DBUG_RETURN(0);
 }
 
-int subselect_union_engine::exec(bool full_scan)
+int subselect_union_engine::exec()
 {
   char const *save_where= thd->where;
-  /* 
-    Ignore the full_scan parameter: the pushed down predicates are only used
-    for filtering, and the caller has disabled them if necessary.
-  */
   int res= unit->exec();
   thd->where= save_where;
   return res;
@@ -1792,7 +1824,7 @@ int subselect_union_engine::exec(bool full_scan)
 
 
 /*
-  Search for at least on row satisfying select condition
+  Search for at least one row satisfying select condition
  
   SYNOPSIS
     subselect_uniquesubquery_engine::scan_table()
@@ -1801,8 +1833,8 @@ int subselect_union_engine::exec(bool full_scan)
     Scan the table using sequential access until we find at least one row
     satisfying select condition.
     
-    The result of this function (info about whether a row was found) is
-    stored in this->empty_result_set.
+    The caller must set this->empty_result_set=FALSE before calling this
+    function. This function will set it to TRUE if it finds a matching row.
 
   RETURN
     FALSE - OK
@@ -1814,7 +1846,6 @@ int subselect_uniquesubquery_engine::scan_table()
   int error;
   TABLE *table= tab->table;
   DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
-  empty_result_set= TRUE;
 
   if (table->file->inited)
     table->file->ha_index_end();
@@ -1907,10 +1938,13 @@ bool subselect_uniquesubquery_engine::copy_ref_key()
       - FALSE otherwise.
 
     In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
-    the caller doesn't distinguish between NULL and FALSE result and we just 
+    the caller doesn't distinguish between NULL and FALSE result and we just
     return FALSE. 
-    Otherwise we make a full table scan to see if there is at least one matching row.
-  
+    Otherwise we make a full table scan to see if there is at least one 
+    matching row.
+    
+    The result of this function (info about whether a row was found) is
+    stored in this->empty_result_set.
   NOTE
     
   RETURN
@@ -1918,11 +1952,12 @@ bool subselect_uniquesubquery_engine::copy_ref_key()
     TRUE  - an error occured while scanning
 */
 
-int subselect_uniquesubquery_engine::exec(bool full_scan)
+int subselect_uniquesubquery_engine::exec()
 {
   DBUG_ENTER("subselect_uniquesubquery_engine::exec");
   int error;
   TABLE *table= tab->table;
+  empty_result_set= TRUE;
  
   /* TODO: change to use of 'full_scan' here? */
   if (copy_ref_key())
@@ -1943,9 +1978,13 @@ int subselect_uniquesubquery_engine::exec(bool full_scan)
   {
     error= 0;
     table->null_row= 0;
-    ((Item_in_subselect *) item)->value= (!table->status &&
-                                          (!cond || cond->val_int()) ? 1 :
-                                          0);
+    if (!table->status && (!cond || cond->val_int()))
+    {
+      ((Item_in_subselect *) item)->value= 1;
+      empty_result_set= FALSE;
+    }
+    else
+      ((Item_in_subselect *) item)->value= 0;
   }
 
   DBUG_RETURN(error != 0);
@@ -2011,7 +2050,7 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine()
     1
 */
 
-int subselect_indexsubquery_engine::exec(bool full_scan)
+int subselect_indexsubquery_engine::exec()
 {
   DBUG_ENTER("subselect_indexsubquery_engine::exec");
   int error;
@@ -2052,8 +2091,9 @@ int subselect_indexsubquery_engine::exec(bool full_scan)
       table->null_row= 0;
       if (!table->status)
       {
-        if (!cond || cond->val_int())
+        if ((!cond || cond->val_int()) && (!having || having->val_int()))
         {
+          empty_result_set= FALSE;
           if (null_finding)
             ((Item_in_subselect *) item)->was_null= 1;
           else
@@ -2196,11 +2236,16 @@ void subselect_indexsubquery_engine::print(String *str)
   str->append(key_info->name);
   if (check_null)
     str->append(STRING_WITH_LEN(" checking NULL"));
-    if (cond)
+  if (cond)
   {
     str->append(STRING_WITH_LEN(" where "));
     cond->print(str);
   }
+  if (having)
+  {
+    str->append(STRING_WITH_LEN(" having "));
+    having->print(str);
+  }
   str->append(')');
 }
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 69ac78d859b..25a0326f8b6 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -94,7 +94,7 @@ public:
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
-  virtual bool exec(bool full_scan);
+  virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
   table_map not_null_tables() const { return 0; }
@@ -104,6 +104,7 @@ public:
   Item *get_tmp_table_item(THD *thd);
   void update_used_tables();
   void print(String *str);
+  virtual bool have_guarded_conds() { return FALSE; }
   bool change_engine(subselect_engine *eng)
   {
     old_engine= engine;
@@ -249,13 +250,21 @@ protected:
   bool transformed;
 public:
   /* Used to trigger on/off conditions that were pushed down to subselect */
-  bool enable_pushed_conds;
+  bool *pushed_cond_guards;
+
+  bool *get_cond_guard(int i)
+  {
+    return pushed_cond_guards ? pushed_cond_guards + i : NULL;
+  }
+  void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; }
+  bool have_guarded_conds() { return test(pushed_cond_guards); }
+
   Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery
 
   Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect()
     :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
-     enable_pushed_conds(TRUE), upper_item(0)
+     pushed_cond_guards(NULL), upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -340,23 +349,22 @@ public:
 
     SYNOPSIS
       exec()
-        full_scan TRUE  - Pushed-down predicates are disabled, the engine
-                          must disable made based on those predicates.
-                  FALSE - Pushed-down predicates are in effect.
+
     DESCRIPTION
       Execute the engine. The result of execution is subquery value that is
       either captured by previously set up select_result-based 'sink' or
       stored somewhere by the exec() method itself.
 
-      A required side effect: if full_scan==TRUE, subselect_engine->no_rows()
-      should return correct result.
+      A required side effect: If at least one pushed-down predicate is
+      disabled, subselect_engine->no_rows() must return correct result after 
+      the exec() call.
 
     RETURN
       0 - OK
-      1 - Either an execution error, or the engine was be "changed", and
+      1 - Either an execution error, or the engine was "changed", and the
           caller should call exec() again for the new engine.
   */
-  virtual int exec(bool full_scan)= 0;
+  virtual int exec()= 0;
   virtual uint cols()= 0; /* return number of columns in select */
   virtual uint8 uncacheable()= 0; /* query is uncacheable */
   enum Item_result type() { return res_type; }
@@ -391,7 +399,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols();
   uint8 uncacheable();
   void exclude();
@@ -415,7 +423,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols();
   uint8 uncacheable();
   void exclude();
@@ -429,11 +437,30 @@ public:
 
 
 struct st_join_table;
+
+
+/*
+  A subquery execution engine that evaluates the subquery by doing one index
+  lookup in a unique index.
+
+  This engine is used to resolve subqueries in forms
+  
+    outer_expr IN (SELECT tbl.unique_key FROM tbl WHERE subq_where) 
+    
+  or, tuple-based:
+  
+    (oe1, .. oeN) IN (SELECT uniq_key_part1, ... uniq_key_partK
+                      FROM tbl WHERE subqwhere) 
+  
+  i.e. the subquery is a single table SELECT without GROUP BY, aggregate
+  functions, etc.
+*/
+
 class subselect_uniquesubquery_engine: public subselect_engine
 {
 protected:
   st_join_table *tab;
-  Item *cond;
+  Item *cond; /* The WHERE condition of subselect */
   /* 
     TRUE<=> last execution produced empty set. Valid only when left
     expression is NULL.
@@ -453,7 +480,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols() { return 1; }
   uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
   void exclude();
@@ -471,16 +498,47 @@ class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine
 {
   /* FALSE for 'ref', TRUE for 'ref-or-null'. */
   bool check_null;
+  /* 
+    The "having" clause. This clause (further reffered to as "artificial
+    having") was inserted by subquery transformation code. It contains 
+    Item(s) that have a side-effect: they record whether the subquery has 
+    produced a row with NULL certain components. We need to use it for cases
+    like
+      (oe1, oe2) IN (SELECT t.key, t.no_key FROM t1)
+    where we do index lookup on t.key=oe1 but need also to check if there
+    was a row such that t.no_key IS NULL.
+    
+    NOTE: This is currently here and not in the uniquesubquery_engine. Ideally
+    it should have been in uniquesubquery_engine in order to allow execution of
+    subqueries like
+    
+      (oe1, oe2) IN (SELECT primary_key, non_key_maybe_null_field FROM tbl)
+
+    We could use uniquesubquery_engine for the first component and let
+    Item_is_not_null_test( non_key_maybe_null_field) to handle the second.
+
+    However, subqueries like the above are currently not handled by index
+    lookup-based subquery engines, the engine applicability check misses
+    them: it doesn't switch the engine for case of artificial having and
+    [eq_]ref access (only for artifical having + ref_or_null or no having).
+    The above example subquery is handled as a full-blown SELECT with eq_ref
+    access to one table.
+
+    Due to this limitation, the "artificial having" currently needs to be 
+    checked by only in indexsubquery_engine.
+  */
+  Item *having;
 public:
 
   // constructor can assign THD because it will be called after JOIN::prepare
   subselect_indexsubquery_engine(THD *thd, st_join_table *tab_arg,
 				 Item_subselect *subs, Item *where,
-				 bool chk_null)
+                                 Item *having_arg, bool chk_null)
     :subselect_uniquesubquery_engine(thd, tab_arg, subs, where),
-     check_null(chk_null)
+     check_null(chk_null),
+     having(having_arg)
   {}
-  int exec(bool full_scan);
+  int exec();
   void print (String *str);
 };
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 534d42e1c17..c4612433f3d 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1197,7 +1197,7 @@ extern const char *command_name[];
 extern const char *first_keyword, *my_localhost, *delayed_user, *binary_keyword;
 extern const char **errmesg;			/* Error messages */
 extern const char *myisam_recover_options_str;
-extern const char *in_left_expr_name, *in_additional_cond;
+extern const char *in_left_expr_name, *in_additional_cond, *in_having_cond;
 extern const char * const triggers_file_ext;
 extern const char * const trigname_file_ext;
 extern Eq_creator eq_creator;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 095b5095c36..6614a07ef3d 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -449,10 +449,13 @@ char *mysqld_unix_port, *opt_mysql_tmpdir;
 const char **errmesg;			/* Error messages */
 const char *myisam_recover_options_str="OFF";
 const char *myisam_stats_method_str="nulls_unequal";
+
 /* name of reference on left espression in rewritten IN subquery */
 const char *in_left_expr_name= "<left expr>";
 /* name of additional condition */
 const char *in_additional_cond= "<IN COND>";
+const char *in_having_cond= "<IN HAVING>";
+
 my_decimal decimal_zero;
 /* classes for comparation parsing/processing */
 Eq_creator eq_creator;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index db119d527d9..54fdd1431e2 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -469,7 +469,7 @@ public:
   void set_thd(THD *thd_arg) { thd= thd_arg; }
 
   friend void lex_start(THD *thd, uchar *buf, uint length);
-  friend int subselect_union_engine::exec(bool);
+  friend int subselect_union_engine::exec();
 
   List<Item> *get_unit_column_types();
 };
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index abb949ae473..b6664abb299 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -513,72 +513,88 @@ err:
 
 
 /*
-  test if it is known for optimisation IN subquery
+  Remove the predicates pushed down into the subquery
 
   SYNOPSIS
-    JOIN::test_in_subselect()
-    where - pointer for variable in which conditions should be
-            stored if subquery is known
+    JOIN::remove_subq_pushed_predicates()
+      where   IN  Must be NULL
+              OUT The remaining WHERE condition, or NULL
 
-  RETURN
-    1 - known
-    0 - unknown
+  DESCRIPTION
+    Given that this join will be executed using (unique|index)_subquery,
+    without "checking NULL", remove the predicates that were pushed down
+    into the subquery.
+
+    We can remove the equalities that will be guaranteed to be true by the
+    fact that subquery engine will be using index lookup.
+
+    If the subquery compares scalar values, we can remove the condition that
+    was wrapped into trig_cond (it will be checked when needed by the subquery
+    engine)
+
+    If the subquery compares row values, we need to keep the wrapped
+    equalities in the WHERE clause: when the left (outer) tuple has both NULL
+    and non-NULL values, we'll do a full table scan and will rely on the
+    equalities corresponding to non-NULL parts of left tuple to filter out
+    non-matching records.
 */
 
-bool JOIN::test_in_subselect(Item **where)
+void JOIN::remove_subq_pushed_predicates(Item **where)
 {
   if (conds->type() == Item::FUNC_ITEM &&
       ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
       ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
       ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
   {
-    join_tab->info= "Using index";
     *where= 0;
-    return 1;
+    return;
   }
   if (conds->type() == Item::COND_ITEM &&
       ((class Item_func *)this->conds)->functype() ==
       Item_func::COND_AND_FUNC)
   {
-    if ((*where= remove_additional_cond(conds)))
-      join_tab->info= "Using index; Using where";
-    else
-      join_tab->info= "Using index";
-    return 1;
+    *where= remove_additional_cond(conds);
   }
-  return 0;
 }
 
 
 /*
-  Check if the passed HAVING clause is a clause added by subquery optimizer
+  Index lookup-based subquery: save some flags for EXPLAIN output
 
   SYNOPSIS
-    is_having_subq_predicates()
-      having  Having clause
+    save_index_subquery_explain_info()
+      join_tab  Subquery's join tab (there is only one as index lookup is
+                only used for subqueries that are single-table SELECTs)
+      where     Subquery's WHERE clause
 
-  RETURN
-    TRUE   The passed HAVING clause was added by the subquery optimizer
-    FALSE  Otherwise
+  DESCRIPTION
+    For index lookup-based subquery (i.e. one executed with
+    subselect_uniquesubquery_engine or subselect_indexsubquery_engine),
+    check its EXPLAIN output row should contain 
+      "Using index" (TAB_INFO_FULL_SCAN_ON_NULL) 
+      "Using Where" (TAB_INFO_USING_WHERE)
+      "Full scan on NULL key" (TAB_INFO_FULL_SCAN_ON_NULL)
+    and set appropriate flags in join_tab->packed_info.
 */
 
-bool is_having_subq_predicates(Item *having)
+static void save_index_subquery_explain_info(JOIN_TAB *join_tab, Item* where)
 {
-  if (having->type() == Item::FUNC_ITEM)
+  join_tab->packed_info= TAB_INFO_HAVE_VALUE;
+  if (join_tab->table->used_keys.is_set(join_tab->ref.key))
+    join_tab->packed_info |= TAB_INFO_USING_INDEX;
+  if (where)
+    join_tab->packed_info |= TAB_INFO_USING_WHERE;
+  for (uint i = 0; i < join_tab->ref.key_parts; i++)
   {
-    if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
-      return TRUE;
-    if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC)
+    if (join_tab->ref.cond_guards[i])
     {
-      having= ((Item_func*)having)->arguments()[0];
-      if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
-        return TRUE;
+      join_tab->packed_info |= TAB_INFO_FULL_SCAN_ON_NULL;
+      break;
     }
-    return TRUE;
   }
-  return FALSE;
 }
 
+
 /*
   global select optimisation.
   return 0 - success
@@ -1017,51 +1033,47 @@ JOIN::optimize()
       if (join_tab[0].type == JT_EQ_REF &&
 	  join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
-	if (test_in_subselect(&where))
-	{
-	  join_tab[0].type= JT_UNIQUE_SUBQUERY;
-	  error= 0;
-	  DBUG_RETURN(unit->item->
-		      change_engine(new
-				    subselect_uniquesubquery_engine(thd,
-								    join_tab,
-								    unit->item,
-								    where)));
-	}
+        remove_subq_pushed_predicates(&where);
+        save_index_subquery_explain_info(join_tab, where);
+        join_tab[0].type= JT_UNIQUE_SUBQUERY;
+        error= 0;
+        DBUG_RETURN(unit->item->
+                    change_engine(new
+                                  subselect_uniquesubquery_engine(thd,
+                                                                  join_tab,
+                                                                  unit->item,
+                                                                  where)));
       }
       else if (join_tab[0].type == JT_REF &&
 	       join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
-	if (test_in_subselect(&where))
-	{
-	  join_tab[0].type= JT_INDEX_SUBQUERY;
-	  error= 0;
-	  DBUG_RETURN(unit->item->
-		      change_engine(new
-				    subselect_indexsubquery_engine(thd,
-								   join_tab,
-								   unit->item,
-								   where,
-								   0)));
-	}
+	remove_subq_pushed_predicates(&where);
+        save_index_subquery_explain_info(join_tab, where);
+        join_tab[0].type= JT_INDEX_SUBQUERY;
+        error= 0;
+        DBUG_RETURN(unit->item->
+                    change_engine(new
+                                  subselect_indexsubquery_engine(thd,
+                                                                 join_tab,
+                                                                 unit->item,
+                                                                 where,
+                                                                 NULL,
+                                                                 0)));
       }
     } else if (join_tab[0].type == JT_REF_OR_NULL &&
 	       join_tab[0].ref.items[0]->name == in_left_expr_name &&
-               is_having_subq_predicates(having))
+               having->name == in_having_cond)
     {
       join_tab[0].type= JT_INDEX_SUBQUERY;
       error= 0;
-
-      if ((conds= remove_additional_cond(conds)))
-	join_tab->info= "Using index; Using where";
-      else
-	join_tab->info= "Using index";
-
+      conds= remove_additional_cond(conds);
+      save_index_subquery_explain_info(join_tab, conds);
       DBUG_RETURN(unit->item->
 		  change_engine(new subselect_indexsubquery_engine(thd,
 								   join_tab,
 								   unit->item,
 								   conds,
+                                                                   having,
 								   1)));
     }
 
@@ -2557,9 +2569,7 @@ typedef struct key_field_t {		// Used when finding key fields
     when val IS NULL.
   */
   bool          null_rejecting; 
-
-  /* TRUE<=> This ref access is an outer subquery reference access */
-  bool          outer_ref;
+  bool         *cond_guard; /* See KEYUSE::cond_guard */
 } KEY_FIELD;
 
 /* Values in optimize */
@@ -2858,7 +2868,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
                                    cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
                                   ((*value)->type() == Item::FIELD_ITEM) &&
                                   ((Item_field*)*value)->field->maybe_null());
-  (*key_fields)->outer_ref=      FALSE;
+  (*key_fields)->cond_guard= NULL;
   (*key_fields)++;
 }
 
@@ -2955,8 +2965,9 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
   }
 
   /* 
-    Subquery optimization: check if the encountered condition is one
-    added by condition push down into subquery.
+    Subquery optimization: Conditions that are pushed down into subqueries
+    are wrapped into Item_func_trig_cond. We process the wrapped condition
+    but need to set cond_guard for KEYUSE elements generated from it.
   */
   {
     if (cond->type() == Item::FUNC_ITEM &&
@@ -2973,7 +2984,7 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
                        sargables);
         // Indicate that this ref access candidate is for subquery lookup:
         for (; save != *key_fields; save++)
-          save->outer_ref= TRUE;
+          save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
       }
       return;
     }
@@ -3153,7 +3164,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
 	  keyuse.used_tables=key_field->val->used_tables();
 	  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
           keyuse.null_rejecting= key_field->null_rejecting;
-          keyuse.outer_ref= key_field->outer_ref;
+          keyuse.cond_guard= key_field->cond_guard;
 	  VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
 	}
       }
@@ -4992,7 +5003,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   if (!(j->ref.key_buff= (byte*) thd->calloc(ALIGN_SIZE(length)*2)) ||
       !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *
 						   (keyparts+1)))) ||
-      !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)))
+      !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
+      !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))
   {
     DBUG_RETURN(TRUE);
   }
@@ -5007,6 +5019,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   if (ftkey)
   {
     j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
+    /* Predicates pushed down into subquery can't be used FT access */
+    j->ref.cond_guards[0]= NULL;
     if (keyuse->used_tables)
       DBUG_RETURN(TRUE);                        // not supported yet. SerG
 
@@ -5023,6 +5037,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
 
       uint maybe_null= test(keyinfo->key_part[i].null_bit);
       j->ref.items[i]=keyuse->val;		// Save for cond removal
+      j->ref.cond_guards[i]= keyuse->cond_guard;
       if (keyuse->null_rejecting) 
         j->ref.null_rejecting |= 1 << i;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
@@ -7653,7 +7668,7 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list,
 
   SYNOPSIS
     remove_additional_cond()
-    conds - condition for processing
+      conds  Condition for processing
 
   RETURN VALUES
     new conditions
@@ -10911,7 +10926,9 @@ int rr_sequential(READ_RECORD *info);
 int init_read_record_seq(JOIN_TAB *tab)
 {
   tab->read_record.read_record= rr_sequential;
-  return tab->read_record.file->ha_rnd_init(1);
+  if (tab->read_record.file->ha_rnd_init(1))
+    return 1;
+  return (*tab->read_record.read_record)(&tab->read_record);
 }
 
 static int
@@ -14819,6 +14836,24 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
         
       if (tab->info)
 	item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
+      else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+      {
+        if (tab->packed_info & TAB_INFO_USING_INDEX)
+          extra.append(STRING_WITH_LEN("; Using index"));
+        if (tab->packed_info & TAB_INFO_USING_WHERE)
+          extra.append(STRING_WITH_LEN("; Using where"));
+        if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+          extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+        /* Skip initial "; "*/
+        const char *str= extra.ptr();
+        uint32 len= extra.length();
+        if (len)
+        {
+          str += 2;
+          len -= 2;
+        }
+	item_list.push_back(new Item_string(str, len, cs));
+      }
       else
       {
         if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || 
@@ -14877,6 +14912,15 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
 	}
 	if (distinct & test_all_bits(used_tables,thd->used_tables))
 	  extra.append(STRING_WITH_LEN("; Distinct"));
+
+        for (uint part= 0; part < tab->ref.key_parts; part++)
+        {
+          if (tab->ref.cond_guards[part])
+          {
+            extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+            break;
+          }
+        }
         
         /* Skip initial "; "*/
         const char *str= extra.ptr();
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ccdd66d5b95..a17d7fcb362 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -35,8 +35,17 @@ typedef struct keyuse_t {
     satisfied if val has NULL 'value'.
   */
   bool null_rejecting;
-  /* TRUE<=> This ref access is an outer subquery reference access */
-  bool outer_ref;
+  /*
+    !NULL - This KEYUSE was created from an equality that was wrapped into
+            an Item_func_trig_cond. This means the equality (and validity of 
+            this KEYUSE element) can be turned on and off. The on/off state 
+            is indicted by the pointed value:
+              *cond_guard == TRUE <=> equality condition is on
+              *cond_guard == FALSE <=> equality condition is off
+
+    NULL  - Otherwise (the source equality can't be turned off)
+  */
+  bool *cond_guard;
 } KEYUSE;
 
 class store_key;
@@ -51,6 +60,18 @@ typedef struct st_table_ref
   byte          *key_buff2;               // key_buff+key_length
   store_key     **key_copy;               //
   Item          **items;                  // val()'s for each keypart
+  /*  
+    Array of pointers to trigger variables. Some/all of the pointers may be
+    NULL.  The ref access can be used iff
+    
+      for each used key part i, (!cond_guards[i] || *cond_guards[i]) 
+
+    This array is used by subquery code. The subquery code may inject
+    triggered conditions, i.e. conditions that can be 'switched off'. A ref 
+    access created from such condition is not valid when at least one of the 
+    underlying conditions is switched off (see subquery code for more details)
+  */
+  bool          **cond_guards;
   /*
     (null_rejecting & (1<<i)) means the condition is '=' and no matching
     rows will be produced if items[i] IS NULL (see add_not_null_conds())
@@ -99,6 +120,13 @@ enum enum_nested_loop_state
   NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4
 };
 
+
+/* Values for JOIN_TAB::packed_info */
+#define TAB_INFO_HAVE_VALUE 1
+#define TAB_INFO_USING_INDEX 2
+#define TAB_INFO_USING_WHERE 4
+#define TAB_INFO_FULL_SCAN_ON_NULL 8
+
 typedef enum_nested_loop_state
 (*Next_select_func)(JOIN *, struct st_join_table *, bool);
 typedef int (*Read_record_func)(struct st_join_table *tab);
@@ -119,7 +147,15 @@ typedef struct st_join_table {
   st_join_table *last_inner;    /* last table table for embedding outer join */
   st_join_table *first_upper;  /* first inner table for embedding outer join */
   st_join_table *first_unmatched; /* used for optimization purposes only     */
+  
+  /* Special content for EXPLAIN 'Extra' column or NULL if none */
   const char	*info;
+  /* 
+    Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra'
+    column, or 0 if there is no info.
+  */
+  uint          packed_info;
+
   Read_record_func read_first_record;
   Next_select_func next_select;
   READ_RECORD	read_record;
@@ -386,7 +422,7 @@ public:
 			  Item_sum ***func);
   int rollup_send_data(uint idx);
   int rollup_write_data(uint idx, TABLE *table);
-  bool test_in_subselect(Item **where);
+  void remove_subq_pushed_predicates(Item **where);
   /*
     Release memory and, if possible, the open tables held by this execution
     plan (and nested plans). It's used to release some tables before