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

Part#2: take into account that join nest that we are marking as constant
might already have constant tables in it. Don't count these tables twice.

1 files changed, 15 insertions, 0 deletions

diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result
index aa9f30885a9..6b671a0759f 100644
--- a/mysql-test/main/join_outer.result
+++ b/mysql-test/main/join_outer.result
@@ -2564,4 +2564,19 @@ drop table t1,t2,t3;
 set optimizer_use_condition_selectivity= @tmp1;
 set use_stat_tables= @tmp2;
 set histogram_size= @tmp3;
+# Another test
+CREATE TABLE t1 (i1 int) ;
+CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ;
+CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ;
+INSERT INTO t3 VALUES (2, NULL);
+CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ;
+CREATE VIEW v4 AS SELECT * FROM t4;
+SELECT 1
+FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1
+LEFT JOIN v4
+RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk
+WHERE t3.pk IN (2);
+1
+drop view v4;
+drop table t1,t2,t3,t4;
 SET optimizer_switch=@save_optimizer_switch;