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/13 22:14:43 UTC
[calcite] 01/01: [CALCITE-5436] Add TIMESTAMP_SUB, TIME_SUB, DATE_SUB 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
commit 5dbfb6e95e1f6426cf382bf7524948b7924badd6
Author: Tanner Clary <ta...@google.com>
AuthorDate: Tue Dec 20 17:50:11 2022 +0000
[CALCITE-5436] Add TIMESTAMP_SUB, TIME_SUB, DATE_SUB functions (enabled in BigQuery library)
Close apache/calcite#3008
---
babel/src/test/resources/sql/big-query.iq | 37 ++++----
.../main/java/org/apache/calcite/sql/SqlKind.java | 17 +++-
.../sql/fun/SqlDatetimeSubtractionOperator.java | 21 +++--
.../calcite/sql/fun/SqlInternalOperators.java | 7 ++
.../calcite/sql/fun/SqlLibraryOperators.java | 32 +++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +-
.../org/apache/calcite/sql/type/OperandTypes.java | 3 +
.../calcite/sql2rel/StandardConvertletTable.java | 49 +++++++++-
site/_docs/reference.md | 5 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 100 +++++++++++++++++++++
10 files changed, 238 insertions(+), 35 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index 909b8f1685..b5177f6e1f 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -1309,15 +1309,15 @@ SELECT
#
# Returns DATE
-!if (false) {
SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_ago;
+---------------+
| five_days_ago |
+---------------+
| 2008-12-20 |
+---------------+
+(1 row)
+
!ok
-!}
#####################################################################
# DATETIME_SUB
@@ -1379,17 +1379,17 @@ SELECT
#
# Returns TIME
-!if (false) {
SELECT
TIME "15:30:00" as original_date,
TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;
-+-----------------------------+------------------------+
-| original_date | earlier |
-+-----------------------------+------------------------+
-| 15:30:00 | 15:20:00 |
-+-----------------------------+------------------------+
++---------------+----------+
+| original_date | earlier |
++---------------+----------+
+| 15:30:00 | 15:20:00 |
++---------------+----------+
+(1 row)
+
!ok
-!}
#####################################################################
# TIMESTAMP_SUB
@@ -1409,17 +1409,18 @@ SELECT
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
-!if (false) {
+
SELECT
- TIMESTAMP("2008-12-25 15:30:00+00") AS original,
- TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;
-+-------------------------+-------------------------+
-| original | earlier |
-+-------------------------+-------------------------+
-| 2008-12-25 15:30:00 UTC | 2008-12-25 15:20:00 UTC |
-+-------------------------+-------------------------+
+ TIMESTAMP "2008-12-25 15:30:00" AS original,
+ TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00", INTERVAL 10 MINUTE) AS earlier;
++---------------------+---------------------+
+| original | earlier |
++---------------------+---------------------+
+| 2008-12-25 15:30:00 | 2008-12-25 15:20:00 |
++---------------------+---------------------+
+(1 row)
+
!ok
-!}
#####################################################################
# DATE_DIFF
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 426a5061eb..6e310c7793 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -424,14 +424,23 @@ public enum SqlKind {
/** {@code LEAST} function (Oracle). */
LEAST,
+ /** {@code DATE_SUB} function (BigQuery). */
+ DATE_SUB,
+
+ /** {@code TIME_ADD} function (BigQuery). */
+ TIME_ADD,
+
+ /** {@code TIME_SUB} function (BigQuery). */
+ TIME_SUB,
+
/** {@code TIMESTAMP_ADD} function (ODBC, SQL Server, MySQL). */
TIMESTAMP_ADD,
/** {@code TIMESTAMP_DIFF} function (ODBC, SQL Server, MySQL). */
TIMESTAMP_DIFF,
- /** {@code TIME_ADD} function (BigQuery semantics). */
- TIME_ADD,
+ /** {@code TIMESTAMP_SUB} function (BigQuery). */
+ TIMESTAMP_SUB,
// prefix operators
@@ -1200,7 +1209,9 @@ public enum SqlKind {
FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS, SEPARATOR,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
SELECT, JOIN, OTHER_FUNCTION, POSITION, CAST, TRIM, FLOOR, CEIL,
- TIMESTAMP_ADD, TIMESTAMP_DIFF, TIME_ADD, EXTRACT, INTERVAL,
+ DATE_SUB, TIME_ADD, TIME_SUB,
+ TIMESTAMP_ADD, TIMESTAMP_DIFF, TIMESTAMP_SUB,
+ EXTRACT, INTERVAL,
LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY,
NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE,
VALUES, WITH, WITH_ITEM, ITEM, SKIP_TO_FIRST, SKIP_TO_LAST,
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java
index e63303e02d..571428d972 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java
@@ -23,7 +23,7 @@ import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
-import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.validate.SqlMonotonicity;
/**
@@ -37,17 +37,22 @@ import org.apache.calcite.sql.validate.SqlMonotonicity;
* additional interval qualifier specification, when in {@link SqlCall} form.
* In {@link org.apache.calcite.rex.RexNode} form, it has only two parameters,
* and the return type describes the desired type of interval.
+ *
+ * <p>When being used for BigQuery's {@code TIMESTAMP_SUB}, {@code TIME_SUB},
+ * and {@code DATE_SUB} operators, this operator subtracts an interval value
+ * from a timestamp value. The return type differs due to differing number of
+ * parameters and ordering. This is accounted for by passing in a
+ * {@link SqlReturnTypeInference} which is passed in by
+ * the standard {@link SqlStdOperatorTable#MINUS_DATE MINUS_DATE}
+ * and the library {@link SqlInternalOperators#MINUS_DATE2 MINUS_DATE2}
+ * operators at their respective initializations.
*/
public class SqlDatetimeSubtractionOperator extends SqlSpecialOperator {
//~ Constructors -----------------------------------------------------------
- public SqlDatetimeSubtractionOperator() {
- super(
- "-",
- SqlKind.MINUS,
- 40,
- true,
- ReturnTypes.ARG2_NULLABLE,
+ public SqlDatetimeSubtractionOperator(String name,
+ SqlReturnTypeInference returnTypeInference) {
+ super("-", SqlKind.MINUS, 40, true, returnTypeInference,
InferTypes.FIRST_KNOWN, OperandTypes.MINUS_DATE_OPERATOR);
}
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index a6cfc789e9..492ac12b99 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -125,6 +125,13 @@ public abstract class SqlInternalOperators {
SqlBasicOperator.create("FETCH")
.withPrecedence(SqlStdOperatorTable.UNION.getLeftPrec() - 2, true);
+ /** 2-argument form of the special minus-date operator
+ * to be used with BigQuery subtraction functions. It differs from
+ * the standard MINUS_DATE operator in that it has 2 arguments,
+ * and subtracts an interval from a datetime. */
+ public static final SqlDatetimeSubtractionOperator MINUS_DATE2 =
+ new SqlDatetimeSubtractionOperator("MINUS_DATE2", ReturnTypes.ARG0_NULLABLE);
+
/** Offset operator is ONLY used for its precedence during unparsing. */
public static final SqlOperator OFFSET =
SqlBasicOperator.create("OFFSET")
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 a94d7b2e9d..a84545705a 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
@@ -122,6 +122,14 @@ public abstract class SqlLibraryOperators {
}
};
+ /** The "DATE_SUB(date, interval)" function (BigQuery);
+ * subtracts interval from the date, independent of any time zone. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction DATE_SUB =
+ SqlBasicFunction.create(SqlKind.DATE_SUB, ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.DATE_INTERVAL)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
/** The "DATEPART(timeUnit, datetime)" function
* (Microsoft SQL Server). */
@LibraryOperator(libraries = {MSSQL})
@@ -722,6 +730,18 @@ public abstract class SqlLibraryOperators {
OperandTypes.DATE, OperandTypes.interval(DATE_UNITS)),
SqlFunctionCategory.TIMEDATE);
+ /** The "TIME_SUB(time, interval)" function (BigQuery);
+ * subtracts an interval from a time, independent of any time zone.
+ *
+ * <p>In BigQuery, the syntax is "TIME_SUB(time, INTERVAL int64 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 TIME_SUB =
+ SqlBasicFunction.create(SqlKind.TIME_SUB, ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.TIME_INTERVAL)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
/** The "TIME_TRUNC(time, timeUnit)" function (BigQuery);
* truncates a TIME value to the beginning of a timeUnit. */
@LibraryOperator(libraries = {BIG_QUERY})
@@ -732,6 +752,18 @@ public abstract class SqlLibraryOperators {
OperandTypes.TIME, OperandTypes.interval(TIME_UNITS)),
SqlFunctionCategory.TIMEDATE);
+ /** The "TIMESTAMP_SUB(timestamp, interval)" function (BigQuery);
+ * subtracts an interval from a timestamp, independent of any time zone.
+ *
+ * <p>In BigQuery, the syntax is "TIMESTAMP_SUB(timestamp,
+ * INTERVAL int64 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_SUB =
+ SqlBasicFunction.create(SqlKind.TIMESTAMP_SUB, ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.TIMESTAMP_INTERVAL)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
/** The "TIMESTAMP_TRUNC(timestamp, timeUnit[, timeZone])" function (BigQuery);
* truncates a TIMESTAMP value to the beginning of a timeUnit. */
@LibraryOperator(libraries = {BIG_QUERY})
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 32c5393b40..2d4189c986 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
@@ -1278,7 +1278,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
* additional interval qualifier specification.</p>
*/
public static final SqlDatetimeSubtractionOperator MINUS_DATE =
- new SqlDatetimeSubtractionOperator();
+ new SqlDatetimeSubtractionOperator("-", ReturnTypes.ARG2_NULLABLE);
/**
* The MULTISET Value Constructor. e.g. "<code>MULTISET[1,2,3]</code>".
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 71ceef5815..ecb006c386 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -703,6 +703,9 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker TIMESTAMP_INTERVAL =
family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.DATETIME_INTERVAL);
+ public static final SqlSingleOperandTypeChecker DATE_INTERVAL =
+ family(SqlTypeFamily.DATE, SqlTypeFamily.DATETIME_INTERVAL);
+
public static final SqlSingleOperandTypeChecker DATETIME_INTERVAL =
family(SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME_INTERVAL);
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 880a92661a..e24787b1b5 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -58,6 +58,7 @@ import org.apache.calcite.sql.fun.SqlBetweenOperator;
import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlDatetimeSubtractionOperator;
import org.apache.calcite.sql.fun.SqlExtractFunction;
+import org.apache.calcite.sql.fun.SqlInternalOperators;
import org.apache.calcite.sql.fun.SqlJsonValueFunction;
import org.apache.calcite.sql.fun.SqlLibrary;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
@@ -169,14 +170,20 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL,
new SubstrConvertlet(SqlLibrary.POSTGRESQL));
- registerOp(SqlLibraryOperators.TIMESTAMP_ADD2,
- new TimestampAddConvertlet());
+ registerOp(SqlLibraryOperators.DATE_SUB,
+ new TimestampSubConvertlet());
registerOp(SqlLibraryOperators.TIME_ADD,
new TimestampAddConvertlet());
- registerOp(SqlLibraryOperators.TIMESTAMP_DIFF3,
- new TimestampDiffConvertlet());
registerOp(SqlLibraryOperators.TIME_DIFF,
new TimestampDiffConvertlet());
+ registerOp(SqlLibraryOperators.TIME_SUB,
+ new TimestampSubConvertlet());
+ registerOp(SqlLibraryOperators.TIMESTAMP_ADD2,
+ new TimestampAddConvertlet());
+ registerOp(SqlLibraryOperators.TIMESTAMP_DIFF3,
+ new TimestampDiffConvertlet());
+ registerOp(SqlLibraryOperators.TIMESTAMP_SUB,
+ new TimestampSubConvertlet());
registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl);
registerOp(SqlLibraryOperators.DECODE,
@@ -1908,6 +1915,40 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
}
}
+ /** Convertlet that handles the BigQuery {@code TIMESTAMP_SUB} function. */
+ private static class TimestampSubConvertlet implements SqlRexConvertlet {
+ @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
+ // TIMESTAMP_SUB(timestamp, interval)
+ // => timestamp - count * INTERVAL '1' UNIT
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final SqlBasicCall operandCall = call.operand(1);
+ SqlIntervalQualifier qualifier = operandCall.operand(1);
+ final RexNode op1 = cx.convertExpression(operandCall.operand(0));
+ final RexNode op2 = cx.convertExpression(call.operand(0));
+ final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier);
+ final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
+ final RexNode interval2Sub;
+ switch (unit) {
+ //Fractional second units are converted to seconds using their associated multiplier.
+ case MICROSECOND:
+ case NANOSECOND:
+ interval2Sub =
+ divide(rexBuilder,
+ multiply(rexBuilder,
+ rexBuilder.makeIntervalLiteral(BigDecimal.ONE, qualifier), op1),
+ BigDecimal.ONE.divide(unit.multiplier,
+ RoundingMode.UNNECESSARY));
+ break;
+ default:
+ interval2Sub = multiply(rexBuilder,
+ rexBuilder.makeIntervalLiteral(unit.multiplier, qualifier), op1);
+ }
+
+ return rexBuilder.makeCall(SqlInternalOperators.MINUS_DATE2,
+ op2, interval2Sub);
+ }
+ }
+
/** Convertlet that handles the {@code TIMESTAMPDIFF} function. */
private static class TimestampDiffConvertlet implements SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index ad55e3a3bb..28573717f2 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2618,6 +2618,7 @@ semantics.
| q | DATEPART(timeUnit, datetime) | Equivalent to `EXTRACT(timeUnit FROM datetime)`
| b | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that is *integer* days after 1970-01-01
| p | DATE_PART(timeUnit, datetime) | Equivalent to `EXTRACT(timeUnit FROM datetime)`
+| b | DATE_SUB(date, interval) | Returns the DATE value that occurs *interval* before *date*
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the granularity of *timeUnit*, rounding to the beginning of the unit
| o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) | Compares *value* to each *valueN* value one by one; if *value* is equal to a *valueN*, returns the corresponding *resultN*, else returns *default*, or NULL if *default* is not specified
| p | DIFFERENCE(string, string) | Returns a measure of the similarity of two strings, namely the number of character positions that their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0 if the `SOUNDEX` values are totally different
@@ -2664,14 +2665,16 @@ semantics.
| 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 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
| b o | TANH(numeric) | Returns the hyperbolic tangent of *numeric*
-| b | TIMESTAMP_ADD(timestamp, interval) | Adds *interval* to *timestamp*, independent of any time zone
+| b | TIMESTAMP_ADD(timestamp, interval) | Returns the TIMESTAMP value that occurs *interval* after *timestamp*
| b | TIMESTAMP_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole number of *timeUnit* between *timestamp* and *timestamp2*. Equivalent to `TIMESTAMPDIFF(timeUnit, timestamp2, timestamp)` and `(timestamp - timestamp2) timeUnit`
| 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
+| b | TIMESTAMP_SUB(timestamp, interval) | Returns the TIMESTAMP value that is *interval* before *timestamp*
| b | TIMESTAMP_TRUNC(timestamp, timeUnit) | Truncates *timestamp* to the granularity of *timeUnit*, rounding to the beginning of the unit
| b | TIME_ADD(time, interval) | Adds *interval* to *time*, independent of any time zone
| b | TIME_DIFF(time, time2, timeUnit) | Returns the whole number of *timeUnit* between *time* and *time2*
+| b | TIME_SUB(time, interval) | Returns the TIME value that is *interval* before *time*
| b | TIME_TRUNC(time, timeUnit) | Truncates *time* to the granularity of *timeUnit*, rounding to the beginning of the unit
| 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*
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 4b20a8b5bb..5cf4561dc7 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8143,6 +8143,106 @@ public class SqlOperatorTest {
isNullValue(), "INTEGER"));
}
+ @Test void testTimestampSub() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.TIMESTAMP_SUB);
+ f0.checkFails("^timestamp_sub(timestamp '2008-12-25 15:30:00', "
+ + "interval 5 minute)^",
+ "No match found for function signature "
+ + "TIMESTAMP_SUB\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ if (Bug.CALCITE_5422_FIXED) {
+ f.checkScalar("timestamp_sub(timestamp '2008-12-25 15:30:00', "
+ + "interval 100000000000 microsecond)",
+ "2008-12-24 11:44:20",
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2008-12-25 15:30:00', "
+ + "interval 100000000 millisecond)",
+ "2008-12-24 11:44:20",
+ "TIMESTAMP(3) NOT NULL");
+ }
+
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 2 second)",
+ "2016-02-24 12:42:23",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 2 minute)",
+ "2016-02-24 12:40:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 2000 hour)",
+ "2015-12-03 04:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 1 day)",
+ "2016-02-23 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 1 month)",
+ "2016-01-24 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 1 year)",
+ "2015-02-24 12:42:25",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkNull("timestamp_sub(CAST(NULL AS TIMESTAMP), interval 5 minute)");
+ }
+
+ @Test void testTimeSub() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.TIME_SUB);
+ f0.checkFails("^time_sub(time '15:30:00', "
+ + "interval 5 minute)^",
+ "No match found for function signature "
+ + "TIME_SUB\\(<TIME>, <INTERVAL_DAY_TIME>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ if (Bug.CALCITE_5422_FIXED) {
+ f.checkScalar("time_sub(time '15:30:00', "
+ + "interval 100000000000 microsecond)",
+ "11:44:20",
+ "TIME(3) NOT NULL");
+ f.checkScalar("time_sub(time '15:30:00', "
+ + "interval 100000000 millisecond)",
+ "11:44:20",
+ "TIME(3) NOT NULL");
+ }
+
+ f.checkScalar("time_sub(time '12:42:25', interval 2 second)",
+ "12:42:23",
+ "TIME(0) NOT NULL");
+ f.checkScalar("time_sub(time '12:42:25', interval 2 minute)",
+ "12:40:25",
+ "TIME(0) NOT NULL");
+ f.checkScalar("time_sub(time '12:42:25', interval 0 minute)",
+ "12:42:25",
+ "TIME(0) NOT NULL");
+ f.checkScalar("time_sub(time '12:42:25', interval 20 hour)",
+ "16:42:25",
+ "TIME(0) NOT NULL");
+ f.checkScalar("time_sub(time '12:34:45', interval -5 second)",
+ "12:34:50",
+ "TIME(0) NOT NULL");
+ f.checkNull("time_sub(CAST(NULL AS TIME), interval 5 minute)");
+ }
+
+ @Test void testDateSub() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.DATE_SUB);
+ f0.checkFails("^date_sub(date '2008-12-25', "
+ + "interval 5 day)^",
+ "No match found for function signature "
+ + "DATE_SUB\\(<DATE>, <INTERVAL_DAY_TIME>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("date_sub(date '2016-02-24', interval 2 day)",
+ "2016-02-22",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(date '2016-02-24', interval 3 month)",
+ "2015-11-24",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(date '2016-02-24', interval 5 year)",
+ "2011-02-24",
+ "DATE NOT NULL");
+ f.checkNull("date_sub(CAST(NULL AS DATE), interval 5 day)");
+ }
+
/** The {@code DATEDIFF} function is implemented in the Babel parser but not
* the Core parser, and therefore gives validation errors. */
@Test void testDateDiff() {