revision-id: 88950694199277e232852d133506f6342f6efe61 (mariadb-10.3.18-173-g8895069) parent(s): affe7fabc7baa36083e7632eb6c3611578d74b48 author: Igor Babaev committer: Igor Babaev timestamp: 2020-02-25 00:47:03 -0800 message: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and join_cache_level=6+ The patch fixes two similar bugs in the commit 8eeb689e9fc57afe19a8dbff354b5f9f167867a9 that added multi_range_read support to partitions. The commit opened a possibility to join a partition table using BKA+MRR. However in some cases it could lead to wrong results or even crashes. This could happened when - index condition pushdown was used to join the table or - the joined table was an inner table of an outer join and 'not exist' optimization was applied or - the join table was the inner table of a semi-join and the first match optimization was applied The bugs were in the code of the call-back functions - partition_multi_range_key_skip_record() and - partition_multi_range_key_skip_index_tuple(). Each of this function consist only of an invocation of another function. Yet a wrong parameter was passed at this invocation. The fix was suggested by Sergey Petrunia and it is apparently in line with original design. The corresponding comprehensive test cases demonstrating the problems caused by the bugs were constructed by me. --- mysql-test/include/partition_mrr.inc | 92 ++++++++++++++++ mysql-test/main/partition_mrr_aria.result | 157 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_innodb.result | 157 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_myisam.result | 164 +++++++++++++++++++++++++++- sql/ha_partition.cc | 6 +- 5 files changed, 569 insertions(+), 7 deletions(-) diff --git a/mysql-test/include/partition_mrr.inc b/mysql-test/include/partition_mrr.inc index 4c28579..f372ccc 100644 --- a/mysql-test/include/partition_mrr.inc +++ b/mysql-test/include/partition_mrr.inc @@ -36,11 +36,103 @@ insert into t3 select 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; + set optimizer_switch='mrr=on'; --replace_column 9 # explain select * from t3 force index (key_col) where key_col < 3; select * from t3 force index (key_col) where key_col < 3; +set optimizer_switch=@save_optimizer_switch; + drop table t1,t3; +--echo # +--echo # MDEV-21544: partitioned table is joined with BKA+MRR +--echo # + +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; + +# tables t0 and t1 contain the same set of records. + +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; + +set join_cache_level=6; +set optimizer_switch='mrr=on'; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +eval explain extended $q2; +eval $q2; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +eval explain extended $q2; +eval $q2; + +insert into t2 values +(3), (4), (5); +analyze table t2; + +let $q1= +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +eval explain extended $q2; +eval $q2; + +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index 7a0c35a..c2be6ee 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -39,6 +39,7 @@ 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; @@ -76,4 +77,160 @@ ID part_id key_col col2 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 OK +test.t1 analyze status OK +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 13 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 8 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 13 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 5 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 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 13 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 5 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; diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index c188f7e..ecef678 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -39,6 +39,7 @@ 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; @@ -76,4 +77,160 @@ ID part_id key_col col2 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 OK +test.t1 analyze status OK +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 13 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 8 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 13 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 5 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 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 13 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 5 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; diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index d989536..ff23982 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -39,6 +39,7 @@ 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; @@ -76,8 +77,164 @@ ID part_id key_col col2 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 OK +test.t1 analyze status OK +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 13 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 8 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 13 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 5 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 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 13 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 5 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; +# # MDEV-21628: Index condition pushdown for a simple condition over # index fields is not used for ref access of partitioned tables when employing BKA # @@ -115,11 +272,8 @@ 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 @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; set join_cache_level=6, optimizer_switch='mrr=on'; @@ -127,12 +281,12 @@ explain 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 Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan # This will use "Using index condition(BKA)" explain 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 Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan set join_cache_level=@tmp1, optimizer_switch=@tmp2; drop table t0,t1,t2; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 130c7f7..48610ca 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -6264,9 +6264,10 @@ static bool partition_multi_range_key_skip_record(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_record"); DBUG_RETURN(hld->partition->m_seq_if->skip_record(hld->partition->m_seq, - range_info, rowid)); + pkmr->ptr, rowid)); } @@ -6275,9 +6276,10 @@ static bool partition_multi_range_key_skip_index_tuple(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_index_tuple"); DBUG_RETURN(hld->partition->m_seq_if->skip_index_tuple(hld->partition->m_seq, - range_info)); + pkmr->ptr)); } ha_rows ha_partition::multi_range_read_info_const(uint keyno,