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

Richard Guo <guofenglinux@gmail.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.)