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.

1 files changed, 153 insertions, 0 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test
index 30e0edeb75a..74349b8c6bb 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;