Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

On Wed, Mar 1, 2023 at 3:10 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Here's said patch. Although this fixes the described problem and
> passes check-world, I'm not totally happy with it yet: it feels
> like the new add_outer_joins_to_relids() function is too expensive
> to be doing every time we construct a join relation.

It seems that this change may affect how we select the appropriate
outer-join clause from redundant versions of that clause for an outer
join, because we make that decision relying on the joinrelids of the
outer join and outer joins below. If we've decided not to add the outer
join's relid to an outer join, we'd choose a clause that does not
contain that outer join's relid. As a result, we may have mismatched
nullingrels in joinqual and the join's target entry. I see this problem
in the query below.

select * from t1 left join t2 on true left join t3 on t2.x left join t4 on
t3.x;

When we build the join of t2/t3 to t4, we have two versions of the
joinqual 't3.x', one with t2/t3 join in the nullingrels, and one
without. The latter one would be chosen since we haven't added t2/t3
join's ojrelid. However, the targetlist of t2/t3 join would have the
t3 Vars marked with the join's ojrelid. So that we see the mismatched
nullingrels.

Do we need to revise how we build target list for outer join by
adjusting the nullingrels of Vars and PHVs from input_rel in a similar
way?

Thanks
Richard