bk commit into 5.0 tree (igor:1.2254) BUG#21698

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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-09-07 11:06:37-07:00, igor@stripped +10 -0
  Fixed bug #21698: erroneously a field could be replaced by an
  equal constant under any circumstances.
  In fact this substitution can be allowed if the field is
  not of a type string or if the field reference serves as 
  an argument of a comparison predicate.  

  mysql-test/r/func_str.result@stripped, 2006-09-07 11:06:33-07:00, igor@stripped +35
-0
    Added test cases for bug #21698.

  mysql-test/r/heap_hash.result@stripped, 2006-09-07 11:06:33-07:00, igor@stripped +1
-1
    Adjusted results after the fix for bug #21198.

  mysql-test/t/func_str.test@stripped, 2006-09-07 11:06:33-07:00, igor@stripped +27 -0
    Added test cases for bug #21698.

  sql/item.cc@stripped, 2006-09-07 11:06:33-07:00, igor@stripped +38 -3
    Fixed bug #21198.
    Added a method to check whether a field reference can be 
    substituted for a constant equal to the field.
    This substitution is allowed if the field is not of a type string
    or if the field reference serves as an argument of a comparison
    predicate.  

  sql/item.h@stripped, 2006-09-07 11:06:33-07:00, igor@stripped +17 -1
    Fixed bug #21698.
    Added a new virtual transformation method for a item 'compile'
    with two callback function parameters.
    Added a new virtual method 'subst_argument_checker' to be used
    as an amnalyzer method.
    This method is supposed to set its in/out argument to NULL for
    the nodes where substitution of a string field for a constant
    is not valid.

  sql/item_cmpfunc.cc@stripped, 2006-09-07 11:06:34-07:00, igor@stripped +55 -6
    Fixed bug #21698.
    Added an implementation of the compile method for class Item_cond.
    First it processes the Item_cond node with a callback function and if
    the latter returns TRUE it proceeds with a transformation performed by
    another callback function. 

  sql/item_cmpfunc.h@stripped, 2006-09-07 11:06:34-07:00, igor@stripped +4 -0
    Fixed bug #21698.
    Added the implementations of 'subst_argument_checker'
    for the Item_func and Item_cond classes.
    This method is supposed to set its in/out argument to NULL for
    the nodes where substitution of a string field for a constant
    is not valid.
    Added the declaration of an implementation of the compile method for 
    class Item_cond.
    First it processes the Item_cond node with a callback function and if
    the latter returns TRUE it proceeds with a transformation performed by
    another callback function. 

  sql/item_func.cc@stripped, 2006-09-07 11:06:34-07:00, igor@stripped +56 -8
    Fixed bug #21698.
    Added an implementation of the compile method for class Item_func.
    First it processes the Item_func node with a callback function and if
    the latter returns TRUE it proceeds with a transformation performed by
    another callback function. 

  sql/item_func.h@stripped, 2006-09-07 11:06:34-07:00, igor@stripped +2 -0
    Fixed bug #21698.
    Added the declaration of the implementation of the compile method for
    class Item_func.
    First it processes the Item_func node with a callback function and if
    the latter returns TRUE it proceeds with a transformation performed by
    another callback function.
     

  sql/sql_select.cc@stripped, 2006-09-07 11:06:34-07:00, igor@stripped +12 -5
    Fixed bug #21698.
    Limited the conditions at which a field can be substituted 
    a for an equal constant in a formula. 
    This substitution is allowed if the field is not of a type string
    or if the field reference serves as an argument of a comparison
    predicate.  

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug21698

