You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mb...@apache.org on 2025/11/27 19:42:17 UTC
(calcite) 01/01: [CALCITE-7270] Add support for a DIVIDE_0_NULL operation
This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch issue7270
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit ef2dbaa505ba143c82e930063734c5ecb0fd98f2
Author: Mihai Budiu <mb...@feldera.com>
AuthorDate: Thu Nov 27 11:37:56 2025 -0800
[CALCITE-7270] Add support for a DIVIDE_0_NULL operation
Signed-off-by: Mihai Budiu <mb...@feldera.com>
---
.../calcite/adapter/enumerable/RexImpTable.java | 16 ++-
.../main/java/org/apache/calcite/plan/Strong.java | 2 +
.../java/org/apache/calcite/rex/RexSimplify.java | 4 +
.../org/apache/calcite/runtime/SqlFunctions.java | 137 +++++++++++++++++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 17 ++-
.../calcite/sql/fun/SqlStdOperatorTable.java | 45 +++++++
.../apache/calcite/sql/type/SqlTypeTransforms.java | 11 ++
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 7 ++
.../calcite/sql/validate/SqlConformanceEnum.java | 13 ++
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql2rel/ConvertDivideToNullable.java | 55 +++++++++
.../apache/calcite/sql2rel/ConvertToChecked.java | 3 +
.../apache/calcite/sql2rel/SqlToRelConverter.java | 15 +++
.../calcite/sql2rel/StandardConvertletTable.java | 18 +++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../org/apache/calcite/test/SqlValidatorTest.java | 2 +
core/src/test/resources/sql/misc.iq | 66 +++++++++-
.../calcite/linq4j/tree/BinaryExpression.java | 2 +
.../apache/calcite/linq4j/tree/ExpressionType.java | 18 +++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 19 ++-
22 files changed, 455 insertions(+), 5 deletions(-)
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 ca8f93feb9..8b4d05fff2 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
@@ -116,6 +116,8 @@
import static org.apache.calcite.linq4j.tree.ExpressionType.Add;
import static org.apache.calcite.linq4j.tree.ExpressionType.AddChecked;
import static org.apache.calcite.linq4j.tree.ExpressionType.Divide;
+import static org.apache.calcite.linq4j.tree.ExpressionType.Divide0Null;
+import static org.apache.calcite.linq4j.tree.ExpressionType.Divide0NullChecked;
import static org.apache.calcite.linq4j.tree.ExpressionType.DivideChecked;
import static org.apache.calcite.linq4j.tree.ExpressionType.Equal;
import static org.apache.calcite.linq4j.tree.ExpressionType.GreaterThan;
@@ -384,6 +386,7 @@
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHARACTER_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHAR_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHECKED_DIVIDE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHECKED_DIVIDE_0_NULL;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHECKED_DIVIDE_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHECKED_MINUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHECKED_MULTIPLY;
@@ -410,6 +413,7 @@
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DEGREES;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DENSE_RANK;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE_0_NULL;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ELEMENT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.EQUALS;
@@ -478,6 +482,7 @@
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MINUS_DATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MOD;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MODE;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MOD_0_NULL;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MULTIPLY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MULTISET_EXCEPT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MULTISET_EXCEPT_DISTINCT;
@@ -828,6 +833,7 @@ void populate1() {
defineBinary(MINUS, Subtract, NullPolicy.STRICT, "minus");
defineBinary(MULTIPLY, Multiply, NullPolicy.STRICT, "multiply");
defineBinary(DIVIDE, Divide, NullPolicy.STRICT, "divide");
+ defineBinary(DIVIDE_0_NULL, Divide, NullPolicy.SEMI_STRICT, "divide0Null");
defineBinary(DIVIDE_INTEGER, Divide, NullPolicy.STRICT, "divide");
defineUnary(UNARY_MINUS, Negate, NullPolicy.STRICT,
BuiltInMethod.BIG_DECIMAL_NEGATE.getMethodName());
@@ -839,8 +845,13 @@ void populate1() {
defineBinary(CHECKED_DIVIDE, DivideChecked, NullPolicy.STRICT, "checkedDivide");
defineBinary(CHECKED_DIVIDE_INTEGER, DivideChecked, NullPolicy.STRICT, "checkedDivide");
defineUnary(CHECKED_UNARY_MINUS, NegateChecked, NullPolicy.STRICT, "checkedUnaryMinus");
+ // nullable division
+ defineBinary(DIVIDE_0_NULL, Divide0Null, NullPolicy.SEMI_STRICT, "nullableDivide");
+ defineBinary(CHECKED_DIVIDE_0_NULL, Divide0NullChecked,
+ NullPolicy.SEMI_STRICT, "checkedNullableDivide");
defineMethod(MOD, BuiltInMethod.MOD.method, NullPolicy.STRICT);
+ defineMethod(MOD_0_NULL, BuiltInMethod.MOD_0_NULL.method, NullPolicy.SEMI_STRICT);
defineMethod(EXP, BuiltInMethod.EXP.method, NullPolicy.STRICT);
defineMethod(POWER, BuiltInMethod.POWER.method, NullPolicy.STRICT);
defineMethod(POWER_PG, BuiltInMethod.POWER_PG.method, NullPolicy.STRICT);
@@ -3347,7 +3358,10 @@ private static class BinaryImplementor extends AbstractRexCallImplementor {
}
// For checked arithmetic call the method.
- if (CHECKED_OPERATORS.contains(op)) {
+ if (CHECKED_OPERATORS.contains(op)
+ || op.kind == SqlKind.DIVIDE_0_NULL
+ || op.kind == SqlKind.MOD_0_NULL
+ || op.kind == SqlKind.CHECKED_DIVIDE_0_NULL) {
return Expressions.call(SqlFunctions.class, backupMethodName, argValueList);
}
diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java b/core/src/main/java/org/apache/calcite/plan/Strong.java
index b92c98f560..ea24562ad1 100644
--- a/core/src/main/java/org/apache/calcite/plan/Strong.java
+++ b/core/src/main/java/org/apache/calcite/plan/Strong.java
@@ -355,6 +355,8 @@ private static Map<SqlKind, Policy> createPolicyMap() {
map.put(SqlKind.CHECKED_MINUS_PREFIX, Policy.ANY);
map.put(SqlKind.CHECKED_TIMES, Policy.ANY);
map.put(SqlKind.CHECKED_DIVIDE, Policy.ANY);
+ map.put(SqlKind.DIVIDE_0_NULL, Policy.AS_IS);
+ map.put(SqlKind.MOD_0_NULL, Policy.AS_IS);
map.put(SqlKind.DIVIDE, Policy.ANY);
map.put(SqlKind.CAST, Policy.ANY);
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 bb7ccf7050..4e384650ff 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -327,6 +327,8 @@ RexNode simplify(RexNode e, RexUnknownAs unknownAs) {
case MINUS:
case TIMES:
case DIVIDE:
+ case DIVIDE_0_NULL:
+ case CHECKED_DIVIDE_0_NULL:
case CHECKED_PLUS:
case CHECKED_MINUS:
case CHECKED_TIMES:
@@ -457,6 +459,8 @@ private RexNode simplifyArithmetic(RexCall e) {
return simplifyMultiply(e);
case DIVIDE:
case CHECKED_DIVIDE:
+ case DIVIDE_0_NULL:
+ case CHECKED_DIVIDE_0_NULL:
return simplifyDivide(e);
default:
throw new IllegalArgumentException("Unsupported arithmetic operation " + e.getKind());
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 45c2f7be97..335bac407f 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2823,6 +2823,95 @@ public static long divide(long b0, BigDecimal b1) {
: ULong.valueOf(UnsignedType.toBigInteger(b0).divide(UnsignedType.toBigInteger(b1)));
}
+ // nullable divide
+
+ @Nullable public static Integer divide0Null(int b0, BigDecimal b1) {
+ if (b1.equals(BigDecimal.ZERO)) {
+ return null;
+ }
+ return BigDecimal.valueOf(b0)
+ .divide(b1, RoundingMode.HALF_DOWN).intValue();
+ }
+
+ @Nullable public static Long divide0Null(long b0, BigDecimal b1) {
+ if (b1.equals(BigDecimal.ZERO)) {
+ return null;
+ }
+ return BigDecimal.valueOf(b0)
+ .divide(b1, RoundingMode.HALF_DOWN).longValue();
+ }
+
+ @Nullable public static @PolyNull UByte divide0Null(@PolyNull UByte b0,
+ @PolyNull UByte b1) {
+ return (b0 == null || b1 == null) ? castNonNull(null)
+ : (b1.intValue() == 0 ? null : UByte.valueOf(b0.intValue() / b1.intValue()));
+ }
+
+ @Nullable public static @PolyNull UShort divide0Null(@PolyNull UShort b0,
+ @PolyNull UShort b1) {
+ return (b0 == null || b1 == null) ? castNonNull(null)
+ : (b1.intValue() == 0 ? null : UShort.valueOf(b0.intValue() / b1.intValue()));
+ }
+
+ @Nullable public static @PolyNull UInteger divide0Null(@PolyNull UInteger b0,
+ @PolyNull UInteger b1) {
+ return (b0 == null || b1 == null) ? castNonNull(null)
+ : (b1.longValue() == 0L ? null : UInteger.valueOf(b0.longValue() / b1.longValue()));
+ }
+
+ @Nullable public static @PolyNull ULong divide0Null(@PolyNull ULong b0,
+ @PolyNull ULong b1) {
+ return (b0 == null || b1 == null) ? castNonNull(null)
+ : (b1.equals(ULong.valueOf(0))
+ ? null
+ : ULong.valueOf(UnsignedType.toBigInteger(b0).divide(UnsignedType.toBigInteger(b1))));
+ }
+
+ /** SQL <code>/</code> operator applied to int values. */
+ @Nullable public static Integer divide0Null(int b0, int b1) {
+ return b1 == 0 ? null : b0 / b1;
+ }
+
+ /** SQL <code>/</code> operator applied to int values; left side may be
+ * null. */
+ @Nullable public static @PolyNull Integer divide0Null(@PolyNull Integer b0, int b1) {
+ return b0 == null ? castNonNull(null) : (b1 == 0 ? null : b0 / b1);
+ }
+
+ /** SQL <code>/</code> operator applied to int values; right side may be
+ * null. */
+ @Nullable public static @PolyNull Integer divide0Null(int b0, @PolyNull Integer b1) {
+ return b1 == null ? castNonNull(null) : (b1 == 0 ? null : b0 / b1);
+ }
+
+ /** SQL <code>/</code> operator applied to nullable int values. */
+ @Nullable public static @PolyNull Integer divide0Null(@PolyNull Integer b0,
+ @PolyNull Integer b1) {
+ return (b0 == null || b1 == null) ? castNonNull(null) : (b1 == 0 ? null : b0 / b1);
+ }
+
+ /** SQL <code>/</code> operator applied to nullable long and int values. */
+ @Nullable public static @PolyNull Long divide0Null(Long b0, @PolyNull Integer b1) {
+ return (b0 == null || b1 == null)
+ ? castNonNull(null)
+ : (b1 == 0 ? null : b0.longValue() / b1.longValue());
+ }
+
+ /** SQL <code>/</code> operator applied to nullable int and long values. */
+ @Nullable public static @PolyNull Long divide0Null(@PolyNull Integer b0, @PolyNull Long b1) {
+ return (b0 == null || b1 == null)
+ ? castNonNull(null)
+ : (b1 == 0 ? null : b0.longValue() / b1.longValue());
+ }
+
+ /** SQL <code>/</code> operator applied to BigDecimal values. */
+ @Nullable public static @PolyNull BigDecimal divide0Null(@PolyNull BigDecimal b0,
+ @PolyNull BigDecimal b1) {
+ return (b0 == null || b1 == null)
+ ? castNonNull(null)
+ : (b1.equals(BigDecimal.ZERO) ? null : b0.divide(b1, MathContext.DECIMAL64));
+ }
+
public static byte checkedDivide(byte b0, byte b1) {
return intToByte(b0 / b1);
}
@@ -3906,6 +3995,54 @@ public static BigDecimal mod(BigDecimal b0, BigDecimal b1) {
return bigDecimals[1];
}
+ /** SQL nullable <code>MOD</code> operator applied to byte values. */
+ @Nullable public static Byte mod0Null(byte b0, byte b1) {
+ if (b1 == 0) {
+ return null;
+ }
+ return (byte) (b0 % b1);
+ }
+
+ /** SQL nullable <code>MOD</code> operator applied to short values. */
+ @Nullable public static Short mod0Null(short b0, short b1) {
+ if (b1 == 0) {
+ return null;
+ }
+ return (short) (b0 % b1);
+ }
+
+ /** SQL nullable <code>MOD</code> operator applied to int values. */
+ @Nullable public static Integer mod0Null(int b0, int b1) {
+ if (b1 == 0) {
+ return null;
+ }
+ return b0 % b1;
+ }
+
+ /** SQL nullable <code>MOD</code> operator applied to long values. */
+ @Nullable public static Long mod0Null(long b0, long b1) {
+ if (b1 == 0L) {
+ return null;
+ }
+ return b0 % b1;
+ }
+
+ @Nullable public static BigDecimal mod0Null(BigDecimal b0, int b1) {
+ return mod0Null(b0, BigDecimal.valueOf(b1));
+ }
+
+ @Nullable public static BigDecimal mod0Null(int b0, BigDecimal b1) {
+ return mod(BigDecimal.valueOf(b0), b1);
+ }
+
+ @Nullable public static BigDecimal mod0Null(BigDecimal b0, BigDecimal b1) {
+ if (b1.equals(BigDecimal.ZERO)) {
+ return null;
+ }
+ final BigDecimal[] bigDecimals = b0.divideAndRemainder(b1);
+ return bigDecimals[1];
+ }
+
// FLOOR
public static double floor(double b0) {
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index d2c57864b8..26af22d8a1 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -296,6 +296,10 @@ public enum SqlKind {
/** Arithmetic remainder operator, "MOD" (and "%" in some dialects). */
MOD,
+ /** Nullable arithmetic remainder operator which returns NULL when remainder is zero,
+ * "MOD" (and "%" in some dialects). */
+ MOD_0_NULL,
+
/**
* Arithmetic plus operator, "+".
*
@@ -335,6 +339,16 @@ public enum SqlKind {
*/
CHECKED_DIVIDE,
+ /**
+ * Unchecked nullable version of DIVIDE, which produces NULL when dividing by zero.
+ */
+ DIVIDE_0_NULL,
+
+ /**
+ * Checked nullable version of DIVIDE, which produces NULL when dividing by zero.
+ */
+ CHECKED_DIVIDE_0_NULL,
+
/**
* Alternation operator in a pattern expression within a
* {@code MATCH_RECOGNIZE} clause.
@@ -1566,7 +1580,8 @@ public enum SqlKind {
CHECKED_PLUS, CHECKED_MINUS, CHECKED_TIMES, CHECKED_DIVIDE);
public static final Set<SqlKind> CHECKED_ARITHMETIC =
- EnumSet.of(CHECKED_PLUS, CHECKED_MINUS, CHECKED_TIMES, CHECKED_DIVIDE, CHECKED_MINUS_PREFIX);
+ EnumSet.of(CHECKED_PLUS, CHECKED_MINUS, CHECKED_TIMES, CHECKED_DIVIDE,
+ CHECKED_DIVIDE_0_NULL, CHECKED_MINUS_PREFIX);
/**
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 074c3103f2..d7f7a472eb 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -67,6 +67,7 @@
import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
@@ -314,6 +315,34 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
InferTypes.FIRST_KNOWN,
OperandTypes.DIVISION_OPERATOR);
+ /**
+ * Nullable division operator, which produces NULL on division by 0, '<code>/</code>'.
+ * Unlike CHECKED_DIVIDE, this is defined for all integer and decimal types.
+ */
+ public static final SqlBinaryOperator DIVIDE_0_NULL =
+ new SqlBinaryOperator(
+ "/",
+ SqlKind.DIVIDE_0_NULL,
+ 60,
+ true,
+ ReturnTypes.QUOTIENT_NULLABLE.andThen(SqlTypeTransforms.FORCE_NULLABLE_NON_FP),
+ InferTypes.FIRST_KNOWN,
+ OperandTypes.DIVISION_OPERATOR);
+
+ /**
+ * Checked nullable division operator, which produces NULL on division by 0,
+ * but still fails on overflow, '<code>/</code>'.
+ */
+ public static final SqlBinaryOperator CHECKED_DIVIDE_0_NULL =
+ new SqlBinaryOperator(
+ "/",
+ SqlKind.CHECKED_DIVIDE_0_NULL,
+ 60,
+ true,
+ ReturnTypes.QUOTIENT_NULLABLE.andThen(SqlTypeTransforms.FORCE_NULLABLE_NON_FP),
+ InferTypes.FIRST_KNOWN,
+ OperandTypes.DIVISION_OPERATOR);
+
/**
* Arithmetic remainder operator, '<code>%</code>',
* an alternative to {@link #MOD} allowed if under certain conformance levels.
@@ -1872,10 +1901,26 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
// Return type is same as divisor (2nd operand)
// SQL2003 Part2 Section 6.27, Syntax Rules 9
SqlBasicFunction.create(SqlKind.MOD,
+ // A rather unfortunate name for this return type strategy
ReturnTypes.NULLABLE_MOD,
OperandTypes.EXACT_NUMERIC_EXACT_NUMERIC)
.withFunctionType(SqlFunctionCategory.NUMERIC);
+ /**
+ * Variant of arithmetic remainder function {@code MOD} which returns NULL when
+ * the denominator is 0.
+ */
+ public static final SqlFunction MOD_0_NULL =
+ // Return type is same as divisor (2nd operand)
+ // SQL2003 Part2 Section 6.27, Syntax Rules 9
+ // Unfortunately there cannot exist two functions in the standard operator
+ // table with the same exact name, so we need to use a different name,
+ // although in SQL this would be shown just as MOD
+ SqlBasicFunction.create("NULLABLE_MOD", SqlKind.MOD_0_NULL,
+ ReturnTypes.NULLABLE_MOD.andThen(SqlTypeTransforms.FORCE_NULLABLE),
+ OperandTypes.EXACT_NUMERIC_EXACT_NUMERIC)
+ .withFunctionType(SqlFunctionCategory.NUMERIC);
+
/** The {@code LN(numeric)} function. */
public static final SqlFunction LN =
SqlBasicFunction.create("LN",
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java
index 253b93b3a0..3a79875e82 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java
@@ -98,6 +98,17 @@ public abstract class SqlTypeTransforms {
opBinding.getTypeFactory().createTypeWithNullability(
requireNonNull(typeToTransform, "typeToTransform"), true);
+ /**
+ * Parameter type-inference transform strategy where a derived type is
+ * transformed into the same type with nulls allowed if the result is not floating point.
+ */
+ public static final SqlTypeTransform FORCE_NULLABLE_NON_FP =
+ (opBinding, typeToTransform) -> {
+ boolean nullable = !SqlTypeName.APPROX_TYPES.contains(typeToTransform.getSqlTypeName());
+ return opBinding.getTypeFactory().createTypeWithNullability(
+ requireNonNull(typeToTransform, "typeToTransform"), nullable);
+ };
+
/**
* Type-inference strategy whereby the result is NOT NULL if any of
* the arguments is NOT NULL; otherwise the type is unchanged.
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 39799a733c..6bb603594c 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -165,6 +165,10 @@ public abstract class SqlAbstractConformance implements SqlConformance {
return SqlConformanceEnum.DEFAULT.checkedArithmetic();
}
+ @Override public boolean nullableDivide() {
+ return SqlConformanceEnum.DEFAULT.nullableDivide();
+ }
+
@Override public boolean supportsUnsignedTypes() {
return SqlConformanceEnum.DEFAULT.supportsUnsignedTypes();
}
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 5e652be9b8..69c5987e00 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -664,6 +664,13 @@ enum SelectAliasLookup {
*/
boolean checkedArithmetic();
+ /**
+ * Whether the implementation uses the nullable form of DIVIDE.
+ * Most SQL dialects will produce a runtime exception on division by zero,
+ * but some dialects return NULL instead (e.g. sqlite).
+ */
+ boolean nullableDivide();
+
/**
* True when the unsigned versions of integer types are supported.
*/
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 8d7d0b5306..6162658a4b 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -478,6 +478,19 @@ public enum SqlConformanceEnum implements SqlConformance {
}
}
+ @Override public boolean nullableDivide() {
+ switch (this) {
+ // The following dialects use nullable divide:
+ // sqlite, postgres, duckdb, mysql and MariaDB (non-strict mode). Note that MYSQL is actually
+ // dynamically-typed, so Calcite cannot implement its behavior accurately, since Calcite is
+ // statically-typed.
+ case MYSQL_5:
+ return true;
+ default:
+ return false;
+ }
+ }
+
@Override public boolean checkedArithmetic() {
switch (this) {
case DEFAULT:
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index 00a77b0ee0..adca402f59 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -170,6 +170,10 @@ protected SqlDelegatingConformance(SqlConformance delegate) {
return delegate.checkedArithmetic();
}
+ @Override public boolean nullableDivide() {
+ return delegate.nullableDivide();
+ }
+
@Override public boolean supportsUnsignedTypes() {
return delegate.supportsUnsignedTypes();
}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/ConvertDivideToNullable.java b/core/src/main/java/org/apache/calcite/sql2rel/ConvertDivideToNullable.java
new file mode 100644
index 0000000000..929460aaa4
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql2rel/ConvertDivideToNullable.java
@@ -0,0 +1,55 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.sql2rel;
+
+import org.apache.calcite.sql.SqlBasicCall;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.util.SqlShuttle;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+/**
+ * Converts a RelNode tree such that division uses nullable division, which
+ * produces NULL on division by zero. Since there is no type information available,
+ * we convert all instances of DIVIDE and MOD operators. However, operators
+ * acting on floating point values or intervals should be converted back.
+ */
+public class ConvertDivideToNullable extends SqlShuttle {
+ public ConvertDivideToNullable() { }
+
+ @Override public @Nullable SqlNode visit(SqlCall call) {
+ SqlNode node = super.visit(call);
+ if (node instanceof SqlBasicCall) {
+ SqlBasicCall newCall = (SqlBasicCall) node;
+ SqlOperator operator = newCall.getOperator();
+ switch (operator.getKind()) {
+ case MOD:
+ operator = SqlStdOperatorTable.MOD_0_NULL;
+ break;
+ case DIVIDE:
+ operator = SqlStdOperatorTable.DIVIDE_0_NULL;
+ break;
+ }
+ newCall.setOperator(operator);
+ return newCall;
+ }
+ return node;
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/ConvertToChecked.java b/core/src/main/java/org/apache/calcite/sql2rel/ConvertToChecked.java
index a5e658e54a..45f030eddd 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/ConvertToChecked.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/ConvertToChecked.java
@@ -88,6 +88,9 @@ class ConvertRexToChecked extends RexShuttle {
case DIVIDE:
operator = SqlStdOperatorTable.CHECKED_DIVIDE;
break;
+ case DIVIDE_0_NULL:
+ operator = SqlStdOperatorTable.CHECKED_DIVIDE_0_NULL;
+ break;
default:
break;
}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index da96b98b06..d6c383abd9 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -165,6 +165,7 @@
import org.apache.calcite.sql.validate.MeasureScope;
import org.apache.calcite.sql.validate.ParameterScope;
import org.apache.calcite.sql.validate.SelectScope;
+import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlLambdaScope;
import org.apache.calcite.sql.validate.SqlMonotonicity;
import org.apache.calcite.sql.validate.SqlNameMatcher;
@@ -624,6 +625,7 @@ public RelRoot convertQuery(
final boolean needsValidation,
final boolean top) {
final boolean unwrapMeasures = !validator().config().embeddedQuery();
+ query = implementArithmetic(validator.config().conformance(), query);
if (needsValidation) {
query = validator().validate(query);
}
@@ -688,6 +690,19 @@ private RelNode unwrapMeasures(RelNode r) {
return r;
}
+ /** Choose implementations for the arithmetic operations according to the
+ * conformance. For example, DIVIDE may be implemented either as DIVIDE or as DIVIDE_0_NULL.
+ * This step has to be done before validation, because the type signature
+ * is different for DIVIDE and DIVIDE_0_NULL. */
+ private SqlNode implementArithmetic(SqlConformance conformance, SqlNode node) {
+ SqlNode converted = node;
+ if (conformance.nullableDivide()) {
+ ConvertDivideToNullable convert = new ConvertDivideToNullable();
+ converted = convert.visitNode(node);
+ }
+ return requireNonNull(converted, "converted");
+ }
+
private RexNode measureToValue(RexNode e) {
return e.getType().isMeasure()
? relBuilder.call(SqlInternalOperators.M2V, e)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 7112fda4a8..e0183e74b6 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -156,6 +156,7 @@ private StandardConvertletTable() {
(cx, call) -> convertIsDistinctFrom(cx, call, true));
registerOp(SqlStdOperatorTable.PLUS, this::convertPlus);
+ registerOp(SqlStdOperatorTable.DIVIDE_0_NULL, this::convertDivide0Null);
registerOp(SqlStdOperatorTable.MINUS,
(cx, call) -> {
@@ -411,6 +412,23 @@ private StandardConvertletTable() {
}
}
+ /** Converts DIVIDE_0_NULL operators to DIVIDE if the result is floating point.
+ * We can only do this after type inference; DIVIDE_0_NULL was inserted before type inference. */
+ private RexNode convertDivide0Null(SqlRexContext cx, SqlCall call) {
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final RexNode left = cx.convertExpression(call.getOperandList().get(0));
+ final RexNode right = cx.convertExpression(call.getOperandList().get(1));
+ final RelDataType returnType = cx.getValidator().getValidatedNodeType(call);
+ SqlOperator operator = call.getOperator();
+ if (SqlTypeName.APPROX_TYPES.contains(returnType.getSqlTypeName())
+ || SqlTypeName.INTERVAL_TYPES.contains(returnType.getSqlTypeName())) {
+ // Replace with standard divide
+ operator = SqlStdOperatorTable.DIVIDE;
+ }
+ return rexBuilder.makeCall(call.getParserPosition(), returnType,
+ operator, ImmutableList.of(left, right));
+ }
+
/** Converts ALL or SOME operators. */
private static RexNode convertQuantifyOperator(SqlRexContext cx, SqlCall call) {
final RexBuilder rexBuilder = cx.getRexBuilder();
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 5fec119d65..7e3b802281 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -428,6 +428,7 @@ public enum BuiltInMethod {
CONVERT_ORACLE(SqlFunctions.class, "convertOracle", String.class, String[].class),
EXP(SqlFunctions.class, "exp", double.class),
MOD(SqlFunctions.class, "mod", long.class, long.class),
+ MOD_0_NULL(SqlFunctions.class, "mod0Null", long.class, long.class),
POWER(SqlFunctions.class, "power", double.class, double.class),
POWER_PG(SqlFunctions.class, "power", BigDecimal.class, BigDecimal.class),
REPEAT(SqlFunctions.class, "repeat", String.class, int.class),
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 129261c9e9..7d36cc998f 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -10136,6 +10136,8 @@ private static int prec(SqlOperator op) {
+ "* left\n"
+ "* left\n" // checked
+ "/ left\n" // checked
+ + "/ left\n" // nullable
+ + "/ left\n" // nullable and checked
+ "/ left\n"
+ "/INT left\n"
+ "/INT left\n" // checked
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index 6db6e0bd45..84f223589e 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -18,6 +18,16 @@
!use post
!set outputformat mysql
+select CAST(5 AS DOUBLE) / 0 as a;
++----------+
+| A |
++----------+
+| Infinity |
++----------+
+(1 row)
+
+!ok
+
# [CALCITE-7060] Enable dumping high-level plans in quidem tests
SELECT * FROM EMP WHERE DEPTNO > 20;
SELECT "EMP"."ENAME", "EMP"."DEPTNO", "EMP"."GENDER"
@@ -2467,10 +2477,62 @@ FROM "hr"."emps";
!ok
-# [CALCITE-6566] JDBC adapter should generate PI function with parentheses in most dialects
-
!use scott-mysql
+# [CALCITE-7270] Add support for a DIVIDE_0_NULL operator
+select 5 / 0 as a;
++---+
+| A |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+# No NULL result produced for double division
+select CAST(5 AS DOUBLE) / 0 as a;
++----------+
+| A |
++----------+
+| Infinity |
++----------+
+(1 row)
+
+!ok
+
+select 5 / 1 as a;
++---+
+| A |
++---+
+| 5 |
++---+
+(1 row)
+
+!ok
+
+# [CALCITE-7270] Add support for a DIVIDE_0_NULL operator
+select 5 % 0 as a;
++---+
+| A |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+select 5 % 1 as a;
++---+
+| A |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+
+# [CALCITE-6566] JDBC adapter should generate PI function with parentheses in most dialects
# PI function
values pi;
No match found for function signature PI()
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/BinaryExpression.java b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/BinaryExpression.java
index 495ef35179..2f208634d7 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/BinaryExpression.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/BinaryExpression.java
@@ -76,6 +76,8 @@ public class BinaryExpression extends Expression {
throw cannotEvaluate();
}
case Divide:
+ case Divide0Null:
+ // If we reach this point for Divide0Null, it is being applied to floating point arguments.
if (primitive == null) {
throw cannotEvaluate();
}
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java
index bcc87c480a..5e5420c591 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java
@@ -146,12 +146,30 @@ public enum ExpressionType {
*/
DivideChecked(" / ", false, 3, false),
+ /**
+ * A nullable division operation, such as (a / b), for numeric
+ * operands, but which returns NULL for a 0 denominator.
+ */
+ Divide0Null(" / ", false, 3, false),
+
+ /**
+ * A checked nullable division operation, such as (a / b), for numeric
+ * operands which returns NULL for a 0 denominator.
+ */
+ Divide0NullChecked(" / ", false, 3, false),
+
/**
* A percent remainder operation, such as (a % b), for numeric
* operands.
*/
Mod(" % ", false, 3, false),
+ /**
+ * A percent remainder operation, such as (a % b), for numeric
+ * operands.
+ */
+ Mod0Null(" % ", false, 3, false),
+
/**
* A node that represents an equality comparison, such as {@code a == b} in
* Java.
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b07ba0c8fa..0842c0587d 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1428,6 +1428,7 @@ ### Arithmetic operators and functions
| POWER(numeric1, numeric2) | Returns *numeric1* raised to the power of *numeric2*
| ABS(numeric) | Returns the absolute value of *numeric*
| MOD(numeric1, numeric2) | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative
+| NULLABLE_MOD(numeric1, numeric2) | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative. Returns NULL when numeric2 is zero
| SQRT(numeric) | Returns the square root of *numeric*
| LN(numeric) | Returns the natural logarithm (base *e*) of *numeric*
| LOG10(numeric) | Returns the base 10 logarithm of *numeric*
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index b11a968458..7af5b78a51 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -398,6 +398,9 @@ protected SqlOperatorFixture fixture() {
// e.g. PLUS and CHECKED_PLUS
assertTrue(SqlKind.CHECKED_ARITHMETIC.contains(routines.get(0).kind)
|| SqlKind.CHECKED_ARITHMETIC.contains(routines.get(1).kind));
+ } else if (routines.size() == 4) {
+ // There are many division operators
+ assertThat(routines.get(0).getName(), is("/"));
} else {
assertThat(routines, hasSize(1));
assertThat(sqlOperator, equalTo(routines.get(0)));
@@ -2753,7 +2756,10 @@ private static void checkConcatWithSeparatorInMSSQL(SqlOperatorFixture f) {
f0.forEachConformance(conformances, this::checkModOperator);
f0.forEachConformance(conformances, this::checkModPrecedence);
f0.forEachConformance(conformances, this::checkModOperatorNull);
- f0.forEachConformance(conformances, this::checkModOperatorDivByZero);
+ f0.forEachConformance(List.of(SqlConformanceEnum.BIG_QUERY), this::checkModOperatorDivByZero);
+ f0.forEachConformance(List.of(SqlConformanceEnum.MYSQL_5),
+ // In mysql a % 0 is NULL
+ this::checkNullableModOperatorDivByZero);
}
void checkModOperator(SqlOperatorFixture f) {
@@ -2794,6 +2800,17 @@ void checkModOperatorDivByZero(SqlOperatorFixture f) {
DIVISION_BY_ZERO_MESSAGE, true);
}
+ // Test for conformances where a % 0 returns NULL
+ void checkNullableModOperatorDivByZero(SqlOperatorFixture f) {
+ // The extra CASE expression is to fool Janino. It does constant
+ // reduction and will throw the divide by zero exception while
+ // compiling the expression. The test framework would then issue
+ // unexpected exception occurred during "validation". You cannot
+ // submit as non-runtime because the janino exception does not have
+ // error position information and the framework is unhappy with that.
+ f.checkNull("3 % case 'a' when 'a' then 0 end");
+ }
+
@Test void testDivideOperator() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.DIVIDE, VmName.EXPAND);