Hello Igor,
I've found a deficiency in the scheme used by the patch: it doesn't work if the
join buffer is re-filled multiple times.
On the first execution, everything works as intended. dsmrr_init() executes
these lines:
rowid_filter= h_arg->pushed_rowid_filter;
h_arg->cancel_pushed_rowid_filter();
then I can see that Mrr_ordered_rndpos_reader::refill_from_index_reader uses
the filter.
The MRR scan continues until it finishes. Then, SQL layer fills the join buffer
again, and calls multi_range_read_init() again, which calls dsmrr_init().
And here, h_arg->pushed_rowid_filter==NULL (as we've cleared it previously),
and the second MRR scan is not used anymore.
Example that I used for debugging (maybe it's larger than necessary):
create table t10 (
pk int primary key,
a int,
b int,
filler char(100),
key(a),
key(b)
);
insert into t10 select
A.a + 1000 *B.a,
A.a + 1000 *B.a,
A.a + 1000 *B.a,
'filler-data=FILLER=DATA'
from one_k A, one_k B;
create table t11 (a int);
insert into t11 select a from one_k where a < 200;
set optimizer_switch='mrr=on';
set join_cache_level=6;
set join_buffer_size=128;
MariaDB [test]> explain select * from t11, t10 where t10.a=t11.a and t10.b <
300;
+------+-------------+-------+------------+---------------+------+---------+------------+--------+-----------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra
|
+------+-------------+-------+------------+---------------+------+---------+------------+--------+-----------------------------------------------------------------------------------------+
| 1 | SIMPLE | t11 | ALL | NULL | NULL | NULL |
NULL | 200 | Using where
|
| 1 | SIMPLE | t10 | ref|filter | a,b | a|b | 5|5 |
test.t11.a | 1 (0%) | Using where; Using join buffer (flat, BKA join);
Rowid-ordered scan; Using rowid filter |
+------+-------------+-------+------------+---------------+------+---------+------------+--------+-----------------------------------------------------------------------------------------+
select * from t11, t10 where t10.a=t11.a and t10.b < 300;
Ideas about the solution:
1. DS-MRR code should put the rowid filter back into the 'h_arg' handler when
the scan
finishes (i.e. returns HA_ERR_END_OF_FILE)
2. DS-MRR code should store the rowid filter internally (and clear it up when
dsmrr_close() is called.
I haven't investigated which is better.
On Thu, Feb 13, 2020 at 10:55:56PM -0800, IgorBabaev wrote:
> revision-id: a906aaee26a7be57fe2db62214179476ec124486
> (mariadb-10.4.11-38-ga906aae)
> parent(s): 7ea413ac2d80c7f03d1dbad90ac30ecddd8b2835
> author: Igor Babaev
> committer: Igor Babaev
> timestamp: 2020-02-13 22:55:56 -0800
> message:
>
> MDEV-21610 Different query results from 10.4.11 to 10.4.12
>
> This patch fixes the following defects/bugs.
> 1. If BKA[H] algorithm was used to join a table for which the optimizer
> had decided to employ a rowid filter the filter actually was not built.
> 2. The patch for the bug MDEV-21356 that added the code canceling pushing
> rowid filter into an engine for the table joined with employment of
> BKA[H] and MRR was not quite correct for Innodb engine because this
> cancellation was done after InnoDB code had already bound the the pushed
> filter to internal InnoDB structures.
>
> ---
> mysql-test/main/rowid_filter_innodb.result | 333
> +++++++++++++++++++++++++++++
> mysql-test/main/rowid_filter_innodb.test | 153 +++++++++++++
> sql/multi_range_read.cc | 39 ++--
> sql/multi_range_read.h | 5 +-
> sql/opt_range.cc | 3 +-
> sql/sql_join_cache.cc | 2 +
> 6 files changed, 515 insertions(+), 20 deletions(-)
>
> diff --git a/mysql-test/main/rowid_filter_innodb.result
> b/mysql-test/main/rowid_filter_innodb.result
> index c59b95b..9423fb1 100644
> --- a/mysql-test/main/rowid_filter_innodb.result
> +++ b/mysql-test/main/rowid_filter_innodb.result
> @@ -2522,3 +2522,336 @@ id select_type table type possible_keys
> key key_len ref rows r_rows filtered r_f
> 1 SIMPLE t1 index a,b PRIMARY 4 NULL 3008 3008.00
> 1.36 0.00 Using where
> DROP TABLE t1;
> SET global innodb_stats_persistent= @stats.save;
> +#
> +# MDEV-21610: Using rowid filter with BKA+MRR
> +#
> +set @stats.save= @@innodb_stats_persistent;
> +set global innodb_stats_persistent=on;
> +CREATE TABLE acli (
> +id bigint(20) NOT NULL,
> +rid varchar(255) NOT NULL,
> +tp smallint(6) NOT NULL DEFAULT 0,
> +PRIMARY KEY (id),
> +KEY acli_rid (rid),
> +KEY acli_tp (tp)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +insert into acli(id,rid,tp) values
> +(184929059698905997,'ABABABABABABABABAB',103),
> +(184929059698905998,'ABABABABABABABABAB',121),
> +(283586039035985921,'00000000000000000000000000000000',103),
> +(2216474704108064678,'020BED6D07B741CE9B10AB2200FEF1DF',103),
> +(2216474704108064679,'020BED6D07B741CE9B10AB2200FEF1DF',121),
> +(3080602882609775593,'B5FCC8C7111E4E3CBC21AAF5012F59C2',103),
> +(3080602882609775594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(3080602882609776594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(3080602882609777595,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(4269412446747236214,'SCSCSCSCSCSCSCSC',103),
> +(4269412446747236215,'SCSCSCSCSCSCSCSC',121),
> +(6341490487802728356,'6072D47E513F4A4794BBAB2200FDB67D',103),
> +(6341490487802728357,'6072D47E513F4A4794BBAB2200FDB67D',121);
> +CREATE TABLE acei (
> +id bigint(20) NOT NULL,
> +aclid bigint(20) NOT NULL DEFAULT 0,
> +atp smallint(6) NOT NULL DEFAULT 0,
> +clus smallint(6) NOT NULL DEFAULT 0,
> +PRIMARY KEY (id),
> +KEY acei_aclid (aclid),
> +KEY acei_clus (clus)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +insert into acei(id,aclid,atp,clus) values
> +(184929059698905999,184929059698905997,0,1),
> +(184929059698906000,184929059698905997,0,1),
> +(184929059698906001,184929059698905997,1,1),
> +(184929059698906002,184929059698905998,1,1),
> +(283586039035985922,283586039035985921,1,1),
> +(2216474704108064684,2216474704108064678,0,1),
> +(2216474704108064685,2216474704108064678,0,1),
> +(2216474704108064686,2216474704108064678,1,1),
> +(2216474704108064687,2216474704108064679,1,1),
> +(3080602882609775595,3080602882609775593,0,1),
> +(3080602882609775596,3080602882609775593,0,1),
> +(3080602882609775597,3080602882609775593,1,1),
> +(3080602882609775598,3080602882609775594,1,1),
> +(3080602882609776595,3080602882609776594,1,1),
> +(3080602882609777596,3080602882609777595,1,1),
> +(4269412446747236216,4269412446747236214,0,1),
> +(4269412446747236217,4269412446747236214,0,1),
> +(4269412446747236218,4269412446747236214,1,1),
> +(4269412446747236219,4269412446747236215,1,1),
> +(6341490487802728358,6341490487802728356,0,1),
> +(6341490487802728359,6341490487802728356,0,1),
> +(6341490487802728360,6341490487802728356,1,1),
> +(6341490487802728361,6341490487802728357,1,1);
> +CREATE TABLE filt (
> +id bigint(20) NOT NULL,
> +aceid bigint(20) NOT NULL DEFAULT 0,
> +clid smallint(6) NOT NULL DEFAULT 0,
> +fh bigint(20) NOT NULL DEFAULT 0,
> +PRIMARY KEY (id),
> +KEY filt_aceid (aceid),
> +KEY filt_clid (clid),
> +KEY filt_fh (fh)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +insert into filt(id,aceid,clid,fh) values
> +(184929059698905999,184929059698905999,1,8948400944397203540),
> +(184929059698906000,184929059698906000,1,-3516039679025944536),
> +(184929059698906001,184929059698906001,1,-3516039679025944536),
> +(184929059698906002,184929059698906001,1,2965370193075218252),
> +(184929059698906003,184929059698906001,1,8948400944397203540),
> +(184929059698906004,184929059698906002,1,2478709353550777738),
> +(283586039035985922,283586039035985922,1,5902600816362013271),
> +(2216474704108064686,2216474704108064684,1,8948400944397203540),
> +(2216474704108064687,2216474704108064685,1,-7244708939311117030),
> +(2216474704108064688,2216474704108064686,1,-7244708939311117030),
> +(2216474704108064689,2216474704108064686,1,7489060986210282479),
> +(2216474704108064690,2216474704108064686,1,8948400944397203540),
> +(2216474704108064691,2216474704108064687,1,-3575268945274980038),
> +(3080602882609775595,3080602882609775595,1,8948400944397203540),
> +(3080602882609775596,3080602882609775596,1,-5420422472375069774),
> +(3080602882609775597,3080602882609775597,1,-5420422472375069774),
> +(3080602882609775598,3080602882609775597,1,8518228073041491534),
> +(3080602882609775599,3080602882609775597,1,8948400944397203540),
> +(3080602882609775600,3080602882609775598,1,6311439873746261694),
> +(3080602882609775601,3080602882609775598,1,6311439873746261694),
> +(3080602882609776595,3080602882609776595,1,-661101805245999843),
> +(3080602882609777596,3080602882609777596,1,-661101805245999843),
> +(3080602882609777597,3080602882609777596,1,2216865386202464067),
> +(4269412446747236216,4269412446747236216,1,8948400944397203540),
> +(4269412446747236217,4269412446747236217,1,-1143096194892676000),
> +(4269412446747236218,4269412446747236218,1,-1143096194892676000),
> +(4269412446747236219,4269412446747236218,1,5313391811364818290),
> +(4269412446747236220,4269412446747236218,1,8948400944397203540),
> +(4269412446747236221,4269412446747236219,1,7624499822621753835),
> +(6341490487802728358,6341490487802728358,1,8948400944397203540),
> +(6341490487802728359,6341490487802728359,1,8141092449587136068),
> +(6341490487802728360,6341490487802728360,1,8141092449587136068),
> +(6341490487802728361,6341490487802728360,1,1291319099896431785),
> +(6341490487802728362,6341490487802728360,1,8948400944397203540),
> +(6341490487802728363,6341490487802728361,1,6701841652906431497);
> +analyze table filt, acei, acli;
> +Table Op Msg_type Msg_text
> +test.filt analyze status Engine-independent statistics collected
> +test.filt analyze status OK
> +test.acei analyze status Engine-independent statistics collected
> +test.acei analyze status OK
> +test.acli analyze status Engine-independent statistics collected
> +test.acli analyze status OK
> +set @save_optimizer_switch=@@optimizer_switch;
> +set @save_join_cache_level=@@join_cache_level;
> +set optimizer_switch='mrr=off';
> +set join_cache_level=2;
> +set statement optimizer_switch='rowid_filter=off' for explain extended
> select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id select_type table type possible_keys key key_len ref
> rows filtered Extra
> +1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp
> acli_tp,acli_rid 2,767 NULL 2 100.00 Using
> intersect(acli_tp,acli_rid); Using where; Using index
> +1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8
> test.t.id 1 100.00 Using where
> +1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8
> test.a.id 1 17.14 Using where
> +Warnings:
> +Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS
> `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS
> `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a`
> join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1
> and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` =
> `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> `test`.`fi`.`fh` in
> (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
> +set statement optimizer_switch='rowid_filter=off' for select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id id aceid clid fh
> +3080602882609775594 3080602882609775600 3080602882609775598 1
> 6311439873746261694
> +3080602882609775594 3080602882609775601 3080602882609775598 1
> 6311439873746261694
> +set statement optimizer_switch='rowid_filter=on' for explain extended select
> t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id select_type table type possible_keys key key_len ref
> rows filtered Extra
> +1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp
> acli_tp,acli_rid 2,767 NULL 2 100.00 Using
> intersect(acli_tp,acli_rid); Using where; Using index
> +1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8
> test.t.id 1 100.00 Using where
> +1 SIMPLE fi ref|filter filt_aceid,filt_fh
> filt_aceid|filt_fh 8|8 test.a.id 1 (17%) 17.14 Using where;
> Using rowid filter
> +Warnings:
> +Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS
> `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS
> `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a`
> join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1
> and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` =
> `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> `test`.`fi`.`fh` in
> (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
> +set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id id aceid clid fh
> +3080602882609775594 3080602882609775600 3080602882609775598 1
> 6311439873746261694
> +3080602882609775594 3080602882609775601 3080602882609775598 1
> 6311439873746261694
> +set optimizer_switch='mrr=on';
> +set join_cache_level=6;
> +set statement optimizer_switch='rowid_filter=off' for explain extended
> select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id select_type table type possible_keys key key_len ref
> rows filtered Extra
> +1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp
> acli_tp,acli_rid 2,767 NULL 2 100.00 Using
> intersect(acli_tp,acli_rid); Using where; Using index
> +1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8
> test.t.id 1 100.00 Using where; Using join buffer (flat, BKA
> join); Rowid-ordered scan
> +1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8
> test.a.id 1 17.14 Using where; Using join buffer (incremental,
> BKA join); Rowid-ordered scan
> +Warnings:
> +Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS
> `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS
> `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a`
> join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1
> and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` =
> `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> `test`.`fi`.`fh` in
> (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
> +set statement optimizer_switch='rowid_filter=off' for select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id id aceid clid fh
> +3080602882609775594 3080602882609775600 3080602882609775598 1
> 6311439873746261694
> +3080602882609775594 3080602882609775601 3080602882609775598 1
> 6311439873746261694
> +set statement optimizer_switch='rowid_filter=on' for explain extended select
> t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id select_type table type possible_keys key key_len ref
> rows filtered Extra
> +1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp
> acli_tp,acli_rid 2,767 NULL 2 100.00 Using
> intersect(acli_tp,acli_rid); Using where; Using index
> +1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8
> test.t.id 1 100.00 Using where; Using join buffer (flat, BKA
> join); Rowid-ordered scan
> +1 SIMPLE fi ref|filter filt_aceid,filt_fh
> filt_aceid|filt_fh 8|8 test.a.id 1 (17%) 17.14 Using where;
> Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid
> filter
> +Warnings:
> +Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS
> `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS
> `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a`
> join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1
> and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` =
> `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> `test`.`fi`.`fh` in
> (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
> +set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +id id aceid clid fh
> +3080602882609775594 3080602882609775600 3080602882609775598 1
> 6311439873746261694
> +3080602882609775594 3080602882609775601 3080602882609775598 1
> 6311439873746261694
> +set statement optimizer_switch='rowid_filter=on' for analyze format=json
> select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> +inner join filt fi on a.id = fi.aceid
> +where
> +t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> +t.tp = 121 and
> +a.atp = 1 and
> +fi.fh in (6311439873746261694,-397087483897438286,
> +8518228073041491534,-5420422472375069774);
> +ANALYZE
> +{
> + "query_block": {
> + "select_id": 1,
> + "r_loops": 1,
> + "r_total_time_ms": "REPLACED",
> + "table": {
> + "table_name": "t",
> + "access_type": "index_merge",
> + "possible_keys": ["PRIMARY", "acli_rid", "acli_tp"],
> + "key_length": "2,767",
> + "index_merge": {
> + "intersect": {
> + "range": {
> + "key": "acli_tp",
> + "used_key_parts": ["tp"]
> + },
> + "range": {
> + "key": "acli_rid",
> + "used_key_parts": ["rid"]
> + }
> + }
> + },
> + "r_loops": 1,
> + "rows": 2,
> + "r_rows": 3,
> + "r_total_time_ms": "REPLACED",
> + "filtered": 100,
> + "r_filtered": 100,
> + "attached_condition": "t.tp = 121 and t.rid =
> 'B5FCC8C7111E4E3CBC21AAF5012F59C2'",
> + "using_index": true
> + },
> + "block-nl-join": {
> + "table": {
> + "table_name": "a",
> + "access_type": "ref",
> + "possible_keys": ["PRIMARY", "acei_aclid"],
> + "key": "acei_aclid",
> + "key_length": "8",
> + "used_key_parts": ["aclid"],
> + "ref": ["test.t.id"],
> + "r_loops": 1,
> + "rows": 1,
> + "r_rows": 3,
> + "r_total_time_ms": "REPLACED",
> + "filtered": 100,
> + "r_filtered": 100
> + },
> + "buffer_type": "flat",
> + "buffer_size": "8Kb",
> + "join_type": "BKA",
> + "mrr_type": "Rowid-ordered scan",
> + "attached_condition": "a.atp = 1",
> + "r_filtered": 100
> + },
> + "block-nl-join": {
> + "table": {
> + "table_name": "fi",
> + "access_type": "ref",
> + "possible_keys": ["filt_aceid", "filt_fh"],
> + "key": "filt_aceid",
> + "key_length": "8",
> + "used_key_parts": ["aceid"],
> + "ref": ["test.a.id"],
> + "rowid_filter": {
> + "range": {
> + "key": "filt_fh",
> + "used_key_parts": ["fh"]
> + },
> + "rows": 6,
> + "selectivity_pct": 17.143,
> + "r_rows": 5,
> + "r_selectivity_pct": 40,
> + "r_buffer_size": "REPLACED",
> + "r_filling_time_ms": "REPLACED"
> + },
> + "r_loops": 1,
> + "rows": 1,
> + "r_rows": 2,
> + "r_total_time_ms": "REPLACED",
> + "filtered": 17.143,
> + "r_filtered": 100
> + },
> + "buffer_type": "incremental",
> + "buffer_size": "603",
> + "join_type": "BKA",
> + "mrr_type": "Rowid-ordered scan",
> + "attached_condition": "fi.fh in
> (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)",
> + "r_filtered": 100
> + }
> + }
> +}
> +set optimizer_switch=@save_optimizer_switch;
> +set join_cache_level=@save_join_cache_level;
> +drop table filt, acei, acli;
> +set global innodb_stats_persistent= @stats.save;
> diff --git a/mysql-test/main/rowid_filter_innodb.test
> b/mysql-test/main/rowid_filter_innodb.test
> index 30e0ede..74349b8 100644
> --- a/mysql-test/main/rowid_filter_innodb.test
> +++ b/mysql-test/main/rowid_filter_innodb.test
> @@ -381,3 +381,156 @@ ORDER BY pk LIMIT 1;
>
> DROP TABLE t1;
> SET global innodb_stats_persistent= @stats.save;
> +
> +--echo #
> +--echo # MDEV-21610: Using rowid filter with BKA+MRR
> +--echo #
> +
> +set @stats.save= @@innodb_stats_persistent;
> +set global innodb_stats_persistent=on;
> +
> +CREATE TABLE acli (
> + id bigint(20) NOT NULL,
> + rid varchar(255) NOT NULL,
> + tp smallint(6) NOT NULL DEFAULT 0,
> + PRIMARY KEY (id),
> + KEY acli_rid (rid),
> + KEY acli_tp (tp)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +
> +insert into acli(id,rid,tp) values
> +(184929059698905997,'ABABABABABABABABAB',103),
> +(184929059698905998,'ABABABABABABABABAB',121),
> +(283586039035985921,'00000000000000000000000000000000',103),
> +(2216474704108064678,'020BED6D07B741CE9B10AB2200FEF1DF',103),
> +(2216474704108064679,'020BED6D07B741CE9B10AB2200FEF1DF',121),
> +(3080602882609775593,'B5FCC8C7111E4E3CBC21AAF5012F59C2',103),
> +(3080602882609775594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(3080602882609776594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(3080602882609777595,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121),
> +(4269412446747236214,'SCSCSCSCSCSCSCSC',103),
> +(4269412446747236215,'SCSCSCSCSCSCSCSC',121),
> +(6341490487802728356,'6072D47E513F4A4794BBAB2200FDB67D',103),
> +(6341490487802728357,'6072D47E513F4A4794BBAB2200FDB67D',121);
> +
> +CREATE TABLE acei (
> + id bigint(20) NOT NULL,
> + aclid bigint(20) NOT NULL DEFAULT 0,
> + atp smallint(6) NOT NULL DEFAULT 0,
> + clus smallint(6) NOT NULL DEFAULT 0,
> + PRIMARY KEY (id),
> + KEY acei_aclid (aclid),
> + KEY acei_clus (clus)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +
> +insert into acei(id,aclid,atp,clus) values
> +(184929059698905999,184929059698905997,0,1),
> +(184929059698906000,184929059698905997,0,1),
> +(184929059698906001,184929059698905997,1,1),
> +(184929059698906002,184929059698905998,1,1),
> +(283586039035985922,283586039035985921,1,1),
> +(2216474704108064684,2216474704108064678,0,1),
> +(2216474704108064685,2216474704108064678,0,1),
> +(2216474704108064686,2216474704108064678,1,1),
> +(2216474704108064687,2216474704108064679,1,1),
> +(3080602882609775595,3080602882609775593,0,1),
> +(3080602882609775596,3080602882609775593,0,1),
> +(3080602882609775597,3080602882609775593,1,1),
> +(3080602882609775598,3080602882609775594,1,1),
> +(3080602882609776595,3080602882609776594,1,1),
> +(3080602882609777596,3080602882609777595,1,1),
> +(4269412446747236216,4269412446747236214,0,1),
> +(4269412446747236217,4269412446747236214,0,1),
> +(4269412446747236218,4269412446747236214,1,1),
> +(4269412446747236219,4269412446747236215,1,1),
> +(6341490487802728358,6341490487802728356,0,1),
> +(6341490487802728359,6341490487802728356,0,1),
> +(6341490487802728360,6341490487802728356,1,1),
> +(6341490487802728361,6341490487802728357,1,1);
> +
> +CREATE TABLE filt (
> + id bigint(20) NOT NULL,
> + aceid bigint(20) NOT NULL DEFAULT 0,
> + clid smallint(6) NOT NULL DEFAULT 0,
> + fh bigint(20) NOT NULL DEFAULT 0,
> + PRIMARY KEY (id),
> + KEY filt_aceid (aceid),
> + KEY filt_clid (clid),
> + KEY filt_fh (fh)
> +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> +
> +insert into filt(id,aceid,clid,fh) values
> +(184929059698905999,184929059698905999,1,8948400944397203540),
> +(184929059698906000,184929059698906000,1,-3516039679025944536),
> +(184929059698906001,184929059698906001,1,-3516039679025944536),
> +(184929059698906002,184929059698906001,1,2965370193075218252),
> +(184929059698906003,184929059698906001,1,8948400944397203540),
> +(184929059698906004,184929059698906002,1,2478709353550777738),
> +(283586039035985922,283586039035985922,1,5902600816362013271),
> +(2216474704108064686,2216474704108064684,1,8948400944397203540),
> +(2216474704108064687,2216474704108064685,1,-7244708939311117030),
> +(2216474704108064688,2216474704108064686,1,-7244708939311117030),
> +(2216474704108064689,2216474704108064686,1,7489060986210282479),
> +(2216474704108064690,2216474704108064686,1,8948400944397203540),
> +(2216474704108064691,2216474704108064687,1,-3575268945274980038),
> +(3080602882609775595,3080602882609775595,1,8948400944397203540),
> +(3080602882609775596,3080602882609775596,1,-5420422472375069774),
> +(3080602882609775597,3080602882609775597,1,-5420422472375069774),
> +(3080602882609775598,3080602882609775597,1,8518228073041491534),
> +(3080602882609775599,3080602882609775597,1,8948400944397203540),
> +(3080602882609775600,3080602882609775598,1,6311439873746261694),
> +(3080602882609775601,3080602882609775598,1,6311439873746261694),
> +(3080602882609776595,3080602882609776595,1,-661101805245999843),
> +(3080602882609777596,3080602882609777596,1,-661101805245999843),
> +(3080602882609777597,3080602882609777596,1,2216865386202464067),
> +(4269412446747236216,4269412446747236216,1,8948400944397203540),
> +(4269412446747236217,4269412446747236217,1,-1143096194892676000),
> +(4269412446747236218,4269412446747236218,1,-1143096194892676000),
> +(4269412446747236219,4269412446747236218,1,5313391811364818290),
> +(4269412446747236220,4269412446747236218,1,8948400944397203540),
> +(4269412446747236221,4269412446747236219,1,7624499822621753835),
> +(6341490487802728358,6341490487802728358,1,8948400944397203540),
> +(6341490487802728359,6341490487802728359,1,8141092449587136068),
> +(6341490487802728360,6341490487802728360,1,8141092449587136068),
> +(6341490487802728361,6341490487802728360,1,1291319099896431785),
> +(6341490487802728362,6341490487802728360,1,8948400944397203540),
> +(6341490487802728363,6341490487802728361,1,6701841652906431497);
> +
> +analyze table filt, acei, acli;
> +
> +let $q=
> +select t.id, fi.*
> +from (acli t inner join acei a on a.aclid = t.id)
> + inner join filt fi on a.id = fi.aceid
> + where
> + t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
> + t.tp = 121 and
> + a.atp = 1 and
> + fi.fh in (6311439873746261694,-397087483897438286,
> + 8518228073041491534,-5420422472375069774);
> +
> +set @save_optimizer_switch=@@optimizer_switch;
> +set @save_join_cache_level=@@join_cache_level;
> +
> +set optimizer_switch='mrr=off';
> +set join_cache_level=2;
> +eval $without_filter explain extended $q;
> +eval $without_filter $q;
> +eval $with_filter explain extended $q;
> +eval $with_filter $q;
> +
> +set optimizer_switch='mrr=on';
> +set join_cache_level=6;
> +eval $without_filter explain extended $q;
> +eval $without_filter $q;
> +eval $with_filter explain extended $q;
> +eval $with_filter $q;
> +--source include/analyze-format.inc
> +eval $with_filter analyze format=json $q;
> +
> +set optimizer_switch=@save_optimizer_switch;
> +set join_cache_level=@save_join_cache_level;
> +
> +drop table filt, acei, acli;
> +
> +set global innodb_stats_persistent= @stats.save;
> diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
> index 7e4c2ed..daeb53d 100644
> --- a/sql/multi_range_read.cc
> +++ b/sql/multi_range_read.cc
> @@ -702,7 +702,8 @@ static int rowid_cmp_reverse(void *file, uchar *a, uchar
> *b)
> int Mrr_ordered_rndpos_reader::init(handler *h_arg,
> Mrr_index_reader *index_reader_arg,
> uint mode,
> - Lifo_buffer *buf)
> + Lifo_buffer *buf,
> + Rowid_filter *filter)
> {
> file= h_arg;
> index_reader= index_reader_arg;
> @@ -710,19 +711,7 @@ int Mrr_ordered_rndpos_reader::init(handler *h_arg,
> is_mrr_assoc= !MY_TEST(mode & HA_MRR_NO_ASSOCIATION);
> index_reader_exhausted= FALSE;
> index_reader_needs_refill= TRUE;
> -
> - /*
> - Currently usage of a rowid filter within InnoDB engine is not supported
> - if the table is accessed by the primary key.
> - With optimizer switches ''mrr' and 'mrr_sort_keys' are both enabled
> - any access by a secondary index is converted to the rndpos access. In
> - InnoDB the rndpos access is always uses the primary key.
> - Do not use pushed rowid filter if the table is accessed actually by the
> - primary key. Use the rowid filter outside the engine code (see
> - Mrr_ordered_rndpos_reader::refill_from_index_reader).
> - */
> - if (file->pushed_rowid_filter && file->primary_key_is_clustered())
> - file->cancel_pushed_rowid_filter();
> + rowid_filter= filter;
>
> return 0;
> }
> @@ -817,10 +806,8 @@ int Mrr_ordered_rndpos_reader::refill_from_index_reader()
> index_reader->position();
>
> /*
> - If the built rowid filter cannot be used at the engine level use it
> here.
> + If the built rowid filter cannot be used at the engine level, use it
> here.
> */
> - Rowid_filter *rowid_filter=
> - file->get_table()->reginfo.join_tab->rowid_filter;
> if (rowid_filter && !file->pushed_rowid_filter &&
> !rowid_filter->check((char *)index_rowid))
> continue;
> @@ -967,6 +954,7 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF
> *seq_funcs,
> handler *h_idx;
> Mrr_ordered_rndpos_reader *disk_strategy= NULL;
> bool do_sort_keys= FALSE;
> + Rowid_filter *rowid_filter= NULL;
> DBUG_ENTER("DsMrr_impl::dsmrr_init");
> /*
> index_merge may invoke a scan on an object for which dsmrr_info[_const]
> @@ -1015,6 +1003,21 @@ int DsMrr_impl::dsmrr_init(handler *h_arg,
> RANGE_SEQ_IF *seq_funcs,
> if (!(keyno == table->s->primary_key && h_idx->primary_key_is_clustered()))
> {
> strategy= disk_strategy= &reader_factory.ordered_rndpos_reader;
> + if (h_arg->pushed_rowid_filter)
> + {
> + /*
> + Currently usage of a rowid filter within InnoDB engine is not
> supported
> + if the table is accessed by the primary key.
> + With optimizer switches ''mrr' and 'mrr_sort_keys' are both enabled
> + any access by a secondary index is converted to the rndpos access. In
> + InnoDB the rndpos access is always uses the primary key.
> + Do not use pushed rowid filter if the table is accessed actually by
> the
> + primary key. Use the rowid filter outside the engine code (see
> + Mrr_ordered_rndpos_reader::refill_from_index_reader).
> + */
> + rowid_filter= h_arg->pushed_rowid_filter;
> + h_arg->cancel_pushed_rowid_filter();
> + }
> }
>
> full_buf= buf->buffer;
> @@ -1101,7 +1104,7 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF
> *seq_funcs,
> n_ranges, mode, &keypar, key_buffer,
> &buf_manager)) ||
> (res= disk_strategy->init(primary_file, index_strategy, mode,
> - &rowid_buffer)))
> + &rowid_buffer, rowid_filter)))
> {
> goto error;
> }
> diff --git a/sql/multi_range_read.h b/sql/multi_range_read.h
> index 0473fef..6be9537 100644
> --- a/sql/multi_range_read.h
> +++ b/sql/multi_range_read.h
> @@ -364,7 +364,7 @@ class Mrr_ordered_rndpos_reader : public Mrr_reader
> {
> public:
> int init(handler *file, Mrr_index_reader *index_reader, uint mode,
> - Lifo_buffer *buf);
> + Lifo_buffer *buf, Rowid_filter *filter);
> int get_next(range_id_t *range_info);
> int refill_buffer(bool initial);
> private:
> @@ -399,6 +399,9 @@ class Mrr_ordered_rndpos_reader : public Mrr_reader
> /* Buffer to store (rowid, range_id) pairs */
> Lifo_buffer *rowid_buffer;
>
> + /* Rowid filter to be checked against (if any) */
> + Rowid_filter *rowid_filter;
> +
> int refill_from_index_reader();
> };
>
> diff --git a/sql/opt_range.cc b/sql/opt_range.cc
> index c47da28..5f034c6 100644
> --- a/sql/opt_range.cc
> +++ b/sql/opt_range.cc
> @@ -2902,7 +2902,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map
> keys_to_use,
> remove_nonrange_trees(¶m, tree);
>
> /* Get best 'range' plan and prepare data for making other plans */
> - if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
> + if ((range_trp= get_key_scans_params(¶m, tree,
> + only_single_index_range_scan,
> TRUE,
> best_read_time)))
> {
> best_trp= range_trp;
> diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
> index 3a509b3..e9ad538 100644
> --- a/sql/sql_join_cache.cc
> +++ b/sql/sql_join_cache.cc
> @@ -2248,6 +2248,8 @@ enum_nested_loop_state
> JOIN_CACHE::join_matching_records(bool skip_last)
> if ((rc= join_tab_execution_startup(join_tab)) < 0)
> goto finish2;
>
> + join_tab->build_range_rowid_filter_if_needed();
> +
> /* Prepare to retrieve all records of the joined table */
> if (unlikely((error= join_tab_scan->open())))
> {
> _______________________________________________
> commits mailing list
> comm...@mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
