Follow-up to the previous: The same optimization suppression needs to

happen if the left-hand side is coming from a LEFT JOIN.

FossilOrigin-Name: cf5b37b3a39013d8ca9de92da2289346caf52b56daff59e19b140cc586a3421f

1 files changed, 16 insertions, 6 deletions

diff --git a/test/join.test b/test/join.test
index 789ae0124..ef2f6335c 100644
--- a/test/join.test
+++ b/test/join.test
@@ -1316,20 +1316,30 @@ do_execsql_test join-31.1 {
   CREATE TABLE t1(c0 INT , c1 INT); INSERT INTO t1(c0, c1) VALUES(NULL,11);
   CREATE TABLE t2(c0 INT NOT NULL);
   CREATE TABLE t2n(c0 INT);
-  CREATE TABLE t3(x INT);           INSERT INTO t3(x) VALUES(4);
-  CREATE TABLE t5(c0 INT, x INT);   INSERT INTO t5 VALUES(NULL, 4);
+  CREATE TABLE t3(x INT);           INSERT INTO t3(x) VALUES(3);
+  CREATE TABLE t4(y INT);           INSERT INTO t4(y) VALUES(4);
+  CREATE TABLE t5(c0 INT, x INT);   INSERT INTO t5 VALUES(NULL, 5);
 }
 do_execsql_test join-31.2 {
   SELECT * FROM t2 RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
-} {NULL 4 NULL}
+} {NULL 3 NULL}
 do_execsql_test join-31.3 {
   SELECT * FROM t2 RIGHT JOIN t3 ON true NATURAL LEFT JOIN t1;
-} {NULL 4 NULL}
+} {NULL 3 NULL}
 do_execsql_test join-31.4 {
   SELECT * FROM t2n RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
-} {NULL 4 NULL}
+} {NULL 3 NULL}
 do_execsql_test join-31.5 {
   SELECT * FROM t5 LEFT JOIN t1 USING(c0);
-} {NULL 4 NULL}
+} {NULL 5 NULL}
+do_execsql_test join-31.6 {
+  SELECT * FROM t3 LEFT JOIN t2 ON true LEFT JOIN t1 USING(c0);
+} {3 NULL NULL}
+do_execsql_test join-31.7 {
+  SELECT * FROM t3 LEFT JOIN t2 ON true NATURAL LEFT JOIN t1;
+} {3 NULL NULL}
+do_execsql_test join-31.8 {
+  SELECT * FROM t3 LEFT JOIN t2 ON true JOIN t4 ON true NATURAL LEFT JOIN t1;
+} {3 NULL 4 NULL}
 
 finish_test