Fix another cause of "wrong varnullingrels" planner failures.

I removed the delay_upper_joins mechanism in commit b448f1c8d,
reasoning that it was only needed when we have a single-table
(SELECT ... WHERE) as the immediate RHS child of a left join,
and we could get rid of that by hoisting the WHERE condition into
the parent join's quals.  However that new code missed a case:
we could have "foo LEFT JOIN ((SELECT ... WHERE) LEFT JOIN bar)",
and if the two left joins can be commuted then we now have the
problematic query shape.  We can fix this too easily enough,
by allowing the syntactically-lower left join to pass through
its parent qual location pointer recursively.  That lets
prepjointree.c discard the SELECT by temporarily hoisting the
WHERE condition into the ancestor join's qual.

Per bug #17978 from Zuming Jiang.

Discussion: https://postgr.es/m/[email protected]

1 files changed, 28 insertions, 3 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index cd1163d039b..6917faec141 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2531,20 +2531,45 @@ select * from int4_tbl t1
     on s.f1 = t1.f1;
                    QUERY PLAN                    
 -------------------------------------------------
+ Hash Right Join
+   Hash Cond: (t2.f1 = t1.f1)
+   ->  Nested Loop Left Join
+         Join Filter: (t2.f1 > 1)
+         ->  Nested Loop Left Join
+               Join Filter: (t2.f1 > 0)
+               Filter: (t3.f1 IS NULL)
+               ->  Seq Scan on int4_tbl t2
+               ->  Materialize
+                     ->  Seq Scan on int4_tbl t3
+         ->  Seq Scan on tenk1 t4
+   ->  Hash
+         ->  Seq Scan on int4_tbl t1
+(13 rows)
+
+explain (costs off)
+select * from int4_tbl t1
+  left join ((select t2.f1 from int4_tbl t2
+                left join int4_tbl t3 on t2.f1 > 0
+                where t2.f1 <> coalesce(t3.f1, -1)) s
+             left join tenk1 t4 on s.f1 > 1)
+    on s.f1 = t1.f1;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
  Nested Loop Left Join
    Join Filter: (t2.f1 > 1)
    ->  Hash Right Join
          Hash Cond: (t2.f1 = t1.f1)
          ->  Nested Loop Left Join
                Join Filter: (t2.f1 > 0)
-               Filter: (t3.f1 IS NULL)
+               Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer))
                ->  Seq Scan on int4_tbl t2
                ->  Materialize
                      ->  Seq Scan on int4_tbl t3
          ->  Hash
                ->  Seq Scan on int4_tbl t1
-   ->  Seq Scan on tenk1 t4
-(13 rows)
+   ->  Materialize
+         ->  Seq Scan on tenk1 t4
+(14 rows)
 
 explain (costs off)
 select * from onek t1