bk commit into 5.0 tree (sergefp:1.2376) BUG#24127

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
5.0 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, 2007-01-12 21:17:47+03:00, sergefp@stripped +9 -0
  BUG#24127: Post-review fixes part 3:

  mysql-test/r/ndb_subquery.result@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +3 -3
    BUG#24127: Post-review fixes part 3:
     - Updated test results

  mysql-test/r/subselect3.result@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +324 -0
    BUG#24127: Post-review fixes part 3:
     - Add more testcases

  mysql-test/t/subselect3.test@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +202 -0
    BUG#24127: Post-review fixes part 3:
     - Add more testcases

  sql/item_cmpfunc.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +7 -6
    BUG#24127: Post-review fixes part 3:
    - Replace Item_in_subselect::enable_pushed_conds access with 
      set_cond_guard_var() calls  

  sql/item_cmpfunc.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +2 -1
    BUG#24127: Post-review fixes part 3:

  sql/item_subselect.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +20 -32
    BUG#24127: Post-review fixes part 3:

  sql/item_subselect.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +12 -6
    BUG#24127: Post-review fixes part 3:

  sql/sql_select.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +12 -11
    BUG#24127: Post-review fixes part 3:

  sql/sql_select.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +17 -11
    BUG#24127: Post-review fixes part 3:

# 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-5.0-bug8804-r11

--- 1.226/sql/item_cmpfunc.cc	2007-01-12 21:17:52 +03:00
+++ 1.227/sql/item_cmpfunc.cc	2007-01-12 21:17:52 +03:00
@@ -816,30 +816,31 @@
         Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; 
         if (cache->cols() == 1)
         {
-          item_subs->enable_pushed_conds= FALSE;
+          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->enable_pushed_conds= TRUE;
+          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 (uint i= 0; i < ncols; i++)
+          for (i= 0; i < ncols; i++)
           {
             if (cache->el(i)->null_value)
-              item_subs->enable_pushed_conds_vec[i]= FALSE;
+              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 (uint i= 0; i < ncols; i++)
-            item_subs->enable_pushed_conds_vec[i]= TRUE;
+          for (i= 0; i < ncols; i++)
+            item_subs->set_cond_guard_var(i, TRUE);
         }
       }
     }

--- 1.137/sql/item_cmpfunc.h	2007-01-12 21:17:52 +03:00
+++ 1.138/sql/item_cmpfunc.h	2007-01-12 21:17:52 +03:00
@@ -304,13 +304,14 @@
 
 class Item_func_trig_cond: public Item_bool_func
 {
-public:
   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; }
+  bool *get_trig_var() { return trig_var; }
 };
 
 class Item_func_not_all :public Item_func_not

--- 1.479/sql/sql_select.cc	2007-01-12 21:17:52 +03:00
+++ 1.480/sql/sql_select.cc	2007-01-12 21:17:52 +03:00
@@ -529,14 +529,14 @@
     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.
+    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 some parts of the left tuple are 
-    NULLs and some aren't, we'll use full table scan and will rely on the 
-    equalities for non-NULL tuple parts to be guaranteed to be true.
-
-    psergey-todo: ^ clarify ^.
+    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.
 */
 
 void JOIN::remove_subq_pushed_predicates(Item **where)
