Back to open source

Wrong rows from a self-join

ParadeDB's JoinScan returned the wrong three rows when a self-join sorted on a column name that both sides share. The cause was one line that compared columns by name. My first fix had the same kind of mistake in it.

paradedb #4763 merged 17 May 2026 +274 −38 in 5 files issue #4567

The query

The report came with a ten-row table joined to itself. Both sides expose a column called ord, and the query sorts by both of them.

SELECT a.ord AS a_ord, b.ord AS b_ord, a.id AS a_id, b.id AS b_id
FROM dup_items a
JOIN dup_items b ON a.grp = b.grp
WHERE a.body @@@ 'left'
  AND b.body @@@ 'right'
ORDER BY a.ord ASC, b.ord ASC
LIMIT 3;

The wrong rows

With JoinScan on, the query returned three rows. With it off, Postgres returned a different three.

JoinScan returned

a_ordb_orda_idb_id
z100a0011001
z200a0012001
z300a0013001

Postgres returns

a_ordb_orda_idb_id
z100a0011001
z100a0021002
z100a0031003
From the issue. Values in red differ from what Postgres returns for the same query.

Every value JoinScan returned is a real row, and the first row is even right. It is only wrong as an answer to this query: the rows are ordered by one of the two sort keys.

The clue

EXPLAIN showed the top-K node, SegmentedTopKExec, sorting on a single physical column, [ord@1 ASC]. The other side's column, ord@3, was gone from the plan.

The cause

active_deferred.dedup_by(|a, b| a.name == b.name);
late_materialization.rs, before the fix

This line removed duplicate deferred columns by name. In a self-join, a.ord and b.ord both resolve to the string "ord", so two columns became one. The planner then dropped the second sort key, and the lookup node never materialized it.

The first fix

I removed the name-based dedup and rewrote the projection to follow each column by its physical index, col.index(), replacing name lookups across late_materialization.rs, segmented_topk_rule.rs, tantivy_lookup_exec.rs and pre_filter.rs. The ignored test from the report passed.

Where my fix was wrong

During review I had relaxed an assertion that kept failing, on the theory that it was only tripping over aliases. A maintainer suspected it was catching something real and asked me to run a second case: two different tables that both output a column called name. JoinScan and Postgres disagreed again.

HashJoinExec projection
[ctid_0@0, name@1, ctid_1@3, name@5]
the sort wanted
p.name, at physical index 3
SegmentedTopKExec got
col_2@1, which is index 1: s.name
From EXPLAIN VERBOSE on the second case, as I reported it in the thread.

My rewrite trusted Column.index() from the DataFusion sort node without accounting for how the JoinScan projection rearranges the schema. Swapping names for indices had fixed one binding mistake and introduced another.

The rebuild

The merged version resolves each sort column against the child schema, and treats the three possible outcomes differently.

let physical_idx = match matches.len() {
    0 => {
        // Name not found — fall back to the logical index.
        // This should not normally happen; a missing name suggests a
        // plan-construction bug where the SortExec column does not exist
        // in the lookup child schema.
        // …
        logical_idx
    }
    1 => {
        // Unique name — use the exact physical position from the schema.
        matches[0]
    }
    _ => {
        // Duplicate names (self-join) — DataFusion already set col.index()
        // to the correct physical position when building the SortExec.
        // TODO: this relies on a DataFusion invariant that col.index() is
        // correct for the duplicate-name case. It breaks for 3-way self-joins
        // or intermediate Projections that reorder same-name groups.
        // Proper fix: thread explicit column lineage similar to trace_column
        // in late_materialization.rs. Tracked in issue #5093.
        logical_idx
    }
};
From resolve_physical_index in segmented_topk_rule.rs, as merged. "// …" marks lines left out.

Both cases now have regression tests that compare JoinScan with Postgres row by row. The self-join test also asserts that both sort keys appear in the plan, so it cannot pass by accident if a later change collapses them again:

SegmentedTopKExec: expr=[ord@3 ASC NULLS LAST, ord@1 ASC NULLS LAST], k=3

What it still does not handle

The duplicate-name branch leans on a DataFusion invariant, not on a real guarantee. A three-way self-join, or a projection that reorders columns with the same name, would map to the wrong column silently. That is written down in the code as a TODO and tracked as #5093, which proposes explicit column lineage in place of the heuristic.

The full pull request on GitHub Next: A panic on PG16, and the fix that needed fixing