Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

 

Thanks for the report!  I think this is a new issue that was not
reported before.  I simplify this query a little for easy debugging as

# explain (costs off)
select * from t1 left join t2 on true left join t3 on t2.x where t3.x is null;
               QUERY PLAN
----------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on t1
   ->  Materialize
         ->  Nested Loop Left Join
               Join Filter: t2.x
               Filter: (t3.x IS NULL)
               ->  Seq Scan on t2
               ->  Materialize
                     ->  Seq Scan on t3
(9 rows)

The qual 't3.x IS NULL' is placed at the wrong place.  This qual's Var
't3.x' is marked with t2/t3 join in its varnullingrels by the parser,
which is right for the user-given order.  After we've commuted t1/t2
join and t2/t3 join, Var 't3.x' can actually be nulled by both t2/t3
join and t1/t2 join.  We neglect to adjust this qual accordingly in this
case.

ISTM that for outer join identity 3, if we are given form
    (A leftjoin B on (Pab)) leftjoin C on (Pbc)
then references to C Vars in higher qual levels would be marked with the
B/C join.  If we've transformed it to form
    A leftjoin (B leftjoin C on (Pbc)) on (Pab)
then references to C Vars in higher qual levels should be adjusted to
include both B/C join and A/B join in their varnullingrels.

References to A Vars and B Vars in higher qual levels do not have this
problem though.

Thanks
Richard