new optimisation for ref_null (SCRUM) (WL#818)

mysql-test/r/subselect.result:
  results of new optimisation
mysql-test/t/subselect.test:
  results of new optimisation
sql/item_cmpfunc.h:
  function type
sql/item_func.h:
  function type
sql/item_subselect.cc:
  new optimisation for ref_or_null
sql/item_subselect.h:
  new optimisation for ref_or_null
sql/sql_select.cc:
  new optimisation for ref_or_null
sql/sql_select.h:
  item_subselect.cc need safe_index_read

6 files changed, 70 insertions, 29 deletions

diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 03775bbf375..d0748269c77 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -669,6 +669,7 @@ public:
   Item_is_not_null_test(Item_in_subselect* ow, Item *a)
     :Item_func_isnull(a), owner(ow)
   {}
+  enum Functype functype() const { return ISNOTNULLTEST_FUNC; }
   longlong val_int();
   const char *func_name() const { return "is_not_null_test"; }
   void update_used_tables();
diff --git a/sql/item_func.h b/sql/item_func.h
index 30a499c2a52..65f80b26021 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -41,7 +41,7 @@ public:
 		  GE_FUNC,GT_FUNC,FT_FUNC,
 		  LIKE_FUNC,NOTLIKE_FUNC,ISNULL_FUNC,ISNOTNULL_FUNC,
 		  COND_AND_FUNC, COND_OR_FUNC, COND_XOR_FUNC, BETWEEN, IN_FUNC,
-		  INTERVAL_FUNC,
+		  INTERVAL_FUNC, ISNOTNULLTEST_FUNC,
 		  SP_EQUALS_FUNC, SP_DISJOINT_FUNC,SP_INTERSECTS_FUNC,
 		  SP_TOUCHES_FUNC,SP_CROSSES_FUNC,SP_WITHIN_FUNC,
 		  SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC,
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 894173f4575..3d2a88d29e5 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -999,8 +999,15 @@ int subselect_indexin_engine::exec()
   DBUG_ENTER("subselect_indexin_engine::exec");
   int error;
   TABLE *table= tab->table;
+
   ((Item_in_subselect *) item)->value= 0;
-  if ((tab->ref.key_err= (*tab->ref.key_copy)->copy()))
+  if (check_null)
+  {
+    *tab->null_ref_key= 0;
+    ((Item_in_subselect *) item)->was_null= 0;
+  }
+
+  if ((*tab->ref.key_copy) && (tab->ref.key_err= (*tab->ref.key_copy)->copy()))
   {
     table->status= STATUS_NOT_FOUND;
     error= -1;
@@ -1022,12 +1029,21 @@ int subselect_indexin_engine::exec()
 	{
 	  if (!cond || cond->val_int())
 	  {
-	    ((Item_in_subselect *) item)->value= 1;
+	    if (check_null && *tab->null_ref_key)
+	      ((Item_in_subselect *) item)->was_null= 1;
+	    else
+	      ((Item_in_subselect *) item)->value= 1;
 	    goto finish;
 	  }
 	}
 	else
-	  goto finish;
+	{
+	  if (!check_null || *tab->null_ref_key)
+	    goto finish;
+	  *tab->null_ref_key= 1;
+	  if (safe_index_read(tab))
+	    goto finish;
+	}
 	error= table->file->index_next_same(table->record[0],
 					    tab->ref.key_buff,
 					    tab->ref.key_length);
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 5749220629f..e2738102ebd 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -228,6 +228,7 @@ public:
   friend class Item_asterisk_remover;
   friend class Item_ref_null_helper;
   friend class Item_is_not_null_test;
+  friend class subselect_indexin_engine;
 };
 
 /* ALL/ANY/SOME subselect */
@@ -337,10 +338,13 @@ public:
 
 class subselect_indexin_engine: public subselect_simplein_engine
 {
+  bool check_null;
 public:
   subselect_indexin_engine(THD *thd, st_join_table *tab_arg,
-			    Item_subselect *subs, Item *where)
-    :subselect_simplein_engine(thd, tab_arg, subs, where)
+			    Item_subselect *subs, Item *where,
+			   bool chk_null)
+    :subselect_simplein_engine(thd, tab_arg, subs, where),
+     check_null(chk_null)
   {}
   int exec();
 };
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index dca5f2019fe..3c5c53422ae 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -456,7 +456,7 @@ err:
 bool JOIN::test_in_subselect(Item **where)
 {
   if (conds->type() == Item::FUNC_ITEM &&
-      ((class Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
+      ((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)
   {
@@ -763,38 +763,57 @@ JOIN::optimize()
   /*
     is this simple IN subquery?
   */
-  if (!group_list && !order && !having &&
+  if (!group_list && !order &&
       unit->item && unit->item->substype() == Item_subselect::IN_SUBS &&
       tables == 1 && conds &&
       !unit->first_select()->next_select())
   {
-    Item *where= 0;
-    if (join_tab[0].type == JT_EQ_REF)
+    if (!having)
     {
-      if (test_in_subselect(&where))
+      Item *where= 0;
+      if (join_tab[0].type == JT_EQ_REF)
       {
-	join_tab[0].type= JT_SIMPLE_IN;
-	error= 0;
-	DBUG_RETURN(unit->item->
-		    change_engine(new subselect_simplein_engine(thd, join_tab,
-								unit->item,
-								where)));
+	if (test_in_subselect(&where))
+	{
+	  join_tab[0].type= JT_SIMPLE_IN;
+	  error= 0;
+	  DBUG_RETURN(unit->item->
+		      change_engine(new subselect_simplein_engine(thd,
+								  join_tab,
+								  unit->item,
+								  where)));
+	}
       }
-    }
-    else if (join_tab[0].type == JT_REF)
-    {
-      if (test_in_subselect(&where))
+      else if (join_tab[0].type == JT_REF)
       {
-	join_tab[0].type= JT_INDEX_IN;
-	error= 0;
-	DBUG_RETURN(unit->item->
-		    change_engine(new subselect_indexin_engine(thd, join_tab,
-							       unit->item,
-							       where)));
+	if (test_in_subselect(&where))
+	{
+	  join_tab[0].type= JT_INDEX_IN;
+	  error= 0;
+	  DBUG_RETURN(unit->item->
+		      change_engine(new subselect_indexin_engine(thd,
+								 join_tab,
+								 unit->item,
+								 where,
+								 0)));
+	}
       }
+    } else if (join_tab[0].type == JT_REF_OR_NULL &&
+	       having->type() == Item::FUNC_ITEM &&
+	       ((Item_func *) having)->functype() ==
+	       Item_func::ISNOTNULLTEST_FUNC)
+    {
+      join_tab[0].type= JT_INDEX_IN;
+      error= 0;
+      DBUG_RETURN(unit->item->
+		  change_engine(new subselect_indexin_engine(thd,
+							     join_tab,
+							     unit->item,
+							     conds,
+							     1)));
     }
+	       
   }
-
   /*
     Need to tell Innobase that to play it safe, it should fetch all
     columns of the tables: this is because MySQL may build row
@@ -5445,7 +5464,7 @@ int report_error(TABLE *table, int error)
 }
 
 
-static int safe_index_read(JOIN_TAB *tab)
+int safe_index_read(JOIN_TAB *tab)
 {
   int error;
   TABLE *table= tab->table;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index e7e22b0107d..7b5aa49b9aa 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -411,3 +411,4 @@ bool cp_buffer_from_ref(TABLE_REF *ref);
 bool error_if_full_join(JOIN *join);
 void relink_tables(SELECT_LEX *select_lex);
 int report_error(TABLE *table, int error);
+int safe_index_read(JOIN_TAB *tab);