On Tue, Jun 27, 2023 at 1:35 PM Michael Paquier <mich...@paquier.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