1drop table if exists t1,t3; 2# 3# MDEV-20611: MRR scan over partitioned InnoDB table produces "Out of memory" error 4# 5create table t1(a int); 6insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 7set @tmp=@@storage_engine; 8set storage_engine=InnoDB; 9create table t3 ( 10ID bigint(20) NOT NULL AUTO_INCREMENT, 11part_id int, 12key_col int, 13col2 int, 14key(key_col), 15PRIMARY KEY (ID,part_id) 16) PARTITION BY RANGE (part_id) 17(PARTITION p1 VALUES LESS THAN (3), 18PARTITION p2 VALUES LESS THAN (7), 19PARTITION p3 VALUES LESS THAN (10) 20); 21show create table t3; 22Table Create Table 23t3 CREATE TABLE `t3` ( 24 `ID` bigint(20) NOT NULL AUTO_INCREMENT, 25 `part_id` int(11) NOT NULL, 26 `key_col` int(11) DEFAULT NULL, 27 `col2` int(11) DEFAULT NULL, 28 PRIMARY KEY (`ID`,`part_id`), 29 KEY `key_col` (`key_col`) 30) ENGINE=InnoDB DEFAULT CHARSET=latin1 31 PARTITION BY RANGE (`part_id`) 32(PARTITION `p1` VALUES LESS THAN (3) ENGINE = InnoDB, 33 PARTITION `p2` VALUES LESS THAN (7) ENGINE = InnoDB, 34 PARTITION `p3` VALUES LESS THAN (10) ENGINE = InnoDB) 35set storage_engine= @tmp; 36insert into t3 select 37A.a+10*B.a, 38A.a, 39B.a, 40123456 41from t1 A, t1 B; 42set @save_optimizer_switch=@@optimizer_switch; 43set optimizer_switch='mrr=on'; 44explain 45select * from t3 force index (key_col) where key_col < 3; 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t3 range key_col key_col 5 NULL # Using where; Rowid-ordered scan 48select * from t3 force index (key_col) where key_col < 3; 49ID part_id key_col col2 501 0 0 123456 511 1 0 123456 522 2 0 123456 5310 0 1 123456 5411 1 1 123456 5512 2 1 123456 5620 0 2 123456 5721 1 2 123456 5822 2 2 123456 593 3 0 123456 604 4 0 123456 615 5 0 123456 626 6 0 123456 6313 3 1 123456 6414 4 1 123456 6515 5 1 123456 6616 6 1 123456 6723 3 2 123456 6824 4 2 123456 6925 5 2 123456 7026 6 2 123456 717 7 0 123456 728 8 0 123456 739 9 0 123456 7417 7 1 123456 7518 8 1 123456 7619 9 1 123456 7727 7 2 123456 7828 8 2 123456 7929 9 2 123456 80set optimizer_switch=@save_optimizer_switch; 81drop table t1,t3; 82# 83# MDEV-21544: partitioned table is joined with BKA+MRR 84# 85set @save_join_cache_level=@@join_cache_level; 86set @save_optimizer_switch=@@optimizer_switch; 87create table t0 ( 88tp int, a int, b int not null, c varchar(12), index idx (a,b) 89); 90insert into t0 values 91(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), 92(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), 93(1,3,30,'yzzy'), (1,93,30,'zzzy'), 94(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), 95(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), 96(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), 97(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), 98(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), 99(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), 100(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), 101(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), 102(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), 103(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), 104(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), 105(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), 106(3,4,30,'zzzyy'), (3,94,30,'yyz'); 107create table t1 ( 108tp int, a int, b int not null, c varchar(12), index idx (a,b) 109) 110partition by list (tp) 111( partition p1 values in (1), 112partition p2 values in (2), 113partition p3 values in (3)); 114insert into t1 select * from t0; 115create table t2 (a int, index idx(a)); 116insert into t2 values 117(1), (2), (3), (4), (5); 118insert into t2 select a+10 from t2; 119insert into t2 select a+20 from t2; 120analyze table t0,t1,t2; 121Table Op Msg_type Msg_text 122test.t0 analyze status OK 123test.t1 analyze status OK 124test.t2 analyze status OK 125set join_cache_level=6; 126set optimizer_switch='mrr=on'; 127explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; 128id select_type table type possible_keys key key_len ref rows filtered Extra 1291 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index 1301 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan 131Warnings: 132Note 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 133select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; 134tp a b c a 1353 4 30 yx 4 1363 4 30 yyxxx 4 1373 4 30 zzyy 4 1383 4 30 zxyy 4 1393 4 30 xxyy 4 1403 4 30 yyzx 4 1413 4 30 zyyy 4 1423 4 30 yzy 4 1433 4 30 zzzyy 4 144explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; 145id select_type table type possible_keys key key_len ref rows filtered Extra 1461 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index 1471 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan 148Warnings: 149Note 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 150select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; 151tp a b c a 1523 4 30 yx 4 1533 4 30 yyxxx 4 1543 4 30 zzyy 4 1553 4 30 zxyy 4 1563 4 30 xxyy 4 1573 4 30 yyzx 4 1583 4 30 zyyy 4 1593 4 30 yzy 4 1603 4 30 zzzyy 4 161explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; 162id select_type table type possible_keys key key_len ref rows filtered Extra 1631 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index 1641 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan 165Warnings: 166Note 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 167select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; 168tp a b c a 1692 3 40 yxx 3 1702 4 40 xx 4 1712 3 40 yyx 3 1722 4 40 xxx 4 1732 3 40 xzzzz 3 1742 4 40 yyyxx 4 1752 3 40 xxx 3 1762 4 40 xx 4 1772 3 40 yyxzx 3 1782 4 40 xyx 4 1792 3 40 xxxzz 3 1802 4 40 xxz 4 1812 3 40 zzxxx 3 1822 4 40 zxx 4 1832 3 40 xyyxx 3 1842 4 40 xzzzx 4 1852 3 40 yzxxx 3 1862 4 40 xxzy 4 187explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; 188id select_type table type possible_keys key key_len ref rows filtered Extra 1891 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index 1901 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan 191Warnings: 192Note 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 193select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; 194tp a b c a 1952 3 40 yxx 3 1962 4 40 xx 4 1972 3 40 yyx 3 1982 4 40 xxx 4 1992 3 40 xzzzz 3 2002 4 40 yyyxx 4 2012 3 40 xxx 3 2022 4 40 xx 4 2032 3 40 yyxzx 3 2042 4 40 xyx 4 2052 3 40 xxxzz 3 2062 4 40 xxz 4 2072 3 40 zzxxx 3 2082 4 40 zxx 4 2092 3 40 xyyxx 3 2102 4 40 xzzzx 4 2112 3 40 yzxxx 3 2122 4 40 xxzy 4 213insert into t2 values 214(3), (4), (5); 215analyze table t2; 216Table Op Msg_type Msg_text 217test.t2 analyze status OK 218explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; 219id select_type table type possible_keys key key_len ref rows filtered Extra 2201 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index 2211 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan 222Warnings: 223Note 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 224select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; 225a tp a b c 226explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; 227id select_type table type possible_keys key key_len ref rows filtered Extra 2281 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index 2291 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan 230Warnings: 231Note 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 232select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; 233a tp a b c 234set join_cache_level=@save_join_cache_level; 235set optimizer_switch=@save_optimizer_switch; 236drop table t0,t1,t2; 237