[Commits] 18bf0bf: MDEV-18956 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters

IgorBabaev igor at mariadb.com
Sat Apr 6 09:14:05 EEST 2019


revision-id: 18bf0bf4968aa80da3a384204d2bfcffbc7dbe7f (mariadb-10.4.3-165-g18bf0bf)
parent(s): a2e477ffd0414248d1d3af2eeafe1e3cffddebc6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-04-05 23:14:05 -0700
message:

MDEV-18956 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters

If SUBS_IN_TO_EXISTS strategy has been chosen for a subquery then
additional conditions are injected into WHERE/ON/HAVING of this subquery
and it may happen that test_quick_select() invoked from
JOIN::make_range_rowid_filters() discovers impossible range. This
must be checked.

---
 mysql-test/main/rowid_filter.result        | 25 +++++++++++++++++++++++++
 mysql-test/main/rowid_filter.test          | 25 +++++++++++++++++++++++++
 mysql-test/main/rowid_filter_innodb.result | 25 +++++++++++++++++++++++++
 sql/sql_select.cc                          | 16 +++++++++++++---
 4 files changed, 88 insertions(+), 3 deletions(-)

diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 2fdff7d..280ced7 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -1987,4 +1987,29 @@ pk	i
 1	10
 2	20
 DROP TABLE t1;
+#
+# MDEV-18956: Possible rowid filter for subquery for which
+#             in_to_exists strategy has been chosen
+#
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (
+pk int auto_increment PRIMARY KEY,
+i1 int, i2 int, c2 varchar(1),
+KEY (i1), KEY (i2)
+) engine=myisam;
+INSERT INTO t2 VALUES
+(1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+(6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+(11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+pk
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
+DROP TABLE t1,t2;
 set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index d84839b..9c53367 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -275,4 +275,29 @@ SELECT * FROM t1 WHERE pk < 5;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-18956: Possible rowid filter for subquery for which
+--echo #             in_to_exists strategy has been chosen
+--echo #
+
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (
+  pk int auto_increment PRIMARY KEY,
+  i1 int, i2 int, c2 varchar(1),
+  KEY (i1), KEY (i2)
+) engine=myisam;
+
+INSERT INTO t2 VALUES
+  (1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+  (6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+  (11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+
+DROP TABLE t1,t2;
+
 set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 6740111..d19aca1 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1916,6 +1916,31 @@ pk	i
 1	10
 2	20
 DROP TABLE t1;
+#
+# MDEV-18956: Possible rowid filter for subquery for which
+#             in_to_exists strategy has been chosen
+#
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (
+pk int auto_increment PRIMARY KEY,
+i1 int, i2 int, c2 varchar(1),
+KEY (i1), KEY (i2)
+) engine=myisam;
+INSERT INTO t2 VALUES
+(1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+(6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+(11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+pk
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
+DROP TABLE t1,t2;
 set @@use_stat_tables=@save_use_stat_tables;
 #
 # MDEV-18755: possible RORI-plan and possible plan with range filter
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 676a73d..f96b340 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1621,12 +1621,22 @@ bool JOIN::make_range_rowid_filters()
     filter_map.merge(tab->table->with_impossible_ranges);
     bool force_index_save= tab->table->force_index;
     tab->table->force_index= true;
-    (void) sel->test_quick_select(thd, filter_map, (table_map) 0,
-                                  (ha_rows) HA_POS_ERROR,
-                                  true, false, true, true);
+    int rc= sel->test_quick_select(thd, filter_map, (table_map) 0,
+                                   (ha_rows) HA_POS_ERROR,
+                                   true, false, true, true);
     tab->table->force_index= force_index_save;
     if (thd->is_error())
       goto no_filter;
+    /*
+      If SUBS_IN_TO_EXISTS strtrategy is chosen for the subquery then
+      additional conditions are injected into WHERE/ON/HAVING and it may
+      happen that the call of test_quick_select() discovers impossible range.
+    */
+    if (rc == -1)
+    {
+      const_table_map|= tab->table->map;
+      goto no_filter;
+    }
     DBUG_ASSERT(sel->quick);
     filter_container=
       tab->range_rowid_filter_info->create_container();


More information about the commits mailing list