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

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> That's right. This issue has something to do with the
> outer-join-aware-Var changes. I reduced the repro to the query below.

Thanks for the simplified test case.

> 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.

It looks to me like we are trying to join (2 7), that is s1 and t3,
to 8 (t4), which would necessitate forming the outer join with relid 11.
That's fine as far as it goes, but the path we're trying to use for
(2 7) is

{NESTPATH
:jpath.path.pathtype 335
:parent_relids (b 2 7)
:required_outer (b 1 9 10 11)
:jpath.outerjoinpath
{SUBQUERYSCANPATH
:path.pathtype 326
:parent_relids (b 2)
:required_outer (b 1)
:jpath.innerjoinpath
{INDEXPATH
:path.pathtype 321
:parent_relids (b 7) t3
:required_outer (b 9 10 11) t5 and both outer joins

That is, the path involves an indexscan on t3 that evidently is using
the "t3.a = coalesce(t5.a,1)" condition, so it needs a post-join value
of t5.a. So it's completely not legit to use this path as an input
for this join. (You could quibble about whether the path could be
marked as needing only one of the two outer joins, but that doesn't
really matter here. It certainly shouldn't be used when we've not
yet formed either OJ.)

So it looks to me like something further up should have rejected this
path as not being usable here. Not sure what's dropping the ball.

Another way to look at it is we should never have formed this index
path at all, because it's not clear to me that it can have any valid
use. We clearly cannot form OJ 11 (t3/t4) without having already
scanned t3, so a path for t3 that requires 11 as an input is silly on
its face. Even if you argue that the required_outer marking for the
path could be reduced to (9 10) on the grounds of identity 3, I still
don't see a valid join order that can use this path. So ideally the
path wouldn't have been made in the first place, it's just a waste
of planner cycles. That's a separate issue though.

regards, tom lane