diff options
| -rw-r--r-- | mysql-test/r/select.result | 29 | ||||
| -rw-r--r-- | mysql-test/t/select.test | 26 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 |
3 files changed, 56 insertions, 0 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 350a05a13c8..9c35e1963e6 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3523,3 +3523,32 @@ select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 from t1 where c9=1 order by c2, c2; match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 drop table t1; +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; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 0686f670edf..166b709f9b6 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3007,3 +3007,29 @@ c7 int, c8 int, c9 int, fulltext key (`c1`)); select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 from t1 where c9=1 order by c2, c2; drop table t1; +# 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; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f2c43833bd9..c8439cba303 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -446,6 +446,7 @@ public: negated= !negated; return this; } + bool subst_argument_checker(byte **arg) { return TRUE; } }; |