On Tue, Jun 27, 2023 at 10:12 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Richard Guo <guofengli...@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.)
I tried this query on v15 and found that we'd also generate this bogus
path for the t3/t4 join.
{NESTPATH
:pathtype 38
:parent_relids (b 2 7)
:required_outer (b 1 9)
:outerjoinpath
{SUBQUERYSCANPATH
:pathtype 28
:parent_relids (b 2)
:required_outer (b 1)
:innerjoinpath
{INDEXPATH
:pathtype 23
:parent_relids (b 7) t3
:required_outer (b 9) t5
The Assert failure is not seen on v15 because outer join relids are not
included in joinrel's relids and required_outer sets.
Thanks
Richard
