Merge 10.3 into 10.4

1 files changed, 93 insertions, 0 deletions

diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index 39c0d1dd22b..e208dfe74c1 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -2391,6 +2391,99 @@ ec70316637232000158bbfc8bcbe5d60
 ebb4620037332000158bbfc8bcbe5d89
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-16751: Server crashes in st_join_table::cleanup or 
+# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2
+#
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=4;
+CREATE TABLE t1 ( id int NOT NULL);
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ;
+INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	4	test.t2.i1	9	Using where; Using join buffer (flat, BNLH join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+set @@join_cache_level= @save_join_cache_level;
+alter table t1 add key(id);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+drop table t1,t2;
+#
+# MDEV-15454: Nested SELECT IN returns wrong results
+#
+CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY);
+CREATE TABLE t2 ( a int, b int );
+INSERT INTO t2  VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96);
+CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b));
+INSERT INTO t3 (c, b) VALUES (27, 96);
+CREATE PROCEDURE prepare_data()
+BEGIN
+DECLARE i INT DEFAULT 1;
+WHILE i < 1000 DO
+INSERT INTO t1 (a) VALUES (i);
+INSERT INTO t2 (a,b) VALUES (i,56);
+INSERT INTO t3 (c,b) VALUES (i,i);
+SET i = i + 1;
+END WHILE;
+END$$
+CALL prepare_data();
+SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27);
+a
+7878
+3465
+1403
+4189
+8732
+5
+set @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch='materialization=off';
+SELECT t1.a FROM t1
+WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5;
+a
+5
+SET optimizer_switch='materialization=on';
+SELECT t1.a FROM t1
+WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5;
+a
+5
+drop procedure prepare_data;
+set @@optimizer_switch= @save_optimizer_switch;
+drop table t1,t2,t3;
+CREATE TABLE t1 ( id int NOT NULL, key(id));
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL);
+INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
+CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
+explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
+1
+1
+1
+1
+drop table t1,t2;
+drop view v1;
 # End of 5.5 tests
 #
 # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT