Merge branch '10.4' into 10.5

1 files changed, 42 insertions, 0 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index ae52f9416b9..6c8b5eda8e8 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2968,3 +2968,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