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

On Wed, Feb 22, 2023 at 2:48 PM Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
wrote:

> 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.

Thanks
Richard