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