Fix mismatched PHVs for join alias Vars flattened more than once When a join alias Var with nonempty varnullingrels expands to an expression that cannot carry the nullingrels itself, add_nullingrels_if_needed wraps it in a PlaceHolderVar with a new phid. If copies of the same unexpanded alias Var are flattened by separate flatten_join_alias_vars calls, each call makes its own PHV, so the copies no longer match. Only one of the PHVs then gets a PlaceHolderInfo, and the others usually lead to errors when creating the plan, such as "too late to create a new PlaceHolderInfo" or "variable not found in subplan target lists". Worse, if an unmatched copy is nested inside another PHV, setrefs.c can quietly compute its expression above the outer join that should null it, giving wrong results.
Such copies can arise in several ways. When a LATERAL UNION ALL subquery is pulled up as an appendrel, its lateral references live on both in the parent RTE's subquery, which find_lateral_references examines on the assumption that the children are exact copies of parts of it, and in the child rels, which are what actually get planned. A PHV made by pullup_replace_vars for a lateral reference can be copied into both the targetlist and the quals of the query, which are flattened separately. And pull_up_simple_subquery flattens the subquery's targetlist, and its jointree quals if it is LATERAL, using the subroot, while the rest of the subquery is flattened later by subquery_planner using the parent root. To fix, keep the PHVs made by add_nullingrels_if_needed in a new PlannerInfo field, and reuse one whenever the same expression is expanded again with the same phrels, as pullup_replace_vars_callback does with its rv_cache. When pull_up_simple_subquery pulls up a subquery, adjust the subroot's list in the same way as its append_rel_list and add it to the parent's, so that the parent's flattening reuses the subroot's PHVs. This makes all expansions of the same join alias share one phid, however the copies came about. This bug dates back to v16, where add_nullingrels_if_needed was introduced. But the fix needs a new PlannerInfo field, and several extensions allocate their own PlannerInfo, so adding it in released branches risks breaking them. Hence, back-patch to v19 only, which has not been released yet. Author: Richard Guo <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/cambws4_tg90kxnvdabpbnx2ogbqtcahav45mpefcdkvo11z...@mail.gmail.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/de903547c742f1be513ccd98a0c5f16b1206ded3 Modified Files -------------- src/backend/optimizer/plan/planner.c | 1 + src/backend/optimizer/prep/prepjointree.c | 15 +- src/backend/optimizer/util/var.c | 30 +++- src/include/nodes/pathnodes.h | 3 + src/test/regress/expected/join.out | 257 ++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 59 +++++++ 6 files changed, 360 insertions(+), 5 deletions(-)
