Disallow joins whose lateral references can never be satisfied If a proposed join has lateral references to rels outside it, join_is_legal() checks that those rels can still be joined to from outside. To do that it collects the rels that must end up on the inner side of an outer join with the proposed join, directly or indirectly, and rejects the join if any of its lateral references point to one of them.
That search followed only outer joins, from min_lefthand to min_righthand. It failed to notice that a rel that laterally references the proposed join, or any rel found by the search, cannot supply a parameter to the proposed join either, and that the same holds for rels on the inner side of an outer join with such a rel. As a result, we could approve a join whose lateral references could never be satisfied. Such a join can never appear in a complete plan. Worse, as the existing comment warns, its apparent legality could mislead the clauseless-join heuristics into thinking that some other join rel need not be formed. In particular, have_join_order_restriction() would not force a clauseless outer join if one of its input rels appeared to have a legal clause join elsewhere, and if that outer join was required, we would fail with "failed to build any N-way joins". To fix, also include in the search any input rel of the current join search that laterally references a rel already found. The input rels are taken from root->initial_rels rather than from the query's base rels, because an input can be the joinrel of a sub-joinlist, as happens for a full join or when from_collapse_limit or join_collapse_limit prevents flattening. Such a joinrel is joined as a unit, so it has to be included as a whole. Back-patch to all supported branches. Author: Richard Guo <[email protected]> Reviewed-by: Tender Wang <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/cambws4_p6iwbsjncypd6-6mwfnog3uj2fzkrxwaqtsynoye...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/60b2a2f5264d9cb2e34b725453e3f8315dba5054 Modified Files -------------- src/backend/optimizer/path/allpaths.c | 3 +- src/backend/optimizer/path/joinrels.c | 21 +++++++ src/test/regress/expected/join.out | 108 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 44 ++++++++++++++ 4 files changed, 175 insertions(+), 1 deletion(-)
