MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:October 20 2006 8:48pm
Subject:bk commit into 4.1 tree (sergefp:1.2533) BUG#23485
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-21 00:48:40+04:00, sergefp@stripped +5 -0
  BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
  - Make the direct subquery execution engine to evaluate NULL IN (SELECT ...) in a special way
    that produces correct results.
  Note: This breaks [unique_]index_subquery, will fix in forthcoming commit. 

  sql/item_cmpfunc.cc@stripped, 2006-10-21 00:48:38+04:00, sergefp@stripped +23 -1
    BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
    Item_in_optimizer::val_int(): when we evaluate "NULL IN (SELECT ...)", and want to 
    distinguish between NULL and FALSE result, do this:
     - disable the predicates we've pushed into subquery, run the subquery and see if it has 
       produced any rows (yes=>NULL, no=>FALSE).      

  sql/item_cmpfunc.h@stripped, 2006-10-21 00:48:39+04:00, sergefp@stripped +53 -0
    BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
     - Backport Item_func_trig_cond from 5.0

  sql/item_func.h@stripped, 2006-10-21 00:48:39+04:00, sergefp@stripped +2 -1
    BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
     - Backport Item_func_trig_cond from 5.0

  sql/item_subselect.cc@stripped, 2006-10-21 00:48:39+04:00, sergefp@stripped +28 -3
    BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
     - When we push predicates down into subselect, wrap them into trigger condition. This is ok,
       these predicates are not used for any optimizations; The only exception is catching
       "ref_or_null" to change subselect engine to index-lookup engine. This will be addressed in
       forthcoming commit (will push only both at once).
     - Add bool no_rows() into subselect_single_select_engine and subselect_union_select_engine.

  sql/item_subselect.h@stripped, 2006-10-21 00:48:39+04:00, sergefp@stripped +19 -1
    BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	pylon.mylan
# Root:	/home/psergey/mysql-4.1-bug8804-review-clean

--- 1.216/sql/item_cmpfunc.cc	2006-10-21 00:48:43 +04:00
+++ 1.217/sql/item_cmpfunc.cc	2006-10-21 00:48:43 +04:00
@@ -677,9 +677,31 @@
 {
   DBUG_ASSERT(fixed == 1);
   cache->store(args[0]);
+  
   if (cache->null_value)
   {
-    null_value= 1;
+    if (((Item_in_subselect*)args[1])->is_top_level_item())
+    {
+      /*
+        We're evaluating "NULL IN (SELECT ...)". The result can be NULL or
+        FALSE, and we can return one instead of another. Just return NULL.
+      */
+      null_value= 1;
+    }
+    else
+    {
+      /*
+        We're evaluating "NULL IN (SELECT ...)". The result is either
+           FALSE if SELECT produces an empty set, or
+           NULL  otherwise.
+        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_int_result();
+      null_value= !((Item_in_subselect*)args[1])->engine->no_rows();
+      ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE;
+    }
     return 0;
   }
   longlong tmp= args[1]->val_int_result();

--- 1.119/sql/item_cmpfunc.h	2006-10-21 00:48:43 +04:00
+++ 1.120/sql/item_cmpfunc.h	2006-10-21 00:48:43 +04:00
@@ -94,7 +94,45 @@
   void fix_length_and_dec() { decimals=0; max_length=1; }
 };
 
+
+/*
+  The class Item_func_trig_cond is used for guarded predicates 
+  which are employed only for internal purposes.
+  A guarded predicates is an object consisting of an a regular or
+  a guarded predicate P and a pointer to a boolean guard variable g. 
+  A guarded predicate P/g is evaluated to true if the value of the
+  guard g is false, otherwise it is evaluated to the same value that
+  the predicate P: val(P/g)= g ? val(P):true.
+  Guarded predicates allow us to include predicates into a conjunction
+  conditionally. Currently they are utilized for pushed down predicates
+  in queries with outer join operations.
+
+  In the future, probably, it makes sense to extend this class to
+  the objects consisting of three elements: a predicate P, a pointer
+  to a variable g and a firing value s with following evaluation
+  rule: val(P/g,s)= g==s? val(P) : true. It will allow us to build only
+  one item for the objects of the form P/g1/g2... 
+
+  Objects of this class are built only for query execution after
+  the execution plan has been already selected. That's why this
+  class needs only val_int out of generic methods. 
+*/
+
+class Item_func_trig_cond: public Item_bool_func
+{
+  bool *trig_var;
+public:
+  Item_func_trig_cond(Item *a, bool *f) : Item_bool_func(a) { trig_var= f; }
+  longlong val_int() { return *trig_var ? args[0]->val_int() : 1; }
+  enum Functype functype() const { return TRIG_COND_FUNC; };
+  const char *func_name() const { return "trigcond"; };
+  bool const_item() const { return FALSE; }
+};
+
+
 class Item_cache;
+
+
 class Item_in_optimizer: public Item_bool_func
 {
 protected:
@@ -113,6 +151,16 @@
     of IN by storing it value in cache item (one of Item_cache* items), 
     then it test cache is it NULL. If left expression (cache) is NULL then
     Item_in_optimizer return NULL, else it evaluate Item_in_subselect.
+
+    psergey: and the above is not correct. NULL IN (SELECT * FROM empty) can
+    evaluate to FALSE in some cases:
+
+    psergey-todo: fix comments here, try to rename. This 'optimizer' doesnt
+    ever attempt to optimize anything!
+
+    This is used for:
+     - direct-execution subqueries.
+     - index-lookup-based subqs.
   */
   longlong val_int();
   void cleanup();
@@ -859,6 +907,11 @@
 /* Functions used by HAVING for rewriting IN subquery */
 
 class Item_in_subselect;
+
+/* 
+  psergey: this is like IS NOT NULL but it also remembers if it ever has
+ * encountered a NULL... 
+*/
 class Item_is_not_null_test :public Item_func_isnull
 {
   Item_in_subselect* owner;

--- 1.133/sql/item_func.h	2006-10-21 00:48:43 +04:00
+++ 1.134/sql/item_func.h	2006-10-21 00:48:43 +04:00
@@ -51,7 +51,8 @@
 		  SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC,
 		  SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING,
 		  SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN,
-		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC};
+		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC,
+                  TRIG_COND_FUNC};
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL };
   enum Type type() const { return FUNC_ITEM; }
   virtual enum Functype functype() const   { return UNKNOWN_FUNC; }

--- 1.145/sql/item_subselect.cc	2006-10-21 00:48:43 +04:00
+++ 1.146/sql/item_subselect.cc	2006-10-21 00:48:43 +04:00
@@ -810,6 +810,7 @@
 						select_lex->ref_pointer_array,
 						(char *)"<ref>",
 						this->full_name()));
