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 1156efba97 [CALCITE-5360] Add TIMESTAMP_ADD function (enabled in
BigQuery library)
1156efba97 is described below
commit 1156efba972c90ab7a484e745d95bcecadf27ecc
Author: Tanner Clary <[email protected]>
AuthorDate: Mon Dec 5 20:11:02 2022 +0000
[CALCITE-5360] Add TIMESTAMP_ADD function (enabled in BigQuery library)
The JDBC and builtin functions already contain a TIMESTAMPADD
function with three arguments; this change adds a
TIMESTAMP_ADD function with two arguments, consistent with
BigQuery, and enabled if you have 'lib=bigquery' in your
connection options.
Close apache/calcite#2998
---
babel/src/test/resources/sql/big-query.iq | 18 +++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 14 +++++++
.../calcite/sql2rel/StandardConvertletTable.java | 28 +++++++++++---
.../src/main/java/org/apache/calcite/util/Bug.java | 15 ++++----
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 43 ++++++++++++++++++++++
6 files changed, 106 insertions(+), 13 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 61c07698ae..7795f54a2b 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -1197,6 +1197,8 @@ SELECT
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
!if (false) {
+# Enable when we have TIMESTAMP() function,
+# and when we allow "+00" timezone offset in timestamp values.
SELECT
TIMESTAMP("2008-12-25 15:30:00+00") AS original,
TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS
later;
@@ -1205,9 +1207,25 @@ SELECT
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC |
+-------------------------+-------------------------+
+(1 row)
+
!ok
!}
+# As previous, but uses TIMESTAMP literal rather than function,
+# and "+00" timezone offsets have been removed.
+SELECT
+ TIMESTAMP "2008-12-25 15:30:00" AS original,
+ TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00", INTERVAL 10 MINUTE) AS later;
++---------------------+---------------------+
+| original | later |
++---------------------+---------------------+
+| 2008-12-25 15:30:00 | 2008-12-25 15:40:00 |
++---------------------+---------------------+
+(1 row)
+
+!ok
+
#####################################################################
# DATE_SUB
#
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 7566f2331a..f8ea6c7821 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
@@ -634,6 +634,20 @@ public abstract class SqlLibraryOperators {
ImmutableSet.<TimeUnitRange>builder()
.addAll(MONTH_UNITS).addAll(DATE_UNITS).addAll(TIME_UNITS).build();
+ /** The "TIMESTAMP_ADD(timestamp_expression, interval_expression)" function
+ * (BigQuery), the two-argument variant of the built-in
+ * {@link SqlStdOperatorTable#TIMESTAMP_ADD TIMESTAMPADD} function, which has
+ * three arguments.
+ *
+ * <p>In BigQuery, the syntax is "TIMESTAMP_ADD(timestamp_expression,
INTERVAL
+ * int64_expression date_part)" but in Calcite the second argument can be any
+ * interval expression, not just an interval literal. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TIMESTAMP_ADD2 =
+ SqlBasicFunction.create(SqlKind.TIMESTAMP_ADD, ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.TIMESTAMP_INTERVAL)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
/** The "TIME_TRUNC(time_expression, time_part)" function (BigQuery);
* truncates a TIME value to the granularity of time_part. The TIME value is
* always rounded to the beginning of time_part. */
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 3b5eda2869..727ea53910 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -165,6 +165,9 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL,
new SubstrConvertlet(SqlLibrary.POSTGRESQL));
+ registerOp(SqlLibraryOperators.TIMESTAMP_ADD2,
+ new TimestampAddConvertlet());
+
registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl);
registerOp(SqlLibraryOperators.DECODE,
StandardConvertletTable::convertDecode);
@@ -261,7 +264,6 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
final SqlRexConvertlet floorCeilConvertlet = new FloorCeilConvertlet();
registerOp(SqlStdOperatorTable.FLOOR, floorCeilConvertlet);
registerOp(SqlStdOperatorTable.CEIL, floorCeilConvertlet);
-
registerOp(SqlStdOperatorTable.TIMESTAMP_ADD, new
TimestampAddConvertlet());
registerOp(SqlStdOperatorTable.TIMESTAMP_DIFF,
new TimestampDiffConvertlet());
@@ -1822,18 +1824,34 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
}
- /** Convertlet that handles the {@code TIMESTAMPADD} function. */
+ /** Convertlet that handles the 3-argument {@code TIMESTAMPADD} function
+ * and the 2-argument BigQuery-style {@code TIMESTAMP_ADD} function. */
private static class TimestampAddConvertlet implements SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
// TIMESTAMPADD(unit, count, timestamp)
// => timestamp + count * INTERVAL '1' UNIT
final RexBuilder rexBuilder = cx.getRexBuilder();
- SqlIntervalQualifier qualifier = call.operand(0);
+ SqlIntervalQualifier qualifier;
+ final RexNode op1;
+ final RexNode op2;
+ switch (call.operandCount()) {
+ case 2:
+ // BigQuery-style 'TIMESTAMP_ADD(timestamp, interval)'
+ final SqlBasicCall operandCall = call.operand(1);
+ qualifier = operandCall.operand(1);
+ op1 = cx.convertExpression(operandCall.operand(0));
+ op2 = cx.convertExpression(call.operand(0));
+ break;
+ default:
+ // JDBC-style 'TIMESTAMPADD(unit, count, timestamp)'
+ qualifier = call.operand(0);
+ op1 = cx.convertExpression(call.operand(1));
+ op2 = cx.convertExpression(call.operand(2));
+ }
+
final TimeFrame timeFrame =
cx.getValidator().validateTimeFrame(qualifier);
final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
final RelDataType type = cx.getValidator().getValidatedNodeType(call);
- final RexNode op1 = cx.convertExpression(call.operand(1));
- final RexNode op2 = cx.convertExpression(call.operand(2));
if (unit == TimeUnit.EPOCH && qualifier.timeFrameName != null) {
// Custom time frames have a different path. They are kept as names,
// and then handled by Java functions such as
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java
b/core/src/main/java/org/apache/calcite/util/Bug.java
index 602984c5cf..09e1cd5832 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -152,14 +152,6 @@ public abstract class Bug {
* Decorrelate sub-queries in Project and Join</a> is fixed. */
public static final boolean CALCITE_1045_FIXED = false;
- /**
- * Whether
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-2223";>[CALCITE-2223]
- * ProjectMergeRule is infinitely matched when is applied after
ProjectReduceExpressions Rule</a>
- * is fixed.
- */
- public static final boolean CALCITE_2223_FIXED = false;
-
/** Whether
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2400";>[CALCITE-2400]
* Allow standards-compliant column ordering for NATURAL JOIN and JOIN USING
@@ -191,6 +183,7 @@ public abstract class Bug {
* Intermittent precision in Druid results when using aggregation functions
over columns of type
* DOUBLE</a> is fixed. */
public static final boolean CALCITE_4204_FIXED = false;
+
/** Whether
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4205";>[CALCITE-4205]
* DruidAdapterIT#testDruidTimeFloorAndTimeParseExpressions2 fails</a> is
fixed. */
@@ -200,12 +193,18 @@ public abstract class Bug {
* Druid plans with small intervals should be chosen over full interval scan
plus filter</a> is
* fixed. */
public static final boolean CALCITE_4213_FIXED = false;
+
/** Whether
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4645";>[CALCITE-4645]
* In Elasticsearch adapter, a range predicate should be translated to a
range query</a> is
* fixed. */
public static final boolean CALCITE_4645_FIXED = false;
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5422";>[CALCITE-5422]
+ * MILLISECOND and MICROSECOND units in INTERVAL literal</a> is fixed. */
+ public static final boolean CALCITE_5422_FIXED = false;
+
/**
* Use this to flag temporary code.
*/
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 74724ef866..c81ed03159 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2653,6 +2653,7 @@ semantics.
| b m o p | SUBSTR(string, position [, substringLength ]) | Returns a portion
of *string*, beginning at character *position*, *substringLength* characters
long. SUBSTR calculates lengths using characters as defined by the input
character set
| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller than the first one
| b o | TANH(numeric) | Returns the hyperbolic
tangent of *numeric*
+| b | TIMESTAMP_ADD(timestamp, interval int64 date_part) | Adds
int64_expression units of date_part to the timestamp, independent of any time
zone.
| b | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP
that is *integer* microseconds after 1970-01-01 00:00:00
| b | TIMESTAMP_MILLIS(integer) | Returns the TIMESTAMP
that is *integer* milliseconds after 1970-01-01 00:00:00
| b | TIMESTAMP_SECONDS(integer) | Returns the TIMESTAMP
that is *integer* seconds after 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 af13da2828..ec03be71e3 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7725,6 +7725,49 @@ public class SqlOperatorTest {
"TIMESTAMP(3) NOT NULL");
}
+ /** Tests {@code TIMESTAMP_ADD}, BigQuery's 2-argument variant of the
+ * 3-argument {@code TIMESTAMPADD} function. */
+ @Test void testTimestampAdd2() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.TIMESTAMP_ADD2);
+ f0.checkFails("^timestamp_add(timestamp '2008-12-25 15:30:00', "
+ + "interval 5 minute)^",
+ "No match found for function signature "
+ + "TIMESTAMP_ADD\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ if (Bug.CALCITE_5422_FIXED) {
+ f.checkScalar("timestamp_add(timestamp '2008-12-25 15:30:00', "
+ + "interval 100000000000 microsecond)",
+ "2008-12-26 19:16:40",
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2008-12-25 15:30:00', "
+ + "interval 100000000 millisecond)",
+ "2008-12-26 19:16:40",
+ "TIMESTAMP(3) NOT NULL");
+ }
+
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval 2
second)",
+ "2016-02-24 12:42:27",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval 2
minute)",
+ "2016-02-24 12:44:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval
-2000 hour)",
+ "2015-12-03 04:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval 1
day)",
+ "2016-02-25 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval 1
month)",
+ "2016-03-24 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_add(timestamp '2016-02-24 12:42:25', interval 1
year)",
+ "2017-02-24 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkNull("timestamp_add(CAST(NULL AS TIMESTAMP), interval 5 minute)");
+ }
+
@Test void testTimestampDiff() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TIMESTAMP_DIFF, VmName.EXPAND);
