1--source include/have_partition.inc
2
3--disable_warnings
4drop table if exists t1,t3;
5--enable_warnings
6
7--echo #
8--echo # MDEV-20611: MRR scan over partitioned InnoDB table produces "Out of memory" error
9--echo #
10create table t1(a int);
11insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
12
13set @tmp=@@storage_engine;
14eval set storage_engine=$engine_type;
15
16create table t3 (
17  ID bigint(20) NOT NULL AUTO_INCREMENT,
18  part_id int,
19  key_col int,
20  col2 int,
21  key(key_col),
22  PRIMARY KEY (ID,part_id)
23) PARTITION BY RANGE (part_id)
24(PARTITION p1 VALUES LESS THAN (3),
25 PARTITION p2 VALUES LESS THAN (7),
26 PARTITION p3 VALUES LESS THAN (10)
27);
28
29show create table t3;
30set storage_engine= @tmp;
31
32insert into t3 select
33  A.a+10*B.a,
34  A.a,
35  B.a,
36  123456
37from t1 A, t1 B;
38
39set @save_optimizer_switch=@@optimizer_switch;
40
41set optimizer_switch='mrr=on';
42--replace_column 9 #
43explain
44select * from t3 force index (key_col) where key_col < 3;
45select * from t3 force index (key_col) where key_col < 3;
46
47set optimizer_switch=@save_optimizer_switch;
48
49drop table t1,t3;
50
51--echo #
52--echo # MDEV-21544: partitioned table is joined with BKA+MRR
53--echo #
54
55set @save_join_cache_level=@@join_cache_level;
56set @save_optimizer_switch=@@optimizer_switch;
57
58create table t0 (
59  tp int, a int, b int not null, c varchar(12), index idx (a,b)
60);
61insert into t0 values
62(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
63(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
64(1,3,30,'yzzy'), (1,93,30,'zzzy'),
65(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
66(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
67(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
68(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
69(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
70(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
71(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
72(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
73(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
74(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
75(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
76(3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
77(3,4,30,'zzzyy'), (3,94,30,'yyz');
78
79create table t1 (
80  tp int, a int, b int not null, c varchar(12), index idx (a,b)
81)
82partition by list (tp)
83( partition p1 values in (1),
84  partition p2 values in (2),
85  partition p3 values in (3));
86insert into t1 select * from t0;
87
88# tables t0 and t1 contain the same set of records.
89
90create table t2 (a int, index idx(a));
91insert into t2 values
92(1), (2), (3), (4), (5);
93insert into t2 select a+10 from t2;
94insert into t2 select a+20 from t2;
95
96analyze table t0,t1,t2;
97
98set join_cache_level=6;
99set optimizer_switch='mrr=on';
100
101let $q1=
102select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
103eval explain extended $q1;
104eval $q1;
105
106let $q2=
107select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
108eval explain extended $q2;
109eval $q2;
110
111let $q1=
112select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
113eval explain extended $q1;
114eval $q1;
115
116let $q2=
117select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
118eval explain extended $q2;
119eval $q2;
120
121insert into t2 values
122(3), (4), (5);
123analyze table t2;
124
125let $q1=
126select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
127eval explain extended $q1;
128eval $q1;
129
130let $q2=
131select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
132eval explain extended $q2;
133eval $q2;
134
135set join_cache_level=@save_join_cache_level;
136set optimizer_switch=@save_optimizer_switch;
137
138drop table t0,t1,t2;
139