On Wed, Sep 16, 2026 at 1:28 AM Fujii Masao <[email protected]> wrote: > EXPLAIN (COSTS OFF) > SELECT z.c2 > FROM t b > LEFT JOIN ( > SELECT COALESCE(s.c, 0) AS c2 > FROM t x > LEFT JOIN (SELECT (SELECT i.a) AS c FROM t i) s ON true > ) ss ON true > CROSS JOIN LATERAL (SELECT ss.c2 OFFSET 0) z;
For this one, the pushed-down copy of PHV has the form PHV(COALESCE(PHV(SubLink))), i.e. one copy nested directly inside another. preprocess_subquery_phvs_walker preprocesses the inner one first and then the outer one, and the outer one's preprocessing runs into the SubPlan just created in the inner one and trips the Assert. I think this one is easy to fix. We can just stop the walker from descending into a copy, since preprocessing the copy's expression takes care of anything nested inside it. > EXPLAIN (COSTS OFF) > SELECT q.c > FROM ( > (SELECT (SELECT i.a) AS c FROM t i) s > FULL JOIN t b(c) USING (c) > ) j > CROSS JOIN LATERAL (SELECT j.c OFFSET 0) q; This one is kind of nastier. Currently the walk preprocessing the pushed-down copies runs at the start of subquery_planner, but copies can also be inserted later, when flatten_join_alias_vars expands a join alias Var of the outer level within a LATERAL subquery and the alias expression contains a PHV, just as shown in the second query. Such copies are never preprocessed, so a SubLink within them survives into the subquery's lateral references. I think we have to insist that the preprocessing happens after join alias expansion, which is what my v2 patch in [1] did. But that patch was not complete: as I explained in [2], it doesn't handle copies that were pushed into a SubLink's subselect. I think we can cover those by also running the preprocessing in preprocess_expression, right before SS_process_sublinks. That way each upper-level PHV is preprocessed exactly once, at the level it belongs to, and always after join alias expansion. Hence, the attached fix. [1] https://postgr.es/m/cambws4-5ahfegy9arfbozdpxy4xfetwgxzgtw7koyojzx9n...@mail.gmail.com [1] https://postgr.es/m/cambws4_bn_0+mhvs3xph8wou47pn3bv5zh3ny56jlhnb-ba...@mail.gmail.com - Richard
v1-0001-Fix-preprocessing-of-PHV-copies-pushed-down-into-.patch
Description: Binary data
