Incorrect results from nested RIGHT JOIN to a reference table

Current scope

A nested RIGHT JOIN from a distributed table to a reference table can cause Citus to omit every shard task and return no rows. A second distributed relation at the outer query level triggers a collision between query-local range-table indexes. CTE reuse, NOT IN, and LIMIT 0 in the original generated query are incidental, not required. This issue now tracks the PG17 generator failure only. The six columnar/isolation failures in the original September 9 nightly report were fixed by #8834; related reports #8812, #8817, and #8832 are resolved.

Evidence

• Original failing job: https://github.com/citusdata/citus/actions/runs/34306161760/job/102323219424
• Original SQL, fixture, result diff, and server logs: https://github.com/citusdata/citus/actions/runs/34306161760/artifacts/10086785095 (cassert_17_generator).
• Generator seed: 1788923688918, query 239.
• Original run commit: 1f0253f51a5ba9d46b4360259dda7af5e14bdf0c.
• Reproduced September 10 against current main 5eaf89417fae86e60fdbfb81029292718e9bd8d5, Citus 15.0devel, PostgreSQL 17.11, using the exact saved CI SQL and data fixture.
• Reproduces with debug_assertions = off. This is a query-correctness issue, not an assertion-only crash or harmless output ordering difference.

Minimal reproduction

Run in a Citus cluster with a coordinator and workers:

SET citus.shard_count = 2;
CREATE TABLE d(id int);
SELECT create_distributed_table('d', 'id');
CREATE TABLE r(id int);
SELECT create_reference_table('r');
INSERT INTO d VALUES (0);
INSERT INTO r VALUES (0);

SELECT s.id
FROM (
    SELECT d.id
    FROM d RIGHT JOIN r USING (id)
    ORDER BY 1
) s
LEFT JOIN d USING (id);

Expected: one row containing 0. Before the fix: no rows, with Task Count: 0 in EXPLAIN. This also reproduces when the outer d is replaced with a distinct colocated distributed table containing the same data. Equivalent inner r LEFT JOIN d, removing the inner ORDER BY, or setting citus.enable_recurring_outer_join_pushdown = off avoids this reproducer.

Root cause and local fix

QueryPushdownSqlTaskList() collected distributed range-table indexes from multiple query levels into one bitmap. IsInnerTableOfOuterJoin() then compared a particular join's outer relation indexes against that cross-query bitmap. Indexes are local to each PlannerInfo: the inner reference relation and outer distributed relation can both have index 2. This falsely classified the preserved reference side as distributed and skipped all shard tasks.

The local fix resolves each outer relation through its own PlannerInfo->simple_rte_array, using the existing distributed-table predicate instead of the cross-query bitmap. Regression coverage includes both same-table and distinct-table nested RIGHT JOINs plus the equivalent LEFT JOIN, with duplicate and unmatched rows.

With the fix, the exact saved query 239 and fixture return count=4, avg=0 in both distributed and local execution on PostgreSQL 17.11. The generator also passes with the original seed. The fix is available in draft PR #8842; this issue remains open pending merge.

Original generator query

Use ddls.sql from the original artifact to create/populate the distributed and reference tables, then execute:

WITH cte_0 AS (
    SELECT table_0.id
    FROM (
        SELECT table_1.id
        FROM dist0 AS table_1
        RIGHT JOIN ref1 AS table_2 USING (id)
        ORDER BY id
    ) AS table_0
    LEFT JOIN dist0 AS table_3 USING (id)
    ORDER BY id
    LIMIT 4
)
SELECT count(*), avg(avgsub.id)
FROM (
    SELECT table_4.id
    FROM cte_0 AS table_4
    WHERE table_4.id NOT IN (
        SELECT table_5.id
        FROM ref0 AS table_5
        INNER JOIN cte_0 AS table_6 USING (id)
        WHERE table_6.id IN (
            SELECT table_7.id
            FROM (
                SELECT table_8.id
                FROM ref0 AS table_8
                WHERE table_8.id < 2
                ORDER BY id
                LIMIT 8
            ) AS table_7
            WHERE table_7.id IN (
                SELECT table_9.id
                FROM dist0 AS table_9
                WHERE table_9.id > 3
                ORDER BY id
            )
            ORDER BY id
            LIMIT 8
        )
        ORDER BY id
        LIMIT 0
    )
    ORDER BY id
    LIMIT 4
) AS avgsub;

The immediate NOT IN subquery is empty because of LIMIT 0, so it should not remove the four CTE rows. The missing rows originate in the nested outer-join task selection described above, before this filter.

The existing generator target also exposes the failure:

make -C src/test/regress check-query-generator seed=1788923688918

Prefer the saved SQL/fixture for exact reproduction: regenerated table choices can vary with Python hash ordering even for the same random seed.

Why the latest green nightly does not resolve this

The September 10 PG17 generator job passed with a different seed, 1789009969517: https://github.com/citusdata/citus/actions/runs/34432358451/job/102730365107 . The original failing SQL still returns the wrong result on that run's main commit.