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_ord | b_ord | a_id | b_id |
|---|---|---|---|
| z100 | a001 | 100 | 1 |
| z200 | a001 | 200 | 1 |
| z300 | a001 | 300 | 1 |
Postgres returns
| a_ord | b_ord | a_id | b_id |
|---|---|---|---|
| z100 | a001 | 100 | 1 |
| z100 | a002 | 100 | 2 |
| z100 | a003 | 100 | 3 |
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);
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
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
}
};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:
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