In deconstruct_distribute_oj_quals, when we've identified a commutable
left join which provides join clause with flexible semantics, we try to
generate multiple versions of the join clause. Here we have the logic
that puts back any ojrelids that were removed from its min_righthand.
/*
* Put any OJ relids that were removed from min_righthand back into
* ojscope, else distribute_qual_to_rels will complain.
*/
ojscope = bms_join(ojscope, bms_intersect(sjinfo->commute_below,
sjinfo->syn_righthand));
I doubt this is necessary. It seems to me that all relids mentioned
within the join clause have already been contained in ojscope, which is
the union of min_lefthand and min_righthand.
I noticed this code because I came across a problem with a query as
below.
create table t (a int);
select t1.a from (t t1 left join t t2 on true) left join (t t3 left join t t4 on t3.a = t4.a) on t2.a = t3.a;
When we deal with qual 't2.a = t3.a', deconstruct_distribute_oj_quals
would always add the OJ relid of t3/t4 into its required_relids, due to
the code above, which I think is wrong. The direct consequence is that
we would miss the plan that joins t2 and t3 directly.
If we add unique constraint for 'a' and try the outer-join removal
logic, we would notice that the left join of t2/t3 cannot be removed
because its join qual is treated as pushed down due to the fact that its
required_relids exceed the scope of the join. I think this is also not
correct.
So is it safe we remove that code?
Thanks
Richard