diff options
Diffstat (limited to 'mysql-test/main/partition_mrr_aria.result')
| -rw-r--r-- | mysql-test/main/partition_mrr_aria.result | 240 |
1 files changed, 240 insertions, 0 deletions
diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result new file mode 100644 index 00000000..7ff5c9b6 --- /dev/null +++ b/mysql-test/main/partition_mrr_aria.result @@ -0,0 +1,240 @@ +drop table if exists t1,t3; +# +# MDEV-20611: MRR scan over partitioned InnoDB table produces "Out of memory" error +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set @tmp=@@default_storage_engine; +set default_storage_engine=Aria; +create table t3 ( +ID bigint(20) NOT NULL AUTO_INCREMENT, +part_id int, +key_col int, +col2 int, +key(key_col), +PRIMARY KEY (ID,part_id) +) PARTITION BY RANGE (part_id) +(PARTITION p1 VALUES LESS THAN (3), +PARTITION p2 VALUES LESS THAN (7), +PARTITION p3 VALUES LESS THAN (10) +); +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `ID` bigint(20) NOT NULL AUTO_INCREMENT, + `part_id` int(11) NOT NULL, + `key_col` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + PRIMARY KEY (`ID`,`part_id`), + KEY `key_col` (`key_col`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`part_id`) +(PARTITION `p1` VALUES LESS THAN (3) ENGINE = Aria, + PARTITION `p2` VALUES LESS THAN (7) ENGINE = Aria, + PARTITION `p3` VALUES LESS THAN (10) ENGINE = Aria) +set default_storage_engine= @tmp; +insert into t3 select +A.a+10*B.a, +A.a, +B.a, +123456 +from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='mrr=on'; +explain +select * from t3 force index (key_col) where key_col < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range key_col key_col 5 NULL # Using where; Rowid-ordered scan +select * from t3 force index (key_col) where key_col < 3; +ID part_id key_col col2 +1 0 0 123456 +1 1 0 123456 +2 2 0 123456 +10 0 1 123456 +11 1 1 123456 +12 2 1 123456 +20 0 2 123456 +21 1 2 123456 +22 2 2 123456 +3 3 0 123456 +4 4 0 123456 +5 5 0 123456 +6 6 0 123456 +13 3 1 123456 +14 4 1 123456 +15 5 1 123456 +16 6 1 123456 +23 3 2 123456 +24 4 2 123456 +25 5 2 123456 +26 6 2 123456 +7 7 0 123456 +8 8 0 123456 +9 9 0 123456 +17 7 1 123456 +18 8 1 123456 +19 9 1 123456 +27 7 2 123456 +28 8 2 123456 +29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; +drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status Engine-independent statistics collected +test.t0 analyze status OK +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 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 +Warnings: +Note 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 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 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 +Warnings: +Note 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 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 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 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 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 +Warnings: +Note 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 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 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 +Warnings: +Note 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 +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 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 +Warnings: +Note 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 +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; |