--- 1.232/sql/item.cc	2006-09-07 11:06:45 -07:00
+++ 1.233/sql/item.cc	2006-09-07 11:06:45 -07:00
@@ -3746,13 +3746,48 @@
 
 
 /*
+  Check whether a field can be substituted by an equal item
+
+  SYNOPSIS
+    equal_fields_propagator()
+      arg - *arg != NULL <-> the field is in the context where
+            substitution for an equal item is valid
+   
+  DESCRIPTION
+    The function checks whether a substitution of the field
+    occurrence for an equal item is valid.
+
+  NOTES
+    The following statement is not always true:
+    x=y => F(x)=F(x/y).
+    This means substitution of an item for an equal item not always
+    yields an equavalent condition.
+    Here's an example:
+      'a'='a '
+      (LENGTH('a')=1) != (LENGTH('a ')=2)
+    Such a substitution is surely valid if either the substituted
+    field is not of a STRING type or if it is an argument of
+    a comparison  predicate.  
+
+  RETURN
+    TRUE   substitution is valid
+    FALSE  otherwise
+*/
+
+bool Item_field::subst_argument_checker(byte **arg)
+{
+  return (result_type() != STRING_RESULT) || (*arg);
+}
+
+
+/*
   Set a pointer to the multiple equality the field reference belongs to
   (if any)
    
   SYNOPSIS
     equal_fields_propagator()
-    arg - reference to list of multiple equalities where
-          the field (this object) is to be looked for
+      arg - reference to list of multiple equalities where
+            the field (this object) is to be looked for
   
   DESCRIPTION
     The function looks for a multiple equality containing the field item
@@ -3764,7 +3799,7 @@
 
   NOTES
     This function is supposed to be called as a callback parameter in calls
-    of the transform method.  
+    of the compile method.  
 
   RETURN VALUES
     pointer to the replacing constant item, if the field item was substituted 

--- 1.206/sql/item.h	2006-09-07 11:06:45 -07:00
+++ 1.207/sql/item.h	2006-09-07 11:06:45 -07:00
@@ -410,7 +410,8 @@
 };
 
 
-typedef bool (Item::*Item_processor)(byte *arg);
+typedef bool (Item::*Item_processor) (byte *arg);
+typedef bool (Item::*Item_analyzer) (byte **argp);
 typedef Item* (Item::*Item_transformer) (byte *arg);
 typedef void (*Cond_traverser) (const Item *item, void *arg);
 
@@ -739,6 +740,14 @@
     return (this->*transformer)(arg);
   }
 
+  virtual Item* compile(Item_analyzer analyzer, byte **arg_p,
+                        Item_transformer transformer, byte *arg_t)
+  {
+    if ((this->*analyzer) (arg_p))
+      return ((this->*transformer) (arg_t));
+    return 0;
+  }
+
    virtual void traverse_cond(Cond_traverser traverser,
                               void *arg, traverse_order order)
    {
@@ -753,6 +762,12 @@
   virtual bool change_context_processor(byte *context) { return 0; }
   virtual bool reset_query_id_processor(byte *query_id) { return 0; }
   virtual bool is_expensive_processor(byte *arg) { return 0; }
+  virtual bool subst_argument_checker(byte **arg)
+  { 
+    if (*arg)
+      *arg= NULL; 
+    return TRUE;     
+  }
 
   virtual Item *equal_fields_propagator(byte * arg) { return this; }
   virtual Item *set_no_const_sub(byte *arg) { return this; }
@@ -1254,6 +1269,7 @@
     return field->can_be_compared_as_longlong();
   }
   Item_equal *find_item_equal(COND_EQUAL *cond_equal);
+  bool subst_argument_checker(byte **arg);
   Item *equal_fields_propagator(byte *arg);
   Item *set_no_const_sub(byte *arg);
   Item *replace_equal_field(byte *arg);

--- 1.216/sql/item_cmpfunc.cc	2006-09-07 11:06:45 -07:00
+++ 1.217/sql/item_cmpfunc.cc	2006-09-07 11:06:45 -07:00
@@ -2747,16 +2747,16 @@
    
   SYNOPSIS
     transform()
-    transformer   the transformer callback function to be applied to the nodes
-                  of the tree of the object
-    arg           parameter to be passed to the transformer
+      transformer   the transformer callback function to be applied to the nodes
+                    of the tree of the object
+      arg           parameter to be passed to the transformer
   
   DESCRIPTION
-    The function recursively applies the transform method with the
-    same transformer to each member item of the condition list.
+    The function recursively applies the transform method to each
+     member item of the condition list.
     If the call of the method for a member item returns a new item
     the old item is substituted for a new one.
-    After this the transform method is applied to the root node
+    After this the transformer is applied to the root node
     of the Item_cond object. 
      
   RETURN VALUES
@@ -2776,6 +2776,55 @@
       li.replace(new_item);
   }
   return Item_func::transform(transformer, arg);
+}
+
+
+/*
+  Compile Item_cond object with a processor and a transformer callback functions
+   
+  SYNOPSIS
+    compile()
+      analyzer      the analyzer callback function to be applied to the nodes
+                    of the tree of the object
+      arg_p         in/out parameter to be passed to the analyzer
+      transformer   the transformer callback function to be applied to the nodes
+                    of the tree of the object
+      arg_t         parameter to be passed to the transformer
+  
+  DESCRIPTION
+    First the function applies the analyzer to the root node of
+    the Item_func object. Then if the analyzer succeeeds (returns TRUE)
+    the function recursively applies the compile method to member
+    item of the condition list.
+    If the call of the method for a member item returns a new item
+    the old item is substituted for a new one.
+    After this the transformer is applied to the root node
+    of the Item_cond object. 
+     
+  RETURN VALUES
+    Item returned as the result of transformation of the root node 
+*/
+
+Item *Item_cond::compile(Item_analyzer analyzer, byte **arg_p,
+                         Item_transformer transformer, byte *arg_t)
+{
+  if (!(this->*analyzer)(arg_p))
+    return 0;
+  
+  byte *arg_v= *arg_p;
+  List_iterator<Item> li(list);
+  Item *item;
+  while ((item= li++))
+  {
+    /* 
+      The same parameter value of arg_p must be passed
+      to analyze any argument of the condition formula.
+    */   
+    Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t);
+    if (new_item && new_item != item)
+      li.replace(new_item);
+  }
+  return Item_func::transform(transformer, arg_t);
 }
 
 void Item_cond::traverse_cond(Cond_traverser traverser,

--- 1.129/sql/item_cmpfunc.h	2006-09-07 11:06:45 -07:00
+++ 1.130/sql/item_cmpfunc.h	2006-09-07 11:06:45 -07:00
@@ -240,6 +240,7 @@
   }
   Item *neg_transformer(THD *thd);
   virtual Item *negated_item();
