From: Date: September 29 2006 4:43pm Subject: bk commit into 5.0 tree (igor:1.2280) BUG#22753 List-Archive: http://lists.mysql.com/commits/12849 X-Bug: 22753 Message-Id: <20060929144332.82ECEBA6D9@igor.local> 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-29 07:43:25-07:00, igor@stripped +3 -0 Fixed bug #22753. After the patch for big 21698 equality propagation stopped working for BETWEEN and IN predicates with STRING arguments. This changeset completes the solution of the above patch. mysql-test/r/select.result@stripped, 2006-09-29 07:43:18-07:00, igor@stripped +29 -0 Added a test case for bug #22735. mysql-test/t/select.test@stripped, 2006-09-29 07:43:18-07:00, igor@stripped +28 -1 Added a test case for bug #22735. sql/item_cmpfunc.h@stripped, 2006-09-29 07:43:18-07:00, igor@stripped +1 -0 Fixed bug #22753. After the patch for big 21698 equality propagation stopped working for BETWEEN and IN predicates with STRING arguments. This changeset completes the solution of the above patch. Added an implementation of the subst_argument_checker method for Item_func_opt_neg (the direct ancestor of Item_func_between and Item_func_in) which allows equality propagation for BETWEEN and IN predicates. # 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-bug22753 --- 1.131/sql/item_cmpfunc.h 2006-09-29 07:43:32 -07:00 +++ 1.132/sql/item_cmpfunc.h 2006-09-29 07:43:32 -07:00 @@ -446,6 +446,7 @@ negated= !negated; return this; } + bool subst_argument_checker(byte **arg) { return TRUE; } }; --- 1.138/mysql-test/r/select.result 2006-09-29 07:43:32 -07:00 +++ 1.139/mysql-test/r/select.result 2006-09-29 07:43:32 -07:00 @@ -3517,3 +3517,32 @@ 2 NULL NULL NULL 2 40 2 NULL NULL NULL 2 50 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); +CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); +INSERT INTO t1 VALUES +('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), +('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); +INSERT INTO t2 VALUES +('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), +('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), +('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), +('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); +EXPLAIN SELECT t2.* +FROM t1 JOIN t2 ON t2.fk=t1.pk +WHERE t2.fk < 'c' AND t2.pk=t1.fk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where +1 SIMPLE t2 ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where +EXPLAIN SELECT t2.* +FROM t1 JOIN t2 ON t2.fk=t1.pk +WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t2 ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where +EXPLAIN SELECT t2.* +FROM t1 JOIN t2 ON t2.fk=t1.pk +WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t2 ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where +DROP TABLE t1,t2; --- 1.112/mysql-test/t/select.test 2006-09-29 07:43:32 -07:00 +++ 1.113/mysql-test/t/select.test 2006-09-29 07:43:32 -07:00 @@ -2996,5 +2996,32 @@ SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; - DROP TABLE t1,t2,t3; + +# +# Bug #22735: no equality propagation for BETWEEN and IN with STRING arguments +# + +CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); +CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); + +INSERT INTO t1 VALUES + ('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), + ('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); +INSERT INTO t2 VALUES + ('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), + ('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), + ('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), + ('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); + +EXPLAIN SELECT t2.* + FROM t1 JOIN t2 ON t2.fk=t1.pk + WHERE t2.fk < 'c' AND t2.pk=t1.fk; +EXPLAIN SELECT t2.* + FROM t1 JOIN t2 ON t2.fk=t1.pk + WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; +EXPLAIN SELECT t2.* + FROM t1 JOIN t2 ON t2.fk=t1.pk + WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; + +DROP TABLE t1,t2;