vldpyatkov commented on code in PR #5393:
URL: https://github.com/apache/calcite/pull/5393#discussion_r4494303837
##########
core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java:
##########
@@ -1004,6 +1004,21 @@ private void checkCeil(int x, int y, int result) {
assertThat(Utilities.compare(empty, empty), is(0));
}
+ /** Composite null keys compare equally for sorting, but must not match in a
merge join. */
+ @Test void testCompareCompositeNullKeys() {
+ final Object key = new Object[]{1, null};
+ assertThat(Utilities.compareCompositeNullsFirst(null, null), is(0));
+ assertThat(Utilities.compareCompositeNullsLast(null, null), is(0));
+ assertThat(Utilities.compareCompositeNullsLastForMergeJoin(null, key),
is(1));
+ assertThat(Utilities.compareCompositeNullsLastForMergeJoin(key, null),
is(-1));
+ assertThat(Utilities.compareCompositeNullsLastForMergeJoin(key, new
Object[]{1, null}), is(0));
Review Comment:
PostgreSQL distinguishes between row constructor comparisons and comparisons
of composite values:
```sql
SELECT ROW(10, NULL::integer) = ROW(10, NULL::integer); -- UNKNOWN
WITH l(id, k) AS (VALUES (1, ROW(10, NULL::integer))),
r(id, k) AS (VALUES (2, ROW(10, NULL::integer)))
SELECT l.id, r.id, l.k, r.k
FROM l JOIN r ON l.k = r.k; -- 1, 2, (10, NULL), (10, NULL)
```
Calcite's HashJoin does not match these ROW values when they contain NULL
fields. I've updated the merge join comparator to follow the same behavior, so
the result does not depend on the join algorithm.
```sql
WITH l(id, k) AS (VALUES (1, ROW(10, CAST(NULL AS INTEGER)))),
r(id, k) AS (VALUES (2, ROW(10, CAST(NULL AS INTEGER))))
SELECT l.id, r.id, l.k, r.k
FROM l JOIN r ON l.k = r.k; -- 0 rows
```
I've also extended CollectionTypeTest.testMergeJoinCompositeKeys, using
compositeJoinCases, to verify that HashJoin and MergeJoin produce the same
results, including ROW keys with nested NULLs.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
