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 <[email protected]> AuthorDate: Thu Nov 27 11:37:56 2025 -0800 [CALCITE-7270] Add support for a DIVIDE_0_NULL operation Signed-off-by: Mihai Budiu <[email protected]> --- .../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);
