diff options
Diffstat (limited to 'mysql-test/main/partition_mrr_innodb.result')
| -rw-r--r-- | mysql-test/main/partition_mrr_innodb.result | 161 |
1 files changed, 161 insertions, 0 deletions
diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index c188f7e9929..2efb10de694 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,164 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status Engine-independent statistics collected +test.t0 analyze status OK +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; |