← Back to team overview

maria-developers team mailing list archive

Re: [Commits] a906aae: MDEV-21610 Different query results from 10.4.11 to 10.4.12

 

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(&param, tree);
>  
>        /* Get best 'range' plan and prepare data for making other plans */
> -      if ((range_trp= get_key_scans_params(&param, tree, FALSE, TRUE,
> +      if ((range_trp= get_key_scans_params(&param, 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
> commits@xxxxxxxxxxx
> 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