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.lepikhov@postgrespro.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