BUG#16393: Let the 'ref' optimizer use ON condition of nested join to construct

'ref' accesses to all tables that are direct children of the nested join and are 
not inner wrt their siblings.


mysql-test/r/join_nested.result:
  Testcase for BUG#16393
mysql-test/t/join_nested.test:
  Testcase for BUG#16393

1 files changed, 14 insertions, 0 deletions

diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 9f23e2d0e2f..145edded486 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -900,3 +900,17 @@ explain select * from t2 left join
    join t5 on t5.a=t3.b) on t3.a=t2.b;
 
 drop table t0, t1, t2, t3, t4, t5, t6, t7;
+
+# BUG#16393
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, filler char(100), key(a));
+insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B;
+create table t3 like t2;
+insert into t3 select * from t2;
+
+explain select * from t1 left join 
+  (t2 left join t3 on (t2.a = t3.a)) 
+  on (t1.a = t2.a);
+drop table t1, t2, t3;
+