Hi hackers,
While wandering around the codes of reducing outer joins, I noticed that
when determining which base rels/Vars are forced nonnullable by given
clause, we don't take SubPlan into consideration. Does anyone happen to
know what is the concern behind that?
IMO, for SubPlans of type ALL/ANY/ROWCOMPARE, we should be able to find
additional nonnullable rels/Vars by descending through their testexpr.
As we know, ALL_SUBLINK/ANY_SUBLINK combine results across tuples
produced by the subplan using AND/OR semantics. ROWCOMPARE_SUBLINK
doesn't allow multiple tuples from the subplan. So we can tell whether
the subplan is strict or not by checking its testexpr, leveraging the
existing codes in find_nonnullable_rels/vars_walker. Below is an
example:
# explain (costs off)
select * from a left join b on a.i = b.i where b.i = ANY (select i from c where c.j = b.j);
QUERY PLAN
-----------------------------------
Hash Join
Hash Cond: (b.i = a.i)
-> Seq Scan on b
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on c
Filter: (j = b.j)
-> Hash
-> Seq Scan on a
(9 rows)
BTW, this change would also have impact on SpecialJoinInfo, especially
for the case of identity 3, because find_nonnullable_rels() is also used
to determine strict_relids from the clause. As an example, consider
select * from a left join b on a.i = b.i
left join c on b.j = ANY (select j from c);
Now we can know the SubPlan is strict for 'b'. Thus the b/c join would
be considered to be legal.
Thanks
Richard
