Merge 10.5 into 10.6

1 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index 221a3118b8a..26081382368 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -2008,6 +2008,58 @@ Note	1003	select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`
 DROP TABLE t1,t2,t3;
 set join_cache_level= @save_join_cache_level;
 set optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-27624: Nested left joins with not_exists optimization
+#             for most inner left join
+#
+set @save_join_cache_level= @@join_cache_level;
+CREATE TABLE t1 (a INT NOT NULL, b INT, c INT);
+INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1);
+CREATE TABLE t2(a INT NOT NULL);
+INSERT INTO t2 VALUES (1), (2);
+CREATE TABLE t3(a INT not null, b INT);
+INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1);
+set join_cache_level = 0;
+EXPLAIN SELECT *
+FROM t1
+LEFT JOIN
+( t2 LEFT JOIN t3 ON t2.a = t3.b )
+ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+WHERE t1.c = 1 AND t3.a is NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists
+SELECT *
+FROM t1
+LEFT JOIN
+( t2 LEFT JOIN t3 ON t2.a = t3.b )
+ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+WHERE t1.c = 1 AND t3.a is NULL;
+a	b	c	a	a	b
+1	3	1	NULL	NULL	NULL
+set join_cache_level = 2;
+EXPLAIN SELECT *
+FROM t1
+LEFT JOIN
+( t2 LEFT JOIN t3 ON t2.a = t3.b )
+ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+WHERE t1.c = 1 AND t3.a is NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists; Using join buffer (incremental, BNL join)
+SELECT *
+FROM t1
+LEFT JOIN
+( t2 LEFT JOIN t3 ON t2.a = t3.b )
+ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+WHERE t1.c = 1 AND t3.a is NULL;
+a	b	c	a	a	b
+1	3	1	NULL	NULL	NULL
+DROP TABLE t1, t2, t3;
+set join_cache_level= @save_join_cache_level;
+# end of 10.3 tests
 CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));