Skip to content

[SQL] Support JOIN on ROW types using IS NOT DISTINCT FROM - #6796

Merged
mihaibudiu merged 1 commit into
feldera:mainfrom
mihaibudiu:issue3398
Aug 14, 2026
Merged

[SQL] Support JOIN on ROW types using IS NOT DISTINCT FROM#6796
mihaibudiu merged 1 commit into
feldera:mainfrom
mihaibudiu:issue3398

Conversation

@mihaibudiu

Copy link
Copy Markdown
Contributor

Fixes #3398

Checklist

  • Unit tests added/updated
  • Integration tests added/updated
  • Documentation updated
  • Changelog updated

Breaking Changes?

  • Feldera SQL (Syntax, Semantics)

Turns out that our implementation for ROW equality did not follow the SQL standard (which one could argue is wrong).
According to the SQL standard ROW equality uses three-valued semantics, and ROW(NULL) = ROW(NULL) evaluates to NULL, which is essentially FALSE. In our implementation it was TRUE.

Rather than change the semantics (and break the results computed by user programs), I have completely disallowed row = row comparisons (making such programs illegal). In practice one can instead use row <=> row, (or row IS NOT DISTINCT FROM row) which has the previous semantics, and which DOES match the SQL standard.

I have also documented the semantics of comparisons - it wasn't documented previously, which makes it plausible to argue that we weren't wrong - since we didn't promise anything.

We may choose to support these operations in the future, but it's not clear which semantics we should follow.

Each SQL dialects has different opinions on how this stuff should work, Postgres itself cannot make up its mind, and it alternates between these two semantics depending on the context where an operation is used. That's a sure recipe for madness.

@mihaibudiu

Copy link
Copy Markdown
Contributor Author

Calcite's own implementation was completely broken, so I fixed it to match the standard in apache/calcite#5147

@mythical-fred mythical-fred left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Solid piece of work. The choice to reject =/<>/!= on ROW values rather than silently switch semantics is the right call — the three-valued behaviour of the standard genuinely surprises users, and the alternative (matching our previous two-valued IS NOT DISTINCT FROM semantics) would have been a silent result-set change on existing programs.

Things I checked and like:

  • The RejectRowEquality shuttle runs post-convertQuery on the RelRoot, which is the right place — Calcite's rewrites for IN, CASE, NULLIF, NATURAL/USING joins have already expanded to = by then, so all the implicit forms get caught. The test file covers each of these.
  • Disabling the ROW-equality expansion in ConvertletTable.convertCall (commenting it out with the explanatory note) prevents it from firing before the shuttle sees the tree — otherwise (a,b) = (c,d) would decompose into field-wise equalities and slip through with a confusing error location. Nice.
  • JoinConditionAnalyzer now converts key types with struct=false, which is what the surrounding key-tuple construction actually wants, and the UnimplementedException("Join on struct types", 3398, ...) guard is removed cleanly.
  • The changelog explicitly calls this out as a breaking SQL change, and the Regression1Tests / RowComparisonTests cover the rewrite paths users will need.
  • visitLogicalValues refactor into compileValues is a real fix for the "GC operators in the graph" bug you mention: the previous LogicalUnion loop silently dropped any input that wasn't a LogicalProject, so LogicalValues rows in a multi-row VALUES inserted via a modify statement went missing without error. Good to have that made loud (UnimplementedException on unknown inputs).

Two prose nits in the RejectRowEquality javadoc — feel free to ignore, they don't block:

  • "A user-defined type is compiled a ROW type" — missing to/as.
  • "Row equality is implied in JOIN conditions, a NATURAL JOINs, a USING clause" — stray a before NATURAL JOINs.

One larger thought, not a blocker: the error message points at the docs URL, which is great, but consider also naming the closest legal rewrite inline (... use 'IS NOT DISTINCT FROM' or its shorthand '<=>' instead) — you already do this for EQUALS/NOT_EQUALS. The NULLIF message is exemplary; the JOIN-condition path currently surfaces the plain EQUALS message which is fine but a hair less directive.

Approving.

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
@mihaibudiu
mihaibudiu added this pull request to the merge queue Aug 13, 2026
Merged via the queue into feldera:main with commit 7b1dfbf Aug 14, 2026
1 check passed
@mihaibudiu
mihaibudiu deleted the issue3398 branch August 14, 2026 04:04
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

[SQL] Support joins on ROW/UDT fields

3 participants