MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins

1 files changed, 20 insertions, 0 deletions

diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test
index 6d20c089bd9..28f544dec7d 100644
--- a/mysql-test/main/join_outer.test
+++ b/mysql-test/main/join_outer.test
@@ -2042,4 +2042,24 @@ DROP TABLE t1,t2;
 
 --echo # end of 5.5 tests
 
+--echo #
+--echo # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2(a int);
+insert into t2 values (0),(1);
+
+create table t3 (a int, b int, key(a));
+insert into t3 select A.a + B.a* 10 + C.a * 100, 12345 from t1 A, t1 B, t1 C;
+
+--echo # Uses range for table t3:
+explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5;
+
+--echo # This must use range for table t3, too:
+explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5;
+
+drop table t1,t2,t3;
+
 SET optimizer_switch=@save_optimizer_switch;