From: Date: September 4 2006 2:42pm Subject: bk commit into 5.0 tree (igor:1.2254) BUG#21698 List-Archive: http://lists.mysql.com/commits/11352 X-Bug: 21698 Message-Id: <20060904124256.B387D4AB75@rurik.mysql.com> 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-04 05:42:47-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-04 05:42:35-07:00, igor@stripped +20 -0 Added test cases for bug #21698. mysql-test/r/heap_hash.result@stripped, 2006-09-04 05:42:35-07:00, igor@stripped +1 -1 Adjusted results after the fix for bug #21198. mysql-test/t/func_str.test@stripped, 2006-09-04 05:42:36-07:00, igor@stripped +17 -0 Added test cases for bug #21698. sql/item.cc@stripped, 2006-09-04 05:42:36-07:00, igor@stripped +39 -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-04 05:42:36-07:00, igor@stripped +14 -0 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 a processor method. This method is supposed to set its in/out argument to FALSE for the nodes where substitution of a string field for a constant is not valid. sql/item_cmpfunc.cc@stripped, 2006-09-04 05:42:37-07:00, igor@stripped +49 -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-04 05:42:37-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 FALSE 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-04 05:42:37-07:00, igor@stripped +51 -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-04 05:42:37-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-04 05:42:37-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-04 05:42:56 -07:00 +++ 1.233/sql/item.cc 2006-09-04 05:42:56 -07:00 @@ -3746,13 +3746,49 @@ /* + Check whether a field can be substituted by an equal item + + SYNOPSIS + equal_fields_propagator() + arg - *arg == TRUE is 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) +{ + bool is_subst_argument= *((bool *) arg); + return result_type() != STRING_RESULT || is_subst_argument; +} + + +/* 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 +3800,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-04 05:42:56 -07:00 +++ 1.207/sql/item.h 2006-09-04 05:42:56 -07:00 @@ -739,6 +739,14 @@ return (this->*transformer)(arg); } + virtual Item* compile(Item_processor processor, byte *arg_p, + Item_transformer transformer, byte *arg_t) + { + if ((this->*processor) (arg_p)) + return ((this->*transformer) (arg_t)); + return 0; + } + virtual void traverse_cond(Cond_traverser traverser, void *arg, traverse_order order) { @@ -753,6 +761,11 @@ 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) + { + *((bool *) arg)= 0; + return TRUE; + } virtual Item *equal_fields_propagator(byte * arg) { return this; } virtual Item *set_no_const_sub(byte *arg) { return this; } @@ -1254,6 +1267,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-04 05:42:56 -07:00 +++ 1.217/sql/item_cmpfunc.cc 2006-09-04 05:42:56 -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,49 @@ li.replace(new_item); } return Item_func::transform(transformer, arg); +} + + +/* + Compile Item_cond object with a processor and a transformer callback functions + + SYNOPSIS + compile() + processor the processor callback function to be applied to the nodes + of the tree of the object + arg_p 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 proccesor to the root node of + the Item_func object. Then if the processor 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_processor processor, byte *arg_p, + Item_transformer transformer, byte *arg_t) +{ + if (!(this->*processor)(arg_p)) + return 0; + List_iterator li(list); + Item *item; + while ((item= li++)) + { + Item *new_item= item->compile(processor, arg_p, 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-04 05:42:56 -07:00 +++ 1.130/sql/item_cmpfunc.h 2006-09-04 05:42:56 -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_processor processor, byte *arg_p, + Item_transformer transformer, byte *arg_t); }; --- 1.299/sql/item_func.cc 2006-09-04 05:42:56 -07:00 +++ 1.300/sql/item_func.cc 2006-09-04 05:42:56 -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,50 @@ return (this->*transformer)(argument); } + +/* + Compile Item_func object with a processor and a transformer callback functions + + SYNOPSIS + compile() + processor the processor callback function to be applied to the nodes + of the tree of the object + arg_p 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 proccesor to the root node of + the Item_func object. Then if the processor 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_processor processor, byte *arg_p, + Item_transformer transformer, byte *arg_t) +{ + if (!(this->*processor)(arg_p)) + return 0; + if (arg_count) + { + Item **arg,**arg_end; + for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++) + { + Item *new_item= (*arg)->compile(processor, arg_p, 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-04 05:42:56 -07:00 +++ 1.149/sql/item_func.h 2006-09-04 05:42:56 -07:00 @@ -187,6 +187,8 @@ } bool walk(Item_processor processor, byte *arg); Item *transform(Item_transformer transformer, byte *arg); + Item* compile(Item_processor processor, 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-04 05:42:56 -07:00 +++ 1.448/sql/sql_select.cc 2006-09-04 05:42:56 -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); + bool is_subst_argument= TRUE; + cond= cond->compile(&Item::subst_argument_checker, + (byte *) &is_subst_argument, + &Item::equal_fields_propagator, + (byte *) inherited); cond->update_used_tables(); } return cond; --- 1.19/mysql-test/r/heap_hash.result 2006-09-04 05:42:56 -07:00 +++ 1.20/mysql-test/r/heap_hash.result 2006-09-04 05:42:56 -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-04 05:42:56 -07:00 +++ 1.119/mysql-test/r/func_str.result 2006-09-04 05:42:56 -07:00 @@ -1113,4 +1113,24 @@ 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 +DROP TABLE t1; End of 5.0 tests --- 1.92/mysql-test/t/func_str.test 2006-09-04 05:42:56 -07:00 +++ 1.93/mysql-test/t/func_str.test 2006-09-04 05:42:56 -07:00 @@ -753,4 +753,21 @@ 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; + +DROP TABLE t1; + --echo End of 5.0 tests