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.

1 files changed, 30 insertions, 0 deletions

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