+    item= new Item_func_trig_cond(item, &enable_pushed_conds);
     /*
       AND and comparison functions can't be changed during fix_fields()
       we can assign select_lex->having here, and pass 0 as last
@@ -833,10 +834,13 @@
       select_lex->item_list.push_back(new Item_int("Not_used",
                                                    (longlong) 1, 21));
       select_lex->ref_pointer_array[0]= select_lex->item_list.head();
+       
       item= func->create(expr, item);
       if (!abort_on_null && orig_item->maybe_null)
       {
-	having= new Item_is_not_null_test(this, having);
+	having= 
+          new Item_func_trig_cond(new Item_is_not_null_test(this, having),
+                                  &enable_pushed_conds);
 	/*
 	  Item_is_not_null_test can't be changed during fix_fields()
 	  we can assign select_lex->having here, and pass 0 as last
@@ -853,8 +857,8 @@
 	  DBUG_RETURN(RES_ERROR);
 	}
 	select_lex->having_fix_field= 0;
-	item= new Item_cond_or(item,
-			       new Item_func_isnull(orig_item));
+	item= new Item_cond_or(item, new Item_func_isnull(orig_item));
+        item= new Item_func_trig_cond(item, &enable_pushed_conds);
       }
       item->name= (char *)in_additional_cond;
       /*
@@ -1292,6 +1296,16 @@
 }
 
 
+/*
+  Check if last execution of the subquery engine produced any rows
+*/
+
+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);
+}
+
 void subselect_uniquesubquery_engine::cleanup()
 {
   DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
@@ -1356,6 +1370,17 @@
   DBUG_ASSERT(0);
   return 1;
 }
+
+
+/*
+  Check if last execution of the subquery engine produced any rows
+*/
+
+bool subselect_single_select_engine::no_rows()
+{ 
+  return test(!join->send_records);
+}
+
 
 static Item_result set_row(List<Item> &item_list, Item *item,
 			   Item_cache **row, bool *maybe_null)

--- 1.63/sql/item_subselect.h	2006-10-21 00:48:43 +04:00
+++ 1.64/sql/item_subselect.h	2006-10-21 00:48:43 +04:00
@@ -216,12 +216,15 @@
   bool abort_on_null;
   bool transformed;
 public:
+  /* Used to trigger on/off conditions that were pushed down to subselect */
+  bool enable_pushed_conds;
+
   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),
-     upper_item(0)
+     enable_pushed_conds(TRUE), upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -239,6 +242,7 @@
   double val();
   String *val_str(String*);
   void top_level_item() { abort_on_null=1; }
+  inline bool is_top_level_item() { return abort_on_null; }
   bool test_limit(st_select_lex_unit *unit);
   void print(String *str);
 
@@ -303,6 +307,18 @@
   virtual void print(String *str)= 0;
   virtual int change_item(Item_subselect *si, select_subselect *result)= 0;
   virtual bool no_tables()= 0;
+  /*
+    Check if subquery produced any rows during last query execution
+  */
+  virtual bool no_rows()
+  { 
+    /* 
+      psergey-TODO: when working on fix for BUG#8804, change this to be a
+      pure virtual function. ATM it can't as there are no implementations
+      for index lookup-based engines.
+    */
+    DBUG_ASSERT(0); return 1;
+  }
 };
 
 
@@ -328,6 +344,7 @@
   void print (String *str);
   int change_item(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  bool no_rows();
 };
 
 
@@ -349,6 +366,7 @@
   void print (String *str);
   int change_item(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  bool no_rows();
 };
 
 
Thread
bk commit into 4.1 tree (sergefp:1.2533) BUG#23485Sergey Petrunia20 Oct