revision-id: db76d677345441246e1521204d9b418da36b6591 (mariadb-10.4.11-36-gdb76d67) parent(s): 4de32015be82d0f484a7b49a427853ea7b6da5fd author: Igor Babaev committer: Igor Babaev timestamp: 2020-01-19 20:03:25 -0800 message: MDEV-21446 Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check with rowid_filter upon concurrent access to table This bug has nothing to do with the concurrent access to table. Rather it concerns queries for which the optimizer decides to employ a rowid filter when accessing an InnoDB table by a secondary index, but later when calling test_if_skip_sort_order() changes its mind to access the table by the primary key. Currently usage of rowid filters is not supported in InnoDB if the table is accessed by the primary key. So in this case usage of a rowid filter to access the table must be prohibited. --- mysql-test/main/rowid_filter_innodb.result | 53 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 49 +++++++++++++++++++++++++++ sql/sql_select.cc | 13 ++++++++ 3 files changed, 115 insertions(+) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 36a59b8..c59b95b 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2469,3 +2469,56 @@ 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; +# +# MDEV-21446: index to access the table is changed for primary key +# +SET @stats.save= @@innodb_stats_persistent; +SET global innodb_stats_persistent=on; +CREATE TABLE t1 ( +pk int auto_increment, +a int, +b int, +primary key (pk), +key (a), +key (b) +) ENGINE=InnoDB; +INSERT INTO t1 (a,b) VALUES +(0,0), (0,9), (0,NULL), (1,2), (4,0), (2,9), (1,0), (NULL,0), (5,NULL), (5,1), +(0,7), (NULL,5), (NULL,0), (2,1), (2,5), (6,NULL), (0,NULL), (NULL,8), (8,5), +(2,NULL), (2,3), (NULL,8), (NULL,6), (1,1), (5,1), (NULL,5), (4,4), (2,4), +(2,5), (1,9), (NULL,0), (3,7), (0,4), (2,8), (1,2), (1,4), (2,1), +(NULL,7), (6,6), (3,0), (4,5), (5,2), (8,2), (NULL,NULL), (8,NULL), +(0,1),(0,7); +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range|filter a,b b|a 5|5 NULL 192 (21%) 21.31 Using index condition; Using where; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between 9 and 10 or `test`.`t1`.`a` is null) and (`test`.`t1`.`b` between 9 and 10 or `test`.`t1`.`b` = 9) +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) +ORDER BY pk LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index a,b PRIMARY 4 NULL 75 54.55 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between 9 and 10 or `test`.`t1`.`a` is null) and (`test`.`t1`.`b` between 9 and 10 or `test`.`t1`.`b` = 9) order by `test`.`t1`.`pk` limit 1 +ANALYZE +SELECT * FROM t1 +WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) +ORDER BY pk LIMIT 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +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; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 1a5c8fe..30e0ede 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -332,3 +332,52 @@ eval EXPLAIN EXTENDED $q; SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1; + +--echo # +--echo # MDEV-21446: index to access the table is changed for primary key +--echo # + +SET @stats.save= @@innodb_stats_persistent; +SET global innodb_stats_persistent=on; + +CREATE TABLE t1 ( + pk int auto_increment, + a int, + b int, + primary key (pk), + key (a), + key (b) +) ENGINE=InnoDB; + +INSERT INTO t1 (a,b) VALUES +(0,0), (0,9), (0,NULL), (1,2), (4,0), (2,9), (1,0), (NULL,0), (5,NULL), (5,1), +(0,7), (NULL,5), (NULL,0), (2,1), (2,5), (6,NULL), (0,NULL), (NULL,8), (8,5), +(2,NULL), (2,3), (NULL,8), (NULL,6), (1,1), (5,1), (NULL,5), (4,4), (2,4), +(2,5), (1,9), (NULL,0), (3,7), (0,4), (2,8), (1,2), (1,4), (2,1), +(NULL,7), (6,6), (3,0), (4,5), (5,2), (8,2), (NULL,NULL), (8,NULL), +(0,1),(0,7); +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; +INSERT INTO t1(a,b) SELECT a, b FROM t1; + +ANALYZE TABLE t1; + +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9); + +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) +ORDER BY pk LIMIT 1; + +ANALYZE +SELECT * FROM t1 + WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) +ORDER BY pk LIMIT 1; + +DROP TABLE t1; +SET global innodb_stats_persistent= @stats.save; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d9d9c22..0e0c5c9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -23241,6 +23241,19 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, join_read_first:join_read_last; tab->type=JT_NEXT; // Read with index_first(), index_next() + /* + Currently usage of rowid filters is not supported in InnoDB + if the table is accessed by the primary key + */ + if (tab->rowid_filter && + tab->index == table->s->primary_key && + table->file->primary_key_is_clustered()) + { + tab->range_rowid_filter_info= 0; + delete tab->rowid_filter; + tab->rowid_filter= 0; + } + if (tab->pre_idx_push_select_cond) { tab->set_cond(tab->pre_idx_push_select_cond);