Checking DuckDB LEFT JOINs: equal row counts can hide missing keys
Verify which left-side identities receive NULL-extended rows, not just the total number of rows.
A substitution can preserve the total
LEFT JOIN validation needs matching state by left-side identity, not only a total row count. A missing key can be offset by another key appearing again. Adding Truthound does not make a row-count-only approval sufficient for this failure mode. [1, 2, 3]
The reported IE_JOIN counterexample
The public DuckDB report describes a LEFT range join with compound self-exclusion. An unmatched left row disappears, while a different, already matched row receives a spurious NULL-extended row. Other join operators are provided as controls. The relevant observation is the wrong identity being marked unmatched, rather than an entirely empty result. [1]
Build the expected set independently
Compare the expected unmatched-key set with the keys of actual NULL-extended rows, preserving the query form and join operator. Serialize composite keys without losing their components. Where duplicate left keys are legitimate, keep an original row identity too. [1, 3]
Equal sizes, different members
Define an illustrative expected unmatched set containing 4:4 and 7:7. An actual set of 4:4|7:7 passes. A set of 1:1|7:7 fails despite having the same number of members. Derive the expected set by evaluating the small input's conditions independently, not by copying the same faulty join output. [1, 3]
The Truthound set check
Truthound Core's DistinctValuesEqualSetValidator can compare the extracted unmatched-key set with its approved reference. A set check discards multiplicity, so workloads where repeated keys matter need another check. For inputs containing NULL coordinates, first verify whether a row-comparison alternative and the compound predicate are equivalent. Truthound does not prove that SQL equivalence automatically. [1, 2, 3]
Application conditions
Preserve left composite keys, original row identities and matched states from the same input. Derive the expected unmatched set through independent predicate evaluation.
Alternatives
Compare complete results from another join path that preserves the compound predicate and NULL semantics.
Limitations
With NULL coordinates, the reported row-comparison alternative may not be equivalent to the compound predicate. Truthound does not determine that equivalence automatically.
Evidence
- Event checked
- Subject checked
- Modified
