Re: remove_useless_joins vs. bug #19560

On Wed, Sep 16, 2026 at 2:16 PM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> And, here's another related issue, though it seems to have been introduced by
> commit 2ebf25e7d70.

> CREATE TABLE t(a integer PRIMARY KEY);
>
> EXPLAIN (COSTS OFF)
> SELECT u.c2
> FROM (
> SELECT CASE WHEN false THEN r.a END AS c
> FROM t l LEFT JOIN t r ON l.a = r.a
> ) s
> RIGHT JOIN t b ON true
> CROSS JOIN LATERAL (
> SELECT (SELECT s.c) AS c2
> UNION ALL
> SELECT (SELECT s.c)
> ) u;
>
> TRAP: failed Assert("context->new_index != INVALID_VAR"), File:
> "rewriteManip.c", Line: 566, PID: 6615

Thanks! I just had a look into this. What happens is that when s is
pulled up, its output c becomes a PHV wrapping the CASE expression,
since s is on the nullable side of the RIGHT JOIN. The two sublinks
"(SELECT s.c)" in the LATERAL subquery each get a copy of that PHV.
Then, because the UNION ALL arms are simple, they are pulled up too,
and their target expressions, ie those two sublinks, are moved into
the appendrel's translated_vars. However, the current walk never
looks at root->append_rel_list, so the PHV copies inside the sublinks
are never preprocessed. At the outer level the CASE folds to NULL,
but the copies still reference r.a, and join removal of r then finds
that reference.

The patch I posted upthread fixes this too, since it preprocesses the
copies from within preprocess_expression, which the translated_vars go
through as well. I'll add this case to its regression tests. Really
nice catch!

- Richard