diff options
author | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-02-16 13:56:06 +0200 |
---|---|---|
committer | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-02-16 13:56:06 +0200 |
commit | 2d4c92abc6023b36950d93f3d2500db484489167 (patch) | |
tree | 1c4284dd60d2c980e91bf41fc6d8eaff2974d489 /mysql-test/r/func_in.result | |
parent | 2b416a11cdaec1ad96345b5b630cb8b41b27500e (diff) | |
download | mariadb-git-2d4c92abc6023b36950d93f3d2500db484489167.tar.gz |
BUG#20420: optimizer reports wrong keys on left join with IN
When checking if an IN predicate can be evaluated using a key
the optimizer makes sure that all the arguments of IN are of
the same result type. To assure that it check whether
Item_func_in::array is filled in.
However Item_func_in::array is set if the types are
the same AND all the arguments are compile time constants.
Fixed by introducing Item_func_in::arg_types_compatible
flag to allow correct checking of the desired condition.
mysql-test/r/func_in.result:
BUG#20420: optimizer reports wrong keys on left join with IN
- reenabled the test case
mysql-test/t/func_in.test:
BUG#20420: optimizer reports wrong keys on left join with IN
- reenabled the test case
sql/item_cmpfunc.cc:
BUG#20420: optimizer reports wrong keys on left join with IN
Check the IN argument types in a correct way
sql/item_cmpfunc.h:
BUG#20420: optimizer reports wrong keys on left join with IN
Check the IN argument types in a correct way
sql/opt_range.cc:
BUG#20420: optimizer reports wrong keys on left join with IN
Check the IN argument types in a correct way
Diffstat (limited to 'mysql-test/r/func_in.result')
-rw-r--r-- | mysql-test/r/func_in.result | 44 |
1 files changed, 44 insertions, 0 deletions
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 9b09cc0a43c..cf3aefa44e6 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -351,6 +351,50 @@ some_id 1 2 drop table t1; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); +CREATE TABLE t3 (a int PRIMARY KEY); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a int PRIMARY KEY,b int); +INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002), +(1003,1003),(1004,1004); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1) +SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +a a b a b a b +3 3 1 3 2 1 1 +3 3 1 3 2 2 2 +4 4 1 4 2 1 1 +4 4 1 4 2 2 2 +EXPLAIN SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index +SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +3 +3 +DROP TABLE t1,t2,t3,t4; End of 5.0 tests create table t1(f1 char(1)); insert into t1 values ('a'),('b'),('1'); |