This is an automated email from the ASF dual-hosted git repository. libenchao pushed a commit to branch site in repository https://gitbox.apache.org/repos/asf/calcite.git
commit b377a545983993cd8ffea3189663bcc583042730 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 | 555 +++++++++++++++++++++ .../calcite/adapter/enumerable/RexImpTable.java | 78 +-- .../org/apache/calcite/runtime/SqlFunctions.java | 252 ++++++++++ .../calcite/sql/fun/SqlLibraryOperators.java | 45 ++ .../org/apache/calcite/sql/type/ReturnTypes.java | 22 + .../org/apache/calcite/util/BuiltInMethod.java | 4 + site/_docs/reference.md | 3 + .../org/apache/calcite/test/SqlOperatorTest.java | 341 +++++++++++++ 8 files changed, 1270 insertions(+), 30 deletions(-) diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index 429ec7b830..c2df06dc91 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -600,6 +600,340 @@ FROM t; !ok !} +##################################################################### +# SAFE_ADD +# +# SAFE_ADD(value1, value2) +# +# Equivalent to the addition operator (+), but returns NULL if overflow/underflow occurs. +SELECT SAFE_ADD(5, 4) as result; ++--------+ +| result | ++--------+ +| 9 | ++--------+ +(1 row) + +!ok + +# Overflow occurs if result is greater than 2^63 - 1 +SELECT SAFE_ADD(9223372036854775807, 2) as overflow_result; ++-----------------+ +| overflow_result | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +# Underflow occurs if result is less than -2^63 +SELECT SAFE_ADD(-9223372036854775806, -3) as underflow_result; ++------------------+ +| underflow_result | ++------------------+ +| | ++------------------+ +(1 row) + +!ok + +SELECT SAFE_ADD(CAST(1.7e308 as DOUBLE), CAST(1.7e308 as DOUBLE)) as double_overflow; ++-----------------+ +| double_overflow | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +SELECT SAFE_ADD(9, cast(9.999999999999999999e75 as DECIMAL(38, 19))) as decimal_overflow; ++------------------+ +| decimal_overflow | ++------------------+ +| | ++------------------+ +(1 row) + +!ok + +# NaN arguments should return NaN +SELECT SAFE_ADD(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result; ++------------+ +| NaN_result | ++------------+ +| NaN | ++------------+ +(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 +# +# SAFE_MULTIPLY(value1, value2) +# +# Equivalent to the mulitply operator (*), but returns NULL if overflow/underflow occurs. +SELECT SAFE_MULTIPLY(5, 4) as result; ++--------+ +| result | ++--------+ +| 20 | ++--------+ +(1 row) + +!ok + +# Overflow occurs if result is greater than 2^63 - 1 +SELECT SAFE_MULTIPLY(9223372036854775807, 2) as overflow_result; ++-----------------+ +| overflow_result | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +# Underflow occurs if result is less than -2^63 +SELECT SAFE_MULTIPLY(-9223372036854775806, 3) as underflow_result; ++------------------+ +| underflow_result | ++------------------+ +| | ++------------------+ +(1 row) + +!ok + +SELECT SAFE_MULTIPLY(CAST(1.7e308 as DOUBLE), CAST(3 as BIGINT)) as double_overflow; ++-----------------+ +| double_overflow | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +SELECT SAFE_MULTIPLY(CAST(-3.5e75 AS DECIMAL(76, 0)), CAST(10 AS BIGINT)) as decimal_overflow; ++------------------+ +| decimal_overflow | ++------------------+ +| | ++------------------+ +(1 row) + +!ok + +# NaN arguments should return NaN +SELECT SAFE_MULTIPLY(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result; ++------------+ +| NaN_result | ++------------+ +| NaN | ++------------+ +(1 row) + +!ok + +##################################################################### +# SAFE_NEGATE +# +# SAFE_NEGATE(value) +# +# Returns value * -1, or NULL on overflow. +SELECT SAFE_NEGATE(5) as result; ++--------+ +| result | ++--------+ +| -5 | ++--------+ +(1 row) + +!ok + +SELECT SAFE_NEGATE(-5) as result; ++--------+ +| result | ++--------+ +| 5 | ++--------+ +(1 row) + +!ok + +SELECT SAFE_NEGATE(-9223372036854775808) as overflow_result; ++-----------------+ +| overflow_result | ++-----------------+ +| | ++-----------------+ +(1 row) + +!ok + +SELECT SAFE_NEGATE(1 + 2 + 3 + 4) as expr_result; ++-------------+ +| expr_result | ++-------------+ +| -10 | ++-------------+ +(1 row) + +!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) # @@ -648,6 +982,227 @@ SELECT (19 % 19) as result; !ok +##################################################################### +# REGEXP_CONTAINS(value, regexp) +# +# Takes two STRING values. Returns TRUE if value is a partial match +# for the regular expression, regexp. +# If the regexp argument is invalid, the function returns an error. +# Uses java.util.regex as a standard for regex processing +# in Calcite instead of RE2 used by BigQuery/GoogleSQL. + +SELECT + email, + REGEXP_CONTAINS(email, '@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid +FROM + (SELECT + ARRAY['[email protected]', '[email protected]', 'www.example.net'] + AS addresses), + UNNEST(addresses) AS email; ++-----------------+----------+ +| email | is_valid | ++-----------------+----------+ +| [email protected] | true | +| [email protected] | true | +| www.example.net | false | ++-----------------+----------+ +(3 rows) + +!ok + +SELECT + email, + REGEXP_CONTAINS(email, '^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$') + AS valid_email_address, + REGEXP_CONTAINS(email, '^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$') + AS without_parentheses +FROM + (SELECT + ARRAY['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]'] + AS addresses), + UNNEST(addresses) AS email; ++----------------+---------------------+---------------------+ +| email | valid_email_address | without_parentheses | ++----------------+---------------------+---------------------+ +| [email protected] | true | true | +| [email protected] | false | true | +| [email protected] | true | true | +| [email protected] | false | true | +| [email protected] | false | false | ++----------------+---------------------+---------------------+ +(5 rows) + +!ok + +SELECT REGEXP_CONTAINS('abc def ghi', '(abc'); +Invalid regular expression for REGEXP_CONTAINS: 'Unclosed group near index 4 (abc' +!error + +SELECT REGEXP_CONTAINS('abc def ghi', '[z-a]'); +Invalid regular expression for REGEXP_CONTAINS: 'Illegal character range near index 3 [z-a] ^' +!error + +SELECT REGEXP_CONTAINS('abc def ghi', '{2,1}'); +Invalid regular expression for REGEXP_CONTAINS: 'Illegal repetition range near index 4 {2,1} ^' +!error + +##################################################################### +# REGEXP_EXTRACT(value, regexp[, position[, occurrence]]) +# +# Returns the substring in value that matches the regexp. +# Returns NULL if there is no match, or if position or occurrence are beyond range. +# Returns an exception if regex, position or occurrence are invalid. + +WITH email_addresses AS + (SELECT '[email protected]' as email + UNION ALL + SELECT '[email protected]' as email + UNION ALL + SELECT '[email protected]' as email) +SELECT + REGEXP_EXTRACT(email, '^[a-zA-Z0-9_.+-]+') + AS user_name +FROM email_addresses; ++-----------+ +| user_name | ++-----------+ +| foo | +| bar | +| baz | ++-----------+ +(3 rows) + +!ok + +WITH +characters AS ( + SELECT 'ab' AS value, '.b' AS regex UNION ALL + SELECT 'ab' AS value, '(.)b' AS regex UNION ALL + SELECT 'xyztb' AS value, '(.)+b' AS regex UNION ALL + SELECT 'ab' AS value, '(z)?b' AS regex +) +SELECT value, regex, REGEXP_EXTRACT(value, regex) AS result FROM characters; ++-------+-------+--------+ +| value | regex | result | ++-------+-------+--------+ +| ab | (.)b | a | +| ab | (z)?b | | +| ab | .b | ab | +| xyztb | (.)+b | t | ++-------+-------+--------+ +(4 rows) + +!ok + +WITH example AS +(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position, +1 AS occurrence UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL +SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL +SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL +SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3 +) +SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex, +position, occurrence) AS regexp_value FROM example; ++--------------------------+---------+----------+------------+--------------+ +| value | regex | position | occurrence | regexp_value | ++--------------------------+---------+----------+------------+--------------+ +| Hello Helloo and Hellooo | H?ello+ | 1 | 1 | Hello | +| Hello Helloo and Hellooo | H?ello+ | 1 | 2 | Helloo | +| Hello Helloo and Hellooo | H?ello+ | 1 | 3 | Hellooo | +| Hello Helloo and Hellooo | H?ello+ | 1 | 4 | | +| Hello Helloo and Hellooo | H?ello+ | 2 | 1 | ello | +| Hello Helloo and Hellooo | H?ello+ | 20 | 1 | | +| Hello Helloo and Hellooo | H?ello+ | 3 | 1 | Helloo | +| Hello Helloo and Hellooo | H?ello+ | 3 | 2 | Hellooo | +| Hello Helloo and Hellooo | H?ello+ | 3 | 3 | | +| cats&dogs&rabbits | \\w+& | 1 | 2 | | +| cats&dogs&rabbits | \\w+& | 2 | 3 | | ++--------------------------+---------+----------+------------+--------------+ +(11 rows) + +!ok + +SELECT REGEXP_EXTRACT("abcadcabcaecghi", "a.+c"); ++--------------+ +| EXPR$0 | ++--------------+ +| abcadcabcaec | ++--------------+ +(1 row) + +!ok + +SELECT REGEXP_EXTRACT("abcadcabcaecghi", "abc(a.c)", 4); ++--------+ +| EXPR$0 | ++--------+ +| aec | ++--------+ +(1 row) + +!ok + +SELECT REGEXP_EXTRACT("abcadcabcaecghi", "a.c", 25); ++--------+ +| EXPR$0 | ++--------+ +| | ++--------+ +(1 row) + +!ok + +SELECT REGEXP_EXTRACT("abcadcabcaecghi", "a.c", 1, 5); ++--------+ +| result | ++--------+ +| true | ++--------+ +(1 row) + +!ok + +SELECT (5 != 5) as result; ++--------+ +| result | ++--------+ +| false | ++--------+ +(1 row) + +!ok + +##################################################################### +# MODULO % OPERATOR (value1 % value2) +# +# Returns the remainder of dividing value1 by value2. +SELECT (5 % 3) as result; ++--------+ +| result | ++--------+ +| 2 | ++--------+ +(1 row) + +!ok + +SELECT (19 % 19) as result; ++--------+ +| result | ++--------+ +| 0 | ++--------+ +(1 row) + +!ok + ##################################################################### # SPLIT # 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 43242677b6..322d7e63b7 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 @@ -218,6 +218,9 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT; 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_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; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_ORDINAL; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SEC; @@ -579,36 +582,51 @@ public class RexImpTable { map.put(LOG, new LogImplementor()); map.put(LOG10, new LogImplementor()); - map.put(RAND, new RandImplementor()); - map.put(RAND_INTEGER, new RandIntegerImplementor()); - - defineMethod(ACOS, "acos", NullPolicy.STRICT); - defineMethod(ACOSH, "acosh", NullPolicy.STRICT); - defineMethod(ASIN, "asin", NullPolicy.STRICT); - defineMethod(ASINH, "asinh", NullPolicy.STRICT); - defineMethod(ATAN, "atan", NullPolicy.STRICT); - defineMethod(ATAN2, "atan2", NullPolicy.STRICT); - defineMethod(ATANH, "atanh", NullPolicy.STRICT); - defineMethod(CBRT, "cbrt", NullPolicy.STRICT); - defineMethod(COS, "cos", NullPolicy.STRICT); - defineMethod(COSH, "cosh", NullPolicy.STRICT); - defineMethod(COT, "cot", NullPolicy.STRICT); - defineMethod(COTH, "coth", NullPolicy.STRICT); - defineMethod(CSC, "csc", NullPolicy.STRICT); - defineMethod(CSCH, "csch", NullPolicy.STRICT); - defineMethod(DEGREES, "degrees", NullPolicy.STRICT); - defineMethod(POW, "power", NullPolicy.STRICT); - defineMethod(RADIANS, "radians", NullPolicy.STRICT); - defineMethod(ROUND, "sround", NullPolicy.STRICT); - defineMethod(SEC, "sec", NullPolicy.STRICT); - defineMethod(SECH, "sech", NullPolicy.STRICT); - defineMethod(SIGN, "sign", NullPolicy.STRICT); - defineMethod(SIN, "sin", NullPolicy.STRICT); - defineMethod(SINH, "sinh", NullPolicy.STRICT); - defineMethod(TAN, "tan", NullPolicy.STRICT); - defineMethod(TANH, "tanh", NullPolicy.STRICT); - defineMethod(TRUNC, "struncate", NullPolicy.STRICT); - defineMethod(TRUNCATE, "struncate", NullPolicy.STRICT); + defineReflective(RAND, BuiltInMethod.RAND.method, + BuiltInMethod.RAND_SEED.method); + defineReflective(RAND_INTEGER, BuiltInMethod.RAND_INTEGER.method, + BuiltInMethod.RAND_INTEGER_SEED.method); + + defineMethod(ACOS, BuiltInMethod.ACOS.method, NullPolicy.STRICT); + defineMethod(ACOSH, BuiltInMethod.ACOSH.method, NullPolicy.STRICT); + defineMethod(ASIN, BuiltInMethod.ASIN.method, NullPolicy.STRICT); + defineMethod(ASINH, BuiltInMethod.ASINH.method, NullPolicy.STRICT); + defineMethod(ATAN, BuiltInMethod.ATAN.method, NullPolicy.STRICT); + defineMethod(ATAN2, BuiltInMethod.ATAN2.method, NullPolicy.STRICT); + defineMethod(ATANH, BuiltInMethod.ATANH.method, NullPolicy.STRICT); + defineMethod(CBRT, BuiltInMethod.CBRT.method, NullPolicy.STRICT); + defineMethod(COS, BuiltInMethod.COS.method, NullPolicy.STRICT); + defineMethod(COSH, BuiltInMethod.COSH.method, NullPolicy.STRICT); + defineMethod(COT, BuiltInMethod.COT.method, NullPolicy.STRICT); + defineMethod(COTH, BuiltInMethod.COTH.method, NullPolicy.STRICT); + defineMethod(CSC, BuiltInMethod.CSC.method, NullPolicy.STRICT); + defineMethod(CSCH, BuiltInMethod.CSCH.method, NullPolicy.STRICT); + defineMethod(DEGREES, BuiltInMethod.DEGREES.method, NullPolicy.STRICT); + defineMethod(IS_INF, BuiltInMethod.IS_INF.method, NullPolicy.STRICT); + defineMethod(IS_NAN, BuiltInMethod.IS_NAN.method, NullPolicy.STRICT); + defineMethod(POW, BuiltInMethod.POWER.method, NullPolicy.STRICT); + defineMethod(RADIANS, BuiltInMethod.RADIANS.method, NullPolicy.STRICT); + defineMethod(ROUND, BuiltInMethod.SROUND.method, NullPolicy.STRICT); + defineMethod(SEC, BuiltInMethod.SEC.method, NullPolicy.STRICT); + defineMethod(SECH, BuiltInMethod.SECH.method, NullPolicy.STRICT); + defineMethod(SIGN, BuiltInMethod.SIGN.method, NullPolicy.STRICT); + defineMethod(SIN, BuiltInMethod.SIN.method, NullPolicy.STRICT); + defineMethod(SINH, BuiltInMethod.SINH.method, NullPolicy.STRICT); + defineMethod(TAN, BuiltInMethod.TAN.method, NullPolicy.STRICT); + defineMethod(TANH, BuiltInMethod.TANH.method, NullPolicy.STRICT); + defineMethod(TRUNC, BuiltInMethod.STRUNCATE.method, NullPolicy.STRICT); + defineMethod(TRUNCATE, BuiltInMethod.STRUNCATE.method, NullPolicy.STRICT); + + 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, + new SafeArithmeticImplementor(BuiltInMethod.SAFE_MULTIPLY.method)); + map.put(SAFE_SUBTRACT, + new SafeArithmeticImplementor(BuiltInMethod.SAFE_SUBTRACT.method)); 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 69834c84f6..bbd34f5916 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -1619,6 +1619,258 @@ public class SqlFunctions { throw notArithmetic("*", b0, b1); } + /** SQL <code>SAFE_ADD</code> function applied to long values. */ + public static @Nullable Long safeAdd(long b0, long b1) { + try { + return Math.addExact(b0, b1); + } catch (ArithmeticException e) { + return null; + } + } + + /** SQL <code>SAFE_ADD</code> function applied to long and BigDecimal values. */ + public static @Nullable BigDecimal safeAdd(long b0, BigDecimal b1) { + BigDecimal ans = BigDecimal.valueOf(b0).add(b1); + return safeDecimal(ans) ? ans : null; + } + + /** SQL <code>SAFE_ADD</code> function applied to BigDecimal and long values. */ + public static @Nullable BigDecimal safeAdd(BigDecimal b0, long b1) { + return safeAdd(b1, b0); + } + + /** SQL <code>SAFE_ADD</code> function applied to BigDecimal values. */ + public static @Nullable BigDecimal safeAdd(BigDecimal b0, BigDecimal b1) { + BigDecimal ans = b0.add(b1); + return safeDecimal(ans) ? ans : null; + } + + /** SQL <code>SAFE_ADD</code> function applied to double and long values. */ + public static @Nullable Double safeAdd(double b0, long b1) { + double ans = b0 + b1; + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_ADD</code> function applied to long and double values. */ + public static @Nullable Double safeAdd(long b0, double b1) { + return safeAdd(b1, b0); + } + + /** SQL <code>SAFE_ADD</code> function applied to double and BigDecimal values. */ + public static @Nullable Double safeAdd(double b0, BigDecimal b1) { + double ans = b0 + b1.doubleValue(); + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_ADD</code> function applied to BigDecimal and double values. */ + public static @Nullable Double safeAdd(BigDecimal b0, double b1) { + return safeAdd(b1, b0); + } + + /** SQL <code>SAFE_ADD</code> function applied to double values. */ + public static @Nullable Double safeAdd(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 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 { + return Math.multiplyExact(b0, b1); + } catch (ArithmeticException e) { + return null; + } + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to long and BigDecimal values. */ + public static @Nullable BigDecimal safeMultiply(long b0, BigDecimal b1) { + BigDecimal ans = BigDecimal.valueOf(b0).multiply(b1); + return safeDecimal(ans) ? ans : null; + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to BigDecimal and long values. */ + public static @Nullable BigDecimal safeMultiply(BigDecimal b0, long b1) { + return safeMultiply(b1, b0); + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to BigDecimal values. */ + public static @Nullable BigDecimal safeMultiply(BigDecimal b0, BigDecimal b1) { + BigDecimal ans = b0.multiply(b1); + return safeDecimal(ans) ? ans : null; + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to double and long values. */ + public static @Nullable Double safeMultiply(double b0, long b1) { + double ans = b0 * b1; + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to long and double values. */ + public static @Nullable Double safeMultiply(long b0, double b1) { + return safeMultiply(b1, b0); + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to double and BigDecimal values. */ + public static @Nullable Double safeMultiply(double b0, BigDecimal b1) { + double ans = b0 * b1.doubleValue(); + return safeDouble(ans) || !Double.isFinite(b0) ? ans : null; + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to BigDecimal and double values. */ + public static @Nullable Double safeMultiply(BigDecimal b0, double b1) { + return safeMultiply(b1, b0); + } + + /** SQL <code>SAFE_MULTIPLY</code> function applied to double values. */ + public static @Nullable Double safeMultiply(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_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. */ + private static boolean safeDecimal(BigDecimal b) { + return b.scale() <= 38 && b.precision() <= 76; + } + + /** Returns whether a double value is safe (that is, has not overflowed). */ + private static boolean safeDouble(double d) { + // If the double is positive and falls between the MIN and MAX double values, + // overflow has not occurred. If the double is negative and falls between the + // 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. + return Math.abs(d) > Double.MIN_VALUE && Math.abs(d) < Double.MAX_VALUE || d == 0; + } + private static RuntimeException notArithmetic(String op, Object b0, Object b1) { return RESOURCE.invalidTypesForArithmetic(b0.getClass().toString(), 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 18abd19975..489ba81a3f 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 @@ -1626,6 +1626,51 @@ public abstract class SqlLibraryOperators { OperandTypes.family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.TIMESTAMP, SqlTypeFamily.ANY)); + /** The "SAFE_ADD(numeric1, numeric2)" function; equivalent to the {@code +} operator but + * returns null if overflow occurs. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction SAFE_ADD = + SqlBasicFunction.create("SAFE_ADD", + ReturnTypes.SUM_FORCE_NULLABLE, + 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}) + public static final SqlFunction SAFE_MULTIPLY = + SqlBasicFunction.create("SAFE_MULTIPLY", + ReturnTypes.PRODUCT_FORCE_NULLABLE, + OperandTypes.NUMERIC_NUMERIC, + SqlFunctionCategory.NUMERIC); + + /** The "SAFE_NEGATE(numeric)" function; negates {@code numeric} and returns null if overflow + * occurs. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction SAFE_NEGATE = + SqlBasicFunction.create("SAFE_NEGATE", + ReturnTypes.ARG0_FORCE_NULLABLE, + OperandTypes.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/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java index e10107f28b..cba66b366b 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 @@ -804,6 +804,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 b02870f351..69f0566fe0 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -452,6 +452,10 @@ public enum BuiltInMethod { RAND_INTEGER(RandomFunction.class, "randInteger", int.class), 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), TANH(SqlFunctions.class, "tanh", long.class), SINH(SqlFunctions.class, "sinh", long.class), diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 12f6467fc1..d5f605fbe5 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2783,6 +2783,9 @@ BigQuery's type system uses confusingly different names for types and functions: | b o | RPAD(string, length[, pattern ]) | Returns a string or bytes value that consists of *string* appended to *length* with *pattern* | b o | RTRIM(string) | Returns *string* with all blanks removed from the end | 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 | b | SAFE_ORDINAL(index) | Similar to `OFFSET` except *index* begins at 1 and null is returned if *index* is out of bounds | * | SEC(numeric) | Returns the secant of *numeric* in radians 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 7771cf1948..880ea466cb 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -6889,6 +6889,347 @@ public class SqlOperatorTest { f.checkNull("truncate(cast(null as double))"); } + @Test void testSafeAddFunc() { + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SAFE_ADD); + f0.checkFails("^safe_add(2, 3)^", + "No match found for function signature " + + "SAFE_ADD\\(<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_add(cast(20 as bigint), cast(20 as bigint))", + "40", "BIGINT"); + f.checkScalar("safe_add(cast(20 as bigint), cast(1.2345 as decimal(5,4)))", + "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(3 as double), cast(3 as bigint))", + "6.0", "DOUBLE"); + f.checkScalar("safe_add(cast(3 as bigint), cast(3 as double))", + "6.0", "DOUBLE"); + f.checkScalar("safe_add(cast(3 as double), cast(1.2345 as decimal(5, 4)))", + "4.2345", "DOUBLE"); + f.checkScalar("safe_add(cast(1.2345 as decimal(5, 4)), cast(3 as double))", + "4.2345", "DOUBLE"); + f.checkScalar("safe_add(cast(3 as double), cast(3 as double))", + "6.0", "DOUBLE"); + // Tests for + and - Infinity + f.checkScalar("safe_add(cast('Infinity' as double), cast(3 as double))", + "Infinity", "DOUBLE"); + f.checkScalar("safe_add(cast('-Infinity' as double), cast(3 as double))", + "-Infinity", "DOUBLE"); + f.checkScalar("safe_add(cast('-Infinity' as double), " + + "cast('Infinity' as double))", "NaN", "DOUBLE"); + // Tests for NaN + f.checkScalar("safe_add(cast('NaN' as double), cast(3 as bigint))", + "NaN", "DOUBLE"); + f.checkScalar("safe_add(cast('NaN' as double), cast(1.23 as decimal(3, 2)))", + "NaN", "DOUBLE"); + f.checkScalar("safe_add(cast('NaN' as double), cast('Infinity' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_add(cast(3 as bigint), cast('NaN' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_add(cast(1.23 as decimal(3, 2)), cast('NaN' as double))", + "NaN", "DOUBLE"); + // Overflow test for each pairing + f.checkNull("safe_add(cast(20 as bigint), " + + "cast(9223372036854775807 as bigint))"); + f.checkNull("safe_add(cast(-20 as bigint), " + + "cast(-9223372036854775807 as bigint))"); + f.checkNull("safe_add(9, cast(9.999999999999999999e75 as DECIMAL(38, 19)))"); + f.checkNull("safe_add(-9, cast(-9.999999999999999999e75 as DECIMAL(38, 19)))"); + f.checkNull("safe_add(cast(9.999999999999999999e75 as DECIMAL(38, 19)), 9)"); + f.checkNull("safe_add(cast(-9.999999999999999999e75 as DECIMAL(38, 19)), -9)"); + f.checkNull("safe_add(cast(9.9e75 as DECIMAL(76, 0)), " + + "cast(9.9e75 as DECIMAL(76, 0)))"); + f.checkNull("safe_add(cast(-9.9e75 as DECIMAL(76, 0)), " + + "cast(-9.9e75 as DECIMAL(76, 0)))"); + f.checkNull("safe_add(cast(1.7976931348623157e308 as double), " + + "cast(9.9e7 as decimal(76, 0)))"); + f.checkNull("safe_add(cast(-1.7976931348623157e308 as double), " + + "cast(-9.9e7 as decimal(76, 0)))"); + f.checkNull("safe_add(cast(9.9e7 as decimal(76, 0)), " + + "cast(1.7976931348623157e308 as double))"); + f.checkNull("safe_add(cast(-9.9e7 as decimal(76, 0)), " + + "cast(-1.7976931348623157e308 as double))"); + f.checkNull("safe_add(cast(1.7976931348623157e308 as double), cast(3 as bigint))"); + f.checkNull("safe_add(cast(-1.7976931348623157e308 as double), " + + "cast(-3 as bigint))"); + f.checkNull("safe_add(cast(3 as bigint), cast(1.7976931348623157e308 as double))"); + f.checkNull("safe_add(cast(-3 as bigint), " + + "cast(-1.7976931348623157e308 as double))"); + f.checkNull("safe_add(cast(3 as double), cast(1.7976931348623157e308 as double))"); + f.checkNull("safe_add(cast(-3 as double), " + + "cast(-1.7976931348623157e308 as double))"); + // Check that null argument retuns null + f.checkNull("safe_add(cast(null as double), cast(3 as bigint))"); + 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)^", + "No match found for function signature " + + "SAFE_MULTIPLY\\(<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_multiply(cast(20 as bigint), cast(20 as bigint))", + "400", "BIGINT"); + f.checkScalar("safe_multiply(cast(20 as bigint), cast(1.2345 as decimal(5,4)))", + "24.6900", "DECIMAL(19, 4)"); + f.checkScalar("safe_multiply(cast(1.2345 as decimal(5,4)), cast(20 as bigint))", + "24.6900", "DECIMAL(19, 4)"); + f.checkScalar("safe_multiply(cast(1.2345 as decimal(5,4)), " + + "cast(2.0 as decimal(2, 1)))", "2.46900", "DECIMAL(7, 5)"); + f.checkScalar("safe_multiply(cast(3 as double), cast(3 as bigint))", + "9.0", "DOUBLE"); + f.checkScalar("safe_multiply(cast(3 as bigint), cast(3 as double))", + "9.0", "DOUBLE"); + f.checkScalar("safe_multiply(cast(3 as double), cast(1.2345 as decimal(5, 4)))", + "3.7035", "DOUBLE"); + f.checkScalar("safe_multiply(cast(1.2345 as decimal(5, 4)), cast(3 as double))", + "3.7035", "DOUBLE"); + f.checkScalar("safe_multiply(cast(3 as double), cast(3 as double))", + "9.0", "DOUBLE"); + // Tests for + and - Infinity + f.checkScalar("safe_multiply(cast('Infinity' as double), cast(3 as double))", + "Infinity", "DOUBLE"); + f.checkScalar("safe_multiply(cast('-Infinity' as double), cast(3 as double))", + "-Infinity", "DOUBLE"); + f.checkScalar("safe_multiply(cast('-Infinity' as double), " + + "cast('Infinity' as double))", "-Infinity", "DOUBLE"); + // Tests for NaN + f.checkScalar("safe_multiply(cast('NaN' as double), cast(3 as bigint))", + "NaN", "DOUBLE"); + f.checkScalar("safe_multiply(cast('NaN' as double), cast(1.23 as decimal(3, 2)))", + "NaN", "DOUBLE"); + f.checkScalar("safe_multiply(cast('NaN' as double), cast('Infinity' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_multiply(cast(3 as bigint), cast('NaN' as double))", + "NaN", "DOUBLE"); + f.checkScalar("safe_multiply(cast(1.23 as decimal(3, 2)), cast('NaN' as double))", + "NaN", "DOUBLE"); + // Overflow test for each pairing + f.checkNull("safe_multiply(cast(20 as bigint), " + + "cast(9223372036854775807 as bigint))"); + f.checkNull("safe_multiply(cast(20 as bigint), " + + "cast(-9223372036854775807 as bigint))"); + f.checkNull("safe_multiply(cast(10 as bigint), cast(3.5e75 as DECIMAL(76, 0)))"); + f.checkNull("safe_multiply(cast(10 as bigint), cast(-3.5e75 as DECIMAL(76, 0)))"); + f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), cast(10 as bigint))"); + f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), cast(10 as bigint))"); + f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), " + + "cast(1.5 as DECIMAL(2, 1)))"); + f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), " + + "cast(1.5 as DECIMAL(2, 1)))"); + f.checkNull("safe_multiply(cast(1.7e308 as double), cast(1.23 as decimal(3, 2)))"); + f.checkNull("safe_multiply(cast(-1.7e308 as double), cast(1.2 as decimal(2, 1)))"); + f.checkNull("safe_multiply(cast(1.2 as decimal(2, 1)), cast(1.7e308 as double))"); + f.checkNull("safe_multiply(cast(1.2 as decimal(2, 1)), cast(-1.7e308 as double))"); + f.checkNull("safe_multiply(cast(1.7e308 as double), cast(3 as bigint))"); + f.checkNull("safe_multiply(cast(-1.7e308 as double), cast(3 as bigint))"); + f.checkNull("safe_multiply(cast(3 as bigint), cast(1.7e308 as double))"); + f.checkNull("safe_multiply(cast(3 as bigint), cast(-1.7e308 as double))"); + f.checkNull("safe_multiply(cast(3 as double), cast(1.7e308 as double))"); + f.checkNull("safe_multiply(cast(3 as double), cast(-1.7e308 as double))"); + // Check that null argument retuns null + f.checkNull("safe_multiply(cast(null as double), cast(3 as bigint))"); + 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_NEGATE function (enabled in BigQuery library)</a>. + */ + @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); + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); + f.checkScalar("safe_negate(cast(20 as bigint))", "-20", + "BIGINT"); + f.checkScalar("safe_negate(cast(-20 as bigint))", "20", + "BIGINT"); + f.checkScalar("safe_negate(cast(1.5 as decimal(2, 1)))", "-1.5", + "DECIMAL(2, 1)"); + f.checkScalar("safe_negate(cast(-1.5 as decimal(2, 1)))", "1.5", + "DECIMAL(2, 1)"); + f.checkScalar("safe_negate(cast(12.3456 as double))", "-12.3456", + "DOUBLE"); + f.checkScalar("safe_negate(cast(-12.3456 as double))", "12.3456", + "DOUBLE"); + // Infinity and NaN tests + f.checkScalar("safe_negate(cast('Infinity' as double))", + "-Infinity", "DOUBLE"); + f.checkScalar("safe_negate(cast('-Infinity' as double))", + "Infinity", "DOUBLE"); + f.checkScalar("safe_negate(cast('NaN' as double))", + "NaN", "DOUBLE"); + // Null cases are rarer for SAFE_NEGATE + f.checkNull("safe_negate(-9223372036854775808)"); + f.checkNull("safe_negate(-1 + -9223372036854775807)"); + f.checkNull("safe_negate(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);
