bk commit into 5.0 tree (sergefp:1.2305) 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, 2006-12-06 04:33:30+03:00, sergefp@stripped +5 -0
  BUG#24127: Post-review fixes batch 1:
  Added comments and fixed not covered wrong query result problems:
  - Let indexsubquery_engine have the "HAVING" clause (which has predicates
    insert by subquery rewrite code) and use it to filter rows. This is needed
    for correct query results.
  - init_read_record_seq() now reads the first record, as it is required
    by its calling convention

  mysql-test/r/subselect3.result@stripped, 2006-12-06 04:33:26+03:00, sergefp@stripped +30 -0
    BUG#24127: Post-review fixes batch 1: more testcases for tricky cases

  mysql-test/t/subselect3.test@stripped, 2006-12-06 04:33:26+03:00, sergefp@stripped +35 -0
    BUG#24127: Post-review fixes batch 1: more testcases for tricky cases

  sql/item_subselect.cc@stripped, 2006-12-06 04:33:26+03:00, sergefp@stripped +19 -11
    BUG#24127: Post-review fixes batch 1:
    - Added comments
    - Let indexsubquery_engine have the "HAVING" clause (which has predicates
      insert by subquery rewrite code) and use it to filter rows. This is needed
      for correct query results.

  sql/item_subselect.h@stripped, 2006-12-06 04:33:26+03:00, sergefp@stripped +53 -3
    BUG#24127: Post-review fixes batch 1:
    - Added comments
    - Let indexsubquery_engine have the "HAVING" clause (which has predicates
      insert by subquery rewrite code) and use it to filter rows. This is needed
      for correct query results.

  sql/sql_select.cc@stripped, 2006-12-06 04:33:26+03:00, sergefp@stripped +5 -1
    BUG#24127: Post-review fixes batch 1:
    - Added comments
    - init_read_record_seq() now reads the first record, as it is required
      by its calling convention

# 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-r10

--- 1.471/sql/sql_select.cc	2006-12-06 04:33:35 +03:00
+++ 1.472/sql/sql_select.cc	2006-12-06 04:33:35 +03:00
@@ -1031,6 +1031,7 @@
                                                                  join_tab,
                                                                  unit->item,
                                                                  where,
+                                                                 NULL,
                                                                  0)));
       }
     } else if (join_tab[0].type == JT_REF_OR_NULL &&
@@ -1046,6 +1047,7 @@
 								   join_tab,
 								   unit->item,
 								   conds,
+                                                                   having,
 								   1)));
     }
 
@@ -10841,7 +10843,9 @@
 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

--- 1.142/sql/item_subselect.cc	2006-12-06 04:33:35 +03:00
+++ 1.143/sql/item_subselect.cc	2006-12-06 04:33:35 +03:00
@@ -1849,7 +1849,7 @@
 
 
 /*
-  Search for at least on row satisfying select condition
+  Search for at least one row satisfying select condition
  
   SYNOPSIS
     subselect_uniquesubquery_engine::scan_table()
@@ -1858,8 +1858,8 @@
     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
@@ -1871,7 +1871,6 @@
   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();
@@ -1964,10 +1963,13 @@
       - 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
@@ -1980,6 +1982,7 @@
   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())
@@ -2000,9 +2003,13 @@
   {
     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);
@@ -2109,8 +2116,9 @@
       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

--- 1.82/sql/item_subselect.h	2006-12-06 04:33:35 +03:00
+++ 1.83/sql/item_subselect.h	2006-12-06 04:33:35 +03:00
@@ -433,11 +433,30 @@
 
 
 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.
@@ -475,14 +494,45 @@
 {
   /* 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 *enabled_conds);
   void print (String *str);

--- 1.3/mysql-test/r/subselect3.result	2006-12-06 04:33:35 +03:00
+++ 1.4/mysql-test/r/subselect3.result	2006-12-06 04:33:35 +03:00
@@ -266,3 +266,33 @@
 NULL	1	100	0
 NULL	2	100	NULL
 drop table t1,t2,t3;
+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 where
a=3 and b is null ;
+oref	a	b	Z
+cc	3	NULL	NULL
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL);
+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;
+oref	a	b	Z
+new1	10	10	NULL
+drop table t1, t2;

--- 1.3/mysql-test/t/subselect3.test	2006-12-06 04:33:35 +03:00
+++ 1.4/mysql-test/t/subselect3.test	2006-12-06 04:33:35 +03:00
@@ -230,3 +230,38 @@
 from t2;
 
 drop table t1,t2,t3;
+
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some arent.
+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);
+
+--cc 3 NULL NULL
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where
a=3 and b is null ;
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL); 
+-- new1, 10, 10, NULL,
+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;
+