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