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 65a5efc0d681d989baa890184c435c6c8340718c Author: Tanner Clary <[email protected]> AuthorDate: Wed Jan 18 17:49:13 2023 +0000 [CALCITE-5484] Add DATETIME_SUB function (enabled in BigQuery library) Close apache/calcite#3078 --- babel/src/test/resources/sql/big-query.iq | 26 +++++++++---- .../calcite/sql/fun/SqlLibraryOperators.java | 9 ++++- .../calcite/sql2rel/StandardConvertletTable.java | 2 + site/_docs/reference.md | 1 + .../org/apache/calcite/test/SqlOperatorTest.java | 45 ++++++++++++++++++++++ 5 files changed, 75 insertions(+), 8 deletions(-) diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index 528ac8797b..30bd2c6133 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -1595,17 +1595,29 @@ SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_ago; # # Returns DATETIME -!if (false) { SELECT DATETIME "2008-12-25 15:30:00" as original_date, DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier; -+-----------------------------+------------------------+ -| original_date | earlier | -+-----------------------------+------------------------+ -| 2008-12-25T15:30:00 | 2008-12-25T15:20:00 | -+-----------------------------+------------------------+ ++---------------------+---------------------+ +| original_date | earlier | ++---------------------+---------------------+ +| 2008-12-25 15:30:00 | 2008-12-25 15:20:00 | ++---------------------+---------------------+ +(1 row) + +!ok + +SELECT + TIMESTAMP "2008-12-25 15:30:00" as original_date, + DATETIME_SUB(TIMESTAMP "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier; ++---------------------+---------------------+ +| original_date | earlier | ++---------------------+---------------------+ +| 2008-12-25 15:30:00 | 2008-12-25 15:20:00 | ++---------------------+---------------------+ +(1 row) + !ok -!} ##################################################################### 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 936194d4e1..fea6ec5298 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 @@ -918,11 +918,18 @@ public abstract class SqlLibraryOperators { * 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 = + public static final SqlBasicFunction TIMESTAMP_SUB = SqlBasicFunction.create(SqlKind.TIMESTAMP_SUB, ReturnTypes.ARG0_NULLABLE, OperandTypes.TIMESTAMP_INTERVAL) .withFunctionType(SqlFunctionCategory.TIMEDATE); + /** BigQuery's {@code DATETIME_SUB(timestamp, interval)} function + * is a synonym for TIMESTAMP_SUB because in Calcite, DATETIME + * is an alias for TIMESTAMP. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction DATETIME_SUB = + TIMESTAMP_SUB.withName("DATETIME_SUB"); + /** 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/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index ddafadccce..fc42bd32fb 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -183,6 +183,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL, new SubstrConvertlet(SqlLibrary.POSTGRESQL)); + registerOp(SqlLibraryOperators.DATETIME_SUB, + new TimestampSubConvertlet()); registerOp(SqlLibraryOperators.DATE_SUB, new TimestampSubConvertlet()); registerOp(SqlLibraryOperators.TIME_ADD, diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 9fef9858f1..db53c6d210 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2649,6 +2649,7 @@ BigQuery's type system uses confusingly different names for types and functions: | b | DATETIME(date) | Converts *date* to a TIMESTAMP value (at midnight) | b | DATETIME(date, timeZone) | Converts *date* to a TIMESTAMP value (at midnight), in *timeZone* | b | DATETIME(year, month, day, hour, minute, second) | Creates a TIMESTAMP for *year*, *month*, *day*, *hour*, *minute*, *second* (all of type INTEGER) +| b | DATETIME_SUB(timestamp, interval) | Returns the TIMESTAMP that occurs *interval* before *timestamp* | 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* 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 a51531284e..5606efeb7a 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -8381,6 +8381,51 @@ public class SqlOperatorTest { f.checkNull("date_sub(CAST(NULL AS DATE), interval 5 day)"); } + /** Tests for BigQuery's DATETIME_SUB() function. Because the operator + * fixture does not currently support type aliases, TIMESTAMPs are used + * in place of DATETIMEs (a Calcite alias of TIMESTAMP) for the function's + * first argument. */ + @Test void testDatetimeSub() { + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.DATETIME_SUB); + f0.checkFails("^datetime_sub(timestamp '2008-12-25 15:30:00', " + + "interval 5 minute)^", + "No match found for function signature " + + "DATETIME_SUB\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false); + + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); + if (Bug.CALCITE_5422_FIXED) { + f.checkScalar("datetime_sub(timestamp '2008-12-25 15:30:00', " + + "interval 100000000000 microsecond)", + "2008-12-24 11:44:20", + "TIMESTAMP(3) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2008-12-25 15:30:00', " + + "interval 100000000 millisecond)", + "2008-12-24 11:44:20", + "TIMESTAMP(3) NOT NULL"); + } + + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 2 second)", + "2016-02-24 12:42:23", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 2 minute)", + "2016-02-24 12:40:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 2000 hour)", + "2015-12-03 04:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 1 day)", + "2016-02-23 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 1 month)", + "2016-01-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_sub(timestamp '2016-02-24 12:42:25', interval 1 year)", + "2015-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkNull("datetime_sub(CAST(NULL AS TIMESTAMP), interval 5 minute)"); + } + /** The {@code DATEDIFF} function is implemented in the Babel parser but not * the Core parser, and therefore gives validation errors. */ @Test void testDateDiff() {
