This is an automated email from the ASF dual-hosted git repository. tanner pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit b19f373b41089077aaccd688db57a32901e19c3d Author: Tanner Clary <[email protected]> AuthorDate: Wed Aug 16 14:02:54 2023 -0700 [CALCITE-5933] Add SAFE_DIVIDE function (enabled in BigQuery library) --- babel/src/test/resources/sql/big-query.iq | 71 ++++++++++ .../calcite/adapter/enumerable/RexImpTable.java | 3 + .../org/apache/calcite/runtime/SqlFunctions.java | 67 ++++++++++ .../calcite/sql/fun/SqlLibraryOperators.java | 9 ++ .../org/apache/calcite/sql/type/ReturnTypes.java | 22 ++++ .../org/apache/calcite/util/BuiltInMethod.java | 1 + site/_docs/reference.md | 1 + .../org/apache/calcite/test/SqlOperatorTest.java | 146 +++++++++++++++------ 8 files changed, 283 insertions(+), 37 deletions(-) diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index f38032ae32..d50969b81a 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -667,6 +667,77 @@ SELECT SAFE_ADD(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result; +------------+ (1 row) +!ok + +##################################################################### +# SAFE_DIVIDE +# +# SAFE_DIVIDE(value1, value2) +# +# Equivalent to the divide operator (/), but returns NULL if overflow/underflow occurs +# or if value2 is zero. +SELECT SAFE_DIVIDE(20, 4) as result; ++--------+ +| result | ++--------+ +| 5.0 | ++--------+ +(1 row) + +!ok + +# Dividing by zero should return NULL +SELECT SAFE_DIVIDE(-9223372036854775806, 0) as zero_result; ++-------------+ +| zero_result | ++-------------+ +| | ++-------------+ +(1 row) + +!ok + +SELECT SAFE_DIVIDE(CAST(1.7e308 as DOUBLE), CAST(1.7e-308 as DOUBLE)) as double_overflow; ++-----------------+ +| double_overflow | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +SELECT SAFE_DIVIDE(CAST(-3.5e75 AS DECIMAL(76, 0)), CAST(3.5e-75 AS DECIMAL(76, 0))) + as decimal_overflow; ++------------------+ +| decimal_overflow | ++------------------+ +| | ++------------------+ +(1 row) + +!ok + +# NaN arguments should return NaN +SELECT SAFE_DIVIDE(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result; ++------------+ +| NaN_result | ++------------+ +| NaN | ++------------+ +(1 row) + +!ok + +# Expression parameters should be valid +SELECT SAFE_DIVIDE(1, CAST(1 AS DOUBLE) / CAST(9223372036854775807 AS DOUBLE)) as expr_result; ++----------------------+ +| expr_result | ++----------------------+ +| 9.223372036854776E18 | ++----------------------+ +(1 row) + !ok ##################################################################### # SAFE_MULTIPLY 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 097da9bbc1..93203afe3c 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 @@ -230,6 +230,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.RLIKE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_ADD; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_DIVIDE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_MULTIPLY; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_NEGATE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_OFFSET; @@ -652,6 +653,8 @@ public class RexImpTable { map.put(SAFE_ADD, new SafeArithmeticImplementor(BuiltInMethod.SAFE_ADD.method)); + map.put(SAFE_DIVIDE, + new SafeArithmeticImplementor(BuiltInMethod.SAFE_DIVIDE.method)); map.put(SAFE_MULTIPLY, new SafeArithmeticImplementor(BuiltInMethod.SAFE_MULTIPLY.method)); map.put(SAFE_NEGATE, 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 b6a7e1be0b..efeac7d0ef 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -2186,6 +2186,73 @@ public class SqlFunctions { return safeDouble(ans) || !isFinite ? ans : null; } + /** SQL <code>SAFE_MULTIPLY</code> function applied to long values. */ + public static @Nullable Double safeDivide(long b0, long b1) { + double ans = (double) b0 / b1; + return safeDouble(ans) && b1 != 0 ? ans : null; + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to long and BigDecimal values. */ + public static @Nullable BigDecimal safeDivide(long b0, BigDecimal b1) { + try { + BigDecimal ans = BigDecimal.valueOf(b0).divide(b1); + return safeDecimal(ans) ? ans : null; + } catch (ArithmeticException e) { + return null; + } + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to BigDecimal and long values. */ + public static @Nullable BigDecimal safeDivide(BigDecimal b0, long b1) { + try { + BigDecimal ans = b0.divide(BigDecimal.valueOf(b1)); + return safeDecimal(ans) ? ans : null; + } catch (ArithmeticException e) { + return null; + } + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to BigDecimal values. */ + public static @Nullable BigDecimal safeDivide(BigDecimal b0, BigDecimal b1) { + try { + BigDecimal ans = b0.divide(b1); + return safeDecimal(ans) ? ans : null; + } catch (ArithmeticException e) { + return null; + } + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to double and long values. */ + public static @Nullable Double safeDivide(double b0, long b1) { + double ans = b0 / b1; + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to long and double values. */ + public static @Nullable Double safeDivide(long b0, double b1) { + double ans = b0 / b1; + return safeDouble(ans) || !Double.isFinite(b1) ? ans : null; + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to double and BigDecimal values. */ + public static @Nullable Double safeDivide(double b0, BigDecimal b1) { + double ans = b0 / b1.doubleValue(); + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to BigDecimal and double values. */ + public static @Nullable Double safeDivide(BigDecimal b0, double b1) { + double ans = b0.doubleValue() / b1; + return safeDouble(ans) || !Double.isFinite(b1) ? ans : null; + } + + /** SQL <code>SAFE_DIVIDE</code> function applied to double values. */ + public static @Nullable Double safeDivide(double b0, double b1) { + double ans = b0 / b1; + boolean isFinite = Double.isFinite(b0) && Double.isFinite(b1); + return safeDouble(ans) || !isFinite ? ans : null; + } + /** SQL <code>SAFE_MULTIPLY</code> function applied to long values. */ public static @Nullable Long safeMultiply(long b0, long b1) { try { diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index badd4aae9c..42492d97e7 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -1732,6 +1732,15 @@ public abstract class SqlLibraryOperators { OperandTypes.NUMERIC_NUMERIC, SqlFunctionCategory.NUMERIC); + /** The "SAFE_DIVIDE(numeric1, numeric2)" function; equivalent to the {@code /} operator but + * returns null if an error occurs, such as overflow or division by zero. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction SAFE_DIVIDE = + SqlBasicFunction.create("SAFE_DIVIDE", + ReturnTypes.DOUBLE_IF_INTEGERS.orElse(ReturnTypes.QUOTIENT_FORCE_NULLABLE), + OperandTypes.NUMERIC_NUMERIC, + SqlFunctionCategory.NUMERIC); + /** The "SAFE_MULTIPLY(numeric1, numeric2)" function; equivalent to the {@code *} operator but * returns null if overflow occurs. */ @LibraryOperator(libraries = {BIG_QUERY}) diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java index 3b13ba24cc..2f9a46d555 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java +++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java @@ -826,6 +826,28 @@ public abstract class ReturnTypes { public static final SqlReturnTypeInference DECIMAL_QUOTIENT_NULLABLE = DECIMAL_QUOTIENT.andThen(SqlTypeTransforms.TO_NULLABLE); + /** + * Type-inference strategy whereby the result type of a call is + * {@link #DOUBLE} if both operands are integer types. + */ + public static final SqlReturnTypeInference DOUBLE_IF_INTEGERS = opBinding -> { + RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); + SqlTypeName type1 = opBinding.getOperandType(0).getSqlTypeName(); + SqlTypeName type2 = opBinding.getOperandType(1).getSqlTypeName(); + boolean isInts = SqlTypeName.INT_TYPES.contains(type1) && SqlTypeName.INT_TYPES.contains(type2); + return isInts ? typeFactory.createTypeWithNullability( + typeFactory.createSqlType(SqlTypeName.DOUBLE), true) : null; + }; + + /** + * Same as {@link #DECIMAL_QUOTIENT_NULLABLE} but returns with nullability if any of + * the operands is nullable or the operation results in overflow by using + * {@link org.apache.calcite.sql.type.SqlTypeTransforms#FORCE_NULLABLE}. Also handles + * multiplication for integers, not just decimals. + */ + public static final SqlReturnTypeInference QUOTIENT_FORCE_NULLABLE = + DECIMAL_QUOTIENT_NULLABLE.orElse(LEAST_RESTRICTIVE).andThen(SqlTypeTransforms.FORCE_NULLABLE); + /** * Type-inference strategy whereby the result type of a call is * {@link #DECIMAL_QUOTIENT_NULLABLE} with a fallback to 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 057045c656..3b8b3f4910 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -487,6 +487,7 @@ public enum BuiltInMethod { RAND_INTEGER_SEED(RandomFunction.class, "randIntegerSeed", int.class, int.class), SAFE_ADD(SqlFunctions.class, "safeAdd", double.class, double.class), + SAFE_DIVIDE(SqlFunctions.class, "safeDivide", double.class, double.class), SAFE_MULTIPLY(SqlFunctions.class, "safeMultiply", double.class, double.class), SAFE_SUBTRACT(SqlFunctions.class, "safeSubtract", double.class, double.class), LOG(SqlFunctions.class, "log", long.class, long.class), diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 6b2f517469..6cc2f03aa5 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2800,6 +2800,7 @@ BigQuery's type system uses confusingly different names for types and functions: | b o | RTRIM(string) | Returns *string* with all blanks removed from the end | b | SAFE_ADD(numeric1, numeric2) | Returns *numeric1* + *numeric2*, or NULL on overflow | b | SAFE_CAST(value AS type) | Converts *value* to *type*, returning NULL if conversion fails +| b | SAFE_DIVIDE(numeric1, numeric2) | Returns *numeric1* / *numeric2*, or NULL on overflow or if *numeric2* is zero | b | SAFE_MULTIPLY(numeric1, numeric2) | Returns *numeric1* * *numeric2*, or NULL on overflow | b | SAFE_NEGATE(numeric) | Returns *numeric* * -1, or NULL on overflow | b | SAFE_OFFSET(index) | Similar to `OFFSET` except null is returned if *index* is out of bounds 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 4d251614ce..5352779c16 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -7418,8 +7418,8 @@ public class SqlOperatorTest { "21.2345", "DECIMAL(19, 4)"); f.checkScalar("safe_add(cast(1.2345 as decimal(5,4)), cast(20 as bigint))", "21.2345", "DECIMAL(19, 4)"); - f.checkScalar("safe_add(cast(1.2345 as decimal(5,4)), " - + "cast(2.0 as decimal(2, 1)))", "3.2345", "DECIMAL(6, 4)"); + f.checkScalar("safe_add(cast(1.2345 as decimal(5,4)), cast(2.0 as decimal(2, 1)))", + "3.2345", "DECIMAL(6, 4)"); f.checkScalar("safe_add(cast(3 as double), cast(3 as bigint))", "6.0", "DOUBLE"); f.checkScalar("safe_add(cast(3 as bigint), cast(3 as double))", @@ -7483,6 +7483,78 @@ public class SqlOperatorTest { f.checkNull("safe_add(cast(3 as double), cast(null as bigint))"); } + @Test void testSafeDivideFunc() { + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SAFE_DIVIDE); + f0.checkFails("^safe_divide(2, 3)^", + "No match found for function signature " + + "SAFE_DIVIDE\\(<NUMERIC>, <NUMERIC>\\)", false); + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); + // Basic test for each of the 9 2-permutations of BIGINT, DECIMAL, and FLOAT + f.checkScalar("safe_divide(cast(2 as bigint), cast(4 as bigint))", + "0.5", "DOUBLE"); + f.checkScalar("safe_divide(cast(15 as bigint), cast(1.2 as decimal(2,1)))", + "12.5", "DECIMAL(19, 0)"); + f.checkScalar("safe_divide(cast(4.5 as decimal(2,1)), cast(3 as bigint))", + "1.5", "DECIMAL(19, 18)"); + f.checkScalar("safe_divide(cast(4.5 as decimal(2,1)), " + + "cast(1.5 as decimal(2, 1)))", "3", "DECIMAL(8, 6)"); + f.checkScalar("safe_divide(cast(3 as double), cast(3 as bigint))", + "1.0", "DOUBLE"); + f.checkScalar("safe_divide(cast(3 as bigint), cast(3 as double))", + "1.0", "DOUBLE"); + f.checkScalar("safe_divide(cast(3 as double), cast(1.5 as decimal(5, 4)))", + "2.0", "DOUBLE"); + f.checkScalar("safe_divide(cast(1.5 as decimal(5, 4)), cast(3 as double))", + "0.5", "DOUBLE"); + f.checkScalar("safe_divide(cast(3 as double), cast(3 as double))", + "1.0", "DOUBLE"); + // Tests for + and - Infinity + f.checkScalar("safe_divide(cast('Infinity' as double), cast(3 as double))", + "Infinity", "DOUBLE"); + f.checkScalar("safe_divide(cast('-Infinity' as double), cast(3 as double))", + "-Infinity", "DOUBLE"); + f.checkScalar("safe_divide(cast('-Infinity' as double), " + + "cast('Infinity' as double))", "NaN", "DOUBLE"); + // Tests for NaN + f.checkScalar("safe_divide(cast('NaN' as double), cast(3 as bigint))", + "NaN", "DOUBLE"); + f.checkScalar("safe_divide(cast('NaN' as double), cast(1.23 as decimal(3, 2)))", + "NaN", "DOUBLE"); + f.checkScalar("safe_divide(cast('NaN' as double), cast('Infinity' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_divide(cast(3 as bigint), cast('NaN' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_divide(cast(1.23 as decimal(3, 2)), cast('NaN' as double))", + "NaN", "DOUBLE"); + f.checkNull("safe_divide(cast(0 as bigint), cast(0 as bigint))"); + f.checkNull("safe_divide(cast(0 as bigint), cast(0 as double))"); + f.checkNull("safe_divide(cast(0 as bigint), cast(0 as decimal(1, 0)))"); + f.checkNull("safe_divide(cast(0 as double), cast(0 as bigint))"); + f.checkNull("safe_divide(cast(0 as double), cast(0 as double))"); + f.checkNull("safe_divide(cast(0 as double), cast(0 as decimal(1, 0)))"); + f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as bigint))"); + f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as double))"); + f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as decimal(1, 0)))"); + // Overflow test for each pairing + f.checkNull("safe_divide(cast(10 as bigint), cast(3.5e-75 as DECIMAL(76, 0)))"); + f.checkNull("safe_divide(cast(10 as bigint), cast(-3.5e75 as DECIMAL(76, 0)))"); + f.checkNull("safe_divide(cast(3.5e75 as DECIMAL(76, 0)), " + + "cast(1.5 as DECIMAL(2, 1)))"); + f.checkNull("safe_divide(cast(-3.5e75 as DECIMAL(76, 0)), " + + "cast(1.5 as DECIMAL(2, 1)))"); + f.checkNull("safe_divide(cast(1.7e308 as double), cast(0.5 as decimal(3, 2)))"); + f.checkNull("safe_divide(cast(-1.7e308 as double), cast(0.5 as decimal(2, 1)))"); + f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(1.7e-309 as double))"); + f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(-1.7e-309 as double))"); + f.checkNull("safe_divide(cast(3 as bigint), cast(1.7e-309 as double))"); + f.checkNull("safe_divide(cast(3 as bigint), cast(-1.7e-309 as double))"); + f.checkNull("safe_divide(cast(3 as double), cast(1.7e-309 as double))"); + f.checkNull("safe_divide(cast(3 as double), cast(-1.7e-309 as double))"); + // Check that null argument retuns null + f.checkNull("safe_divide(cast(null as double), cast(3 as bigint))"); + f.checkNull("safe_divide(cast(3 as double), cast(null as bigint))"); + } + @Test void testSafeMultiplyFunc() { final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SAFE_MULTIPLY); f0.checkFails("^safe_multiply(2, 3)^", @@ -7561,8 +7633,8 @@ public class SqlOperatorTest { @Test void testSafeNegateFunc() { final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SAFE_NEGATE); f0.checkFails("^safe_negate(2)^", - "No match found for function signature " - + "SAFE_NEGATE\\(<NUMERIC>\\)", false); + "No match found for function signature " + + "SAFE_NEGATE\\(<NUMERIC>\\)", false); final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); f.checkScalar("safe_negate(cast(20 as bigint))", "-20", "BIGINT"); @@ -7596,79 +7668,79 @@ public class SqlOperatorTest { @Test void testSafeSubtractFunc() { final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SAFE_SUBTRACT); f0.checkFails("^safe_subtract(2, 3)^", - "No match found for function signature " - + "SAFE_SUBTRACT\\(<NUMERIC>, <NUMERIC>\\)", false); + "No match found for function signature " + + "SAFE_SUBTRACT\\(<NUMERIC>, <NUMERIC>\\)", false); final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); // Basic test for each of the 9 2-permutations of BIGINT, DECIMAL, and FLOAT f.checkScalar("safe_subtract(cast(20 as bigint), cast(20 as bigint))", - "0", "BIGINT"); + "0", "BIGINT"); f.checkScalar("safe_subtract(cast(20 as bigint), cast(-1.2345 as decimal(5,4)))", - "21.2345", "DECIMAL(19, 4)"); + "21.2345", "DECIMAL(19, 4)"); f.checkScalar("safe_subtract(cast(1.2345 as decimal(5,4)), cast(-20 as bigint))", - "21.2345", "DECIMAL(19, 4)"); + "21.2345", "DECIMAL(19, 4)"); f.checkScalar("safe_subtract(cast(1.23 as decimal(3,2)), " - + "cast(-2.0 as decimal(2, 1)))", "3.23", "DECIMAL(4, 2)"); + + "cast(-2.0 as decimal(2, 1)))", "3.23", "DECIMAL(4, 2)"); f.checkScalar("safe_subtract(cast(3 as double), cast(-3 as bigint))", - "6.0", "DOUBLE"); + "6.0", "DOUBLE"); f.checkScalar("safe_subtract(cast(3 as bigint), cast(-3 as double))", - "6.0", "DOUBLE"); + "6.0", "DOUBLE"); f.checkScalar("safe_subtract(cast(3 as double), cast(-1.2345 as decimal(5, 4)))", - "4.2345", "DOUBLE"); + "4.2345", "DOUBLE"); f.checkScalar("safe_subtract(cast(1.2345 as decimal(5, 4)), cast(-3 as double))", - "4.2345", "DOUBLE"); + "4.2345", "DOUBLE"); f.checkScalar("safe_subtract(cast(3 as double), cast(3 as double))", - "0.0", "DOUBLE"); + "0.0", "DOUBLE"); // Tests for + and - Infinity f.checkScalar("safe_subtract(cast('Infinity' as double), cast(3 as double))", - "Infinity", "DOUBLE"); + "Infinity", "DOUBLE"); f.checkScalar("safe_subtract(cast('-Infinity' as double), cast(3 as double))", - "-Infinity", "DOUBLE"); + "-Infinity", "DOUBLE"); f.checkScalar("safe_subtract(cast('Infinity' as double), " - + "cast('Infinity' as double))", "NaN", "DOUBLE"); + + "cast('Infinity' as double))", "NaN", "DOUBLE"); // Tests for NaN f.checkScalar("safe_subtract(cast('NaN' as double), cast(3 as bigint))", - "NaN", "DOUBLE"); + "NaN", "DOUBLE"); f.checkScalar("safe_subtract(cast('NaN' as double), cast(1.23 as decimal(3, 2)))", - "NaN", "DOUBLE"); + "NaN", "DOUBLE"); f.checkScalar("safe_subtract(cast('NaN' as double), cast('Infinity' as double))", - "NaN", "DOUBLE"); + "NaN", "DOUBLE"); f.checkScalar("safe_subtract(cast(3 as bigint), cast('NaN' as double))", - "NaN", "DOUBLE"); + "NaN", "DOUBLE"); f.checkScalar("safe_subtract(cast(1.23 as decimal(3, 2)), cast('NaN' as double))", - "NaN", "DOUBLE"); + "NaN", "DOUBLE"); // Overflow test for each pairing f.checkNull("safe_subtract(cast(20 as bigint), " - + "cast(-9223372036854775807 as bigint))"); + + "cast(-9223372036854775807 as bigint))"); f.checkNull("safe_subtract(cast(-20 as bigint), " - + "cast(9223372036854775807 as bigint))"); + + "cast(9223372036854775807 as bigint))"); f.checkNull("safe_subtract(9, cast(-9.999999999999999999e75 as DECIMAL(38, 19)))"); f.checkNull("safe_subtract(-9, cast(9.999999999999999999e75 as DECIMAL(38, 19)))"); f.checkNull("safe_subtract(cast(-9.999999999999999999e75 as DECIMAL(38, 19)), 9)"); f.checkNull("safe_subtract(cast(9.999999999999999999e75 as DECIMAL(38, 19)), -9)"); f.checkNull("safe_subtract(cast(-9.9e75 as DECIMAL(76, 0)), " - + "cast(9.9e75 as DECIMAL(76, 0)))"); + + "cast(9.9e75 as DECIMAL(76, 0)))"); f.checkNull("safe_subtract(cast(9.9e75 as DECIMAL(76, 0)), " - + "cast(-9.9e75 as DECIMAL(76, 0)))"); + + "cast(-9.9e75 as DECIMAL(76, 0)))"); f.checkNull("safe_subtract(cast(1.7976931348623157e308 as double), " - + "cast(-9.9e7 as decimal(76, 0)))"); + + "cast(-9.9e7 as decimal(76, 0)))"); f.checkNull("safe_subtract(cast(-1.7976931348623157e308 as double), " - + "cast(9.9e7 as decimal(76, 0)))"); + + "cast(9.9e7 as decimal(76, 0)))"); f.checkNull("safe_subtract(cast(9.9e7 as decimal(76, 0)), " - + "cast(-1.7976931348623157e308 as double))"); + + "cast(-1.7976931348623157e308 as double))"); f.checkNull("safe_subtract(cast(-9.9e7 as decimal(76, 0)), " - + "cast(1.7976931348623157e308 as double))"); + + "cast(1.7976931348623157e308 as double))"); f.checkNull("safe_subtract(cast(1.7976931348623157e308 as double), " - + "cast(-3 as bigint))"); + + "cast(-3 as bigint))"); f.checkNull("safe_subtract(cast(-1.7976931348623157e308 as double), " - + "cast(3 as bigint))"); + + "cast(3 as bigint))"); f.checkNull("safe_subtract(cast(3 as bigint), " - + "cast(-1.7976931348623157e308 as double))"); + + "cast(-1.7976931348623157e308 as double))"); f.checkNull("safe_subtract(cast(-3 as bigint), " - + "cast(1.7976931348623157e308 as double))"); + + "cast(1.7976931348623157e308 as double))"); f.checkNull("safe_subtract(cast(3 as double), " - + "cast(-1.7976931348623157e308 as double))"); + + "cast(-1.7976931348623157e308 as double))"); f.checkNull("safe_subtract(cast(-3 as double), " - + "cast(1.7976931348623157e308 as double))"); + + "cast(1.7976931348623157e308 as double))"); // Check that null argument retuns null f.checkNull("safe_subtract(cast(null as double), cast(3 as bigint))"); f.checkNull("safe_subtract(cast(3 as double), cast(null as bigint))");
