Re: remove_useless_joins vs. bug #19560

On Mon, Sep 14, 2026 at 2:10 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Mon, Sep 14, 2026 at 2:32 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > In short, LGTM, please push.
>
> Thanks! Pushed and back-patched to v16.

Thanks for working on this!

I used Codex to review this commit, and it reported two cases that could
cause assertion failures. I confirmed that both cases actually cause
assertion failures:

(1)
CREATE TEMP TABLE t(a integer);

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;

TRAP: failed Assert("!IsA(node, SubPlan)"), File: "var.c", Line: 993, PID: 68500
0 postgres 0x0000000101106872
ExceptionalCondition + 178
1 postgres 0x0000000100db49be
flatten_join_alias_vars_mutator + 1438
2 postgres 0x0000000100caaf3e
expression_tree_mutator_impl + 8078
3 postgres 0x0000000100db48a9
flatten_join_alias_vars_mutator + 1161
4 postgres 0x0000000100caaa42
expression_tree_mutator_impl + 6802
5 postgres 0x0000000100db4a71
flatten_join_alias_vars_mutator + 1617
6 postgres 0x0000000100ca9f19
expression_tree_mutator_impl + 3945
7 postgres 0x0000000100db4a71
flatten_join_alias_vars_mutator + 1617
8 postgres 0x0000000100db4414
flatten_join_alias_vars + 116
9 postgres 0x0000000100d5c9f1
preprocess_expression + 97
10 postgres 0x0000000100d60671
preprocess_subquery_phvs_walker + 241
11 postgres 0x0000000100ca78a2
expression_tree_walker_impl + 2610
12 postgres 0x0000000100d606a9
preprocess_subquery_phvs_walker + 297
13 postgres 0x0000000100ca7d82
expression_tree_walker_impl + 3858
14 postgres 0x0000000100d606a9
preprocess_subquery_phvs_walker + 297
15 postgres 0x0000000100ca8973
query_tree_walker_impl + 83
16 postgres 0x0000000100d605cc
preprocess_subquery_phvs_walker + 76
17 postgres 0x0000000100ca8e3d
range_table_entry_walker_impl + 173
18 postgres 0x0000000100ca8d58
range_table_walker_impl + 152
19 postgres 0x0000000100ca8c9a
query_tree_walker_impl + 890
20 postgres 0x0000000100d5c985
preprocess_subquery_phvs + 53
21 postgres 0x0000000100d5b66e
subquery_planner + 1758
22 postgres 0x0000000100d5a471
standard_planner + 1201
23 postgres 0x0000000100d59f9f planner + 95
24 postgres 0x0000000100eee3a4 pg_plan_query + 132
25 postgres 0x0000000100afd776
standard_ExplainOneQuery + 214
26 postgres 0x0000000100afd496 ExplainOneQuery + 182
27 postgres 0x0000000100afd218 ExplainQuery + 376
28 postgres 0x0000000100ef8f8f
standard_ProcessUtility + 2159
29 postgres 0x0000000100ef8719 ProcessUtility + 313
30 postgres 0x0000000100ef7f2c
PortalRunUtility + 236
31 postgres 0x0000000100ef6bee FillPortalStore + 238
32 postgres 0x0000000100ef67c1 PortalRun + 641
33 postgres 0x0000000100ef18a8
exec_simple_query + 1400
34 postgres 0x0000000100ef09e5 PostgresMain + 2981
35 postgres 0x0000000100ee9528 BackendMain + 168
36 postgres 0x0000000100dd6f89
postmaster_child_launch + 377
37 postgres 0x0000000100ddd535 BackendStartup + 277
38 postgres 0x0000000100ddbaa5 ServerLoop + 341
39 postgres 0x0000000100dda8a9 PostmasterMain + 5753
40 postgres 0x0000000100c73933 main + 771
41 dyld 0x00007ff810660530 start + 3056

(2)
CREATE TEMP TABLE t(a integer);

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;

TRAP: failed Assert("!checkExprHasSubLink((Node *) phv)"), File:
"paramassign.c", Line: 691, PID: 68067
0 postgres 0x000000010c53a872
ExceptionalCondition + 178
1 postgres 0x000000010c1cc030
identify_current_nestloop_params + 544
2 postgres 0x000000010c18336e
create_nestloop_plan + 734
3 postgres 0x000000010c17a261
create_join_plan + 113
4 postgres 0x000000010c178e0d
create_plan_recurse + 109
5 postgres 0x000000010c178ce5 create_plan + 101
6 postgres 0x000000010c18e4aa
standard_planner + 1258
7 postgres 0x000000010c18df9f planner + 95
8 postgres 0x000000010c3223a4 pg_plan_query + 132
9 postgres 0x000000010bf31776
standard_ExplainOneQuery + 214
10 postgres 0x000000010bf31496 ExplainOneQuery + 182
11 postgres 0x000000010bf31218 ExplainQuery + 376
12 postgres 0x000000010c32cf8f
standard_ProcessUtility + 2159
13 postgres 0x000000010c32c719 ProcessUtility + 313
14 postgres 0x000000010c32bf2c
PortalRunUtility + 236
15 postgres 0x000000010c32abee FillPortalStore + 238
16 postgres 0x000000010c32a7c1 PortalRun + 641
17 postgres 0x000000010c3258a8
exec_simple_query + 1400
18 postgres 0x000000010c3249e5 PostgresMain + 2981
19 postgres 0x000000010c31d528 BackendMain + 168
20 postgres 0x000000010c20af89
postmaster_child_launch + 377
21 postgres 0x000000010c211535 BackendStartup + 277
22 postgres 0x000000010c20faa5 ServerLoop + 341
23 postgres 0x000000010c20e8a9 PostmasterMain + 5753
24 postgres 0x000000010c0a7933 main + 771
25 dyld 0x00007ff810660530 start + 3056

Regards,

--
Fujii Masao