revision-id: 6450aab26c73d0522da40b2ece0552dc2113f7a4 (mariadb-10.4.11-238-g6450aab) parent(s): bf2a244406c36cd12bc53f9a30c8a2cab191f235 author: Igor Babaev committer: Igor Babaev timestamp: 2021-12-22 18:57:19 -0800 message: MDEV-22846 Server crashes in handler_index_cond_check on SELECT If the optimizer decides to rewrites a NOT IN predicand of the form outer_expr IN (SELECT inner_col FROM ... WHERE subquery_where) into the EXISTS subquery EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_col OR inner_col IS NULL)) then the pushed equality predicate outer_expr=inner_col can be used for ref[or_null] access if inner_col is a reference to an indexed column. In this case if there is a selective range condition over this column then a Rowid filter may be employed coupled the with ref[or_null] access. The filter is 'pushed' into the engine and in InnoDB currently it cannot be used with index look-ups by primary key. The ref[or_null] access can be used only when outer_expr is not NULL. Otherwise the original predicand is evaluated to TRUE only if the result set returned by the query SELECT 1 FROM ... WHERE subquery_where is empty. When performing this evaluation the executor switches to the table scan by primary key. Before this patch the pushed filter still remained marked as active and the engine tried to apply the filter. This was incorrect and in InnoDB this attempt to use the filter led to an assertion failure. This patch fixes the problem by disabling usage of the filter when outer_expr is evaluated to NULL. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/rowid_filter_innodb.result | 42 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 34 ++++++++++++++++++++++++ sql/handler.h | 16 ++++++++++++ sql/item_subselect.cc | 4 +++ 4 files changed, 96 insertions(+) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 05d97b7..fcdaa94 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2913,3 +2913,45 @@ set optimizer_switch=@save_optimizer_switch; set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; +# +# MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +# +CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb; +INSERT INTO t1 VALUES +(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +pk c1 +15 o +16 x +19 t +35 k +36 h +43 h +53 l +62 a +71 NULL +128 y +129 NULL +133 NULL +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 6 (33%) 33.33 Using where; Full scan on NULL key; Using rowid filter +2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 15 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` = `test`.`t1`.`pk` and `test`.`t2`.`i1` is not null and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`)))) +DROP TABLE t1,t2; +# End of 10.4 tests diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 74349b8..874f8d3 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -534,3 +534,37 @@ set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; + +--echo # +--echo # MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +--echo # + +# set @stats.save= @@innodb_stats_persistent; +# set global innodb_stats_persistent=on; + +CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb; +INSERT INTO t1 VALUES +(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); + +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; + +let $q= +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 + WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2; + +# set global innodb_stats_persistent= @stats.save; + +--echo # End of 10.4 tests diff --git a/sql/handler.h b/sql/handler.h index cdcb117..0a429f1 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3095,6 +3095,8 @@ class handler :public Sql_alloc /* Rowid filter pushed into the engine */ Rowid_filter *pushed_rowid_filter; + /* Used for disabling/enabling pushed_rowid_filter */ + Rowid_filter *save_pushed_rowid_filter; /* true when the pushed rowid filter has been already filled */ bool rowid_filter_is_active; @@ -3167,6 +3169,7 @@ class handler :public Sql_alloc pushed_idx_cond(NULL), pushed_idx_cond_keyno(MAX_KEY), pushed_rowid_filter(NULL), + save_pushed_rowid_filter(NULL), rowid_filter_is_active(0), auto_inc_intervals_count(0), m_psi(NULL), set_top_table_fields(FALSE), top_table(0), @@ -4224,6 +4227,19 @@ class handler :public Sql_alloc rowid_filter_is_active= false; } + virtual void disable_pushed_rowid_filter() + { + save_pushed_rowid_filter= pushed_rowid_filter; + pushed_rowid_filter= NULL; + rowid_filter_is_active= false; + } + + virtual void enable_pushed_rowid_filter() + { + pushed_rowid_filter= save_pushed_rowid_filter; + rowid_filter_is_active= true; + } + virtual bool rowid_filter_push(Rowid_filter *rowid_filter) { return true; } /* Needed for partition / spider */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c50f87c..8204586 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3924,6 +3924,8 @@ int subselect_single_select_engine::exec() tab->save_read_record= tab->read_record.read_record_func; tab->read_record.read_record_func= rr_sequential; tab->read_first_record= read_first_record_seq; + if (tab->rowid_filter) + tab->table->file->disable_pushed_rowid_filter(); tab->read_record.thd= join->thd; tab->read_record.ref_length= tab->table->file->ref_length; tab->read_record.unlock_row= rr_unlock_row; @@ -3944,6 +3946,8 @@ int subselect_single_select_engine::exec() tab->read_record.ref_length= 0; tab->read_first_record= tab->save_read_first_record; tab->read_record.read_record_func= tab->save_read_record; + if (tab->rowid_filter) + tab->table->file->enable_pushed_rowid_filter(); } executed= 1; if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN) &&