1let $engine_type= myisam;
2
3--source include/partition_mrr.inc
4
5--echo #
6--echo # MDEV-21628: Index condition pushdown for a simple condition over
7--echo # index fields is not used for ref access of partitioned tables when employing BKA
8--echo #
9
10create table t0 (
11tp int, a int, b int, c varchar(12), index idx (a,b)
12);
13
14insert into t0 values
15(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
16(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
17(1,3,30,'yzzy'), (1,93,30,'zzzy'),
18(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
19(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
20(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
21(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
22(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
23(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
24(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
25(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
26(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
27(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
28(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
29(3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
30(3,4,30,'zzzyy'), (3,94,30,'yyz');
31
32create table t1 (
33tp int, a int, b int, c varchar(12), index idx (a,b)
34) engine=myisam
35partition by list (tp)
36( partition p1 values in (1),
37  partition p2 values in (2),
38  partition p3 values in (3));
39insert into t1 select * from t0;
40
41create table t2 (a int, index idx(a)) engine=myisam;
42insert into t2 values (1), (2), (3), (4), (5);
43insert into t2 select a+10 from t2;
44insert into t2 select a+20 from t2;
45
46analyze table t0,t1,t2;
47
48set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch;
49set join_cache_level=6, optimizer_switch='mrr=on';
50
51explain
52select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
53
54--echo # This will use "Using index condition(BKA)"
55explain
56select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
57
58set join_cache_level=@tmp1, optimizer_switch=@tmp2;
59
60drop table t0,t1,t2;
61
62
63