MDEV-22534 fixing remaining test regressions with --recordbb-11.1-mdev-22534

All explain output diffs. Looks like either improvements or
inconsequential (not 100% sure), also ran extra selects statements
near the diffs to for sanity check.

4 files changed, 39 insertions, 23 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index da6c848e334..ad8661ffb2d 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -3433,7 +3433,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	a1	ALL	NULL	NULL	NULL	NULL	60	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` between 3 and 5 and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`))))
+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`,`test`.`t1`.`pk`),<exists>(/* select#2 */ select `test`.`t2`.`c1`,`test`.`t2`.`i1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` between 3 and 5 and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`) and (<cache>(`test`.`t1`.`pk`) = `test`.`t2`.`i1` or `test`.`t2`.`i1` is null) having `test`.`t2`.`i1` is null)))
 SELECT * FROM t1
 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
 WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 29e7e11f0ba..45b0d01c688 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -225,7 +225,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2a	unique_subquery	PRIMARY	PRIMARY	8	const,test.t1.pk	1	Using index; Using where; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2a	unique_subquery	PRIMARY	PRIMARY	8	const,func	1	Using index; Using where; Full scan on NULL key
 SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk);
 pk	i
 SELECT * FROM t1 WHERE 1+NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk);
@@ -265,7 +265,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2c	index_subquery	it2c	it2c	8	const,test.t1.pk	1	Using index; Using where; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2c	index_subquery	it2c	it2c	8	const,func	1	Using index; Using where; Full scan on NULL key
 SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk);
 pk	i
 SELECT * FROM t1 WHERE NULL     IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) IS UNKNOWN;
@@ -303,7 +303,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2a	unique_subquery	PRIMARY	PRIMARY	8	const,test.t1.pk	1	Using index; Using where; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2a	const	PRIMARY	PRIMARY	8	const,const	1	Using where; Using index; Full scan on NULL key
 SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk);
 pk	i
 0	10
@@ -335,7 +335,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2c	index_subquery	it2c	it2c	8	const,test.t1.pk	1	Using index; Using where; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2c	index_subquery	it2c	it2c	8	const,const	0	Using index; Using where; Full scan on NULL key
 SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk);
 pk	i
 0	10
diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result
index ead53edf311..428c5af481a 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.result
+++ b/mysql-test/main/subselect_extra_no_semijoin.result
@@ -454,7 +454,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
-2	DEPENDENT SUBQUERY	<derived3>	ref	key1	key1	8	const,const	0	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	8	const,const	0	Using where
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
@@ -476,7 +476,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	5	test.t1.a	1	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	10	func,func	1	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a);
 a	b
diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result
index d756775226c..b7d9383a4a1 100644
--- a/mysql-test/main/update_use_source.result
+++ b/mysql-test/main/update_use_source.result
@@ -504,11 +504,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -1777,11 +1779,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -3051,11 +3055,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -4324,11 +4330,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -5598,11 +5606,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -6871,11 +6881,13 @@ test.t1	analyze	status	OK
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -8145,11 +8157,13 @@ test.t1	analyze	note	The storage engine for the table doesn't support analyze
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0
@@ -9410,11 +9424,13 @@ test.t1	analyze	note	The storage engine for the table doesn't support analyze
 explain select * from t1 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 explain update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	
-1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	a	ALL	NULL	NULL	NULL	NULL	32	
 update t1 set c3=c3+110 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
 affected rows: 32
 info: Rows matched: 32  Changed: 32  Warnings: 0