Fixed LP bug #675516.

When pushing the condition for a table in the function
JOIN_TAB::make_scan_filter the optimizer must not push
conditions from WHERE if the table is some inner table
of an outer join..

1 files changed, 46 insertions, 0 deletions

diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index a44de1b5e71..c7acf434011 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2579,5 +2579,51 @@ SET SESSION join_cache_level = DEFAULT;
 
 DROP TABLE t1,t2,t3,t4,t5;
 
+--echo #
+--echo # Bug #675516: nested outer join with 3 tables in the nest
+--echo #             using BNL + BNLH 
+--echo #
+
+CREATE TABLE t1 (a1 int, b1 int, c1 int) ;
+INSERT INTO t1 VALUES (7,8,0), (6,4,0);
+
+CREATE TABLE t2 (a2 int) ;
+INSERT INTO t2 VALUES (5);
+
+CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ;
+INSERT INTO t3 VALUES (2,5,0);
+
+CREATE TABLE t4 (a4 int, b4 int, c4 int) ;
+INSERT INTO t4 VALUES (7,8,0);
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3,t4;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;