You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/01/16 22:46:29 UTC
[calcite] branch main updated: [CALCITE-5405] Add POW and TRUNC functions (enabled in BigQuery library)
This is an automated email from the ASF dual-hosted git repository.
jhyde 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 6f0692e93a [CALCITE-5405] Add POW and TRUNC functions (enabled in BigQuery library)
6f0692e93a is described below
commit 6f0692e93a2da27a2ec230963209f4a08fbdcad2
Author: Tanner Clary <ta...@google.com>
AuthorDate: Mon Nov 28 23:36:48 2022 +0000
[CALCITE-5405] Add POW and TRUNC functions (enabled in BigQuery library)
Close apache/calcite#2992
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../calcite/sql/fun/SqlLibraryOperators.java | 8 ++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 7 ++--
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 43 ++++++++++++++++++++++
5 files changed, 61 insertions(+), 3 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index ad63918654..3a0bb47e7e 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
@@ -149,6 +149,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.LOGICAL_AND;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.LOGICAL_OR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.POW;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
@@ -168,6 +169,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIME_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MILLIS;
@@ -471,6 +473,7 @@ public class RexImpTable {
defineMethod(COSH, "cosh", NullPolicy.STRICT);
defineMethod(COT, "cot", NullPolicy.STRICT);
defineMethod(DEGREES, "degrees", NullPolicy.STRICT);
+ defineMethod(POW, "power", NullPolicy.STRICT);
defineMethod(RADIANS, "radians", NullPolicy.STRICT);
defineMethod(ROUND, "sround", NullPolicy.STRICT);
defineMethod(SIGN, "sign", NullPolicy.STRICT);
@@ -478,6 +481,7 @@ public class RexImpTable {
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);
map.put(PI, new PiImplementor());
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 a84545705a..e4690df632 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
@@ -877,6 +877,14 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction POW =
+ SqlStdOperatorTable.POWER.withName("POW");
+
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TRUNC =
+ SqlStdOperatorTable.TRUNCATE.withName("TRUNC");
+
/** Infix "::" cast operator used by PostgreSQL, for example
* {@code '100'::INTEGER}. */
@LibraryOperator(libraries = { POSTGRESQL })
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 2d4189c986..950a103680 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1610,10 +1610,11 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
* example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if the
* second operand is negative.
*/
- public static final SqlFunction POWER =
+ public static final SqlBasicFunction POWER =
SqlBasicFunction.create("POWER",
ReturnTypes.DOUBLE_NULLABLE,
- OperandTypes.NUMERIC_NUMERIC);
+ OperandTypes.NUMERIC_NUMERIC,
+ SqlFunctionCategory.NUMERIC);
/** The {@code SQRT(numeric)} function. */
public static final SqlFunction SQRT =
@@ -1754,7 +1755,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
SqlFunctionCategory.NUMERIC);
/** The {@code TRUNCATE(numeric [, numeric])} function. */
- public static final SqlFunction TRUNCATE =
+ public static final SqlBasicFunction TRUNCATE =
SqlBasicFunction.create("TRUNCATE",
ReturnTypes.ARG0_NULLABLE,
OperandTypes.NUMERIC_OPTIONAL_INTEGER,
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 28573717f2..1c0f81332a 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2650,6 +2650,7 @@ semantics.
| b m p | MD5(string) | Calculates an MD5 128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in the connection's locale, of the month in *datetime*; for example, it returns '二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| b | POW(numeric1, numeric2) | Returns *numeric1* raised to the power *numeric2*
| m o | REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [, matchType]]]) | Replaces all substrings of *string* that match *regexp* with *rep* at the starting *pos* in expr (if omitted, the default is 1), *occurrence* means which occurrence of a match to search for (if omitted, the default is 1), *matchType* specifies how to perform matching
| b m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
| b m | REVERSE(string) | Returns *string* with the order of the characters reversed
@@ -2679,6 +2680,7 @@ semantics.
| o p | TO_DATE(string, format) | Converts *string* to a date using the format *format*
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format*
| b o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
+| b | TRUNC(numeric1 [, numeric2 ]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point
| b | UNIX_MICROS(timestamp) | Returns the number of microseconds since 1970-01-01 00:00:00
| b | UNIX_MILLIS(timestamp) | Returns the number of milliseconds since 1970-01-01 00:00:00
| b | UNIX_SECONDS(timestamp) | Returns the number of seconds since 1970-01-01 00:00:00
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 5cf4561dc7..16f7742614 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5359,6 +5359,14 @@ public class SqlOperatorTest {
f.checkNull("radians(cast(null as double))");
}
+ @Test void testPowFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.POW)
+ .withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalarApprox("pow(2,3)", "DOUBLE NOT NULL", isExactly("8.0"));
+ f.checkNull("pow(2, cast(null as integer))");
+ f.checkNull("pow(cast(null as integer), 2)");
+ }
@Test void testRoundFunc() {
final SqlOperatorFixture f = fixture();
@@ -5496,6 +5504,41 @@ public class SqlOperatorTest {
f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer);
}
+ @Test void testTruncFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.TRUNC)
+ .withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkType("trunc(42, -1)", "INTEGER NOT NULL");
+ f.checkType("trunc(cast(42 as float), 1)", "FLOAT NOT NULL");
+ f.checkType("trunc(case when false then 42 else null end, -1)",
+ "INTEGER");
+ f.enableTypeCoercion(false)
+ .checkFails("^trunc('abc', 'def')^",
+ "Cannot apply 'TRUNC' to arguments of type "
+ + "'TRUNC\\(<CHAR\\(3\\)>, <CHAR\\(3\\)>\\)'\\. Supported "
+ + "form\\(s\\): 'TRUNC\\(<NUMERIC>, <INTEGER>\\)'",
+ false);
+ f.checkType("trunc('abc', 'def')", "DECIMAL(19, 9) NOT NULL");
+ f.checkScalar("trunc(42, -1)", 40, "INTEGER NOT NULL");
+ f.checkScalar("trunc(cast(42.345 as decimal(2, 3)), 2)",
+ BigDecimal.valueOf(4234, 2), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("trunc(cast(-42.345 as decimal(2, 3)), 2)",
+ BigDecimal.valueOf(-4234, 2), "DECIMAL(2, 3) NOT NULL");
+ f.checkNull("trunc(cast(null as integer), 1)");
+ f.checkNull("trunc(cast(null as double), 1)");
+ f.checkNull("trunc(43.21, cast(null as integer))");
+
+ f.checkScalar("trunc(42)", 42, "INTEGER NOT NULL");
+ f.checkScalar("trunc(42.324)",
+ BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
+ f.checkScalar("trunc(cast(42.324 as float))", 42F,
+ "FLOAT NOT NULL");
+ f.checkScalar("trunc(cast(42.345 as decimal(2, 3)))",
+ BigDecimal.valueOf(42, 0), "DECIMAL(2, 3) NOT NULL");
+ f.checkNull("trunc(cast(null as integer))");
+ f.checkNull("trunc(cast(null as double))");
+ }
+
@Test void testTruncateFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TRUNCATE, VmName.EXPAND);