Re: Assert !bms_overlap(joinrel->relids, required_outer)

On Wed, Jun 28, 2023 at 10:09 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> However, given that what we need is to exclude parameterization
> that depends on the currently-formed OJ, it seems to me we can do
> it more simply and without any new JoinPathExtraData field,
> as attached. What do you think?

I think it makes sense. At first I wondered if we should also exclude
parameterization that depends on OJs that have already been formed as
part of this joinrel. But it seems not possible that the input paths
have parameterization dependency on these OJs. So it should be
sufficient to only consider the currently-formed OJ.

> > * I think we need to check the incompatible relids also in
> > try_hashjoin_path and try_mergejoin_path besides try_nestloop_path.
>
> I think this isn't necessary, at least in my formulation.
> Those cases will go through calc_non_nestloop_required_outer
> which has
>
> /* neither path can require rels from the other */
> Assert(!bms_overlap(outer_paramrels, inner_path->parent->relids));
> Assert(!bms_overlap(inner_paramrels, outer_path->parent->relids));
>
> In order to have a dependency on an OJ, a path would have to have
> a dependency on at least one of the OJ's base relations too, so
> I think these assertions show that the case won't arise. (Of
> course, if someone can trip one of these assertions, I'm wrong.)

Hmm, while this holds in most cases, it does not if the joins have been
commuted according to identity 3. If we change the t3/t4 join's qual to
't3.a = t4.a' to make hashjoin possible, we'd see the same Assert
failure through try_hashjoin_path. I think it's also possible for merge
join.

explain (costs off)
select 1 from t t1
join lateral
(select t1.a from (select 1) foo offset 0) s1 on true
join
(select 1 from t t2
inner join t t3
left join t t4 left join t t5 on t4.a = 1
on t3.a = t4.a on false
where t3.a = coalesce(t5.a,1)) as s2
on true;
server closed the connection unexpectedly

Thanks
Richard