+  bool subst_argument_checker(byte **arg) { return TRUE; }
 };
 
 class Item_func_not :public Item_bool_func
@@ -1171,6 +1172,9 @@
   Item *transform(Item_transformer transformer, byte *arg);
   void traverse_cond(Cond_traverser, void *arg, traverse_order order);
   void neg_arguments(THD *thd);
+  bool subst_argument_checker(byte **arg) { return TRUE; }
+  Item *compile(Item_analyzer analyzer, byte **arg_p,
+                Item_transformer transformer, byte *arg_t);
 };
 
 

--- 1.299/sql/item_func.cc	2006-09-07 11:06:45 -07:00
+++ 1.300/sql/item_func.cc	2006-09-07 11:06:45 -07:00
@@ -234,22 +234,21 @@
 }
 
 
-
 /*
   Transform an Item_func object with a transformer callback function
    
   SYNOPSIS
     transform()
-    transformer   the transformer callback function to be applied to the nodes
-                  of the tree of the object
-    argument      parameter to be passed to the transformer
+      transformer   the transformer callback function to be applied to the nodes
+                    of the tree of the object
+      argument      parameter to be passed to the transformer
   
   DESCRIPTION
-    The function recursively applies the transform method with the
-    same transformer to each argument the function.
-    If the call of the method for a member item returns a new item
+    The function recursively applies the transform method to each
+    argument of the Item_func node.
+    If the call of the method for an argument item returns a new item
     the old item is substituted for a new one.
-    After this the transform method is applied to the root node
+    After this the transformer is applied to the root node
     of the Item_func object. 
      
   RETURN VALUES
@@ -273,6 +272,55 @@
   return (this->*transformer)(argument);
 }
 
+
+/*
+  Compile Item_func object with a processor and a transformer callback functions
+   
+  SYNOPSIS
+    compile()
+      analyzer      the analyzer callback function to be applied to the nodes
+                    of the tree of the object
+      arg_p         in/out parameter to be passed to the processor
+      transformer   the transformer callback function to be applied to the nodes
+                    of the tree of the object
+      arg_t         parameter to be passed to the transformer
+  
+  DESCRIPTION
+    First the function applies the analyzer to the root node of
+    the Item_func object. Then if the analizer succeeeds (returns TRUE)
+    the function recursively applies the compile method to each argument
+    of the Item_func node.
+    If the call of the method for an argument item returns a new item
+    the old item is substituted for a new one.
+    After this the transformer is applied to the root node
+    of the Item_func object. 
+     
+  RETURN VALUES
+    Item returned as the result of transformation of the root node 
+*/
+
+Item *Item_func::compile(Item_analyzer analyzer, byte **arg_p,
+                         Item_transformer transformer, byte *arg_t)
+{
+  if (!(this->*analyzer)(arg_p))
+    return 0;
+  byte *arg_v= *arg_p;
+  if (arg_count)
+  {
+    Item **arg,**arg_end;
+    for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
+    {
+      /* 
+        The same parameter value of arg_p must be passed
+        to analyze any argument of the condition formula.
+      */   
+      Item *new_item= (*arg)->compile(analyzer, &arg_v, transformer, arg_t);
+      if (new_item && *arg != new_item)
+        current_thd->change_item_tree(arg, new_item);
+    }
+  }
+  return (this->*transformer)(arg_t);
+}
 
 /* See comments in Item_cmp_func::split_sum_func() */
 

