Re: Assert failure of the cross-check for nullingrels - Mailing list pgsql-hackers

From Richard Guo
Subject Re: Assert failure of the cross-check for nullingrels
Date
Msg-id CAMbWs48VKcu-eYnqXTpFsbSqPve1aqcGv=mmYaJ4qNC-qUWTdQ@mail.gmail.com
Whole thread Raw
In response to Re: Assert failure of the cross-check for nullingrels  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Assert failure of the cross-check for nullingrels
Re: Assert failure of the cross-check for nullingrels
List pgsql-hackers

On Fri, May 19, 2023 at 12:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bleah.  The other solution I'd been poking at involved adding an
extra check for clone clauses, as attached (note this requires
8a2523ff3).  This survives your example, but I wonder if it might
reject all the clones in some cases.  It seems a bit expensive
too, although as I said before, I don't think the clone cases get
traversed all that often.

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.
 
Perhaps another answer could be to compare against syn_righthand
for clone clauses and min_righthand for non-clones?  That seems
mighty unprincipled though.

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

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: PG 16 draft release notes ready
Next
From: "Zhijie Hou (Fujitsu)"
Date:
Subject: RE: Reload configuration more frequently in apply worker.