Wrong results from common_subplan when two UNION ALL arms share a join subtree (1.5.1 onward, fixed on 1.6 dev)

What happens?

Two UNION ALL arms that reach the same three-way join return wrong values for a concatenated string column. Both r values in the concatenation come out as the second join's r, and the first join's is lost. It's a silent wrong answer, no error.

SET disabled_optimizers='common_subplan' gives the expected rows on every affected version. On 1.5.5 the plan puts the shared join subtree in a CTE with two CTE_SCANs; on 1.5.0 and on the 1.6 dev builds there is no CTE and the answer is right.

The inner WHERE s2.r < s3.r and WHERE s3.r < s4.r matter: they duplicate the predicate already inside pairs, and removing either one makes the bug disappear. This is reduced from generated SQL, where that duplication arises naturally.

Versions I ran the repro on:

• correct: 1.4.2 (which has no common_subplan optimizer), 1.5.0
• wrong: 1.5.1, 1.5.2, 1.5.3, 1.5.4, 1.5.5, and 1.5.6.dev40, the v1.5 nightly as of 2026-09-16
• correct: 1.6.0.dev64, 1.6.0.dev339, 1.6.0.dev379, 2.0.0.dev2609121639 (v2.0.0-alpha41344, 81bc275dd6)

So it is a regression in 1.5.1, still present on the v1.5 branch head, and already gone on the 1.6 dev line. #21386, the fix for #21372, is the only change to src/optimizer/common_subplan_optimizer.cpp between the v1.5.0 and v1.5.1 tags, so it looks like the origin, though that is file-level ancestry rather than a bisect. I didn't find the commit that fixed it on main.

A backport to 1.5 and a regression test would both be worth having.

The other common_subplan issues I found are all closed. The closest is #24624, which a maintainer closed as fixed on current main and pointed at #24600. That is where this one is fixed too, so it may be the same defect the 1.5 line never received. The rest are #25170, #24781, #23585 and #22133.

To Reproduce

CREATE VIEW q AS SELECT CAST(v AS VARCHAR) AS id, v AS r FROM range(0, 3) t(v);

CREATE VIEW pairs AS SELECT a.id AS a, b.id AS b FROM q a, q b WHERE a.r < b.r;

CREATE VIEW ids AS
  SELECT s2.id || s3.id AS x
  FROM pairs s1 JOIN q s2 ON s2.id = s1.a JOIN q s3 ON s3.id = s1.b
  WHERE s2.r < s3.r;

CREATE VIEW s AS SELECT x AS id FROM ids GROUP BY x;

CREATE VIEW p AS
  SELECT id, max(nm) AS nm FROM (
    SELECT s1.id AS id, NULL AS nm FROM s s1
    UNION ALL
    SELECT s3.id || s4.id AS id, 'n_' || s3.r || '_' || s4.r AS nm
    FROM pairs s2 JOIN q s3 ON s3.id = s2.a JOIN q s4 ON s4.id = s2.b
    WHERE s3.r < s4.r
  ) GROUP BY id;

SELECT nm FROM p WHERE nm IS NOT NULL ORDER BY 1;

Expected n_0_1, n_0_2, n_1_2. On 1.5.5 the CLI returns:

┌─────────┐
│   nm    │
│ varchar │
├─────────┤
│ n_1_1   │
│ n_2_2   │
│ n_2_2   │
└─────────┘

That is v1.5.5 (Variegata) d8cdaa33fd; the Python client (3.12.12) returns the same three rows.

OS:

macOS 26.7 (Darwin 25.6.0)

DuckDB Version:

1.5.5

DuckDB Client:

Python 3.12.12, and the 1.5.5 CLI

Hardware:

Apple silicon (arm64)

Full Name:

Chris Coey

Affiliation:

RelationalAI