From 2218664aec3fcd26c41c79a1db3e330cd50d2bd9 Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Wed, 16 Sep 2026 18:13:02 +0900 Subject: [PATCH v1] Fix mismatched PHVs for join aliases in LATERAL UNION ALL subqueries When a LATERAL UNION ALL subquery is pulled up as an appendrel, its lateral references live on in two places: 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. Both copies had their join alias Vars expanded later, by separate flatten_join_alias_vars calls in subquery_planner. If an expansion needs a PlaceHolderVar to carry nullingrels, each call made its own PHV with a different phid, so the PHVs demanded by the children had no PlaceHolderInfo, leading to "too late to create a new PlaceHolderInfo" errors when creating the plan. To fix, expand join alias Vars in the LATERAL subquery in pull_up_simple_union_all, before its rtable is copied, so that all copies share the same PHVs. This early expansion is safe because a LATERAL item can reference only FROM items to its left, which pull_up_subqueries has already processed, so their join alias lists are final. Back-patch to v16, where the problem first appeared. --- src/backend/optimizer/prep/prepjointree.c | 13 +++ src/test/regress/expected/join.out | 126 ++++++++++++++++++++++ src/test/regress/sql/join.sql | 25 +++++ 3 files changed, 164 insertions(+) diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index dfe320beccd..3272f0bcbae 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1774,6 +1774,19 @@ pull_up_simple_union_all(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte) int rtoffset = list_length(root->parse->rtable); List *rtable; + /* + * If the subquery is LATERAL, expand any join alias Vars of the parent + * query now, before copying its rtable, so that all copies share the PHVs + * the expansion may create. find_lateral_references relies on the + * children matching the parent RTE's subquery. Expanding them this early + * is OK: a LATERAL item can reference only FROM items to its left, which + * pull_up_subqueries has already processed, so their join alias lists are + * final. + */ + if (rte->lateral) + rte->subquery = subquery = (Query *) + flatten_join_alias_vars(root, root->parse, (Node *) subquery); + /* * Make a modifiable copy of the subquery's rtable, so we can adjust * upper-level Vars in it. There are no such Vars in the setOperations diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 75544fe6aa3..96a63d7d05c 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -9788,6 +9788,132 @@ select * from generate_series(100,200) g, 123 | 4567890123456789 | 123 (3 rows) +-- lateral UNION ALL referencing a join alias Var that needs a PHV; the +-- appendrel parent and children must share the PHV +-- Here the Var is a whole-row reference to a nullable join. +explain (verbose, costs off) +select ss.j from + int4_tbl a left join (int4_tbl b cross join int4_tbl c) j on true, + lateral ((select j offset 0) union all select null) ss; + QUERY PLAN +------------------------------------------------------------- + Nested Loop + Output: ((ROW(b.f1, c.f1))) + -> Nested Loop Left Join + Output: (ROW(b.f1, c.f1)) + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Materialize + Output: (ROW(b.f1, c.f1)) + -> Nested Loop + Output: ROW(b.f1, c.f1) + -> Seq Scan on public.int4_tbl b + Output: b.f1 + -> Materialize + Output: c.f1 + -> Seq Scan on public.int4_tbl c + Output: c.f1 + -> Append + -> Result + Output: (ROW(b.f1, c.f1)) + -> Result + Output: NULL::record +(21 rows) + +explain (verbose, costs off) +select ss.j from + int4_tbl a left join (int4_tbl b cross join int4_tbl c) j on true, + lateral (select j union all select null) ss; + QUERY PLAN +------------------------------------------------------------- + Nested Loop + Output: ((ROW(b.f1, c.f1))) + -> Nested Loop Left Join + Output: (ROW(b.f1, c.f1)) + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Materialize + Output: (ROW(b.f1, c.f1)) + -> Nested Loop + Output: ROW(b.f1, c.f1) + -> Seq Scan on public.int4_tbl b + Output: b.f1 + -> Materialize + Output: c.f1 + -> Seq Scan on public.int4_tbl c + Output: c.f1 + -> Append + -> Result + Output: (ROW(b.f1, c.f1)) + -> Result + Output: NULL::record +(21 rows) + +-- Here the Var is a merged column of a full join with a non-Var input +explain (verbose, costs off) +select ss.c from + int4_tbl a left join + ((select f1 + 0 as c from int4_tbl) s full join int4_tbl b(c) using (c)) j + on true, + lateral ((select j.c offset 0) union all select 1) ss; + QUERY PLAN +-------------------------------------------------------------- + Nested Loop + Output: ((COALESCE((int4_tbl.f1 + 0), b.c))) + -> Nested Loop Left Join + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Materialize + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Hash Full Join + Output: COALESCE((int4_tbl.f1 + 0), b.c) + Hash Cond: ((int4_tbl.f1 + 0) = b.c) + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + -> Hash + Output: b.c + -> Seq Scan on public.int4_tbl b + Output: b.c + -> Append + -> Result + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Result + Output: 1 +(22 rows) + +explain (verbose, costs off) +select ss.c from + int4_tbl a left join + ((select f1 + 0 as c from int4_tbl) s full join int4_tbl b(c) using (c)) j + on true, + lateral (select j.c union all select 1) ss; + QUERY PLAN +-------------------------------------------------------------- + Nested Loop + Output: ((COALESCE((int4_tbl.f1 + 0), b.c))) + -> Nested Loop Left Join + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Materialize + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Hash Full Join + Output: COALESCE((int4_tbl.f1 + 0), b.c) + Hash Cond: ((int4_tbl.f1 + 0) = b.c) + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + -> Hash + Output: b.c + -> Seq Scan on public.int4_tbl b + Output: b.c + -> Append + -> Result + Output: (COALESCE((int4_tbl.f1 + 0), b.c)) + -> Result + Output: 1 +(22 rows) + -- lateral with VALUES explain (costs off) select count(*) from tenk1 a, diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index fb83a96e939..b8cff40bd07 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -3847,6 +3847,31 @@ select * from generate_series(100,200) g, lateral (select * from int8_tbl a where g = q1 union all select * from int8_tbl b where g = q2) ss; +-- lateral UNION ALL referencing a join alias Var that needs a PHV; the +-- appendrel parent and children must share the PHV +-- Here the Var is a whole-row reference to a nullable join. +explain (verbose, costs off) +select ss.j from + int4_tbl a left join (int4_tbl b cross join int4_tbl c) j on true, + lateral ((select j offset 0) union all select null) ss; +explain (verbose, costs off) +select ss.j from + int4_tbl a left join (int4_tbl b cross join int4_tbl c) j on true, + lateral (select j union all select null) ss; +-- Here the Var is a merged column of a full join with a non-Var input +explain (verbose, costs off) +select ss.c from + int4_tbl a left join + ((select f1 + 0 as c from int4_tbl) s full join int4_tbl b(c) using (c)) j + on true, + lateral ((select j.c offset 0) union all select 1) ss; +explain (verbose, costs off) +select ss.c from + int4_tbl a left join + ((select f1 + 0 as c from int4_tbl) s full join int4_tbl b(c) using (c)) j + on true, + lateral (select j.c union all select 1) ss; + -- lateral with VALUES explain (costs off) select count(*) from tenk1 a, -- 2.37.1 (Apple Git-137.1)