summaryrefslogtreecommitdiff
path: root/mysql-test/main/join_nested.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/join_nested.test')
-rw-r--r--mysql-test/main/join_nested.test38
1 files changed, 38 insertions, 0 deletions
diff --git a/mysql-test/main/join_nested.test b/mysql-test/main/join_nested.test
index cc1bd327031..8ac6b5a1d6b 100644
--- a/mysql-test/main/join_nested.test
+++ b/mysql-test/main/join_nested.test
@@ -1419,3 +1419,41 @@ DROP TABLE t1,t2,t3;
set join_cache_level= @save_join_cache_level;
set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # MDEV-27624: Nested left joins with not_exists optimization
+--echo # for most inner left join
+--echo #
+
+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);
+
+let $q=
+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;
+
+set join_cache_level = 0;
+eval EXPLAIN $q;
+eval $q;
+
+set join_cache_level = 2;
+eval EXPLAIN $q;
+eval $q;
+
+DROP TABLE t1, t2, t3;
+
+set join_cache_level= @save_join_cache_level;
+
+--echo # end of 10.3 tests