revision-id: 78069a2106486ebe4d0d1e5991cd9ba5c989ec7c (mariadb-10.4.11-30-g78069a2) parent(s): 1c97cd339e9513b152727f386573c8c048db0281 author: Igor Babaev committer: Igor Babaev timestamp: 2020-01-18 13:26:03 -0800 message: 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. --- mysql-test/main/rowid_filter_innodb.result | 114 ++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 117 +++++++++++++++++++++++++++++ sql/multi_range_read.cc | 24 ++++++ 3 files changed, 255 insertions(+) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 2f57ee0..36a59b8 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`.`administrati ve_stree t2` 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_cit y`,`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 f1b7b0d..1a5c8fe 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 4fc386a..7e4c2ed 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;