@@ -2965,8 +2965,9 @@
   }
 
   /* 
-    Subquery optimization: check if the encountered condition is one
-    added by condition push down into subquery. (psergey-todo: better comment)
+    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 &&
@@ -2983,7 +2984,7 @@
                        sargables);
         // Indicate that this ref access candidate is for subquery lookup:
         for (; save != *key_fields; save++)
-          save->cond_guard= (((Item_func_trig_cond*)cond)->trig_var);
+          save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
       }
       return;
     }
@@ -7667,7 +7668,7 @@
 
   SYNOPSIS
     remove_additional_cond()
-    conds - condition for processing
+      conds  Condition for processing
 
   RETURN VALUES
     new conditions

--- 1.115/sql/sql_select.h	2007-01-12 21:17:52 +03:00
+++ 1.116/sql/sql_select.h	2007-01-12 21:17:52 +03:00
@@ -36,13 +36,14 @@
   */
   bool null_rejecting;
   /*
-    > 0 - This element was created from a triggered condition (which will 
-          be turned of for "NULL IN (SELECT ...)" that was pushed down into
-          a subselect. The number is the number of column, e.g. for 
-                       (a, b) IN (SELECT x, keypart2 ...) 
-          we'll get a KEYUSE created for "keypart2=b" with outer_ref_col == 1.
-    psergey-todo: fix comment
-    UINT_MAX - Otherwise
+    !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;
@@ -60,10 +61,15 @@
   store_key     **key_copy;               //
   Item          **items;                  // val()'s for each keypart
   /*  
-    Array of numbers of pushed-down subq predicates that were used to
-    construct equalities on keypart #i. If equality on keypart #i was not
-    constructed from pushed-down predicate, outer_ref_col[i]==UINT_MAX.
-    psergey-todo: change-comment!
+    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;
   /*

--- 1.146/sql/item_subselect.cc	2007-01-12 21:17:52 +03:00
+++ 1.147/sql/item_subselect.cc	2007-01-12 21:17:52 +03:00
@@ -565,8 +565,7 @@
 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), enable_pushed_conds_vec(&enable_pushed_conds),
-  upper_item(0)
+  pushed_cond_guards(NULL), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -950,20 +949,13 @@
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
   }
   if (!abort_on_null && left_expr->maybe_null)
-    enable_pushed_conds_vec= &enable_pushed_conds;
+  {
+    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)
   {
@@ -981,7 +973,7 @@
         We can encounter "NULL IN (SELECT ...)". Wrap the added condition
         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));
     }
     
     /*
@@ -1022,7 +1014,7 @@
         if (left_expr->maybe_null)
         {
           if (!(having= new Item_func_trig_cond(having,
-                                                &enable_pushed_conds)))
+                                                get_cond_guard(0))))
             DBUG_RETURN(RES_ERROR);
         }
 	/*
@@ -1051,7 +1043,7 @@
       */
       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);
       }
       /*
@@ -1094,7 +1086,7 @@
         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;
@@ -1173,11 +1165,11 @@
 
     if (!abort_on_null && left_expr->maybe_null)
     {
-      if (!(enable_pushed_conds_vec= (bool*)join->thd->alloc(sizeof(bool) *
-                                                             left_expr->cols())))
+      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++)
-        enable_pushed_conds_vec[i]= TRUE;
+        pushed_cond_guards[i]= TRUE;
     }
   }
 
@@ -1227,8 +1219,7 @@
       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,
-                                                enable_pushed_conds_vec + i)))
+        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);
@@ -1243,8 +1234,7 @@
       if (!abort_on_null && left_expr->el(i)->maybe_null)
       {
         if (!(item_nnull_test= 
-              new Item_func_trig_cond(item_nnull_test,
-                                      enable_pushed_conds_vec + i)))
+              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);
@@ -1320,12 +1310,10 @@
         */
         if (left_expr->el(i)->maybe_null)
         {
-          if (!(item= new Item_func_trig_cond(item,
-                                              enable_pushed_conds_vec + i)))
+          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,
-                                          enable_pushed_conds_vec + i)))
+                  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);
@@ -1774,7 +1762,7 @@
     bool have_changed_access= FALSE;
     JOIN_TAB *changed_tabs[MAX_TABLES];
     JOIN_TAB **last_changed_tab= changed_tabs;
-    if (1)//enabled_conds) //psergey-todo:!
+    if (item->have_guarded_conds())
     {
       /*
         For at least one of the pushed predicates the following is true:
@@ -1792,13 +1780,13 @@
             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;
-              //psergey-todo: break; ? 
+              break;
             }
           }
         }

--- 1.85/sql/item_subselect.h	2007-01-12 21:17:52 +03:00
+++ 1.86/sql/item_subselect.h	2007-01-12 21:17:52 +03:00
@@ -104,6 +104,7 @@
   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,15 +250,21 @@
   bool transformed;
 public:
   /* Used to trigger on/off conditions that were pushed down to subselect */
-  bool enable_pushed_conds;
-  bool *enable_pushed_conds_vec;
+  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), enable_pushed_conds_vec(NULL), upper_item(0)
+     pushed_cond_guards(NULL), upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -349,10 +356,9 @@
       stored somewhere by the exec() method itself.
 
       A required side effect: If at least one pushed-down predicate is
-      disabled, call of subselect_engine->no_rows()must return correct
-      result after this call.
+      disabled, subselect_engine->no_rows() must return correct result after 
+      the exec() call.
 
-      psergey-todo: check comment
     RETURN
       0 - OK
       1 - Either an execution error, or the engine was "changed", and the

--- 1.5/mysql-test/r/subselect3.result	2007-01-12 21:17:52 +03:00
+++ 1.6/mysql-test/r/subselect3.result	2007-01-12 21:17:52 +03:00
@@ -305,3 +305,327 @@
 Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS
