[Commits] be83f81: MDEV-19919 Assertion `!prebuilt->index->is_primary()' failed

IgorBabaev igor at mariadb.com
Sat Nov 23 05:11:58 EET 2019


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);
 }
 


More information about the commits mailing list