revision-id: be83f81fbf8fea75955e047c13501443bb953452 (mariadb-10.4.10-23-gbe83f81) parent(s): 6cedb671e99038f1a10e0d8504f835aaabed9780 author: Igor Babaev committer: Igor Babaev timestamp: 2019-11-22 19:11:58 -0800 message: MDEV-19919 Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check For a single table query with ORDER BY and several sargable range conditions the optimizer may choose an execution plan that employs a rowid filter. In this case it is important to build the filter before calling the function JOIN_TAB::sort_table() that creates sort index for the result set, because when this is index created the filter has to be already filled. After the sort index has been created the filter must be deactivated. If not to do this the innodb function row_search_idx_cond_check() is getting confused when it has to read rows from the created sort index by using ha_rnd_pos(). The order of actions mentioned above is needed also when processing a join query if sorting is performed for the first non constant table in the chosen execution plan. --- mysql-test/main/rowid_filter_innodb.result | 35 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 33 ++++++++++++++++++++++++++++ sql/sql_select.cc | 8 +++++-- 3 files changed, 74 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 390c783..37e32f0 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2210,3 +2210,38 @@ a b drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; +# +# MDEV-19919: use of rowid filter for innodb table + ORDER BY +# +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t1 ( +a INT, +b VARCHAR(10), +c VARCHAR(1024), +KEY (b), +KEY (c) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), +(2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), +(1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), +(1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), +(NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'), +(3,'g','x'), (2,'h','y'); +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 a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range|filter b,c b|c 13|1027 NULL 5 (42%) 41.67 Using index condition; Using where; Using filesort; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a` +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; +a +1 +5 +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 240cd92..4a6c431 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -96,3 +96,36 @@ drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; + +--echo # +--echo # MDEV-19919: use of rowid filter for innodb table + ORDER BY +--echo # + +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; + +CREATE TABLE t1 ( + a INT, + b VARCHAR(10), + c VARCHAR(1024), + KEY (b), + KEY (c) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), + (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), + (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), + (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), + (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'), + (3,'g','x'), (2,'h','y'); + +ANALYZE TABLE t1; + +EXPLAIN EXTENDED +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; + +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; + +DROP TABLE t1; +SET GLOBAL innodb_stats_persistent= @stats.save; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a05c2f8..1ee2a17 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21101,11 +21101,12 @@ int join_init_read_record(JOIN_TAB *tab) */ if (tab->distinct && tab->remove_duplicates()) // Remove duplicates. return 1; - if (tab->filesort && tab->sort_table()) // Sort table. - return 1; tab->build_range_rowid_filter_if_needed(); + if (tab->filesort && tab->sort_table()) // Sort table. + return 1; + DBUG_EXECUTE_IF("kill_join_init_read_record", tab->join->thd->set_killed(KILL_QUERY);); if (tab->select && tab->select->quick && tab->select->quick->reset()) @@ -21165,6 +21166,9 @@ JOIN_TAB::sort_table() JOIN::ordered_index_order_by : JOIN::ordered_index_group_by)); rc= create_sort_index(join->thd, join, this, NULL); + /* Disactivate rowid filter if it was used when creating sort index */ + if (rowid_filter) + table->file->rowid_filter_is_active= false; return (rc != 0); }