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 commits@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