This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 5c8e38bffe [CALCITE-7678] Runtime equality for ROW values produces
incorrect results
5c8e38bffe is described below
commit 5c8e38bffe0679f1765679691d0a9305527fe7d3
Author: Mihai Budiu <[email protected]>
AuthorDate: Sun Aug 2 16:41:06 2026 -0700
[CALCITE-7678] Runtime equality for ROW values produces incorrect results
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/adapter/enumerable/PhysTypeImpl.java | 76 ++-
.../calcite/adapter/enumerable/RexImpTable.java | 11 +
.../java/org/apache/calcite/plan/RelOptUtil.java | 13 +-
.../java/org/apache/calcite/rex/RexSimplify.java | 5 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 60 ++
.../sql2rel/TopDownGeneralDecorrelator.java | 16 +-
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
core/src/test/resources/sql/row-equality.iq | 615 +++++++++++++++++++++
.../apache/calcite/linq4j/function/Functions.java | 143 ++++-
site/_docs/reference.md | 26 +
10 files changed, 957 insertions(+), 9 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
index ce7d016a6c..2efbb25d22 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
@@ -585,7 +585,42 @@ private RelDataType toStruct(RelDataType type) {
}
@Override public @Nullable Expression comparer() {
- return format.comparer();
+ final Expression comparer = format.comparer();
+ if (comparer != null) {
+ return comparer;
+ }
+ if (anyFieldContainsStruct(rowType)) {
+ // A row or key containing struct (ROW) values needs deep equality;
+ // the default equality of the runtime representations of struct values
(Object[], List)
+ // compares nested Object[] values by reference. Here we implement the
"not
+ // distinct" semantics used by GROUP BY, DISTINCT and set operations.
+ return Expressions.call(BuiltInMethod.DEEP_COMPARER.method);
+ }
+ return null;
+ }
+
+ /** Returns whether any field of {@code rowType} contains a struct value:
+ * the field is itself a struct, or a collection or map whose elements
+ * contain one. */
+ private static boolean anyFieldContainsStruct(RelDataType rowType) {
+ return rowType.getFieldList().stream()
+ .anyMatch(f -> containsStruct(f.getType()));
+ }
+
+ private static boolean containsStruct(RelDataType type) {
+ if (type.isStruct()) {
+ return true;
+ }
+ final RelDataType componentType = type.getComponentType();
+ if (componentType != null && containsStruct(componentType)) {
+ return true;
+ }
+ final RelDataType keyType = type.getKeyType();
+ if (keyType != null && containsStruct(keyType)) {
+ return true;
+ }
+ final RelDataType valueType = type.getValueType();
+ return valueType != null && containsStruct(valueType);
}
private List<Expression> fieldReferences(
@@ -764,9 +799,17 @@ private static Expression
getListExpression(Expressions.FluentList<Expression> l
Expression exp = getListExpressionAllowSingleElement(list);
for (int i = list.size() - 1; i >= 0; i--) {
if (nullExclusionFlags.get(i)) {
+ final RelDataType fieldType =
+ rowType.getFieldList().get(fields.get(i)).getType();
+ // Under the SQL = operator, a NULL never compares TRUE. A
+ // ROW containing a NULL field (at any nesting depth) cannot
+ // compare TRUE either: the pairwise comparison of its fields yields
+ // UNKNOWN or FALSE. In both these cases the result is null.
+ final Expression isNull = fieldType.isStruct()
+ ? structIsNullOrContainsNullExpression(list.get(i), fieldType)
+ : Expressions.equal(list.get(i), Expressions.constant(null));
exp =
- Expressions.condition(
- Expressions.equal(list.get(i), Expressions.constant(null)),
+ Expressions.condition(isNull,
Expressions.constant(null),
exp);
}
@@ -774,6 +817,33 @@ private static Expression
getListExpression(Expressions.FluentList<Expression> l
return Expressions.lambda(Function1.class, exp, v1);
}
+ /** Returns an expression that evaluates whether {@code e}, a value of
+ * ROW type {@code type}, is null or has a null field, descending into
+ * struct-typed fields (but not into collection-typed fields). */
+ private static Expression structIsNullOrContainsNullExpression(Expression e,
+ RelDataType type) {
+ Expression result = Expressions.equal(e, Expressions.constant(null));
+ for (Ord<RelDataTypeField> field : Ord.zip(type.getFieldList())) {
+ final RelDataType fieldType = field.e.getType();
+ if (!fieldType.isStruct() && !fieldType.isNullable()) {
+ continue;
+ }
+ // structAccess handles both runtime representations of a struct value
+ // (Object[] and List); it is only evaluated when e is not null,
+ // thanks to the short-circuit OR.
+ final Expression access =
+ Expressions.call(BuiltInMethod.STRUCT_ACCESS.method, e,
+ Expressions.constant(field.i),
+ Expressions.constant(field.e.getName()));
+ result =
+ Expressions.orElse(result,
+ fieldType.isStruct()
+ ? structIsNullOrContainsNullExpression(access, fieldType)
+ : Expressions.equal(access, Expressions.constant(null)));
+ }
+ return result;
+ }
+
@Override public Expression fieldReference(
Expression expression, int field) {
return fieldReference(expression, field, null);
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 7a5ea10f14..1ea24311e3 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -3404,6 +3404,17 @@ private static class BinaryImplementor extends
AbstractRexCallImplementor {
final Type type1 = argValueList.get(1).getType();
final SqlBinaryOperator op = (SqlBinaryOperator) call.getOperator();
final RelDataType relDataType0 = call.getOperands().get(0).getType();
+
+ // Comparing whole ROW values needs three-valued logic: a NULL field
+ // makes the result UNKNOWN, which a boolean-valued comparison of the
+ // row representation cannot express. The call type is nullable
+ // whenever any field is (see SqlTypeUtil.containsNullable).
+ if (EQUALS_OPERATORS.contains(op) && relDataType0.isStruct()) {
+ return Expressions.call(SqlFunctions.class,
+ op.getKind() == SqlKind.EQUALS ? "rowEq" : "rowNe",
+ argValueList);
+ }
+
final Expression fieldComparator =
generateCollatorExpression(relDataType0.getCollation());
if (fieldComparator != null) {
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index b615c31d85..97146b625a 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -2377,7 +2377,7 @@ public static boolean equalType(String desc0, RelNode
rel0, String desc1,
* Returns a translation of the <code>IS DISTINCT FROM</code> (or <code>IS
* NOT DISTINCT FROM</code>) sql operator.
*
- * @param neg if false, returns a translation of IS NOT DISTINCT FROM
+ * @param neg if true, returns a translation of IS NOT DISTINCT FROM
*/
public static RexNode isDistinctFrom(
RexBuilder rexBuilder,
@@ -2402,14 +2402,21 @@ public static RexNode isDistinctFrom(
rexBuilder.makeFieldAccess(
y,
yField.getIndex());
+ // Recurse into a struct field rather than comparing it whole: a
+ // nested "=" is three-valued, and IS [NOT] DISTINCT FROM must reduce
+ // to two-valued logic over scalar leaves.
RexNode newCall =
- isDistinctFromInternal(rexBuilder, newX, newY, neg);
+ newX.getType().isStruct()
+ ? isDistinctFrom(rexBuilder, newX, newY, neg)
+ : isDistinctFromInternal(rexBuilder, newX, newY, neg);
if (ret == null) {
ret = newCall;
} else {
+ // Two rows are not distinct only when every field pair is not
+ // distinct, but they are distinct as soon as one pair is.
ret =
rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
+ neg ? SqlStdOperatorTable.AND : SqlStdOperatorTable.OR,
ret,
newCall);
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index b275c8b6d9..386964963c 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -673,7 +673,10 @@ private <C extends Comparable<C>> RexNode
simplifyComparison(RexCall e,
// Simplify "x <op> x"
final RexNode o0 = operands.get(0);
final RexNode o1 = operands.get(1);
- if (o0.equals(o1) && RexUtil.isDeterministic(o0)) {
+ // "x = x" does not hold for a ROW with a nullable field, which evaluates
to UNKNOWN
+ final boolean nullableStruct =
+ o0.getType().isStruct() && SqlTypeUtil.containsNullable(o0.getType());
+ if (o0.equals(o1) && RexUtil.isDeterministic(o0) && !nullableStruct) {
RexNode newExpr;
switch (e.getKind()) {
case EQUALS:
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 4b9b48041f..15d526393b 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2229,6 +2229,66 @@ public static boolean eq(Object b0, Object b1) {
return b0.equals(b1);
}
+ /** SQL <code>=</code> operator applied to ROW values, with the standard's
+ * three-valued row comparison: FALSE as soon as one field pair is unequal,
+ * UNKNOWN (null) when a field pair involves a NULL and no pair is unequal,
+ * TRUE otherwise.
+ *
+ * <p>A nested ROW, represented as {@code Object[]}, follows the same rule.
+ * A collection-valued field is compared as a whole, because e.g., ARRAY
equality
+ * uses IS NOT DISTINCT FROM semantics and never yields UNKNOWN. */
+ public static @Nullable Boolean rowEq(@Nullable Object b0, @Nullable Object
b1) {
+ if (b0 == null || b1 == null) {
+ return null;
+ }
+ final List<?> l0 = rowAsList(b0);
+ final List<?> l1 = rowAsList(b1);
+ if (l0 == null || l1 == null) {
+ // Not a representation we can take apart; fall back to total equality.
+ return Functions.compareListItems(b0, b1) == 0;
+ }
+ if (l0.size() != l1.size()) {
+ return false;
+ }
+ boolean sawNull = false;
+ for (int i = 0; i < l0.size(); i++) {
+ final Object f0 = l0.get(i);
+ final Object f1 = l1.get(i);
+ if (f0 == null || f1 == null) {
+ sawNull = true;
+ } else if (f0 instanceof Object[] && f1 instanceof Object[]) {
+ final Boolean nested = rowEq(f0, f1);
+ if (nested == null) {
+ sawNull = true;
+ } else if (!nested) {
+ return false;
+ }
+ } else if (Functions.compareListItems(f0, f1) != 0) {
+ return false;
+ }
+ }
+ return sawNull ? null : true;
+ }
+
+ /** SQL <code><></code> operator applied to ROW values; the
+ * three-valued negation of {@link #rowEq}. */
+ public static @Nullable Boolean rowNe(@Nullable Object b0, @Nullable Object
b1) {
+ final Boolean eq = rowEq(b0, b1);
+ return eq == null ? null : !eq;
+ }
+
+ /** Views a ROW value as the list of its fields; returns null if the value is
+ * not one of the representations a ROW may have. */
+ private static @Nullable List<?> rowAsList(Object o) {
+ if (o instanceof Object[]) {
+ return Arrays.asList((Object[]) o);
+ }
+ if (o instanceof List) {
+ return (List<?>) o;
+ }
+ return null;
+ }
+
/** SQL <code>=</code> operator applied to List values. */
public static boolean eq(List<?> b0, List<?> b1) {
return eqNullable(b0, b1);
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index 4139cf4b2b..ead86414f2 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -892,7 +892,7 @@ private boolean tryReplaceFreeVarsToInputRef(
if (pair != null) {
// equi-condition will filter NULL values, so need to add IS NOT
NULL for input ref
if (condition.isA(SqlKind.EQUALS)) {
- newConditions.add(builder.isNotNull(pair.right));
+ newConditions.add(isNotNullDeep(pair.right));
}
corDefToInputIndex.put(pair.left, pair.right.getIndex());
continue;
@@ -909,6 +909,20 @@ private boolean tryReplaceFreeVarsToInputRef(
return replacedCorDef.size() == corDefs.size() &&
corDefs.containsAll(replacedCorDef);
}
+ /** Returns a condition that holds when an expression {@code ref} that may
have a ROW type
+ * contains no 'NULL' field at any depth. */
+ private RexNode isNotNullDeep(RexNode ref) {
+ if (!ref.getType().isStruct()) {
+ return builder.isNotNull(ref);
+ }
+ final List<RexNode> conditions = new ArrayList<>();
+ conditions.add(builder.isNotNull(ref));
+ for (int i = 0; i < ref.getType().getFieldCount(); i++) {
+
conditions.add(isNotNullDeep(builder.getRexBuilder().makeFieldAccess(ref, i)));
+ }
+ return RexUtil.composeConjunction(builder.getRexBuilder(), conditions);
+ }
+
private @Nullable Pair<CorDef, RexInputRef>
getPairOfFreeVarAndInputRefInEqui(RexNode condition) {
if (!condition.isA(SqlKind.EQUALS) &&
!condition.isA(SqlKind.IS_NOT_DISTINCT_FROM)) {
return null;
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 629357b4e0..6b4e52fdf9 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -321,6 +321,7 @@ public enum BuiltInMethod {
NULLS_COMPARATOR2(Functions.class, "nullsComparator", boolean.class,
boolean.class, Comparator.class),
ARRAY_COMPARER(Functions.class, "arrayComparer"),
+ DEEP_COMPARER(Functions.class, "deepComparer"),
FUNCTION0_APPLY(Function0.class, "apply"),
FUNCTION1_APPLY(Function1.class, "apply", Object.class),
ARRAYS_AS_LIST(Arrays.class, "asList", Object[].class),
diff --git a/core/src/test/resources/sql/row-equality.iq
b/core/src/test/resources/sql/row-equality.iq
new file mode 100644
index 0000000000..a61e38704a
--- /dev/null
+++ b/core/src/test/resources/sql/row-equality.iq
@@ -0,0 +1,615 @@
+# row-equality.iq - Tests for equality of ROW values at runtime
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use scott
+!set outputformat mysql
+
+# Test cases for https://issues.apache.org/jira/browse/CALCITE-7678
+# [CALCITE-7678] Runtime equality for ROW values produces incorrect results
+#
+# Calcite has one ROW type, compared everywhere with the standard's row
+# semantics: "(a, b)" and "ROW(a, b)" have the same representation, and a
+# CREATE TYPE structured type uses the same RelRecordType as a ROW
+# expression.
+#
+# Each query below carries a comment recording its status on PostgreSQL 14.
+# Note that Postgres does NOT implement the standard SQL semantics for nested
ROW comparisons.
+
+#####################################################################
+# GROUP BY
+
+# GROUP BY a flat ROW value.
+# Validated on PostgreSQL 14: same result.
+SELECT r, COUNT(*) AS c
+FROM (SELECT ROW(x, y) AS r
+ FROM (VALUES (1, 'a'), (1, 'a'), (2, 'b')) AS v(x, y)) AS t
+GROUP BY r
+ORDER BY c;
++--------+---+
+| R | C |
++--------+---+
+| {2, b} | 1 |
+| {1, a} | 2 |
++--------+---+
+(2 rows)
+
+!ok
+
+# GROUP BY a ROW value with a NULL field: grouping uses not-distinct
+# semantics, so the two ROW(2, NULL) values belong to the same group.
+# Validated on PostgreSQL 14: same result.
+SELECT r, COUNT(*) AS c
+FROM (SELECT ROW(x, y) AS r
+ FROM (VALUES (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t
+GROUP BY r
+ORDER BY c;
++-----------+---+
+| R | C |
++-----------+---+
+| {1, a} | 1 |
+| {2, null} | 2 |
++-----------+---+
+(2 rows)
+
+!ok
+
+# GROUP BY a nested ROW value.
+# Validated on PostgreSQL 14: same result.
+SELECT r, COUNT(*) AS c
+FROM (SELECT ROW(ROW(x, y), z) AS r
+ FROM (VALUES (1, 'a', 10), (1, 'a', 10), (2, 'b', 20)) AS v(x, y, z)) AS
t
+GROUP BY r
+ORDER BY c;
++--------------+---+
+| R | C |
++--------------+---+
+| {{2, b}, 20} | 1 |
+| {{1, a}, 10} | 2 |
++--------------+---+
+(2 rows)
+
+!ok
+
+#####################################################################
+# DISTINCT
+
+# SELECT DISTINCT over ROW values, including ones with a NULL field.
+# Validated on PostgreSQL 14: same result.
+SELECT DISTINCT r
+FROM (SELECT ROW(x, y) AS r
+ FROM (VALUES (1, 'a'), (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t
+ORDER BY r;
++-----------+
+| R |
++-----------+
+| {1, a} |
+| {2, null} |
++-----------+
+(2 rows)
+
+!ok
+
+#####################################################################
+# Set operations
+
+# UNION removes duplicate ROW values, including ones with a NULL field.
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(x, y) AS r
+FROM (VALUES (1, 'a'), (2, NULL)) AS v(x, y)
+UNION
+SELECT ROW(x, y) AS r
+FROM (VALUES (2, NULL)) AS w(x, y)
+ORDER BY r;
++-----------+
+| R |
++-----------+
+| {1, a} |
+| {2, null} |
++-----------+
+(2 rows)
+
+!ok
+
+# INTERSECT over ROW values.
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(x, y) AS r
+FROM (VALUES (1, 'a'), (2, 'b')) AS v(x, y)
+INTERSECT
+SELECT ROW(x, y) AS r
+FROM (VALUES (2, 'b'), (3, 'c')) AS w(x, y);
++--------+
+| R |
++--------+
+| {2, b} |
++--------+
+(1 row)
+
+!ok
+
+# EXCEPT over ROW values.
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(x, y) AS r
+FROM (VALUES (1, 'a'), (2, 'b')) AS v(x, y)
+EXCEPT
+SELECT ROW(x, y) AS r
+FROM (VALUES (2, 'b'), (3, 'c')) AS w(x, y);
++--------+
+| R |
++--------+
+| {1, a} |
++--------+
+(1 row)
+
+!ok
+
+#####################################################################
+# JOIN on ROW values, strict equality
+
+# A flat ROW value equals itself and nothing else.
+# Validated on PostgreSQL 14: same result.
+WITH t(x, r) AS (
+ SELECT x, ROW(x, y)
+ FROM (VALUES (1, 'a'), (2, 'b')) AS v(x, y))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r = t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
+| 2 | 2 |
++----+----+
+(2 rows)
+
+!ok
+
+# A nested ROW value equals itself and nothing else.
+# Validated on PostgreSQL 14: same result.
+WITH t(x, r) AS (
+ SELECT x, ROW(ROW(x, y), z)
+ FROM (VALUES (1, 'a', 10), (2, 'b', 20)) AS v(x, y, z))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r = t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
+| 2 | 2 |
++----+----+
+(2 rows)
+
+!ok
+
+# A NULL field makes the strict comparison UNKNOWN, so ROW(2, NULL) does
+# not join with itself. Postgres returns 2 rows instead of one, because
+# in Postgres (1, 'a') is not the same as ROW(1, 'a').
+WITH t(x, r) AS (
+ SELECT x, ROW(x, y)
+ FROM (VALUES (1, 'a'), (2, NULL)) AS v(x, y))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r = t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
++----+----+
+(1 row)
+
+!ok
+
+# Same problem as above when validated on Postgres.
+WITH t(x, r) AS (
+ SELECT x, ROW(ROW(x, y), z)
+ FROM (VALUES (1, 'a', 10), (2, NULL, 20)) AS v(x, y, z))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r = t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
++----+----+
+(1 row)
+
+!ok
+
+#####################################################################
+# JOIN on ROW values using IS NOT DISTINCT FROM
+
+# Under not-distinct semantics the ROW(2, NULL) pair matches.
+# Validated on PostgreSQL 14: same result.
+WITH t(x, r) AS (
+ SELECT x, ROW(x, y)
+ FROM (VALUES (1, 'a'), (2, NULL)) AS v(x, y))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r IS NOT DISTINCT FROM t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
+| 2 | 2 |
++----+----+
+(2 rows)
+
+!ok
+
+# The same, for a NULL in the inner ROW.
+# Validated on PostgreSQL 14: same result.
+WITH t(x, r) AS (
+ SELECT x, ROW(ROW(x, y), z)
+ FROM (VALUES (1, 'a', 10), (2, NULL, 20)) AS v(x, y, z))
+SELECT t1.x AS x1, t2.x AS x2
+FROM t AS t1
+JOIN t AS t2 ON t1.r IS NOT DISTINCT FROM t2.r
+ORDER BY x1;
++----+----+
+| X1 | X2 |
++----+----+
+| 1 | 1 |
+| 2 | 2 |
++----+----+
+(2 rows)
+
+!ok
+
+#####################################################################
+# Comparison in predicate position
+
+# Row comparison is three-valued. Row 2 is UNKNOWN because the only
+# difference is a NULL field pair; row 3 is FALSE because the first fields
+# already differ, which outranks the NULL pair. IS DISTINCT FROM stays
+# two-valued throughout.
+#
+# Postgres answers TRUE for row 2, because tuples are not ROW values in
Postgres.
+WITH t(id, a, b) AS (
+ SELECT id, ROW(x, y), ROW(z, w)
+ FROM (VALUES (1, 1, 'a', 1, 'a'),
+ (2, 1, NULL, 1, NULL),
+ (3, 1, NULL, 2, NULL),
+ (4, 1, 'a', 2, 'b')) AS v(id, x, y, z, w))
+SELECT id, a = b AS eq, a <> b AS ne, a IS DISTINCT FROM b AS dist
+FROM t
+ORDER BY id;
++----+-------+-------+-------+
+| ID | EQ | NE | DIST |
++----+-------+-------+-------+
+| 1 | true | false | false |
+| 2 | | | false |
+| 3 | false | true | true |
+| 4 | false | true | true |
++----+-------+-------+-------+
+(4 rows)
+
+!ok
+
+# A NULL nested inside an inner ROW makes the comparison UNKNOWN too.
+# Postgres answers TRUE for row 2.
+WITH t(id, a, b) AS (
+ SELECT id, ROW(ROW(x, y), x), ROW(ROW(z, w), z)
+ FROM (VALUES (1, 1, 'a', 1, 'a'),
+ (2, 1, NULL, 1, NULL)) AS v(id, x, y, z, w))
+SELECT id, a = b AS eq
+FROM t
+ORDER BY id;
++----+------+
+| ID | EQ |
++----+------+
+| 1 | true |
+| 2 | |
++----+------+
+(2 rows)
+
+!ok
+
+# Constant folding must reach the same answer as the runtime.
+# In PostgreSQL 14: the deeply nested ROWs evaluate to TRUE there.
+SELECT ROW(1, CAST(NULL AS VARCHAR))
+ = ROW(1, CAST(NULL AS VARCHAR)) AS flat,
+ ROW(ROW(1, CAST(NULL AS VARCHAR)), 2)
+ = ROW(ROW(1, CAST(NULL AS VARCHAR)), 2) AS nested2,
+ ROW(ROW(ROW(1, CAST(NULL AS VARCHAR)), 2), 3)
+ = ROW(ROW(ROW(1, CAST(NULL AS VARCHAR)), 2), 3) AS nested3;
++------+---------+---------+
+| FLAT | NESTED2 | NESTED3 |
++------+---------+---------+
+| | | |
++------+---------+---------+
+(1 row)
+
+!ok
+
+# With no nullable field anywhere, "x = x" still folds to TRUE.
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(ROW(1, CAST('a' AS VARCHAR)), 2)
+ = ROW(ROW(1, CAST('a' AS VARCHAR)), 2) AS all_non_null;
++--------------+
+| ALL_NON_NULL |
++--------------+
+| true |
++--------------+
+(1 row)
+
+!ok
+
+# Three levels of nesting, with the NULL at the innermost level. The
+# UNKNOWN has to propagate all the way out for =, while IS [NOT] DISTINCT
+# FROM stays two-valued at every level.
+WITH t(id, a, b) AS (
+ SELECT id, ROW(ROW(ROW(x, y), x), x), ROW(ROW(ROW(z, w), z), z)
+ FROM (VALUES (1, 1, 'a', 1, 'a'),
+ (2, 1, NULL, 1, NULL),
+ (3, 1, NULL, 2, NULL)) AS v(id, x, y, z, w))
+SELECT id, a = b AS eq, a IS DISTINCT FROM b AS dist,
+ a IS NOT DISTINCT FROM b AS ndf
+FROM t
+ORDER BY id;
++----+-------+-------+-------+
+| ID | EQ | DIST | NDF |
++----+-------+-------+-------+
+| 1 | true | false | true |
+| 2 | | false | true |
+| 3 | false | true | false |
++----+-------+-------+-------+
+(3 rows)
+
+!ok
+
+#####################################################################
+# Nested ROW values produced by a query
+
+WITH t(id, r) AS (
+ SELECT id, ROW(ROW(x, y), x)
+ FROM (VALUES (1, 1, 'a'), (2, 1, NULL)) AS v(id, x, y))
+SELECT t1.id, t1.r = t2.r AS eq, t1.r IS DISTINCT FROM t2.r AS dist
+FROM t AS t1
+JOIN t AS t2 ON t1.id = t2.id
+ORDER BY t1.id;
++----+------+-------+
+| ID | EQ | DIST |
++----+------+-------+
+| 1 | true | false |
+| 2 | | false |
++----+------+-------+
+(2 rows)
+
+!ok
+
+# Grouped after a UNION ALL: the four rows collapse to two values.
+# Validated on PostgreSQL 14: same result.
+SELECT COUNT(*) AS n FROM (
+ SELECT r FROM (
+ SELECT ROW(ROW(x, y), x) AS r
+ FROM (VALUES (1, 'a'), (2, NULL)) AS v(x, y)
+ UNION ALL
+ SELECT ROW(ROW(x, y), x) AS r
+ FROM (VALUES (1, 'a'), (2, NULL)) AS w(x, y)) AS u
+ GROUP BY r) AS g;
++---+
+| N |
++---+
+| 2 |
++---+
+(1 row)
+
+!ok
+
+# The NULL field is produced by an aggregate rather than written literally.
+# In PostgreSQL 14 row 1 is TRUE.
+SELECT g, ROW(ROW(g, MIN(v)), g) = ROW(ROW(g, MIN(v)), g) AS eq
+FROM (VALUES (1, CAST(NULL AS VARCHAR)), (2, 'x')) AS t(g, v)
+GROUP BY g
+ORDER BY g;
++---+------+
+| G | EQ |
++---+------+
+| 1 | |
+| 2 | true |
++---+------+
+(2 rows)
+
+!ok
+
+# PostgreSQL 14 differs: TRUE for row 2.
+WITH t(id, r) AS (
+ SELECT id, ROW(ROW(x, y), x)
+ FROM (VALUES (1, 1, 'a'), (2, 1, NULL)) AS v(id, x, y))
+SELECT id,
+ EXISTS (SELECT 1 FROM t AS u WHERE u.r = t.r) AS ex,
+ EXISTS (SELECT 1 FROM t AS u WHERE u.r IS NOT DISTINCT FROM t.r) AS
ex_ndf
+FROM t
+ORDER BY id;
++----+-------+--------+
+| ID | EX | EX_NDF |
++----+-------+--------+
+| 1 | true | true |
+| 2 | false | true |
++----+-------+--------+
+(2 rows)
+
+!ok
+
+#####################################################################
+# IN predicates
+#
+# IN and NOT IN are defined in terms of =, so over ROW values they inherits
+# three-valued comparisons.
+
+# Both operands are row constructors.
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(1, CAST(NULL AS VARCHAR))
+ IN (ROW(1, CAST(NULL AS VARCHAR))) AS in_null,
+ ROW(1, CAST(NULL AS VARCHAR))
+ NOT IN (ROW(1, CAST(NULL AS VARCHAR))) AS notin_null,
+ ROW(1, 'a') IN (ROW(1, 'a'), ROW(2, 'b')) AS in_true,
+ ROW(1, CAST(NULL AS VARCHAR)) IN (ROW(2, 'b')) AS in_false;
++---------+------------+---------+----------+
+| IN_NULL | NOTIN_NULL | IN_TRUE | IN_FALSE |
++---------+------------+---------+----------+
+| | | true | false |
++---------+------------+---------+----------+
+(1 row)
+
+!ok
+
+# Struct-typed columns rather than constructors.
+# In PostgreSQL 14 row 2 is TRUE/FALSE.
+WITH t(id, a, b) AS (
+ SELECT id, ROW(x, y), ROW(z, w)
+ FROM (VALUES (1, 1, 'a', 1, 'a'),
+ (2, 1, NULL, 1, NULL),
+ (3, 1, NULL, 2, NULL)) AS v(id, x, y, z, w))
+SELECT id, a IN (b) AS in_b, a NOT IN (b) AS notin_b
+FROM t
+ORDER BY id;
++----+-------+---------+
+| ID | IN_B | NOTIN_B |
++----+-------+---------+
+| 1 | true | false |
+| 2 | | |
+| 3 | false | true |
++----+-------+---------+
+(3 rows)
+
+!ok
+
+# Row-valued IN against a sub-query.
+# Validated on PostgreSQL 14: same result.
+SELECT (1, CAST(NULL AS VARCHAR))
+ IN (SELECT 1, CAST(NULL AS VARCHAR)) AS in_subq;
++---------+
+| IN_SUBQ |
++---------+
+| |
++---------+
+(1 row)
+
+!ok
+
+#####################################################################
+# ROW values inside a collection
+#
+# A ROW nested in a collection is compared by a different rule
+# (IS NOT DISTINCT FROM) than a
+# top-level ROW. Array equality is total: it compares element-wise and
+# never yields UNKNOWN, so the NULL fields of the nested ROW values are
+# treated as equal. ROW(NULL) is not equal with ROW(NULL), but
+# ARRAY[ROW(NULL)] is equal to ARRAY[ROW(NULL)].
+#
+# Postgres agrees with Calcite on every query in this section, because
+# both compare collections totally.
+
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(1, CAST(NULL AS VARCHAR))
+ = ROW(1, CAST(NULL AS VARCHAR)) AS bare,
+ ARRAY[ROW(1, CAST(NULL AS VARCHAR))]
+ = ARRAY[ROW(1, CAST(NULL AS VARCHAR))] AS in_array;
++------+----------+
+| BARE | IN_ARRAY |
++------+----------+
+| | true |
++------+----------+
+(1 row)
+
+!ok
+
+# Validated on PostgreSQL 14: same result.
+SELECT ARRAY[ROW(1, CAST(NULL AS VARCHAR))]
+ = ARRAY[ROW(2, CAST(NULL AS VARCHAR))] AS differ;
++--------+
+| DIFFER |
++--------+
+| false |
++--------+
+(1 row)
+
+!ok
+
+# Validated on PostgreSQL 14: same result.
+WITH t(id, a, b) AS (
+ SELECT id, ARRAY[ROW(x, y)], ARRAY[ROW(z, w)]
+ FROM (VALUES (1, 1, 'a', 1, 'a'),
+ (2, 1, NULL, 1, NULL),
+ (3, 1, NULL, 2, NULL)) AS v(id, x, y, z, w))
+SELECT id, a = b AS eq, a IS DISTINCT FROM b AS dist
+FROM t
+ORDER BY id;
++----+-------+-------+
+| ID | EQ | DIST |
++----+-------+-------+
+| 1 | true | false |
+| 2 | true | false |
+| 3 | false | true |
++----+-------+-------+
+(3 rows)
+
+!ok
+
+# Validated on PostgreSQL 14: same result.
+SELECT ROW(1, ARRAY[ROW(1, CAST(NULL AS VARCHAR))])
+ = ROW(1, ARRAY[ROW(1, CAST(NULL AS VARCHAR))]) AS row_of_array;
++--------------+
+| ROW_OF_ARRAY |
++--------------+
+| true |
++--------------+
+(1 row)
+
+!ok
+
+# Validated on PostgreSQL 14: same result.
+SELECT a, COUNT(*) AS c
+FROM (SELECT ARRAY[ROW(x, y)] AS a
+ FROM (VALUES (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t
+GROUP BY a
+ORDER BY c;
++-------------+---+
+| A | C |
++-------------+---+
+| [{1, a}] | 1 |
+| [{2, null}] | 2 |
++-------------+---+
+(2 rows)
+
+!ok
+
+# A MAP holding ROW values also uses IS NOT DISTINCT FROM
+# Postgres has no MAP type.
+SELECT c
+FROM (SELECT COUNT(*) AS c
+ FROM (SELECT MAP['k', ROW(x, y)] AS m
+ FROM (VALUES (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t
+ GROUP BY m) AS g
+ORDER BY c;
++---+
+| C |
++---+
+| 1 |
+| 2 |
++---+
+(2 rows)
+
+!ok
+
+# End row-equality.iq
diff --git
a/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
b/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
index 6c0a41297b..f61f65a6d2 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
@@ -81,6 +81,9 @@ private Functions() {}
private static final EqualityComparer<@Nullable Object[]> ARRAY_COMPARER =
new ArrayEqualityComparer();
+ private static final EqualityComparer<@Nullable Object> DEEP_COMPARER =
+ new DeepEqualityComparer();
+
private static final Function1 CONSTANT_NULL_FUNCTION1 =
(Function1<Object, @Nullable Object>) s -> null;
@@ -488,6 +491,20 @@ public static <T, T2> EqualityComparer<T> selectorComparer(
return new SelectorEqualityComparer<>(selector);
}
+ /**
+ * Returns an {@link EqualityComparer} that compares values deeply:
+ * {@code Object[]} arrays and {@link List}s are compared element-wise and
+ * recursively, and compare equal to each other when their elements are
+ * equal, regardless of container kind; primitive arrays are compared by
+ * content; {@code null} equals {@code null}.
+ *
+ * <p>This implements the SQL "not distinct" semantics used by
+ * {@code GROUP BY}, {@code DISTINCT} and set operations. */
+ @SuppressWarnings("unchecked")
+ public static <T> EqualityComparer<T> deepComparer() {
+ return (EqualityComparer) DEEP_COMPARER;
+ }
+
/** Array equality comparer. */
private static class ArrayEqualityComparer
implements EqualityComparer<@Nullable Object[]> {
@@ -500,6 +517,127 @@ private static class ArrayEqualityComparer
}
}
+ /** Deep equality comparer; see {@link #deepComparer()}. */
+ private static class DeepEqualityComparer
+ implements EqualityComparer<@Nullable Object> {
+ @Override public boolean equal(@Nullable Object v1, @Nullable Object v2) {
+ return deepEquals(v1, v2);
+ }
+
+ @Override public int hashCode(@Nullable Object t) {
+ return deepHashCode(t);
+ }
+
+ private static boolean deepEquals(@Nullable Object v1, @Nullable Object
v2) {
+ if (v1 == v2) {
+ return true;
+ }
+ if (v1 == null || v2 == null) {
+ return false;
+ }
+ // Normalize both to List: an ARRAY of ROW is a List of Object[],
+ // and each element is normalized in turn.
+ final @Nullable List<?> list1 = asListOrNull(v1);
+ final @Nullable List<?> list2 = asListOrNull(v2);
+ if (list1 != null && list2 != null) {
+ final int n = list1.size();
+ if (n != list2.size()) {
+ return false;
+ }
+ for (int i = 0; i < n; i++) {
+ if (!deepEquals(list1.get(i), list2.get(i))) {
+ return false;
+ }
+ }
+ return true;
+ }
+ if (list1 != null || list2 != null) {
+ return false;
+ }
+ if (v1 instanceof Map && v2 instanceof Map) {
+ return mapDeepEquals((Map<?, ?>) v1, (Map<?, ?>) v2);
+ }
+ if (v1.getClass().isArray() && v2.getClass().isArray()) {
+ // Primitive arrays (e.g. byte[] for BINARY values).
+ return Arrays.deepEquals(new Object[] {v1}, new Object[] {v2});
+ }
+ return v1.equals(v2);
+ }
+
+ /** Compares two maps as unordered sets of entries, comparing keys and
+ * values deeply.
+ *
+ * <p>Java {@link Map#equals} is already order-independent, but it looks a
+ * key up by that key's own hashCode and equals, which matches a struct key
+ * only by reference; hence the scan. */
+ private static boolean mapDeepEquals(Map<?, ?> m1, Map<?, ?> m2) {
+ if (m1.size() != m2.size()) {
+ return false;
+ }
+ // Remove on match, so that keys that are deep-equal but distinct to
+ // Java, as two Object[] with the same contents are, pair up one to one.
+ final List<Map.Entry<?, ?>> unmatched = new ArrayList<>(m2.entrySet());
+ for (Map.Entry<?, ?> e1 : m1.entrySet()) {
+ boolean found = false;
+ for (int i = 0; i < unmatched.size(); i++) {
+ final Map.Entry<?, ?> e2 = unmatched.get(i);
+ if (deepEquals(e1.getKey(), e2.getKey())
+ && deepEquals(e1.getValue(), e2.getValue())) {
+ unmatched.remove(i);
+ found = true;
+ break;
+ }
+ }
+ if (!found) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ /** Computes a hash code that is equal for values that
+ * {@link #deepEquals} considers equal; in particular, an
+ * {@code Object[]} and a {@link List} with equal elements hash alike. */
+ private static int deepHashCode(@Nullable Object o) {
+ if (o == null) {
+ return 0x789d;
+ }
+ final @Nullable List<?> list = asListOrNull(o);
+ if (list != null) {
+ int h = 1;
+ for (Object element : list) {
+ h = 31 * h + deepHashCode(element);
+ }
+ return h;
+ }
+ if (o instanceof Map) {
+ // Sum of per-entry hashes, as Map.hashCode does, so that the hash
+ // ignores entry order just as mapDeepEquals does.
+ int h = 0;
+ for (Map.Entry<?, ?> e : ((Map<?, ?>) o).entrySet()) {
+ h += deepHashCode(e.getKey()) ^ deepHashCode(e.getValue());
+ }
+ return h;
+ }
+ if (o.getClass().isArray()) {
+ return Arrays.deepHashCode(new Object[] {o});
+ }
+ return o.hashCode();
+ }
+
+ /** Views {@code o} as a list if it is a {@code List} or an
+ * {@code Object[]}; returns null otherwise. */
+ private static @Nullable List<?> asListOrNull(Object o) {
+ if (o instanceof List) {
+ return (List<?>) o;
+ }
+ if (o instanceof Object[]) {
+ return Arrays.asList((Object[]) o);
+ }
+ return null;
+ }
+ }
+
/** Identity equality comparer. */
private static class IdentityEqualityComparer
implements EqualityComparer<Object> {
@@ -636,7 +774,10 @@ private static BigDecimal toBigDecimal(Number number) {
: new BigDecimal(number.doubleValue());
}
- private static int compareListItems(@Nullable Object item0, @Nullable Object
item1) {
+ /** Compares two values as elements of a list, array or row: nested
+ * collections and arrays are compared element-wise, numbers are compared by
+ * value regardless of their Java type, and nulls sort last. */
+ public static int compareListItems(@Nullable Object item0, @Nullable Object
item1) {
if (item0 == item1) {
return 0;
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 56fbf8a86c..4b198449ae 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1483,6 +1483,32 @@ ### Comparison operators
| <=>
{% endhighlight %}
+Note:
+
+* Comparing two `ROW` values with `=` or `<>` compares their fields pairwise,
+ using three-valued logic: the result is FALSE if some pair of fields is
+ unequal, UNKNOWN if some pair involves a null and no pair is unequal, and
+ TRUE otherwise. For example, `ROW(1, NULL) = ROW(1, NULL)` is UNKNOWN, but
+ `ROW(1, NULL) = ROW(2, NULL)` is FALSE.
+* `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` treat nulls as equal, so on
+ `ROW` values they always return TRUE or FALSE. Two rows are distinct if some
+ pair of their fields is distinct.
+* `JOIN ON ROW(a, b) = ROW(c, d)` uses this definition of row equality. This
is
+ equivalent to expanding equality for rows to their corresponding fields
recursively:
+ `JOIN a = c AND b = d`.
+* `IN` and `NOT IN` are defined in terms of `=`, and over `ROW` values they
+ inherit the same three-valued result.
+ `ROW(1, NULL) IN (ROW(1, NULL))` and the corresponding `NOT IN` expression
+ evaluate to UNKNOWN. The quantified comparisons
+ `SOME`, `ANY` and `ALL` currently do not accept `ROW` operands.
+* Comparing two collection values (`ARRAY`, `MULTISET`, `MAP`) treats NULL
+ elements as equal, so the result is never UNKNOWN. A `ROW` nested in a
+ collection is therefore compared the way `IS NOT DISTINCT FROM` compares it,
+ and a NULL inside a collection does *not* make a comparison of the enclosing
+ `ROW` value UNKNOWN.
+* `GROUP BY`, `DISTINCT` and the set operators (`UNION`, `INTERSECT`, `EXCEPT`)
+ compare values as `IS NOT DISTINCT FROM` does.
+
### Logical operators
| Operator syntax | Description