--- 1.148/sql/item_func.h	2006-09-07 11:06:45 -07:00
+++ 1.149/sql/item_func.h	2006-09-07 11:06:45 -07:00
@@ -187,6 +187,8 @@
   }
   bool walk(Item_processor processor, byte *arg);
   Item *transform(Item_transformer transformer, byte *arg);
+  Item* compile(Item_analyzer analyzer, byte **arg_p,
+                Item_transformer transformer, byte *arg_t);
   void traverse_cond(Cond_traverser traverser,
                      void * arg, traverse_order order);
   bool is_expensive_processor(byte *arg);

--- 1.447/sql/sql_select.cc	2006-09-07 11:06:45 -07:00
+++ 1.448/sql/sql_select.cc	2006-09-07 11:06:46 -07:00
@@ -6593,8 +6593,8 @@
 
   SYNOPSIS
     build_equal_items_for_cond()
-    cond       condition(expression) where to make replacement
-    inherited  path to all inherited multiple equality items
+      cond       condition(expression) where to make replacement
+      inherited  path to all inherited multiple equality items
 
   DESCRIPTION
     At each 'and' level the function detects items for equality predicates
@@ -6608,7 +6608,9 @@
     The function also traverses the cond tree and and for each field reference
     sets a pointer to the multiple equality item containing the field, if there
     is any. If this multiple equality equates fields to a constant the
-    function replace the field reference by the constant.
+    function replaces the field reference by the constant in the cases 
+    when the field is not of a string type or when the field reference is
+    just an argument of a comparison predicate.
     The function also determines the maximum number of members in 
     equality lists of each Item_cond_and object assigning it to
     cond_equal->max_members of this object and updating accordingly
