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 25f326cf0a [CALCITE-5827] Add IS_INF and IS_NAN functions (enabled in
BigQuery library)
25f326cf0a is described below
commit 25f326cf0a94d5e65bd82abc95b676cec6b1fd43
Author: Tanner Clary <[email protected]>
AuthorDate: Sun Jul 9 23:09:53 2023 -0700
[CALCITE-5827] Add IS_INF and IS_NAN functions (enabled in BigQuery library)
---
babel/src/test/resources/sql/big-query.iq | 73 ++++++++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../org/apache/calcite/runtime/SqlFunctions.java | 30 +++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 16 +++++
site/_docs/reference.md | 6 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 58 +++++++++++++++++
6 files changed, 185 insertions(+), 2 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 6a6970dfba..a0e7782a30 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -856,6 +856,79 @@ SELECT SPLIT('h,e,l,l,o')[SAFE_ORDINAL(7)] as
safe_ordinal_idx;
SELECT OFFSET(1);
java.sql.SQLException: Error while executing SQL "SELECT OFFSET(1)": parse
failed: Incorrect syntax near the keyword 'OFFSET' at line 1, column 8.
!error
+
+#####################################################################
+# IS_INF
+#
+# IS_INF(x)
+#
+# Returns whether x is infinite.
+SELECT IS_INF(3) AS non_inf_result;
++----------------+
+| non_inf_result |
++----------------+
+| false |
++----------------+
+(1 row)
+
+!ok
+
+SELECT IS_INF(CAST('Infinity' AS DOUBLE)) AS inf_result;
++------------+
+| inf_result |
++------------+
+| true |
++------------+
+(1 row)
+
+!ok
+
+SELECT IS_INF(CAST('NaN' AS DOUBLE)) AS nan_result;
++------------+
+| nan_result |
++------------+
+| false |
++------------+
+(1 row)
+
+!ok
+
+#####################################################################
+# IS_NAN
+#
+# IS_NAN(x)
+#
+# Returns whether x is NaN.
+SELECT IS_NAN(3) AS non_nan_result;
++----------------+
+| non_nan_result |
++----------------+
+| false |
++----------------+
+(1 row)
+
+!ok
+
+SELECT IS_NAN(CAST('Infinity' AS DOUBLE)) AS inf_result;
++------------+
+| inf_result |
++------------+
+| false |
++------------+
+(1 row)
+
+!ok
+
+SELECT IS_NAN(CAST('NaN' AS DOUBLE)) AS nan_result;
++------------+
+| nan_result |
++------------+
+| true |
++------------+
+(1 row)
+
+!ok
+
#####################################################################
# LN
#
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 7220052d10..531a0ce013 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
@@ -179,6 +179,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE32;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_HEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ILIKE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.IS_INF;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.IS_NAN;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_INSERT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_KEYS;
@@ -604,6 +606,8 @@ public class RexImpTable {
defineMethod(CSC, "csc", NullPolicy.STRICT);
defineMethod(CSCH, "csch", NullPolicy.STRICT);
defineMethod(DEGREES, "degrees", NullPolicy.STRICT);
+ defineMethod(IS_INF, "isInf", NullPolicy.STRICT);
+ defineMethod(IS_NAN, "isNaN", NullPolicy.STRICT);
defineMethod(POW, "power", NullPolicy.STRICT);
defineMethod(RADIANS, "radians", NullPolicy.STRICT);
defineMethod(ROUND, "sround", NullPolicy.STRICT);
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 98fa1330bd..79def765a2 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2297,6 +2297,36 @@ public class SqlFunctions {
return Math.toDegrees(b0);
}
+ /** SQL <code>IS_INF</code> operator applied to BigDecimal values. */
+ public static boolean isInf(BigDecimal b0) {
+ return Double.isInfinite(b0.doubleValue());
+ }
+
+ /** SQL <code>IS_INF</code> operator applied to double values. */
+ public static boolean isInf(double b0) {
+ return Double.isInfinite(b0);
+ }
+
+ /** SQL <code>IS_INF</code> operator applied to float values. */
+ public static boolean isInf(float b0) {
+ return Float.isInfinite(b0);
+ }
+
+ /** SQL <code>IS_NAN</code> operator applied to BigDecimal values. */
+ public static boolean isNaN(BigDecimal b0) {
+ return Double.isNaN(b0.doubleValue());
+ }
+
+ /** SQL <code>IS_NAN</code> operator applied to double values. */
+ public static boolean isNaN(double b0) {
+ return Double.isNaN(b0);
+ }
+
+ /** SQL <code>IS_NAN</code> operator applied to float values. */
+ public static boolean isNaN(float b0) {
+ return Float.isNaN(b0);
+ }
+
// RADIANS
/** SQL <code>RADIANS</code> operator applied to BigDecimal values. */
public static double radians(BigDecimal 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 dce8eb81df..6b234c2165 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
@@ -1794,6 +1794,22 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
+ /** The "IS_INF(value)" function. Returns whether value is infinite. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction IS_INF =
+ SqlBasicFunction.create("IS_INF",
+ ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ /** The "IS_NAN(value)" function. Returns whether value is NaN. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction IS_NAN =
+ SqlBasicFunction.create("IS_NAN",
+ ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
/** The "LOG(value [, value2])" function.
*
* @see SqlStdOperatorTable#LN
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 63c169a753..cf521bc0ce 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2731,10 +2731,12 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b o | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
| b h s | IF(condition, value1, value2) | Returns *value1* if
*condition* is TRUE, *value2* otherwise
| b | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
-| b o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
-| m | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
| p | string1 NOT ILIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*, ignoring case (similar to `NOT LIKE`)
+| b o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
+| m | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
+| b | IS_INF(value) | Returns whether *value*
is infinite
+| b | IS_NAN(value) | Returns whether *value*
is NaN
| m | JSON_TYPE(jsonValue) | Returns a string value
indicating the type of *jsonValue*
| m | JSON_DEPTH(jsonValue) | Returns an integer
value indicating the depth of *jsonValue*
| m | JSON_PRETTY(jsonValue) | Returns a
pretty-printing of *jsonValue*
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 36e4068c42..53147eb9ac 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6801,6 +6801,64 @@ public class SqlOperatorTest {
f.checkNull("pow(cast(null as integer), 2)");
}
+ @Test void testInfinity() {
+ final SqlOperatorFixture f = fixture();
+ f.checkScalar("cast('Infinity' as double)", "Infinity",
+ "DOUBLE NOT NULL");
+ f.checkScalar("cast('-Infinity' as double)", "-Infinity",
+ "DOUBLE NOT NULL");
+ f.checkScalar("cast('Infinity' as real)", "Infinity",
+ "REAL NOT NULL");
+ f.checkScalar("cast('-Infinity' as real)", "-Infinity",
+ "REAL NOT NULL");
+ }
+
+ @Test void testNaN() {
+ final SqlOperatorFixture f = fixture();
+ f.checkScalar("cast('NaN' as double)", "NaN",
+ "DOUBLE NOT NULL");
+ f.checkScalar("cast('NaN' as real)", "NaN",
+ "REAL NOT NULL");
+ }
+
+ @Test void testIsInfFunc() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.IS_INF);
+ f0.checkFails("^is_inf(3)^",
+ "No match found for function signature IS_INF\\(<NUMERIC>\\)",
+ false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkBoolean("is_inf(3)", false);
+ f.checkBoolean("is_inf(1.2345)", false);
+ f.checkBoolean("is_inf(cast('NaN' as double))", false);
+ f.checkBoolean("is_inf(cast('NaN' as real))", false);
+ f.checkBoolean("is_inf(cast('Infinity' as double))", true);
+ f.checkBoolean("is_inf(cast('Infinity' as float))", true);
+ f.checkBoolean("is_inf(cast('Infinity' as real))", true);
+ f.checkBoolean("is_inf(cast('-Infinity' as double))", true);
+ f.checkBoolean("is_inf(cast('-Infinity' as float))", true);
+ f.checkBoolean("is_inf(cast('-Infinity' as real))", true);
+ f.checkNull("is_inf(cast(null as double))");
+ }
+
+ @Test void testIsNaNFunc() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.IS_NAN);
+ f0.checkFails("^is_nan(3)^",
+ "No match found for function signature IS_NAN\\(<NUMERIC>\\)",
+ false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkBoolean("is_nan(3)", false);
+ f.checkBoolean("is_nan(1.2345)", false);
+ f.checkBoolean("is_nan(cast('Infinity' as double))", false);
+ f.checkBoolean("is_nan(cast('Infinity' as float))", false);
+ f.checkBoolean("is_nan(cast('Infinity' as real))", false);
+ f.checkBoolean("is_nan(cast('-Infinity' as double))", false);
+ f.checkBoolean("is_nan(cast('-Infinity' as float))", false);
+ f.checkBoolean("is_nan(cast('-Infinity' as real))", false);
+ f.checkBoolean("is_nan(cast('NaN' as double))", true);
+ f.checkBoolean("is_nan(cast('NaN' as real))", true);
+ f.checkNull("is_nan(cast(null as double))");
+ }
+
@Test void testRoundFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.ROUND, VmName.EXPAND);
