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 cb97d299d2a04485a84916cff3f7be5497028b31 Author: Tanner Clary <[email protected]> AuthorDate: Fri Jan 13 01:26:44 2023 +0000 [CALCITE-5469] Add DATETIME_ADD, DATETIME_DIFF, DATE_ADD, DATE_DIFF functions (enabled in BigQuery library) Some of the *DIFF functions truncate both operands to the start of the unit before subtracting. The rule seems to be: * standard TIMESTAMPDIFF does not truncate before subtracting; * BigQuery TIMESTAMP_DIFF, DATETIME_DIFF and DATE_DIFF functions truncate before subtracting when applied to date intervals (includes, for example, DAY, SQL_TSI_DAY, WEEK, ISOWEEK, WEEK(THURSDAY), MONTH, YEAR, CENTURY). Change FLOOR, CEIL, DATETIME_TRUNC to no-op when the unit is MICROSECOND or NANOSECOND. Previously they threw a divide-by-zero exception. Move the definitions of date, time, week intervals from OperandTypes to become methods on SqlIntervalQualifier. The methods now work correctly for ISOWEEK, WEEK(weekday), SQL_TSI_* time frames. Co-authored-by: Tanner Clary <[email protected]> Co-authored-by: Julian Hyde <[email protected]> Close apache/calcite#3079 --- babel/src/main/codegen/config.fmpp | 1 + babel/src/test/resources/sql/big-query.iq | 97 ++++++----- core/src/main/codegen/default_config.fmpp | 2 + core/src/main/codegen/templates/Parser.jj | 62 +++++++ .../calcite/adapter/enumerable/RexImpTable.java | 7 + .../apache/calcite/sql/SqlIntervalQualifier.java | 76 +++++++++ .../main/java/org/apache/calcite/sql/SqlKind.java | 5 +- .../calcite/sql/fun/SqlLibraryOperators.java | 31 +++- .../org/apache/calcite/sql/type/OperandTypes.java | 45 +----- .../calcite/sql2rel/StandardConvertletTable.java | 76 +++++++-- site/_docs/reference.md | 6 + .../org/apache/calcite/test/SqlOperatorTest.java | 178 ++++++++++++++++++++- 12 files changed, 487 insertions(+), 99 deletions(-) diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp index 972cb47b8d..eb0d5f1ef6 100644 --- a/babel/src/main/codegen/config.fmpp +++ b/babel/src/main/codegen/config.fmpp @@ -157,6 +157,7 @@ data: { "CYCLE" "DATA" # "DATE" + "DATETIME_DIFF" "DAY" "DEALLOCATE" "DEC" diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index 30bd2c6133..59c0d09d18 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -1335,15 +1335,15 @@ select unix_date(datetime '2008-12-25') as d; # # Returns DATE -!if (false) { SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_later; -+--------------------+ -| five_days_later | -+--------------------+ -| 2008-12-30 | -+--------------------+ ++-----------------+ +| five_days_later | ++-----------------+ +| 2008-12-30 | ++-----------------+ +(1 row) + !ok -!} ##################################################################### # DATETIME_ADD @@ -1372,17 +1372,29 @@ SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_later; # # Returns DATETIME -!if (false) { SELECT DATETIME "2008-12-25 15:30:00" as original_date, DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later; -+-----------------------------+------------------------+ -| original_date | later | -+-----------------------------+------------------------+ -| 2008-12-25T15:30:00 | 2008-12-25T15:40:00 | -+-----------------------------+------------------------+ ++---------------------+---------------------+ +| original_date | later | ++---------------------+---------------------+ +| 2008-12-25 15:30:00 | 2008-12-25 15:40:00 | ++---------------------+---------------------+ +(1 row) + +!ok + +SELECT + DATETIME "2008-12-25 15:30:00" as original_date, + DATETIME_ADD(TIMESTAMP "2008-12-25 15:30:00", INTERVAL 20 MINUTE) as later; ++---------------------+---------------------+ +| original_date | later | ++---------------------+---------------------+ +| 2008-12-25 15:30:00 | 2008-12-25 15:50:00 | ++---------------------+---------------------+ +(1 row) + !ok -!} ##################################################################### # TIME_ADD @@ -1709,27 +1721,27 @@ SELECT # # Returns INT64 -!if (false) { SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) AS days_diff; +-----------+ | days_diff | +-----------+ -| 559 | +| 559 | +-----------+ +(1 row) + !ok -!} -!if (false) { SELECT DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) AS days_diff, DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) AS weeks_diff; +-----------+------------+ | days_diff | weeks_diff | +-----------+------------+ -| 1 | 1 | +| 1 | 1 | +-----------+------------+ +(1 row) + !ok -!} # The example above shows the result of DATE_DIFF for two days in # succession. DATE_DIFF with the date part WEEK returns 1 because @@ -1744,17 +1756,18 @@ SELECT # because the second date belongs to the ISO year 2015. The first # Thursday of the 2015 calendar year was 2015-01-01, so the ISO year # 2015 begins on the preceding Monday, 2014-12-29. -!if (false) { + SELECT DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff, DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff; +-----------+--------------+ | year_diff | isoyear_diff | +-----------+--------------+ -| 3 | 2 | +| 3 | 2 | +-----------+--------------+ +(1 row) + !ok -!} # The following example shows the result of DATE_DIFF for two days in # succession. The first date falls on a Monday and the second date @@ -1763,7 +1776,6 @@ SELECT # with the date part WEEK(MONDAY) returns 1. DATE_DIFF with the date # part ISOWEEK also returns 1 because ISO weeks begin on Monday. -!if (false) { SELECT DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff, DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff, @@ -1771,10 +1783,11 @@ SELECT +-----------+-------------------+--------------+ | week_diff | week_weekday_diff | isoweek_diff | +-----------+-------------------+--------------+ -| 0 | 1 | 1 | +| 0 | 1 | 1 | +-----------+-------------------+--------------+ +(1 row) + !ok -!} ##################################################################### # DATETIME_DIFF @@ -1810,21 +1823,20 @@ SELECT # # Returns INT64 -!if (false) { SELECT DATETIME "2010-07-07 10:20:00" as first_datetime, DATETIME "2008-12-25 15:30:00" as second_datetime, DATETIME_DIFF(DATETIME "2010-07-07 10:20:00", DATETIME "2008-12-25 15:30:00", DAY) as difference; -+----------------------------+------------------------+------------------------+ -| first_datetime | second_datetime | difference | -+----------------------------+------------------------+------------------------+ -| 2010-07-07T10:20:00 | 2008-12-25T15:30:00 | 559 | -+----------------------------+------------------------+------------------------+ ++---------------------+---------------------+------------+ +| first_datetime | second_datetime | difference | ++---------------------+---------------------+------------+ +| 2010-07-07 10:20:00 | 2008-12-25 15:30:00 | 559 | ++---------------------+---------------------+------------+ +(1 row) + !ok -!} -!if (false) { SELECT DATETIME_DIFF(DATETIME '2017-10-15 00:00:00', DATETIME '2017-10-14 00:00:00', DAY) as days_diff, @@ -1833,10 +1845,11 @@ SELECT +-----------+------------+ | days_diff | weeks_diff | +-----------+------------+ -| 1 | 1 | +| 1 | 1 | +-----------+------------+ +(1 row) + !ok -!} # The example above shows the result of DATETIME_DIFF for two # DATETIMEs that are 24 hours apart. DATETIME_DIFF with the part WEEK @@ -1854,7 +1867,6 @@ SELECT # 2015-01-01, so the ISO year 2015 begins on the preceding Monday, # 2014-12-29. -!if (false) { SELECT DATETIME_DIFF('2017-12-30 00:00:00', '2014-12-30 00:00:00', YEAR) AS year_diff, @@ -1863,10 +1875,11 @@ SELECT +-----------+--------------+ | year_diff | isoyear_diff | +-----------+--------------+ -| 3 | 2 | +| 3 | 2 | +-----------+--------------+ +(1 row) + !ok -!} # The following example shows the result of DATETIME_DIFF for two days # in succession. The first date falls on a Monday and the second date @@ -1876,7 +1889,6 @@ SELECT # 1. DATETIME_DIFF with the date part ISOWEEK also returns 1 because # ISO weeks begin on Monday. -!if (false) { SELECT DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff, DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff, @@ -1884,10 +1896,11 @@ SELECT +-----------+-------------------+--------------+ | week_diff | week_weekday_diff | isoweek_diff | +-----------+-------------------+--------------+ -| 0 | 1 | 1 | +| 0 | 1 | 1 | +-----------+-------------------+--------------+ +(1 row) + !ok -!} ##################################################################### # TIME_DIFF diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp index a1ff7d0549..7e63b05bd6 100644 --- a/core/src/main/codegen/default_config.fmpp +++ b/core/src/main/codegen/default_config.fmpp @@ -83,7 +83,9 @@ parser: { "CURSOR_NAME" "DATA" "DATABASE" + "DATE_DIFF" "DATE_TRUNC" + "DATETIME_DIFF" "DATETIME_INTERVAL_CODE" "DATETIME_INTERVAL_PRECISION" "DAYS" diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 0b9dec5216..369591b395 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -6150,10 +6150,14 @@ SqlNode BuiltinFunctionCall() : } | node = DateTimeConstructorCall() { return node; } + | + node = DateDiffFunctionCall() { return node; } | node = DateTruncFunctionCall() { return node; } | node = TimestampAddFunctionCall() { return node; } + | + node = DatetimeDiffFunctionCall() { return node; } | node = TimestampDiffFunctionCall() { return node; } | @@ -6663,6 +6667,28 @@ SqlCall JsonArrayAggFunctionCall() : } } +/** + * Parses a call to BigQuery's DATE_DIFF. + */ +SqlCall DateDiffFunctionCall() : +{ + final List<SqlNode> args = new ArrayList<SqlNode>(); + final Span s; + final SqlIntervalQualifier unit; +} +{ + <DATE_DIFF> { s = span(); } + <LPAREN> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + unit = TimeUnitOrName() { args.add(unit); } + <RPAREN> { + return SqlLibraryOperators.DATE_DIFF.createCall(s.end(this), args); + } +} + /** * Parses a call to TIMESTAMPADD. */ @@ -6736,6 +6762,28 @@ SqlCall TimestampDiff3FunctionCall() : } } +/** + * Parses BigQuery's built-in DATETIME_DIFF() function. + */ +SqlCall DatetimeDiffFunctionCall() : +{ + final List<SqlNode> args = new ArrayList<SqlNode>(); + final Span s; + final SqlIntervalQualifier unit; +} +{ + <DATETIME_DIFF> { s = span(); } + <LPAREN> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + unit = TimeUnitOrName() { args.add(unit); } + <RPAREN> { + return SqlLibraryOperators.DATETIME_DIFF.createCall(s.end(this), args); + } +} + /** * Parses a call to DATE_TRUNC. */ @@ -7313,6 +7361,18 @@ SqlNode JdbcFunctionCall() : s = span(); } ( + LOOKAHEAD(1) + call = DateDiffFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); + } + | + LOOKAHEAD(1) + call = DatetimeDiffFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); + } + | LOOKAHEAD(1) call = TimestampAddFunctionCall() { name = call.getOperator().getName(); @@ -7719,8 +7779,10 @@ SqlPostfixOperator PostfixRowOperator() : | < DATE: "DATE" > | < DATE_TRUNC: "DATE_TRUNC" > | < DATETIME: "DATETIME" > +| < DATETIME_DIFF: "DATETIME_DIFF" > | < DATETIME_INTERVAL_CODE: "DATETIME_INTERVAL_CODE" > | < DATETIME_INTERVAL_PRECISION: "DATETIME_INTERVAL_PRECISION" > +| < DATE_DIFF: "DATE_DIFF" > | < DAY: "DAY" > | < DAYS: "DAYS" > | < DEALLOCATE: "DEALLOCATE" > 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 013f748b61..fa71552e23 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 @@ -2289,6 +2289,13 @@ public class RexImpTable { private Expression call(Expression operand, Type type, TimeUnit timeUnit) { + if (timeUnit.multiplier.compareTo(BigDecimal.ONE) < 0) { + // MICROSECOND has a multiplier of 0.001, + // NANOSECOND has a multiplier of 0.000001. + // In integer arithmetic, these underflow to zero, so we get a + // divide-by-zero exception. FLOOR and CEIL on these units should no-op. + return EnumUtils.convert(operand, type); + } return Expressions.call(SqlFunctions.class, methodName, EnumUtils.convert(operand, type), EnumUtils.convert( diff --git a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java index 4c20fea592..0dce371574 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java @@ -20,6 +20,7 @@ import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeSystem; +import org.apache.calcite.rel.type.TimeFrames; import org.apache.calcite.runtime.CalciteContextException; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.SqlTypeName; @@ -29,9 +30,12 @@ import org.apache.calcite.sql.validate.SqlValidatorScope; import org.apache.calcite.util.Litmus; import org.apache.calcite.util.Util; +import com.google.common.collect.ImmutableSet; + import org.checkerframework.checker.nullness.qual.Nullable; import java.math.BigDecimal; +import java.util.Set; import java.util.regex.Matcher; import java.util.regex.Pattern; @@ -93,6 +97,52 @@ public class SqlIntervalQualifier extends SqlNode { private static final BigDecimal INT_MAX_VALUE_PLUS_ONE = BigDecimal.valueOf(Integer.MAX_VALUE).add(BigDecimal.ONE); + private static final Set<TimeUnitRange> TIME_UNITS = + ImmutableSet.of(TimeUnitRange.HOUR, + TimeUnitRange.MINUTE, + TimeUnitRange.SECOND); + + private static final Set<TimeUnitRange> MONTH_UNITS = + ImmutableSet.of(TimeUnitRange.MILLENNIUM, + TimeUnitRange.CENTURY, + TimeUnitRange.DECADE, + TimeUnitRange.YEAR, + TimeUnitRange.ISOYEAR, + TimeUnitRange.QUARTER, + TimeUnitRange.MONTH); + + private static final Set<TimeUnitRange> DAY_UNITS = + ImmutableSet.of(TimeUnitRange.WEEK, + TimeUnitRange.DAY); + + private static final Set<TimeUnitRange> DATE_UNITS = + ImmutableSet.<TimeUnitRange>builder() + .addAll(MONTH_UNITS).addAll(DAY_UNITS).build(); + + private static final Set<String> WEEK_FRAMES = + ImmutableSet.<String>builder() + .addAll(TimeFrames.WEEK_FRAME_NAMES) + .add("ISOWEEK") + .add("WEEK") + .add("SQL_TSI_WEEK") + .build(); + + private static final Set<String> TSI_TIME_FRAMES = + ImmutableSet.of( + "SQL_TSI_FRAC_SECOND", + "SQL_TSI_MICROSECOND", + "SQL_TSI_SECOND", + "SQL_TSI_MINUTE", + "SQL_TSI_HOUR"); + + private static final Set<String> TSI_DATE_FRAMES = + ImmutableSet.of( + "SQL_TSI_DAY", + "SQL_TSI_WEEK", + "SQL_TSI_MONTH", + "SQL_TSI_QUARTER", + "SQL_TSI_YEAR"); + //~ Instance fields -------------------------------------------------------- private final int startPrecision; @@ -197,6 +247,32 @@ public class SqlIntervalQualifier extends SqlNode { } } + /** Whether this is a DATE interval (including all week intervals). */ + public boolean isDate() { + return DATE_UNITS.contains(timeUnitRange) + || timeFrameName != null && TSI_DATE_FRAMES.contains(timeFrameName) + || isWeek(); + } + + /** Whether this is a TIME interval. */ + public boolean isTime() { + return TIME_UNITS.contains(timeUnitRange) + || timeFrameName != null && TSI_TIME_FRAMES.contains(timeFrameName); + } + + /** Whether this is a TIMESTAMP interval (including all week intervals). */ + public boolean isTimestamp() { + return isDate() || isTime(); + } + + /** Whether this qualifier represents {@code WEEK}, {@code ISOWEEK}, + * or {@code WEEK(}<i>weekday</i>{@code )} + * (for <i>weekday</i> in {@code SUNDAY} .. {@code SATURDAY}). */ + public boolean isWeek() { + return timeUnitRange == TimeUnitRange.WEEK + || timeFrameName != null && WEEK_FRAMES.contains(timeFrameName); + } + @Override public void validate( SqlValidator validator, SqlValidatorScope scope) { 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 269b80e2d7..8c8a83eaf4 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -424,6 +424,9 @@ public enum SqlKind { /** {@code LEAST} function (Oracle). */ LEAST, + /** {@code DATE_DIFF} function (BigQuery Semantics). */ + DATE_ADD, + /** {@code DATE_SUB} function (BigQuery). */ DATE_SUB, @@ -1215,7 +1218,7 @@ 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, - DATE_SUB, TIME_ADD, TIME_SUB, + DATE_ADD, DATE_SUB, TIME_ADD, TIME_SUB, TIMESTAMP_ADD, TIMESTAMP_DIFF, TIMESTAMP_SUB, EXTRACT, INTERVAL, LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY, 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 fea6ec5298..620cb5b724 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 @@ -90,6 +90,21 @@ public abstract class SqlLibraryOperators { ReturnTypes.DATE_NULLABLE, OperandTypes.CHARACTER_CHARACTER_DATETIME, SqlFunctionCategory.TIMEDATE); + /** THE "DATE_ADD(date, interval)" function + * (BigQuery) adds the interval to the date. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction DATE_ADD = + SqlBasicFunction.create(SqlKind.DATE_ADD, ReturnTypes.ARG0_NULLABLE, + OperandTypes.DATE_INTERVAL) + .withFunctionType(SqlFunctionCategory.TIMEDATE); + + /** THE "DATE_DIFF(date, date2, timeUnit)" function + * (BigQuery) returns the number of timeUnit in (date - date2). */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction DATE_DIFF = + new SqlTimestampDiffFunction("DATE_DIFF", + OperandTypes.family(SqlTypeFamily.DATE, SqlTypeFamily.DATE, SqlTypeFamily.ANY)); + /** The "DATEADD(timeUnit, numeric, datetime)" function * (Microsoft SQL Server, Redshift, Snowflake). */ @LibraryOperator(libraries = {MSSQL, POSTGRESQL}) @@ -846,7 +861,7 @@ public abstract class SqlLibraryOperators { * 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 = + public static final SqlBasicFunction TIMESTAMP_ADD2 = SqlBasicFunction.create(SqlKind.TIMESTAMP_ADD, ReturnTypes.ARG0_NULLABLE, OperandTypes.TIMESTAMP_INTERVAL) .withFunctionType(SqlFunctionCategory.TIMEDATE); @@ -988,6 +1003,20 @@ public abstract class SqlLibraryOperators { ReturnTypes.BIGINT_NULLABLE, OperandTypes.TIMESTAMP, SqlFunctionCategory.TIMEDATE); + /** The "DATETIME_ADD(timestamp, interval)" function (BigQuery). + * As {@code TIMESTAMP_ADD}, returns a Calcite {@code TIMESTAMP} + * (which BigQuery calls a {@code DATETIME}). */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction DATETIME_ADD = + TIMESTAMP_ADD2.withName("DATETIME_ADD"); + + /** The "DATETIME_DIFF(timestamp, timestamp2, timeUnit)" function (BigQuery). */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction DATETIME_DIFF = + new SqlTimestampDiffFunction("DATETIME_DIFF", + OperandTypes.family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.TIMESTAMP, + SqlTypeFamily.ANY)); + /** The "CHAR(n)" function; returns the character whose ASCII code is * {@code n} % 256, or null if {@code n} < 0. */ @LibraryOperator(libraries = {MYSQL, SPARK}) 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 1f23a81707..7c191977aa 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 @@ -20,8 +20,8 @@ import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeComparability; import org.apache.calcite.rel.type.RelDataTypeFactory; -import org.apache.calcite.rel.type.TimeFrames; import org.apache.calcite.sql.SqlCallBinding; +import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlOperandCountRange; @@ -70,38 +70,6 @@ import static java.util.Objects.requireNonNull; * @see org.apache.calcite.sql.type.InferTypes */ public abstract class OperandTypes { - private static final Set<TimeUnitRange> TIME_UNITS = - ImmutableSet.of(TimeUnitRange.HOUR, - TimeUnitRange.MINUTE, - TimeUnitRange.SECOND); - - private static final Set<TimeUnitRange> MONTH_UNITS = - ImmutableSet.of(TimeUnitRange.MILLENNIUM, - TimeUnitRange.CENTURY, - TimeUnitRange.DECADE, - TimeUnitRange.YEAR, - TimeUnitRange.ISOYEAR, - TimeUnitRange.QUARTER, - TimeUnitRange.MONTH); - - private static final Set<TimeUnitRange> DAY_UNITS = - ImmutableSet.of(TimeUnitRange.WEEK, - TimeUnitRange.DAY); - - private static final Set<TimeUnitRange> DATE_UNITS = - ImmutableSet.<TimeUnitRange>builder() - .addAll(MONTH_UNITS).addAll(DAY_UNITS).build(); - - private static final Set<TimeUnitRange> TIMESTAMP_UNITS = - ImmutableSet.<TimeUnitRange>builder() - .addAll(DATE_UNITS).addAll(TIME_UNITS).build(); - - private static final Set<String> WEEK_FRAMES = - ImmutableSet.<String>builder() - .addAll(TimeFrames.WEEK_FRAME_NAMES) - .add("ISOWEEK") - .add("WEEK") - .build(); private OperandTypes() { } @@ -157,17 +125,14 @@ public abstract class OperandTypes { * WEEK_WEDNESDAY, etc.) */ public static SqlSingleOperandTypeChecker dateInterval() { - return new IntervalOperandTypeChecker(intervalQualifier -> - DATE_UNITS.contains(intervalQualifier.timeUnitRange) - || WEEK_FRAMES.contains(intervalQualifier.timeFrameName)); + return new IntervalOperandTypeChecker(SqlIntervalQualifier::isDate); } /** * Creates a checker for TIME intervals (HOUR, SECOND, etc.) */ public static SqlSingleOperandTypeChecker timeInterval() { - return new IntervalOperandTypeChecker(intervalQualifier -> - TIME_UNITS.contains(intervalQualifier.timeUnitRange)); + return new IntervalOperandTypeChecker(SqlIntervalQualifier::isTime); } /** @@ -175,9 +140,7 @@ public abstract class OperandTypes { * WEEK_WEDNESDAY, HOUR, SECOND, etc.) */ public static SqlSingleOperandTypeChecker timestampInterval() { - return new IntervalOperandTypeChecker(intervalQualifier -> - TIMESTAMP_UNITS.contains(intervalQualifier.timeUnitRange) - || WEEK_FRAMES.contains(intervalQualifier.timeFrameName)); + return new IntervalOperandTypeChecker(SqlIntervalQualifier::isTimestamp); } /** 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 fc42bd32fb..1a11eb6681 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -93,6 +93,7 @@ import java.math.RoundingMode; import java.util.ArrayList; import java.util.List; import java.util.Objects; +import java.util.function.UnaryOperator; import java.util.stream.Collectors; import static org.apache.calcite.sql.type.NonNullableAccessors.getComponentTypeOrThrow; @@ -183,10 +184,18 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL, new SubstrConvertlet(SqlLibrary.POSTGRESQL)); - registerOp(SqlLibraryOperators.DATETIME_SUB, - new TimestampSubConvertlet()); + registerOp(SqlLibraryOperators.DATE_ADD, + new TimestampAddConvertlet()); + registerOp(SqlLibraryOperators.DATE_DIFF, + new TimestampDiffConvertlet()); registerOp(SqlLibraryOperators.DATE_SUB, new TimestampSubConvertlet()); + registerOp(SqlLibraryOperators.DATETIME_ADD, + new TimestampAddConvertlet()); + registerOp(SqlLibraryOperators.DATETIME_DIFF, + new TimestampDiffConvertlet()); + registerOp(SqlLibraryOperators.DATETIME_SUB, + new TimestampSubConvertlet()); registerOp(SqlLibraryOperators.TIME_ADD, new TimestampAddConvertlet()); registerOp(SqlLibraryOperators.TIME_DIFF, @@ -1969,34 +1978,75 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { /** Convertlet that handles the {@code TIMESTAMPDIFF} function. */ private static class TimestampDiffConvertlet implements SqlRexConvertlet { @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) { + // The standard TIMESTAMPDIFF and BigQuery's TIMESTAMP_DIFF have two key + // differences. The first being the order of the subtraction, outlined + // below. The second is that BigQuery truncates each timestamp to the + // specified time unit before the difference is computed. + // + // In fact, all BigQuery functions (TIMESTAMP_DIFF, DATETIME_DIFF, + // DATE_DIFF) truncate before subtracting when applied to date intervals + // (DAY, WEEK, ISOWEEK, MONTH, YEAR, etc.) + // + // For example, if computing the number of weeks between two timestamps, + // one occurring on a Saturday and the other occurring the next day on + // Sunday, their week difference is 1. This is because the first timestamp + // is truncated to the previous Sunday. This is done by making calls to + // TIMESTAMP_TRUNC and the difference is then computed using their + // results. + // // TIMESTAMPDIFF(unit, t1, t2) // => (t2 - t1) UNIT // TIMESTAMP_DIFF(t1, t2, unit) // => (t1 - t2) UNIT SqlIntervalQualifier qualifier; + final boolean preTruncate; final RexNode op1; final RexNode op2; if (call.operand(0).getKind() == SqlKind.INTERVAL_QUALIFIER) { qualifier = call.operand(0); + preTruncate = false; op1 = cx.convertExpression(call.operand(1)); op2 = cx.convertExpression(call.operand(2)); } else { qualifier = call.operand(2); + preTruncate = qualifier.isDate(); op1 = cx.convertExpression(call.operand(1)); op2 = cx.convertExpression(call.operand(0)); } final RexBuilder rexBuilder = cx.getRexBuilder(); + final RelDataTypeFactory typeFactory = cx.getTypeFactory(); final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier); final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH); + UnaryOperator<RexNode> truncateFn = UnaryOperator.identity(); 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. final RexLiteral timeFrameName = rexBuilder.makeLiteral(qualifier.timeFrameName); + // This additional logic accounts for BigQuery truncating prior to + // computing the difference. + if (preTruncate) { + truncateFn = e -> + rexBuilder.makeCall(e.getType(), + SqlLibraryOperators.TIMESTAMP_TRUNC, + ImmutableList.of(e, timeFrameName)); + } return rexBuilder.makeCall(cx.getValidator().getValidatedNodeType(call), SqlStdOperatorTable.TIMESTAMP_DIFF, - ImmutableList.of(timeFrameName, op1, op2)); + ImmutableList.of(timeFrameName, truncateFn.apply(op1), + truncateFn.apply(op2))); + } + + if (preTruncate) { + // The timestamps should be truncated unless the time unit is HOUR, in + // which case only the whole number of hours between the timestamps + // should be returned. + final RexNode timeUnit = cx.convertExpression(qualifier); + truncateFn = e -> + rexBuilder.makeCall(e.getType(), + SqlLibraryOperators.TIMESTAMP_TRUNC, + ImmutableList.of(e, timeUnit)); } BigDecimal multiplier = BigDecimal.ONE; @@ -2029,18 +2079,20 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { qualifier.getParserPosition()); break; } + final RelDataType intervalType = - cx.getTypeFactory().createTypeWithNullability( - cx.getTypeFactory().createSqlIntervalType(qualifier), + typeFactory.createTypeWithNullability( + typeFactory.createSqlIntervalType(qualifier), op1.getType().isNullable() || op2.getType().isNullable()); - final RexCall rexCall = (RexCall) rexBuilder.makeCall( - intervalType, SqlStdOperatorTable.MINUS_DATE, - ImmutableList.of(op2, op1)); + final RexNode call2 = + rexBuilder.makeCall(intervalType, + SqlStdOperatorTable.MINUS_DATE, + ImmutableList.of(truncateFn.apply(op2), truncateFn.apply(op1))); final RelDataType intType = - cx.getTypeFactory().createTypeWithNullability( - cx.getTypeFactory().createSqlType(sqlTypeName), - SqlTypeUtil.containsNullable(rexCall.getType())); - RexNode e = rexBuilder.makeCast(intType, rexCall); + typeFactory.createTypeWithNullability( + typeFactory.createSqlType(sqlTypeName), + SqlTypeUtil.containsNullable(call2.getType())); + RexNode e = rexBuilder.makeCast(intType, call2); return rexBuilder.multiplyDivide(e, multiplier, divider); } } diff --git a/site/_docs/reference.md b/site/_docs/reference.md index db53c6d210..f0cfa5c75f 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -544,8 +544,10 @@ DATA, DATABASE, **DATE**, **DATETIME**, +DATETIME_DIFF, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, +DATE_DIFF, DATE_TRUNC, **DAY**, DAYS, @@ -2649,9 +2651,13 @@ 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_ADD(timestamp, interval) | Returns the TIMESTAMP value that occurs *interval* after *timestamp* +| b | DATETIME_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole number of *timeUnit* between *timestamp* and *timestamp2* | 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_ADD(date, interval) | Returns the DATE value that occurs *interval* after *date* +| b | DATE_DIFF(date, date2, timeUnit) | Returns the whole number of *timeUnit* between *date* and *date2* | 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 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 5606efeb7a..578afc91ae 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -7647,6 +7647,12 @@ public class SqlOperatorTest { "12:34:00", "TIME(0) NOT NULL"); f.checkScalar("floor(timestamp '2015-02-19 12:34:56.78' to second)", "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("floor(timestamp '2015-02-19 12:34:56.78' to millisecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("floor(timestamp '2015-02-19 12:34:56.78' to microsecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("floor(timestamp '2015-02-19 12:34:56.78' to nanosecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); f.checkScalar("floor(timestamp '2015-02-19 12:34:56' to minute)", "2015-02-19 12:34:00", "TIMESTAMP(0) NOT NULL"); f.checkScalar("floor(timestamp '2015-02-19 12:34:56' to year)", @@ -7686,6 +7692,12 @@ public class SqlOperatorTest { "13:00:00", "TIME(0) NOT NULL"); f.checkScalar("ceil(timestamp '2015-02-19 12:34:56.78' to second)", "2015-02-19 12:34:57", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("ceil(timestamp '2015-02-19 12:34:56.78' to millisecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("ceil(timestamp '2015-02-19 12:34:56.78' to microsecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); + f.checkScalar("ceil(timestamp '2015-02-19 12:34:56.78' to nanosecond)", + "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); f.checkScalar("ceil(timestamp '2015-02-19 12:34:56.00' to second)", "2015-02-19 12:34:56", "TIMESTAMP(2) NOT NULL"); f.checkScalar("ceil(timestamp '2015-02-19 12:34:56' to minute)", @@ -8016,6 +8028,53 @@ public class SqlOperatorTest { f.checkNull("timestamp_add(CAST(NULL AS TIMESTAMP), interval 5 minute)"); } + /** Tests BigQuery's {@code DATETIME_ADD(timestamp, interval)} function. + * When Calcite runs in BigQuery mode, {@code DATETIME} is a type alias for + * {@code TIMESTAMP} and this function follows the same behavior as + * {@code TIMESTAMP_ADD(timestamp, interval)}. The tests below use + * {@code TIMESTAMP} values rather than the {@code DATETIME} alias because the + * operator fixture does not currently support type aliases. */ + @Test void testDatetimeAdd() { + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.DATETIME_ADD); + f0.checkFails("^datetime_add(timestamp '2008-12-25 15:30:00', " + + "interval 5 minute)^", + "No match found for function signature " + + "DATETIME_ADD\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false); + + final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY); + if (Bug.CALCITE_5422_FIXED) { + f.checkScalar("datetime_add(timestamp '2008-12-25 15:30:00', " + + "interval 100000000000 microsecond)", + "2008-12-26 19:16:40", + "TIMESTAMP(3) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2008-12-25 15:30:00', " + + "interval 100000000 millisecond)", + "2008-12-26 19:16:40", + "TIMESTAMP(3) NOT NULL"); + } + + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval 2 second)", + "2016-02-24 12:42:27", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval 2 minute)", + "2016-02-24 12:44:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval -2000 hour)", + "2015-12-03 04:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval 1 day)", + "2016-02-25 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval 1 month)", + "2016-03-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkScalar("datetime_add(timestamp '2016-02-24 12:42:25', interval 1 year)", + "2017-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + f.checkNull("datetime_add(CAST(NULL AS TIMESTAMP), interval 5 minute)"); + } + /** Tests {@code TIMESTAMP_DIFF}, BigQuery's variant of the * {@code TIMESTAMPDIFF} function, which differs in the ordering * of the parameters and the ordering of the subtraction between @@ -8093,7 +8152,7 @@ public class SqlOperatorTest { f.checkScalar("timestamp_diff(date '2016-03-15', " + "date '2016-06-14', " + s + ")", - "-2", "INTEGER NOT NULL")); + "-3", "INTEGER NOT NULL")); MONTH_VARIANTS.forEach(s -> f.checkScalar("timestamp_diff(date '2019-09-01', " + "date '2020-03-01', " @@ -8131,6 +8190,122 @@ public class SqlOperatorTest { isNullValue(), "INTEGER")); } + /** Tests BigQuery's {@code DATETIME_DIFF(timestamp, timestamp2, timeUnit)} + * function. When Calcite runs in BigQuery mode, {@code DATETIME} is a type + * alias for {@code TIMESTAMP} and this function follows the same behavior as + * {@code TIMESTAMP_DIFF(timestamp, timestamp2, timeUnit)}. The tests below + * use {@code TIMESTAMP} values rather than the {@code DATETIME} alias because + * the operator fixture does not currently support type aliases. */ + @Test void testDatetimeDiff() { + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.DATETIME_DIFF); + f0.checkFails("^datetime_diff(timestamp '2008-12-25 15:30:00', " + + "timestamp '2008-12-25 16:30:00', " + + "minute)^", + "No match found for function signature " + + "DATETIME_DIFF\\(<TIMESTAMP>, <TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false); + + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.DATETIME_DIFF); + HOUR_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2016-02-24 12:42:25', " + + "timestamp '2016-02-24 15:42:25', " + + s + ")", + "-3", "INTEGER NOT NULL")); + MICROSECOND_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2016-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:20', " + + s + ")", + "5000000", "INTEGER NOT NULL")); + YEAR_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2014-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:25', " + + s + ")", + "-2", "INTEGER NOT NULL")); + WEEK_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2014-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:25', " + + s + ")", + "-104", "INTEGER NOT NULL")); + WEEK_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2014-02-19 12:42:25', " + + "timestamp '2016-02-24 12:42:25', " + + s + ")", + "-105", "INTEGER NOT NULL")); + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2014-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:25', " + + s + ")", + "-24", "INTEGER NOT NULL")); + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2019-09-01 12:42:25', " + + "timestamp '2020-03-01 12:42:25', " + + s + ")", + "-6", "INTEGER NOT NULL")); + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2019-09-01 12:42:25', " + + "timestamp '2016-08-01 12:42:25', " + + s + ")", + "37", "INTEGER NOT NULL")); + QUARTER_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2014-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:25', " + + s + ")", + "-8", "INTEGER NOT NULL")); + f.checkScalar("datetime_diff(timestamp '2014-02-24 12:42:25', " + + "timestamp '2614-02-24 12:42:25', " + + "CENTURY)", + "-6", "INTEGER NOT NULL"); + QUARTER_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(timestamp '2016-02-24 12:42:25', " + + "cast(null as timestamp), " + + s + ")", + isNullValue(), "INTEGER")); + + // datetime_diff with date + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-03-15', " + + "date '2016-06-14', " + + s + ")", + "-3", "INTEGER NOT NULL")); + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2019-09-01', " + + "date '2020-03-01', " + + s + ")", + "-6", "INTEGER NOT NULL")); + MONTH_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2019-09-01', " + + "date '2016-08-01', " + + s + ")", + "37", "INTEGER NOT NULL")); + DAY_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-06-15', " + + "date '2016-06-14', " + + s + ")", + "1", "INTEGER NOT NULL")); + HOUR_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-06-15', " + + "date '2016-06-14', " + + s + ")", + "24", "INTEGER NOT NULL")); + HOUR_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-06-15', " + + "date '2016-06-15', " + + s + ")", + "0", "INTEGER NOT NULL")); + MINUTE_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-06-15', " + + "date '2016-06-14', " + + s + ")", + "1440", "INTEGER NOT NULL")); + DAY_VARIANTS.forEach(s -> + f.checkScalar("datetime_diff(date '2016-06-15', " + + "cast(null as date), " + + s + ")", + isNullValue(), "INTEGER")); + } + @ValueSource(booleans = {true, false}) @ParameterizedTest(name = "CoercionEnabled: {0}") void testTimestampDiff(boolean coercionEnabled) { @@ -8701,7 +8876,6 @@ public class SqlOperatorTest { "VARCHAR(2000) NOT NULL"); } - @Test void testDenseRankFunc() { final SqlOperatorFixture f = fixture(); f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);
