[SQL] Support JOIN on ROW types using IS NOT DISTINCT FROM - #6796
Conversation
|
Calcite's own implementation was completely broken, so I fixed it to match the standard in apache/calcite#5147 |
mythical-fred
left a comment
There was a problem hiding this comment.
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
RejectRowEqualityshuttle runs post-convertQueryon theRelRoot, which is the right place — Calcite's rewrites forIN,CASE,NULLIF,NATURAL/USINGjoins 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. JoinConditionAnalyzernow converts key types withstruct=false, which is what the surrounding key-tuple construction actually wants, and theUnimplementedException("Join on struct types", 3398, ...)guard is removed cleanly.- The changelog explicitly calls this out as a breaking SQL change, and the
Regression1Tests/RowComparisonTestscover the rewrite paths users will need. visitLogicalValuesrefactor intocompileValuesis a real fix for the "GC operators in the graph" bug you mention: the previousLogicalUnionloop silently dropped any input that wasn't aLogicalProject, soLogicalValuesrows in a multi-rowVALUESinserted via a modify statement went missing without error. Good to have that made loud (UnimplementedExceptionon 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"— missingto/as."Row equality is implied in JOIN conditions, a NATURAL JOINs, a USING clause"— strayabeforeNATURAL 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>
Fixes #3398
Checklist
Breaking Changes?
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 toNULL, which is essentiallyFALSE. In our implementation it wasTRUE.Rather than change the semantics (and break the results computed by user programs), I have completely disallowed
row = rowcomparisons (making such programs illegal). In practice one can instead userow <=> row, (orrow 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.