fixed bug 185 (constant IN (SELECT field ...) do not return NULL correctly)

6 files changed, 86 insertions, 7 deletions

diff --git a/sql/item.h b/sql/item.h
index 6a7ebd506ac..4862ad21fbe 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -603,6 +603,7 @@ public:
     item(it) 
   {}
   bool fix_fields(THD *, struct st_table_list *, Item ** ref);
+  Item **storage() {return &item;}
 };
 
 /*
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 09f0aeefb09..ffe89dbb84c 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1781,6 +1781,43 @@ longlong Item_func_isnull::val_int()
   return args[0]->is_null() ? 1: 0;
 }
 
+longlong Item_is_not_null_test::val_int()
+{
+  DBUG_ENTER("Item_is_not_null_test::val_int");
+  if (!used_tables_cache)
+  {
+    owner->was_null|= (!cached_value);
+    DBUG_PRINT("info", ("cached :%d", cached_value));
+    DBUG_RETURN(cached_value);
+  }
+  if (args[0]->is_null())
+  {
+    DBUG_PRINT("info", ("null"))
+    owner->was_null|= 1;
+    DBUG_RETURN(0);
+  }
+  else
+    DBUG_RETURN(1);
+}
+
+/* Optimize case of not_null_column IS NULL */
+void Item_is_not_null_test::update_used_tables()
+{
+  if (!args[0]->maybe_null)
+  {
+    used_tables_cache= 0;			/* is always true */
+    cached_value= (longlong) 1;
+  }
+  else
+  {
+    args[0]->update_used_tables();
+    if (!(used_tables_cache=args[0]->used_tables()))
+    {
+      /* Remember if the value is always NULL or never NULL */
+      cached_value= (longlong) !args[0]->is_null();
+    }
+  }
+}
 
 longlong Item_func_isnotnull::val_int()
 {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index e7670755396..5e246e3e285 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -648,6 +648,7 @@ class Item_func_in :public Item_int_func
 
 class Item_func_isnull :public Item_bool_func
 {
+protected:
   longlong cached_value;
 public:
   Item_func_isnull(Item *a) :Item_bool_func(a) {}
@@ -656,11 +657,11 @@ public:
   void fix_length_and_dec()
   {
     decimals=0; max_length=1; maybe_null=0;
-    Item_func_isnull::update_used_tables();
+    update_used_tables();
   }
   const char *func_name() const { return "isnull"; }
   /* Optimize case of not_null_column IS NULL */
-  void update_used_tables()
+  virtual void update_used_tables()
   {
     if (!args[0]->maybe_null)
     {
@@ -680,6 +681,22 @@ public:
   optimize_type select_optimize() const { return OPTIMIZE_NULL; }
 };
 
+/* Functions used by HAVING for rewriting IN subquery */
+
+class Item_in_subselect;
+class Item_is_not_null_test :public Item_func_isnull
+{
+  Item_in_subselect* owner;
+public:
+  Item_is_not_null_test(Item_in_subselect* ow, Item *a)
+    :Item_func_isnull(a), owner(ow)
+  {}
+  longlong val_int();
+  const char *func_name() const { return "is_not_null_test"; }
+  void update_used_tables();
+};
+
+
 class Item_func_isnotnull :public Item_bool_func
 {
 public:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 76451680b59..6c0b799b4de 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -508,9 +508,31 @@ void Item_in_subselect::single_value_transformer(THD *thd,
       sl->item_list.push_back(new Item_int("Not_used", (longlong) 1, 21));
       if (sl->table_list.elements)
       {
-	item= (*func)(expr, new Item_asterisk_remover(this, item,
-						      (char *)"<no matter>",
-						      (char*)"<result>"));
+	Item *having= item, *isnull= item;
+	if (item->type() == Item::FIELD_ITEM &&
+	    ((Item_field*) item)->field_name[0] == '*')
+	{
+	  Item_asterisk_remover *remover;
+	  item= remover= new Item_asterisk_remover(this, item,
+						   (char*)"<no matter>",
+						   (char*)"<result>");
+	  having= 
+	    new Item_is_not_null_test(this,
+				      new Item_ref(remover->storage(),
+						   (char*)"<no matter>",
+						   (char*)"<null test>"));
+	  isnull=
+	    new Item_is_not_null_test(this,
+				      new Item_ref(remover->storage(),
+						   (char*)"<no matter>",
+						   (char*)"<null test>"));
+	}
+	having= new Item_is_not_null_test(this, having);
+	sl->having= (sl->having ?
+		     new Item_cond_and(having, sl->having) :
+		     having);
+	item= new Item_cond_or((*func)(expr, item),
+			       new Item_func_isnull(isnull));
 	sl->where= and_items(sl->where, item);
       }
       else
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 351c4af7f33..fc4dad5a6b3 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -183,6 +183,7 @@ public:
 
   friend class Item_asterisk_remover;
   friend class Item_ref_null_helper;
+  friend class Item_is_not_null_test;
 };
 
 /* ALL/ANY/SOME subselect */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3fdff4e96a4..f2dc2b2afd6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -299,7 +299,7 @@ JOIN::prepare(Item ***rref_pointer_array,
     DBUG_RETURN(-1);				/* purecov: inspected */
 
   ref_pointer_array= *rref_pointer_array;
-
+  
   if (having)
   {
     thd->where="having clause";
@@ -313,6 +313,7 @@ JOIN::prepare(Item ***rref_pointer_array,
     if (having->with_sum_func)
       having->split_sum_func(ref_pointer_array, all_fields);
   }
+  
   if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
     DBUG_RETURN(-1);
   /*
@@ -426,7 +427,7 @@ JOIN::optimize()
 
 #ifdef HAVE_REF_TO_FIELDS			// Not done yet
   /* Add HAVING to WHERE if possible */
-  if (having && !group_list && ! sum_func_count)
+  if (having && !group_list && !sum_func_count)
   {
     if (!conds)
     {