Don't require outer joins for lateral PHV references outside subqueries When a rel laterally references a PHV, create_lateral_join_info() adds the PHV's whole ph_eval_at to the rel's lateral_relids, including any outer-join relids. That forces those outer joins to be formed before the rel can be joined, which join_is_legal() enforces since commit 55fa1b067.
That is necessary for a subquery. A subquery is planned separately and references the PHV through a single Param, so some nestloop must supply the PHV's value as a whole, and that is only possible once the outer joins in its ph_eval_at have been formed. But a function, VALUES or tablesample scan does not need the PHV as a whole. When the outer joins in its ph_eval_at have not been formed yet, replace_nestloop_params() recurses into the PHV's expression and parameterizes the Vars within it, so the scan computes the expression itself. That gives the same answer, because the scan can only be joined below such an outer join via outer-join identity 3, which requires its join clause to be strict for the nullable side. So the rows the outer join would null-extend cannot match it anyway. We already rely on this for PHVs that reference other PHVs laterally, since commit efeb12ef0. For such rels the outer-join relids are therefore not a real requirement, and listing them needlessly rejects join orders that identity 3 permits. To fix, include outer-join relids in lateral_relids only when the referencing rel is a subquery. No back-patch. This is a planner improvement rather than a bug fix, and we prefer not to change plans in stable branches. Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAMbWs4-fcAULTpWthZMHGOFK1J+Hz=dvr1to-m20cc317ro...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/77e5aeef59cd2fa680e54e552767e56d07cbab4a Modified Files -------------- src/backend/optimizer/plan/initsplan.c | 16 ++++++++++++++-- src/test/regress/expected/join.out | 35 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 17 +++++++++++++++++ 3 files changed, 66 insertions(+), 2 deletions(-)
