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=myisam; 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=MyISAM DEFAULT CHARSET=latin1 31 PARTITION BY RANGE (`part_id`) 32(PARTITION `p1` VALUES LESS THAN (3) ENGINE = MyISAM, 33 PARTITION `p2` VALUES LESS THAN (7) ENGINE = MyISAM, 34 PARTITION `p3` VALUES LESS THAN (10) ENGINE = MyISAM) 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 Engine-independent statistics collected 123test.t0 analyze status OK 124test.t1 analyze status Engine-independent statistics collected 125test.t1 analyze status OK 126test.t2 analyze status Engine-independent statistics collected 127test.t2 analyze status OK 128set join_cache_level=6; 129set optimizer_switch='mrr=on'; 130explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; 131id select_type table type possible_keys key key_len ref rows filtered Extra 1321 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1331 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 134Warnings: 135Note 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 136select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; 137tp a b c a 1383 4 30 yx 4 1393 4 30 yyxxx 4 1403 4 30 zzyy 4 1413 4 30 zxyy 4 1423 4 30 xxyy 4 1433 4 30 yyzx 4 1443 4 30 zyyy 4 1453 4 30 yzy 4 1463 4 30 zzzyy 4 147explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; 148id select_type table type possible_keys key key_len ref rows filtered Extra 1491 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1501 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 151Warnings: 152Note 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 153select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; 154tp a b c a 1553 4 30 yx 4 1563 4 30 yyxxx 4 1573 4 30 zzyy 4 1583 4 30 zxyy 4 1593 4 30 xxyy 4 1603 4 30 yyzx 4 1613 4 30 zyyy 4 1623 4 30 yzy 4 1633 4 30 zzzyy 4 164explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; 165id select_type table type possible_keys key key_len ref rows filtered Extra 1661 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1671 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan 168Warnings: 169Note 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 170select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; 171tp a b c a 1722 3 40 yxx 3 1732 4 40 xx 4 1742 3 40 yyx 3 1752 4 40 xxx 4 1762 3 40 xzzzz 3 1772 4 40 yyyxx 4 1782 3 40 xxx 3 1792 4 40 xx 4 1802 3 40 yyxzx 3 1812 4 40 xyx 4 1822 3 40 xxxzz 3 1832 4 40 xxz 4 1842 3 40 zzxxx 3 1852 4 40 zxx 4 1862 3 40 xyyxx 3 1872 4 40 xzzzx 4 1882 3 40 yzxxx 3 1892 4 40 xxzy 4 190explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; 191id select_type table type possible_keys key key_len ref rows filtered Extra 1921 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1931 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 194Warnings: 195Note 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 196select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; 197tp a b c a 1982 3 40 yxx 3 1992 4 40 xx 4 2002 3 40 yyx 3 2012 4 40 xxx 4 2022 3 40 xzzzz 3 2032 4 40 yyyxx 4 2042 3 40 xxx 3 2052 4 40 xx 4 2062 3 40 yyxzx 3 2072 4 40 xyx 4 2082 3 40 xxxzz 3 2092 4 40 xxz 4 2102 3 40 zzxxx 3 2112 4 40 zxx 4 2122 3 40 xyyxx 3 2132 4 40 xzzzx 4 2142 3 40 yzxxx 3 2152 4 40 xxzy 4 216insert into t2 values 217(3), (4), (5); 218analyze table t2; 219Table Op Msg_type Msg_text 220test.t2 analyze status Engine-independent statistics collected 221test.t2 analyze status OK 222explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; 223id select_type table type possible_keys key key_len ref rows filtered Extra 2241 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 2251 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 226Warnings: 227Note 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 228select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; 229a tp a b c 230explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; 231id select_type table type possible_keys key key_len ref rows filtered Extra 2321 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 2331 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 234Warnings: 235Note 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 236select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; 237a tp a b c 238set join_cache_level=@save_join_cache_level; 239set optimizer_switch=@save_optimizer_switch; 240drop table t0,t1,t2; 241# 242# MDEV-21628: Index condition pushdown for a simple condition over 243# index fields is not used for ref access of partitioned tables when employing BKA 244# 245create table t0 ( 246tp int, a int, b int, c varchar(12), index idx (a,b) 247); 248insert into t0 values 249(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), 250(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), 251(1,3,30,'yzzy'), (1,93,30,'zzzy'), 252(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), 253(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), 254(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), 255(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), 256(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), 257(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), 258(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), 259(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), 260(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), 261(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), 262(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), 263(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), 264(3,4,30,'zzzyy'), (3,94,30,'yyz'); 265create table t1 ( 266tp int, a int, b int, c varchar(12), index idx (a,b) 267) engine=myisam 268partition by list (tp) 269( partition p1 values in (1), 270partition p2 values in (2), 271partition p3 values in (3)); 272insert into t1 select * from t0; 273create table t2 (a int, index idx(a)) engine=myisam; 274insert into t2 values (1), (2), (3), (4), (5); 275insert into t2 select a+10 from t2; 276insert into t2 select a+20 from t2; 277analyze table t0,t1,t2; 278Table Op Msg_type Msg_text 279test.t0 analyze status Engine-independent statistics collected 280test.t0 analyze status OK 281test.t1 analyze status Engine-independent statistics collected 282test.t1 analyze status OK 283test.t2 analyze status Engine-independent statistics collected 284test.t2 analyze status OK 285set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; 286set join_cache_level=6, optimizer_switch='mrr=on'; 287explain 288select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; 289id select_type table type possible_keys key key_len ref rows Extra 2901 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 2911 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan 292# This will use "Using index condition(BKA)" 293explain 294select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; 295id select_type table type possible_keys key key_len ref rows Extra 2961 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 2971 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan 298set join_cache_level=@tmp1, optimizer_switch=@tmp2; 299drop table t0,t1,t2; 300