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:47:47 UTC
[calcite] branch main updated (6f0692e93a -> 98ae817e44)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
discard 6f0692e93a [CALCITE-5405] Add POW and TRUNC functions (enabled in BigQuery library)
new 98ae817e44 [CALCITE-5404] Add POW and TRUNC functions (enabled in BigQuery library)
This update added new revisions after undoing existing revisions.
That is to say, some revisions that were in the old version of the
branch are not in the new version. This situation occurs
when a user --force pushes a change and generates a repository
containing something like this:
* -- * -- B -- O -- O -- O (6f0692e93a)
\
N -- N -- N refs/heads/main (98ae817e44)
You should already have received notification emails for all of the O
revisions, and so the following emails describe only the N revisions
from the common base, B.
Any revisions marked "omit" are not gone; other references still
refer to them. Any revisions marked "discard" are gone forever.
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
[calcite] 01/01: [CALCITE-5404] Add POW and TRUNC functions (enabled in BigQuery library)
Posted by jh...@apache.org.
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
commit 98ae817e44fec4ec33ba7c8c0486ae02ce143f87
Author: Tanner Clary <ta...@google.com>
AuthorDate: Mon Nov 28 23:36:48 2022 +0000
[CALCITE-5404] 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);