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 <[email protected]> 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() {
