Fix nestloop parameter assignment for PHVs evaluated above outer joins A NestLoop can supply a PlaceHolderVar as a parameter if the PHV can be evaluated from the lefthand rels plus the required-outer rels of the join. A PHV's ph_eval_at can include outer-join relids, but required-outer sets need not: create_lateral_join_info() keeps outer-join relids out of the lateral dependencies it derives from ph_lateral. So when an outer join within ph_eval_at had been computed by an enclosing nestloop's outer side, the test in identify_current_nestloop_params() failed and no plan node claimed the parameter. This resulted in "failed to assign all NestLoopParams to plan nodes", or, if an unrelated upper NestLoop happened to cover ph_eval_at and claimed the parameter, "plan should not reference subplan's variable".
To fix, have create_nestloop_plan() also count the outer joins that have already been computed by the outer rels in scope. Back-patch to v18, where commit a16ef313f made it possible to compute a PHV that is used as a nestloop parameter in the outer side of the NestLoop. v16 and v17 can fail the same way, since ph_eval_at has included outer-join relids since v16, but they lack that machinery. Fixing them would mean adding join-order restrictions to have_dangerous_phv() that could change plans that work today, which does not seem worth the risk for a failure this unlikely. Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAMbWs4-fcAULTpWthZMHGOFK1J+Hz=dvr1to-m20cc317ro...@mail.gmail.com Backpatch-through: 18 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e84fb3f1c9c25867739580470bec48b19f18081d Modified Files -------------- src/backend/optimizer/plan/createplan.c | 9 ++++++ src/test/regress/expected/join.out | 53 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 18 +++++++++++ 3 files changed, 80 insertions(+)
