DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE; CREATE TABLE t1 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t2 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t3 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t4 AS SELECT true AS x FROM generate_series(0,1) x; ANALYZE;
EXPLAIN (ANALYZE, COSTS OFF) SELECT ALL t1.x FROM t1, t2 LEFT OUTER JOIN t3 ON t3.x LEFT OUTER JOIN t4 ON t3.x WHERE t4.x ISNULL;
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.
