You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ta...@apache.org on 2023/08/24 15:56:28 UTC
[calcite] branch main updated: [CALCITE-5770] Add SAFE_SUBTRACT function (enabled in BigQuery library)
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
The following commit(s) were added to refs/heads/main by this push:
new b8acbea163 [CALCITE-5770] Add SAFE_SUBTRACT function (enabled in BigQuery library)
b8acbea163 is described below
commit b8acbea163fbfd9fa4e6d5ee66a05dbd449b80d3
Author: Tanner Clary <ta...@google.com>
AuthorDate: Wed Aug 16 10:42:56 2023 -0700
[CALCITE-5770] Add SAFE_SUBTRACT function (enabled in BigQuery library)
---
babel/src/test/resources/sql/big-query.iq | 80 ++++++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 64 ++++++++++++++--
.../calcite/sql/fun/SqlLibraryOperators.java | 9 +++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 85 ++++++++++++++++++++++
6 files changed, 236 insertions(+), 5 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index bcd785b32d..515e083f6a 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -737,6 +737,86 @@ SELECT SAFE_MULTIPLY(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
!ok
+#####################################################################
+# SAFE_SUBTRACT
+#
+# SAFE_SUBTRACT(value1, value2)
+#
+# Equivalent to the subtraction operator (-), but returns NULL if overflow/underflow occurs.
+SELECT SAFE_SUBTRACT(5, 4) as result;
++--------+
+| result |
++--------+
+| 1 |
++--------+
+(1 row)
+
+!ok
+
+# Overflow occurs if result is greater than 2^63 - 1
+SELECT SAFE_SUBTRACT(9223372036854775807, -2) as overflow_result;
++-----------------+
+| overflow_result |
++-----------------+
+| |
++-----------------+
+(1 row)
+
+!ok
+
+# Underflow occurs if result is less than -2^63
+SELECT SAFE_SUBTRACT(-9223372036854775806, 3) as underflow_result;
++------------------+
+| underflow_result |
++------------------+
+| |
++------------------+
+(1 row)
+
+!ok
+
+SELECT SAFE_SUBTRACT(CAST(1.7e308 as DOUBLE), CAST(-1.7e308 as DOUBLE)) as double_overflow;
++-----------------+
+| double_overflow |
++-----------------+
+| |
++-----------------+
+(1 row)
+
+!ok
+
+SELECT SAFE_SUBTRACT(9, cast(-9.999999999999999999e75 as DECIMAL(38, 19))) as decimal_overflow;
++------------------+
+| decimal_overflow |
++------------------+
+| |
++------------------+
+(1 row)
+
+!ok
+
+# NaN arguments should return NaN
+SELECT SAFE_SUBTRACT(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
++------------+
+| NaN_result |
++------------+
+| NaN |
++------------+
+(1 row)
+
+!ok
+
+# Expression parameters should be valid
+SELECT SAFE_SUBTRACT(1 + 2 + 3, 5) as expr_result;
++-------------+
+| expr_result |
++-------------+
+| 1 |
++-------------+
+(1 row)
+
+!ok
+
#####################################################################
# NOT EQUAL Operator (value1 != value2)
#
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 1f8994072a..4edb190505 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
@@ -231,6 +231,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_MULTIPLY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_OFFSET;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_ORDINAL;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_SUBTRACT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SEC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SECH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
@@ -636,6 +637,7 @@ public class RexImpTable {
map.put(SAFE_ADD, new SafeArithmeticImplementor("safeAdd"));
map.put(SAFE_MULTIPLY, new SafeArithmeticImplementor("safeMultiply"));
+ map.put(SAFE_SUBTRACT, new SafeArithmeticImplementor("safeSubtract"));
map.put(PI, new PiImplementor());
return populate2();
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 8caa2803dd..6837fbb9dc 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1970,6 +1970,64 @@ public class SqlFunctions {
return safeDouble(ans) || !isFinite ? ans : null;
}
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to long values. */
+ public static @Nullable Long safeSubtract(long b0, long b1) {
+ try {
+ return Math.subtractExact(b0, b1);
+ } catch (ArithmeticException e) {
+ return null;
+ }
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to long and BigDecimal values. */
+ public static @Nullable BigDecimal safeSubtract(long b0, BigDecimal b1) {
+ BigDecimal ans = BigDecimal.valueOf(b0).subtract(b1);
+ return safeDecimal(ans) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to BigDecimal and long values. */
+ public static @Nullable BigDecimal safeSubtract(BigDecimal b0, long b1) {
+ BigDecimal ans = b0.subtract(BigDecimal.valueOf(b1));
+ return safeDecimal(ans) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to BigDecimal values. */
+ public static @Nullable BigDecimal safeSubtract(BigDecimal b0, BigDecimal b1) {
+ BigDecimal ans = b0.subtract(b1);
+ return safeDecimal(ans) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to double and long values. */
+ public static @Nullable Double safeSubtract(double b0, long b1) {
+ double ans = b0 - b1;
+ return safeDouble(ans) || !Double.isFinite(b0) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to long and double values. */
+ public static @Nullable Double safeSubtract(long b0, double b1) {
+ double ans = b0 - b1;
+ return safeDouble(ans) || !Double.isFinite(b1) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to double and BigDecimal values. */
+ public static @Nullable Double safeSubtract(double b0, BigDecimal b1) {
+ double ans = b0 - b1.doubleValue();
+ return safeDouble(ans) || !Double.isFinite(b0) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to BigDecimal and double values. */
+ public static @Nullable Double safeSubtract(BigDecimal b0, double b1) {
+ double ans = b0.doubleValue() - b1;
+ return safeDouble(ans) || !Double.isFinite(b1) ? ans : null;
+ }
+
+ /** SQL <code>SAFE_SUBTRACT</code> function applied to double values. */
+ public static @Nullable Double safeSubtract(double b0, double b1) {
+ double ans = b0 - b1;
+ boolean isFinite = Double.isFinite(b0) && Double.isFinite(b1);
+ return safeDouble(ans) || !isFinite ? ans : null;
+ }
+
/** Returns whether a BigDecimal value is safe (that is, has not overflowed).
* According to BigQuery, BigDecimal overflow occurs if the precision is greater
* than 76 or the scale is greater than 38. */
@@ -1984,11 +2042,7 @@ public class SqlFunctions {
// negated MIN and MAX double values, overflow has not occurred. Otherwise,
// overflow has occurred. Important to note that 'Double.MIN_VALUE' refers to
// minimum positive value.
- if (d < Double.MAX_VALUE && d > Double.MIN_VALUE) {
- return true;
- } else {
- return d > -Double.MAX_VALUE && d < -Double.MIN_VALUE;
- }
+ return Math.abs(d) > Double.MIN_VALUE && Math.abs(d) < Double.MAX_VALUE || d == 0;
}
private static RuntimeException notArithmetic(String op, Object b0,
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 6bceb25d3e..7cf793fc51 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
@@ -1709,6 +1709,15 @@ public abstract class SqlLibraryOperators {
OperandTypes.NUMERIC_NUMERIC,
SqlFunctionCategory.NUMERIC);
+ /** The "SAFE_SUBTRACT(numeric1, numeric2)" function; equivalent to the {@code -} operator but
+ * returns null if overflow occurs. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction SAFE_SUBTRACT =
+ SqlBasicFunction.create("SAFE_SUBTRACT",
+ ReturnTypes.SUM_FORCE_NULLABLE,
+ OperandTypes.NUMERIC_NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
/** The "CHAR(n)" function; returns the character whose ASCII code is
* {@code n} % 256, or null if {@code n} < 0. */
@LibraryOperator(libraries = {MYSQL, SPARK})
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 31067da6e5..ebbc7c2a52 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2798,6 +2798,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| b | SAFE_MULTIPLY(numeric1, numeric2) | Returns *numeric1* * *numeric2*, or NULL on overflow
| b | SAFE_OFFSET(index) | Similar to `OFFSET` except null is returned if *index* is out of bounds
| b | SAFE_ORDINAL(index) | Similar to `OFFSET` except *index* begins at 1 and null is returned if *index* is out of bounds
+| b | SAFE_SUBTRACT(numeric1, numeric2) | Returns *numeric1* - *numeric2*, or NULL on overflow
| * | SEC(numeric) | Returns the secant of *numeric* in radians
| * | SECH(numeric) | Returns the hyperbolic secant of *numeric*
| b m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string
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 ec30702df8..029412b15e 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7478,6 +7478,91 @@ public class SqlOperatorTest {
f.checkNull("safe_multiply(cast(3 as double), cast(null as bigint))");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5770">[CALCITE-5770]
+ * Add SAFE_SUBTRACT function (enabled in BigQuery library).</a>.
+ */
+ @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);
+ 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");
+ f.checkScalar("safe_subtract(cast(20 as bigint), cast(-1.2345 as decimal(5,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)");
+ f.checkScalar("safe_subtract(cast(1.23 as decimal(3,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");
+ f.checkScalar("safe_subtract(cast(3 as bigint), cast(-3 as double))",
+ "6.0", "DOUBLE");
+ f.checkScalar("safe_subtract(cast(3 as double), cast(-1.2345 as decimal(5, 4)))",
+ "4.2345", "DOUBLE");
+ f.checkScalar("safe_subtract(cast(1.2345 as decimal(5, 4)), cast(-3 as double))",
+ "4.2345", "DOUBLE");
+ f.checkScalar("safe_subtract(cast(3 as double), cast(3 as double))",
+ "0.0", "DOUBLE");
+ // Tests for + and - Infinity
+ f.checkScalar("safe_subtract(cast('Infinity' as double), cast(3 as double))",
+ "Infinity", "DOUBLE");
+ f.checkScalar("safe_subtract(cast('-Infinity' as double), cast(3 as double))",
+ "-Infinity", "DOUBLE");
+ f.checkScalar("safe_subtract(cast('Infinity' as double), "
+ + "cast('Infinity' as double))", "NaN", "DOUBLE");
+ // Tests for NaN
+ f.checkScalar("safe_subtract(cast('NaN' as double), cast(3 as bigint))",
+ "NaN", "DOUBLE");
+ f.checkScalar("safe_subtract(cast('NaN' as double), cast(1.23 as decimal(3, 2)))",
+ "NaN", "DOUBLE");
+ f.checkScalar("safe_subtract(cast('NaN' as double), cast('Infinity' as double))",
+ "NaN", "DOUBLE");
+ f.checkScalar("safe_subtract(cast(3 as bigint), cast('NaN' as double))",
+ "NaN", "DOUBLE");
+ f.checkScalar("safe_subtract(cast(1.23 as decimal(3, 2)), cast('NaN' as double))",
+ "NaN", "DOUBLE");
+ // Overflow test for each pairing
+ f.checkNull("safe_subtract(cast(20 as bigint), "
+ + "cast(-9223372036854775807 as bigint))");
+ f.checkNull("safe_subtract(cast(-20 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)))");
+ f.checkNull("safe_subtract(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)))");
+ f.checkNull("safe_subtract(cast(-1.7976931348623157e308 as double), "
+ + "cast(9.9e7 as decimal(76, 0)))");
+ f.checkNull("safe_subtract(cast(9.9e7 as decimal(76, 0)), "
+ + "cast(-1.7976931348623157e308 as double))");
+ f.checkNull("safe_subtract(cast(-9.9e7 as decimal(76, 0)), "
+ + "cast(1.7976931348623157e308 as double))");
+ f.checkNull("safe_subtract(cast(1.7976931348623157e308 as double), "
+ + "cast(-3 as bigint))");
+ f.checkNull("safe_subtract(cast(-1.7976931348623157e308 as double), "
+ + "cast(3 as bigint))");
+ f.checkNull("safe_subtract(cast(3 as bigint), "
+ + "cast(-1.7976931348623157e308 as double))");
+ f.checkNull("safe_subtract(cast(-3 as bigint), "
+ + "cast(1.7976931348623157e308 as double))");
+ f.checkNull("safe_subtract(cast(3 as double), "
+ + "cast(-1.7976931348623157e308 as double))");
+ f.checkNull("safe_subtract(cast(-3 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))");
+ }
+
@Test void testNullifFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.NULLIF, VM_EXPAND);