Re: Assert failure of the cross-check for nullingrels

On Fri, May 19, 2023 at 12:33 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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