Re: remove_useless_joins vs. bug #19560

On Wed, Sep 16, 2026 at 1:28 AM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> 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-5aHFeGy9ArFbozdpxy4xFEtWgXzGTW7KoYOjzX9nXGA@mail.gmail.com
[1] https://postgr.es/m/CAMbWs4_BN_0+mHvS3xpH8WOU47PN3bV5zh3Ny56JLHnB-bamww@mail.gmail.com

- Richard