@@ -6756,9 +6758,14 @@
     /* 
       For each field reference in cond, not from equal item predicates,
       set a pointer to the multiple equality it belongs to (if there is any)
+      as soon the field is not of a string type or the field reference is
+      an argument of a comparison predicate. 
     */ 
-    cond= cond->transform(&Item::equal_fields_propagator,
-                            (byte *) inherited);
+    byte *dummy;
+    cond= cond->compile(&Item::subst_argument_checker,
+                        &dummy, 
+                        &Item::equal_fields_propagator,
+                        (byte *) inherited);
     cond->update_used_tables();
   }
   return cond;

--- 1.19/mysql-test/r/heap_hash.result	2006-09-07 11:06:46 -07:00
+++ 1.20/mysql-test/r/heap_hash.result	2006-09-07 11:06:46 -07:00
@@ -354,7 +354,7 @@
 explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a =
concat('',t1.name) and t3.b=t1.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	heap_idx	heap_idx	22	const	7	Using where
-1	SIMPLE	t3	ref	a	a	44	const,const	7	Using where
+1	SIMPLE	t3	ref	a	a	44	func,const	7	Using where
 drop table t1, t2, t3;
 create temporary table t1 ( a int, index (a) ) engine=memory;
 insert into t1 values (1),(2),(3),(4),(5);

--- 1.118/mysql-test/r/func_str.result	2006-09-07 11:06:46 -07:00
+++ 1.119/mysql-test/r/func_str.result	2006-09-07 11:06:46 -07:00
@@ -1113,4 +1113,39 @@
 select "18383815659218730760" + 0;
 "18383815659218730760" + 0
 1.8383815659219e+19
+CREATE TABLE t1 (code varchar(10));
+INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
+SELECT ASCII(code), code FROM t1 WHERE code='A12';
+ASCII(code)	code
+97	a12
+65	A12
+SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
+ASCII(code)	code
+65	A12
+INSERT INTO t1 VALUES ('a12 '), ('A12  ');
+SELECT LENGTH(code), code FROM t1 WHERE code='A12';
+LENGTH(code)	code
+3	a12
+3	A12
+4	a12 
+5	A12  
+SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
+LENGTH(code)	code
+5	A12  
+ALTER TABLE t1 ADD INDEX (code);
+CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
+INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
+SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id 
+WHERE t2.id='a12' AND (code < 'a00' OR LENGTH(code)=5);
+code	id
+A12  	a12
+EXPLAIN EXTENDED 
+SELECT * FROM t1 INNER JOIN t2 ON code=id 
+WHERE id='a12' AND (code < 'a00' OR LENGTH(code)=5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	code	code	13	const	3	Using where; Using index
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	12	const	1	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1`
join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` =
_latin1'a12') and (length(`test`.`t1`.`code`) = 5))
+DROP TABLE t1,t2;
 End of 5.0 tests

--- 1.92/mysql-test/t/func_str.test	2006-09-07 11:06:46 -07:00
+++ 1.93/mysql-test/t/func_str.test	2006-09-07 11:06:46 -07:00
@@ -753,4 +753,31 @@
 select conv("18383815659218730760",10,10) + 0;
 select "18383815659218730760" + 0;
 
+#
+# Bug #21698: substitution of a string field for a constant under a function 
+#
+
+CREATE TABLE t1 (code varchar(10));
+INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
+
+SELECT ASCII(code), code FROM t1 WHERE code='A12';
+SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
+
+INSERT INTO t1 VALUES ('a12 '), ('A12  ');
+
+SELECT LENGTH(code), code FROM t1 WHERE code='A12';
+SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
+
+ALTER TABLE t1 ADD INDEX (code);
+CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
+INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
+
+SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id 
+  WHERE t2.id='a12' AND (code < 'a00' OR LENGTH(code)=5);
+EXPLAIN EXTENDED 
+SELECT * FROM t1 INNER JOIN t2 ON code=id 
+  WHERE id='a12' AND (code < 'a00' OR LENGTH(code)=5);
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests