Re: Assert failure of the cross-check for nullingrels

I tried with v4 patch and find that, as you predicted, it might reject
all the clones in some cases.  Check the query below

explain (costs off)
select * from t t1
    left join t t2 on t1.a = t2.a
    left join t t3 on t2.a = t3.a
    left join t t4 on t3.a = t4.a and t2.b = t4.b;
                QUERY PLAN
------------------------------------------
 Hash Left Join
   Hash Cond: (t2.b = t4.b)
   ->  Hash Left Join
         Hash Cond: (t2.a = t3.a)
         ->  Hash Left Join
               Hash Cond: (t1.a = t2.a)
               ->  Seq Scan on t t1
               ->  Hash
                     ->  Seq Scan on t t2
         ->  Hash
               ->  Seq Scan on t t3
   ->  Hash
         ->  Seq Scan on t t4
(13 rows)

So the qual 't3.a = t4.a' is missing in this plan shape.

 

I also checked this solution with the same query.

explain (costs off)
select * from t t1
    left join t t2 on t1.a = t2.a
    left join t t3 on t2.a = t3.a
    left join t t4 on t3.a = t4.a and t2.b = t4.b;
                            QUERY PLAN
------------------------------------------------------------------
 Hash Left Join
   Hash Cond: ((t3.a = t4.a) AND (t3.a = t4.a) AND (t2.b = t4.b))
   ->  Hash Left Join
         Hash Cond: (t2.a = t3.a)
         ->  Hash Left Join
               Hash Cond: (t1.a = t2.a)
               ->  Seq Scan on t t1
               ->  Hash
                     ->  Seq Scan on t t2
         ->  Hash
               ->  Seq Scan on t t3
   ->  Hash
         ->  Seq Scan on t t4
(13 rows)

This time the qual 't3.a = t4.a' is back, but twice.

I keep thinking about my proposal in v2 patch.  It seems more natural to
me to fix this issue, because an outer join's quals are always treated
as a whole when we check if identity 3 applies in make_outerjoininfo, as
well as when we adjust the outer join's quals for commutation in
deconstruct_distribute_oj_quals.  So when it comes to check if quals are
computable at a join level, they should be still treated as a whole.
This should have the same effect regarding qual placement if the quals
of an outer join are in form of 'qual1 OR qual2 OR ...' rather than
'qual1 AND qual2 AND ...'.

Thanks
Richard