|
|
Test case
|
CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(1)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('bar','b'),('aaa','a');
|
|
CREATE TABLE t2 (c VARCHAR(3)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES ('bar'),('ccc');
|
|
CREATE TABLE t3 (d VARCHAR(2), e INT) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES ('fo',1),('ba',2);
|
|
CREATE TABLE t4 (f INT) ENGINE=MyISAM;
|
INSERT INTO t4 VALUES (1),(2);
|
|
CREATE TABLE t5 (
|
f_key VARCHAR(3) CHARACTER SET utf8,
|
f1 INT,
|
f2 VARCHAR(3),
|
f3 VARCHAR(3),
|
f4 INT,
|
f5 VARCHAR(1024),
|
f6 VARCHAR(3),
|
f7 VARCHAR(1024),
|
f8 VARCHAR(1024),
|
key (f_key)
|
) ENGINE=MyISAM;
|
|
INSERT INTO t5 VALUES
|
('foo',1,'foo','foo',1,'foo','foo',REPEAT('foo',60),'foo'),
|
('bar',2,'bar','bar',2,'bar','bar',REPEAT('bar',240),'bar'),
|
('baz',3,'baz','baz',3,'baz','baz','baz',REPEAT('baz',250)),
|
('qux',4,'qux','qux',4,REPEAT('qux',220),'qux','qux','qux');
|
|
set optimizer_switch = 'mrr=on';
|
set join_cache_level = 6;
|
|
SELECT t5.f_key, t5.f1
|
FROM ( ( ( t1 LEFT JOIN t2 ON t2.c = t1.a ) LEFT JOIN t3 ON t1.b = t3.d ) LEFT JOIN t4 ON t3.e = t4.f ) LEFT JOIN t5 ON t5.f_key = t2.c;
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
Actual result
|
f_key f1
|
NULL NULL
|
NULL NULL
|
|
Expected result
|
f_key f1
|
bar 2
|
NULL NULL
|
|
EXPLAIN
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t2 hash_ALL NULL #hash#$hj 7 test.t1.a 2 100.00 Using where; Using join buffer (flat, BNLH join)
|
1 SIMPLE t3 hash_ALL NULL #hash#$hj 6 test.t1.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)
|
1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t3.e 2 100.00 Using where; Using join buffer (incremental, BNLH join)
|
1 SIMPLE t5 ref f_key f_key 12 func 2 100.00 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan
|
Warnings:
|
Note 1003 select `test`.`t5`.`f_key` AS `f_key`,`test`.`t5`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` is not null))) left join `test`.`t3` on(((`test`.`t1`.`b` = `test`.`t3`.`d`) and (`test`.`t1`.`b` is not null))) left join `test`.`t4` on(((`test`.`t4`.`f` = `test`.`t3`.`e`) and (`test`.`t3`.`e` is not null))) left join `test`.`t5` on((`test`.`t5`.`f_key` = convert(`test`.`t2`.`c` using utf8))) where 1
|
|