`b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and
trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or
isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) =
`test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having
(trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and
trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where
((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
 drop table t1, t2;
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('aa', 20, NULL),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, NULL),
+('ee', 10, NULL),
+('ee', 10, NULL),
+('ff', 20, 2),
+('ff', 20, 1);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('ff', 2),
+('cc', 3),
+('aa', 1),
+('dd', NULL),
+('bb', NULL);
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	0
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where 
+a in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+aa	1
+select oref, a from t2 where 
+a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+bb	2
+ff	2
+dd	NULL
+update t1 set ie=3 where oref='ff' and ie=1;
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+alter table t1 add index idx(ie);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL
key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+alter table t1 drop index idx;
+alter table t1 add index idx(oref,ie);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	ref_or_null	idx	idx	10	test.t2.oref,func	4	Using where; Using
index; Full scan on NULL key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+explain 
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1) Z 
+from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	ref	idx	idx	5	test.t2.oref	2	Using where; Using temporary; Using
filesort
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1) Z 
+from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+select oref, a, b, 
+(a,b) in (select min(ie1),max(ie2) from t1 
+where oref=t2.oref group by grp) Z 
+from t2;
+oref	a	b	Z
+ee	NULL	1	0
+bb	2	1	0
+ff	2	2	0
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where 
+(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+aa	1	1
+select oref, a, b from t2 where
+(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+ee	NULL	1
+bb	2	1
+ff	2	2
+dd	1	NULL
+alter table t1 add index idx(ie1,ie2);
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from
t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL
key
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL
key
+Warnings:
+Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS
`b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and
trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or
isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) =
`test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having
(trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and
trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, 5),
+('cc', 10, 6);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('cc', 5),
+('cc', 2),
+('cc', NULL),
+('aa', 1),
+('bb', NULL);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where; Full scan
on NULL key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+cc	5
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+ee	NULL
+bb	2
+cc	2
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using
filesort
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+drop table t1,t2;

--- 1.6/mysql-test/t/subselect3.test	2007-01-12 21:17:52 +03:00
+++ 1.7/mysql-test/t/subselect3.test	2007-01-12 21:17:52 +03:00
@@ -271,3 +271,205 @@
 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where
a=10 and b=10; 
 drop table t1, t2;
 
+# Now test different column types:
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+ ('aa', 20, NULL),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, NULL),
+ 
+ ('ee', 10, NULL),
+ ('ee', 10, NULL),
+ 
+ ('ff', 20, 2),
+ ('ff', 20, 1);
+
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('ff', 2),
+  ('cc', 3),
+  ('aa', 1),
+  ('dd', NULL),
+  ('bb', NULL);
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a from t2 where 
+  a in (select min(ie) from t1 where oref=t2.oref group by grp);
+  
+select oref, a from t2 where 
+  a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+
+#
+update t1 set ie=3 where oref='ff' and ie=1;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+
+#
+alter table t1 add index idx(ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+alter table t1 drop index idx;
+alter table t1 add index idx(oref,ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+                                   group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+                                       group by grp having min(ie) > 1);
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+
+ ('bb', 10, 3, 1),
+
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+ 
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+ 
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+  ('ee', NULL, 1),
+  ('bb', 2, 1),
+  ('ff', 2, 2),
+  ('cc', 3, NULL),
+  ('bb', NULL, NULL),
+  ('aa', 1, 1),
+  ('dd', 1, NULL);
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b, 
+             (a,b) in (select min(ie1),max(ie2) from t1 
+                       where oref=t2.oref group by grp) Z 
+from t2;
+
+select oref, a, b from t2 where 
+  (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+  
+select oref, a, b from t2 where
+  (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+
+alter table t1 add index idx(ie1,ie2);
+
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from
t2;
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, 5),
+ ('cc', 10, 6);
+ 
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('cc', 5),
+  ('cc', 2),
+  ('cc', NULL),
+  ('aa', 1),
+  ('bb', NULL);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+drop table t1,t2;
+

--- 1.3/mysql-test/r/ndb_subquery.result	2007-01-12 21:17:52 +03:00
+++ 1.4/mysql-test/r/ndb_subquery.result	2007-01-12 21:17:52 +03:00
@@ -9,7 +9,7 @@
 explain select * from t2 where p NOT IN (select p from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
-2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	
 select * from t2 where p NOT IN (select p from t1) order by p;
 p	u	o
 4	4	4
@@ -17,7 +17,7 @@
 explain select * from t2 where p NOT IN (select u from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
-2	DEPENDENT SUBQUERY	t1	unique_subquery	u	u	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	unique_subquery	u	u	4	func	1	
 select * from t2 where p NOT IN (select u from t1) order by p;
 p	u	o
 4	4	4
@@ -25,7 +25,7 @@
 explain select * from t2 where p NOT IN (select o from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
-2	DEPENDENT SUBQUERY	t1	index_subquery	o	o	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	index_subquery	o	o	4	func	1	
 select * from t2 where p NOT IN (select o from t1) order by p;
 p	u	o
 4	4	4