MDEV-21356 ERROR 1032 Can't find record when running simple, single-table query

This bug could happen when both optimizer switches 'mrr' and 'mrr_sort_keys'
are enabled and the optimizer decided to use a rowid filter when
accessing an InnoDB table by a secondary key. With the above setting
any access by a secondary is converted to the rndpos access. In InnoDB the
rndpos access uses the primary key.
Currently usage of a rowid filter within InnoDB engine is not supported
if the table is accessed by 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 egine code instead.

3 files changed, 255 insertions, 0 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 2f57ee0de35..36a59b8047e 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2355,3 +2355,117 @@ count(0)
 0
 drop table t1;
 set global innodb_stats_persistent= @stats.save;
+#
+# MDEV-21356: usage of range filter with range access employing
+#             optimizer_switch='mrr=on,mrr_sort_keys=on';
+#
+CREATE TABLE t1 (
+id int(11) unsigned NOT NULL AUTO_INCREMENT,
+domain varchar(255) NOT NULL,
+registrant_name varchar(255) DEFAULT NULL,
+registrant_organization varchar(255) DEFAULT NULL,
+registrant_street1 varchar(255) DEFAULT NULL,
+registrant_street2 varchar(255) DEFAULT NULL,
+registrant_street3 varchar(255) DEFAULT NULL,
+registrant_street4 varchar(255) DEFAULT NULL,
+registrant_street5 varchar(255) DEFAULT NULL,
+registrant_city varchar(255) DEFAULT NULL,
+registrant_postal_code varchar(255) DEFAULT NULL,
+registrant_country varchar(255) DEFAULT NULL,
+registrant_email varchar(255) DEFAULT NULL,
+registrant_telephone varchar(255) DEFAULT NULL,
+administrative_name varchar(255) DEFAULT NULL,
+administrative_organization varchar(255) DEFAULT NULL,
+administrative_street1 varchar(255) DEFAULT NULL,
+administrative_street2 varchar(255) DEFAULT NULL,
+administrative_street3 varchar(255) DEFAULT NULL,
+administrative_street4 varchar(255) DEFAULT NULL,
+administrative_street5 varchar(255) DEFAULT NULL,
+administrative_city varchar(255) DEFAULT NULL,
+administrative_postal_code varchar(255) DEFAULT NULL,
+administrative_country varchar(255) DEFAULT NULL,
+administrative_email varchar(255) DEFAULT NULL,
+administrative_telephone varchar(255) DEFAULT NULL,
+technical_name varchar(255) DEFAULT NULL,
+technical_organization varchar(255) DEFAULT NULL,
+technical_street1 varchar(255) DEFAULT NULL,
+technical_street2 varchar(255) DEFAULT NULL,
+technical_street3 varchar(255) DEFAULT NULL,
+technical_street4 varchar(255) DEFAULT NULL,
+technical_street5 varchar(255) DEFAULT NULL,
+technical_city varchar(255) DEFAULT NULL,
+technical_postal_code varchar(255) DEFAULT NULL,
+technical_country varchar(255) DEFAULT NULL,
+technical_email varchar(255) DEFAULT NULL,
+technical_telephone varchar(255) DEFAULT NULL,
+json longblob NOT NULL,
+timestamp timestamp NOT NULL DEFAULT current_timestamp(),
+PRIMARY KEY (id),
+KEY ixEventWhoisDomainDomain (domain),
+KEY ixEventWhoisDomainTimestamp (timestamp)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 (
+id, domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp) VALUES
+(60380, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:18:28'),
+(60383, 'www.bestwestern.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2016-12-22 09:27:06'),
+(80392, 'www.dfinitions.fr', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, '', '2017-01-30 08:02:01'),
+(80407, 'www.firma.o2.pl', null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, 'AZ.pl Sp. z o.o.', 'Al. Papieza Jana Pawla II 19/2',
+null, null, null, null, '70-453 Szczecin', null, 'POLAND', null,
+'48914243780', '', '2017-01-30 08:24:51'),
+(80551, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, null, null, null, null, null, null, null,
+null, null, null, null, null, '', '2017-01-30 10:00:56'),
+(80560, 'www.blackmer-mouvex.com', 'MARIE-PIERRE PRODEAU', 'MOUVEX',
+'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+'PRODEAU@MOUVEX.COM', null, 'MARIE-PIERRE PRODEAU', 'MOUVEX',
+'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+'PRODEAU@MOUVEX.COM', '33 386498630', 'LAURENT SOUCHELEAU', 'MOUVEX',
+'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+'SOUCHELEAU@MOUVEX.COM', '33 386498643', '', '2017-01-30 10:04:38'),
+(80566, 'www.inup.com', 'MAXIMILIAN V. KETELHODT', null,
+'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY',
+'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 'MAXIMILIAN V. KETELHODT',
+null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937',
+'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580',
+'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null,
+null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM',
+'492214307580', '', '2017-01-30 10:08:29');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_sort_keys=on';
+SELECT * FROM t1
+WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
+timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+ORDER BY timestamp DESC;
+id	domain	registrant_name	registrant_organization	registrant_street1	registrant_street2	registrant_street3	registrant_street4	registrant_street5	registrant_city	registrant_postal_code	registrant_country	registrant_email	registrant_telephone	administrative_name	administrative_organization	administrative_street1	administrative_street2	administrative_street3	administrative_street4	administrative_street5	administrative_city	administrative_postal_code	administrative_country	administrative_email	administrative_telephone	technical_name	technical_organization	technical_street1	technical_street2	technical_street3	technical_street4	technical_street5	technical_city	technical_postal_code	technical_country	technical_email	technical_telephone	json	timestamp
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
+timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+ORDER BY timestamp DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref|filter	ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp	ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp	767|4	const	2 (14%)	14.29	Using index condition; Using where; Using filesort; Using rowid filter
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`t1`.`timestamp` desc
+SET optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1;
diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test
index f1b7b0dbbb0..1a5c8fe73d5 100644
--- a/mysql-test/main/rowid_filter_innodb.test
+++ b/mysql-test/main/rowid_filter_innodb.test
@@ -215,3 +215,120 @@ eval $q;
 
 drop table t1;
 set global innodb_stats_persistent= @stats.save;
+
+--echo #
+--echo # MDEV-21356: usage of range filter with range access employing
+--echo #             optimizer_switch='mrr=on,mrr_sort_keys=on';
+--echo #
+
+CREATE TABLE t1 (
+  id int(11) unsigned NOT NULL AUTO_INCREMENT,
+  domain varchar(255) NOT NULL,
+  registrant_name varchar(255) DEFAULT NULL,
+  registrant_organization varchar(255) DEFAULT NULL,
+  registrant_street1 varchar(255) DEFAULT NULL,
+  registrant_street2 varchar(255) DEFAULT NULL,
+  registrant_street3 varchar(255) DEFAULT NULL,
+  registrant_street4 varchar(255) DEFAULT NULL,
+  registrant_street5 varchar(255) DEFAULT NULL,
+  registrant_city varchar(255) DEFAULT NULL,
+  registrant_postal_code varchar(255) DEFAULT NULL,
+  registrant_country varchar(255) DEFAULT NULL,
+  registrant_email varchar(255) DEFAULT NULL,
+  registrant_telephone varchar(255) DEFAULT NULL,
+  administrative_name varchar(255) DEFAULT NULL,
+  administrative_organization varchar(255) DEFAULT NULL,
+  administrative_street1 varchar(255) DEFAULT NULL,
+  administrative_street2 varchar(255) DEFAULT NULL,
+  administrative_street3 varchar(255) DEFAULT NULL,
+  administrative_street4 varchar(255) DEFAULT NULL,
+  administrative_street5 varchar(255) DEFAULT NULL,
+  administrative_city varchar(255) DEFAULT NULL,
+  administrative_postal_code varchar(255) DEFAULT NULL,
+  administrative_country varchar(255) DEFAULT NULL,
+  administrative_email varchar(255) DEFAULT NULL,
+  administrative_telephone varchar(255) DEFAULT NULL,
+  technical_name varchar(255) DEFAULT NULL,
+  technical_organization varchar(255) DEFAULT NULL,
+  technical_street1 varchar(255) DEFAULT NULL,
+  technical_street2 varchar(255) DEFAULT NULL,
+  technical_street3 varchar(255) DEFAULT NULL,
+  technical_street4 varchar(255) DEFAULT NULL,
+  technical_street5 varchar(255) DEFAULT NULL,
+  technical_city varchar(255) DEFAULT NULL,
+  technical_postal_code varchar(255) DEFAULT NULL,
+  technical_country varchar(255) DEFAULT NULL,
+  technical_email varchar(255) DEFAULT NULL,
+  technical_telephone varchar(255) DEFAULT NULL,
+  json longblob NOT NULL,
+  timestamp timestamp NOT NULL DEFAULT current_timestamp(),
+  PRIMARY KEY (id),
+  KEY ixEventWhoisDomainDomain (domain),
+  KEY ixEventWhoisDomainTimestamp (timestamp)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO t1 (
+id, domain, registrant_name, registrant_organization, registrant_street1,
+registrant_street2, registrant_street3, registrant_street4, registrant_street5,
+registrant_city, registrant_postal_code, registrant_country, registrant_email,
+registrant_telephone, administrative_name, administrative_organization,
+administrative_street1, administrative_street2, administrative_street3,
+administrative_street4, administrative_street5, administrative_city,
+administrative_postal_code, administrative_country, administrative_email,
+administrative_telephone, technical_name, technical_organization,
+technical_street1, technical_street2, technical_street3, technical_street4,
+technical_street5, technical_city, technical_postal_code, technical_country,
+technical_email, technical_telephone, json, timestamp) VALUES
+(60380, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, '', '2016-12-22 09:18:28'),
+(60383, 'www.bestwestern.fr', null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, '', '2016-12-22 09:27:06'),
+(80392, 'www.dfinitions.fr', null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, '', '2017-01-30 08:02:01'),
+(80407, 'www.firma.o2.pl', null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, 'AZ.pl Sp. z o.o.', 'Al. Papieza Jana Pawla II 19/2',
+ null, null, null, null, '70-453 Szczecin', null, 'POLAND', null,
+ '48914243780', '', '2017-01-30 08:24:51'),
+(80551, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, '', '2017-01-30 10:00:56'),
+(80560, 'www.blackmer-mouvex.com', 'MARIE-PIERRE PRODEAU', 'MOUVEX',
+ '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+ 'PRODEAU@MOUVEX.COM', null, 'MARIE-PIERRE PRODEAU', 'MOUVEX',
+ '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+ 'PRODEAU@MOUVEX.COM', '33 386498630', 'LAURENT SOUCHELEAU', 'MOUVEX',
+ '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE',
+ 'SOUCHELEAU@MOUVEX.COM', '33 386498643', '', '2017-01-30 10:04:38'),
+(80566, 'www.inup.com', 'MAXIMILIAN V. KETELHODT', null,
+ 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY',
+ 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 'MAXIMILIAN V. KETELHODT',
+ null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937',
+ 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580',
+ 'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null,
+ null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM',
+ '492214307580', '', '2017-01-30 10:08:29');
+
+SET @save_optimizer_switch=@@optimizer_switch;
+
+SET optimizer_switch='mrr=on,mrr_sort_keys=on';
+
+let $q=
+SELECT * FROM t1
+  WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
+        timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
+ORDER BY timestamp DESC;
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+
+SET optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1;
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index 4fc386a0afe..7e4c2ed1f53 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -19,6 +19,7 @@
 #include "sql_select.h"
 #include "key.h"
 #include "sql_statistics.h"
+#include "rowid_filter.h"
 
 static ulonglong key_block_no(TABLE *table, uint keyno, ha_rows keyentry_pos)
 {
@@ -709,6 +710,20 @@ 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();
+
   return 0;
 }
 
@@ -801,6 +816,15 @@ 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.
+    */
+    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;
+
     /* Put rowid, or {rowid, range_id} pair into the buffer */
     rowid_buffer->write_ptr1= index_rowid;
     rowid_buffer->write_ptr2= (uchar*)&range_info;