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.

4 files changed, 102 insertions, 0 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index af7ff3210a5..faa9714bb99 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2919,3 +2919,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 74349b8c6bb..d121405f08d 100644
--- a/mysql-test/main/rowid_filter_innodb.test
+++ b/mysql-test/main/rowid_filter_innodb.test
@@ -534,3 +534,33 @@ 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 #
+
+
+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;
+
+--echo # End of 10.4 tests
diff --git a/sql/handler.h b/sql/handler.h
index 815641b49a8..71b30abeba1 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -3147,6 +3147,9 @@ public:
   Rowid_filter *pushed_rowid_filter;
   /* true when the pushed rowid filter has been already filled */
   bool rowid_filter_is_active;
+  /* Used for disabling/enabling pushed_rowid_filter */
+  Rowid_filter *save_pushed_rowid_filter;
+  bool save_rowid_filter_is_active;
 
   Discrete_interval auto_inc_interval_for_cur_row;
   /**
@@ -3214,6 +3217,8 @@ public:
     pushed_idx_cond_keyno(MAX_KEY),
     pushed_rowid_filter(NULL),
     rowid_filter_is_active(0),
+    save_pushed_rowid_filter(NULL),
+    save_rowid_filter_is_active(false),
     auto_inc_intervals_count(0),
     m_psi(NULL), set_top_table_fields(FALSE), top_table(0),
     top_table_field(0), top_table_fields(0),
@@ -4258,6 +4263,27 @@ public:
    rowid_filter_is_active= false;
  }
 
+ virtual void disable_pushed_rowid_filter()
+ {
+   DBUG_ASSERT(pushed_rowid_filter != NULL &&
+               save_pushed_rowid_filter == NULL);
+   save_pushed_rowid_filter= pushed_rowid_filter;
+   if (rowid_filter_is_active)
+     save_rowid_filter_is_active= rowid_filter_is_active;
+   pushed_rowid_filter= NULL;
+   rowid_filter_is_active= false;
+ }
+
+ virtual void enable_pushed_rowid_filter()
+ {
+   DBUG_ASSERT(save_pushed_rowid_filter != NULL &&
+               pushed_rowid_filter == NULL);
+   pushed_rowid_filter= save_pushed_rowid_filter;
+   if (save_rowid_filter_is_active)
+     rowid_filter_is_active= true;
+   save_pushed_rowid_filter= NULL;
+ }
+
  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 82b40966e4f..56ab0f648ee 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -4026,6 +4026,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;
@@ -4046,6 +4048,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) &&