1 files changed, 111 insertions, 0 deletions
diff --git a/mysql-test/main/join_nested_jcl6.test b/mysql-test/main/join_nested_jcl6.test
new file mode 100644
index 00000000000..838427e85e6
--- /dev/null
+++ b/mysql-test/main/join_nested_jcl6.test
@@ -0,0 +1,111 @@
+# 
+# Run join_nested.test with BKA enabled 
+#
+
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='optimize_join_buffer_size=on';
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+
+set join_cache_level=6;
+show variables like 'join_cache_level';
+
+set @optimizer_switch_for_join_nested_test=@@optimizer_switch;
+set @join_cache_level_for_join_nested_test=@@join_cache_level;
+
+--source join_nested.test
+
+#
+# BUG#35835: queries with nested outer joins with BKA enabled
+#            
+
+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));
+CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+
+INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0);
+INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0);
+INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 
+       LEFT JOIN 
+       ( 
+         (t6, t7)
+         LEFT JOIN 
+         t8
+         ON t7.b=t8.b AND t6.b < 10 
+       )
+       ON t6.b >= 2 AND t5.b=t7.b AND
+          (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 
+       LEFT JOIN 
+       ( 
+         (t6, t7)
+         LEFT JOIN 
+         t8
+         ON t7.b=t8.b AND t6.b < 10
+       )
+       ON t6.b >= 2 AND t5.b=t7.b AND
+          (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
+
+DELETE FROM t5;
+DELETE FROM t6;
+DELETE FROM t7;
+DELETE FROM t8;
+
+INSERT INTO t5 VALUES (1,3,0), (3,2,0);
+INSERT INTO t6 VALUES (3,3,0);
+INSERT INTO t7 VALUES (1,2,0);
+INSERT INTO t8 VALUES (1,1,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.a=1, t8)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.a=1, t8)
+       ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.b=2, t8)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.b=2, t8)
+       ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t8, t6 LEFT JOIN t7 ON t7.a=1)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t8, t6 LEFT JOIN t7 ON t7.a=1)
+       ON (t5.b=t8.b);
+
+DROP TABLE t5,t6,t7,t8;
+
+set join_cache_level=default;
+show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_join_nested_test=NULL;
+set @join_cache_level_for_join_nested_test=NULL;