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

On Tue, Jun 27, 2023 at 1:35 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Mon, Jun 26, 2023 at 11:05:43PM -0500, Jaime Casanova wrote:
> > The attached query makes beta2 crash with attached backtrace.
> > Interestingly the index on ref_6 is needed to make it crash, without
> > it the query works fine.
>
> Issue reproduced here. I am adding an open item, whose owner should
> be Tom?

That's right. This issue has something to do with the
outer-join-aware-Var changes. I reduced the repro to the query below.

create table t (a int);
create index on t(a);

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 t4.a = 1 on false
where t3.a = coalesce(t5.a,1)) as s2
on true;

When joining s1/t3 to t4, the relid of outer join t3/t4 appears both in
the joinrel's relids and in the joinrel's required outer rels, which
causes the Assert failure. I think it's reasonable for it to appear in
the joinrel's relids, because we're forming this outer join. I doubt
that it should appear in the joinrel's required outer rels. So I'm
wondering if we can fix this issue by manually removing the outer join's
relid from the joinrel's required_outer, something like:

if (bms_is_member(extra->sjinfo->ojrelid, joinrel->relids))
required_outer = bms_del_member(required_outer,
extra->sjinfo->ojrelid);

This would be needed in try_nestloop_path, try_mergejoin_path and
try_hashjoin_path after the required_outer set is computed for the join
path. It seems quite hacky though, not sure if this is the right thing
to do.

Thanks
Richard