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 13ef58b50655ce63d88f60c736c5fae4701a8300 Author: Tanner Clary <tannercl...@google.com> AuthorDate: Wed Jan 11 17:42:24 2023 +0000 [CALCITE-5450] Allow 'WEEK(weekday)' time frame as argument to functions such as EXTRACT, DATE_TRUNC; allow WEEK and QUARTER in interval literals In OperandTypes, add strategies for matching DATE, TIME, TIMESTAMP frames; DATE and TIMESTAMP frames include ISOYEAR, WEEK(THURSDAY). Enable tests for ISOYEAR, now that the upgrade to Avatica 1.23 has brought in the fix to [CALCITE-5369], ISOYEAR in FLOOR and CEIL functions. Co-authored-by: Tanner Clary <tannercl...@google.com> Co-authored-by: Oliver Lee <oliver...@google.com> --- babel/src/test/resources/sql/big-query.iq | 30 +++-- core/src/main/codegen/default_config.fmpp | 2 + core/src/main/codegen/templates/Parser.jj | 123 ++++++++++++------ .../calcite/adapter/enumerable/RexImpTable.java | 1 + .../org/apache/calcite/rel/type/TimeFrames.java | 24 +++- .../calcite/sql/fun/SqlLibraryOperators.java | 35 +---- .../sql/type/IntervalOperandTypeChecker.java | 11 +- .../org/apache/calcite/sql/type/OperandTypes.java | 68 +++++++++- .../org/apache/calcite/test/SqlValidatorTest.java | 30 +++++ .../org/apache/calcite/test/TimeFrameTest.java | 10 ++ site/_docs/reference.md | 13 +- .../apache/calcite/sql/parser/SqlParserTest.java | 142 ++++++++++++++++++++- .../org/apache/calcite/test/SqlOperatorTest.java | 52 ++++++++ 13 files changed, 450 insertions(+), 91 deletions(-) diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index 53a337bfd1..4b1c4bc501 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -1827,22 +1827,34 @@ SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR) AS neg # Returns DATE WITH Dates AS ( SELECT DATE_TRUNC(DATE '2008-12-25', YEAR) as d , "year" as frame UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', ISOYEAR), "isoyear" UNION ALL SELECT DATE_TRUNC(DATE '2008-12-25', QUARTER), "quarter" UNION ALL SELECT DATE_TRUNC(DATE '2008-12-25', MONTH), "month" UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', WEEK), "week" UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', WEEK(SUNDAY)), "week(sunday)" UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', WEEK(MONDAY)), "week(monday)" UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', WEEK(TUESDAY)), "week(tuesday)" UNION ALL + SELECT DATE_TRUNC(DATE '2008-12-25', ISOWEEK), "isoweek" UNION ALL SELECT DATE_TRUNC(DATE '2008-12-25', DAY), "day" ) SELECT * FROM Dates; -+------------+---------+ -| d | frame | -+------------+---------+ -| 2008-01-01 | year | -| 2008-10-01 | quarter | -| 2008-12-01 | month | -| 2008-12-25 | day | -+------------+---------+ -(4 rows) ++------------+---------------+ +| d | frame | ++------------+---------------+ +| 2008-01-01 | year | +| 2007-12-31 | isoyear | +| 2008-10-01 | quarter | +| 2008-12-01 | month | +| 2008-12-21 | week | +| 2008-12-21 | week(sunday) | +| 2008-12-22 | week(monday) | +| 2008-12-23 | week(tuesday) | +| 2008-12-22 | isoweek | +| 2008-12-25 | day | ++------------+---------------+ +(10 rows) !ok diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp index 31002559f4..c1847a2541 100644 --- a/core/src/main/codegen/default_config.fmpp +++ b/core/src/main/codegen/default_config.fmpp @@ -214,6 +214,7 @@ parser: { "PRIVILEGES" "PUBLIC" "QUARTER" + "QUARTERS" "READ" "RELATIVE" "REPEATABLE" @@ -350,6 +351,7 @@ parser: { "VERSION" "VIEW" "WEEK" + "WEEKS" "WORK" "WRAPPER" "WRITE" diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 25935f7d4e..0514b815e2 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -4853,6 +4853,15 @@ TimeUnit Year() : <YEARS> { return warn(TimeUnit.YEAR); } } +TimeUnit Quarter() : +{ +} +{ + <QUARTER> { return TimeUnit.QUARTER; } +| + <QUARTERS> { return warn(TimeUnit.QUARTER); } +} + TimeUnit Month() : { } @@ -4862,6 +4871,15 @@ TimeUnit Month() : <MONTHS> { return warn(TimeUnit.MONTH); } } +TimeUnit Week() : +{ +} +{ + <WEEK> { return TimeUnit.WEEK; } +| + <WEEKS> { return warn(TimeUnit.WEEK); } +} + TimeUnit Day() : { } @@ -4913,9 +4931,15 @@ SqlIntervalQualifier IntervalQualifier() : LOOKAHEAD(2) <TO> end = Month() | { end = null; } ) + | + start = Quarter() { s = span(); } startPrec = PrecisionOpt() + { end = null; } | start = Month() { s = span(); } startPrec = PrecisionOpt() { end = null; } + | + start = Week() { s = span(); } startPrec = PrecisionOpt() + { end = null; } | start = Day() { s = span(); } startPrec = PrecisionOpt() ( @@ -4977,7 +5001,9 @@ SqlIntervalQualifier IntervalQualifierStart() : ( ( start = Year() + | start = Quarter() | start = Month() + | start = Week() | start = Day() | start = Hour() | start = Minute() @@ -4997,59 +5023,75 @@ SqlIntervalQualifier IntervalQualifierStart() : } } -/** - * Parses time unit for CEIL and FLOOR functions. +/** Parses a built-in time unit (e.g. "YEAR") + * or user-defined time frame (e.g. "MINUTE15") + * and in each case returns a {@link SqlIntervalQualifier}. * - * <p>Includes NANOSECOND, MILLISECOND, which were previously only allowed in - * the EXTRACT function. + * <p>The units are used in several functions, incuding CEIL, FLOOR, EXTRACT. + * Includes NANOSECOND, MILLISECOND, which were previously allowed in EXTRACT + * but not CEIL, FLOOR. + * + * <p>Includes {@code WEEK} and {@code WEEK(SUNDAY)} through + {@code WEEK(SATURDAY)}. * * <p>Does not include SQL_TSI_DAY, SQL_TSI_FRAC_SECOND etc. These will be * parsed as identifiers and can be resolved in the validator if they are * registered as abbreviations in your time frame set. */ -TimeUnit TimeUnit() : -{ -} -{ - <NANOSECOND> { return TimeUnit.NANOSECOND; } -| <MICROSECOND> { return TimeUnit.MICROSECOND; } -| <MILLISECOND> { return TimeUnit.MILLISECOND; } -| <SECOND> { return TimeUnit.SECOND; } -| <MINUTE> { return TimeUnit.MINUTE; } -| <HOUR> { return TimeUnit.HOUR; } -| <DAY> { return TimeUnit.DAY; } -| <DOW> { return TimeUnit.DOW; } -| <DOY> { return TimeUnit.DOY; } -| <ISODOW> { return TimeUnit.ISODOW; } -| <ISOYEAR> { return TimeUnit.ISOYEAR; } -| <WEEK> { return TimeUnit.WEEK; } -| <MONTH> { return TimeUnit.MONTH; } -| <QUARTER> { return TimeUnit.QUARTER; } -| <YEAR> { return TimeUnit.YEAR; } -| <EPOCH> { return TimeUnit.EPOCH; } -| <DECADE> { return TimeUnit.DECADE; } -| <CENTURY> { return TimeUnit.CENTURY; } -| <MILLENNIUM> { return TimeUnit.MILLENNIUM; } -} - -/** Parses a built-in time unit (e.g. "YEAR") - * or user-defined time frame (e.g. "MINUTE15") - * and in each case returns a {@link SqlIntervalQualifier}. */ SqlIntervalQualifier TimeUnitOrName() : { + final Span span; + final String w; final TimeUnit unit; final SqlIdentifier unitName; } { - LOOKAHEAD(1) - unit = TimeUnit() { - return new SqlIntervalQualifier(unit, null, getPos()); - } + LOOKAHEAD(2) + <NANOSECOND> { return new SqlIntervalQualifier(TimeUnit.NANOSECOND, null, getPos()); } +| <MICROSECOND> { return new SqlIntervalQualifier(TimeUnit.MICROSECOND, null, getPos()); } +| <MILLISECOND> { return new SqlIntervalQualifier(TimeUnit.MILLISECOND, null, getPos()); } +| <SECOND> { return new SqlIntervalQualifier(TimeUnit.SECOND, null, getPos()); } +| <MINUTE> { return new SqlIntervalQualifier(TimeUnit.MINUTE, null, getPos()); } +| <HOUR> { return new SqlIntervalQualifier(TimeUnit.HOUR, null, getPos()); } +| <DAY> { return new SqlIntervalQualifier(TimeUnit.DAY, null, getPos()); } +| <DOW> { return new SqlIntervalQualifier(TimeUnit.DOW, null, getPos()); } +| <DOY> { return new SqlIntervalQualifier(TimeUnit.DOY, null, getPos()); } +| <ISODOW> { return new SqlIntervalQualifier(TimeUnit.ISODOW, null, getPos()); } +| <ISOYEAR> { return new SqlIntervalQualifier(TimeUnit.ISOYEAR, null, getPos()); } +| <WEEK> { span = span(); } + ( + LOOKAHEAD(2) + <LPAREN> w = weekdayName() <RPAREN> { + return new SqlIntervalQualifier(w, span.end(this)); + } + | + { return new SqlIntervalQualifier(TimeUnit.WEEK, null, getPos()); } + ) +| <MONTH> { return new SqlIntervalQualifier(TimeUnit.MONTH, null, getPos()); } +| <QUARTER> { return new SqlIntervalQualifier(TimeUnit.QUARTER, null, getPos()); } +| <YEAR> { return new SqlIntervalQualifier(TimeUnit.YEAR, null, getPos()); } +| <EPOCH> { return new SqlIntervalQualifier(TimeUnit.EPOCH, null, getPos()); } +| <DECADE> { return new SqlIntervalQualifier(TimeUnit.DECADE, null, getPos()); } +| <CENTURY> { return new SqlIntervalQualifier(TimeUnit.CENTURY, null, getPos()); } +| <MILLENNIUM> { return new SqlIntervalQualifier(TimeUnit.MILLENNIUM, null, getPos()); } | unitName = SimpleIdentifier() { return new SqlIntervalQualifier(unitName.getSimple(), unitName.getParserPosition()); } } +String weekdayName() : +{ +} +{ + <SUNDAY> { return "WEEK_SUNDAY"; } +| <MONDAY> { return "WEEK_MONDAY"; } +| <TUESDAY> { return "WEEK_TUESDAY"; } +| <WEDNESDAY> { return "WEEK_WEDNESDAY"; } +| <THURSDAY> { return "WEEK_THURSDAY"; } +| <FRIDAY> { return "WEEK_FRIDAY"; } +| <SATURDAY> { return "WEEK_SATURDAY"; } +} + /** * Parses a dynamic parameter marker. */ @@ -7686,6 +7728,7 @@ SqlPostfixOperator PostfixRowOperator() : | < FRAC_SECOND: "FRAC_SECOND" > | < FRAME_ROW: "FRAME_ROW" > | < FREE: "FREE" > +| < FRIDAY: "FRIDAY" > | < FROM: "FROM" > { beforeTableName(); } | < FULL: "FULL" > | < FUNCTION: "FUNCTION" > @@ -7804,6 +7847,7 @@ SqlPostfixOperator PostfixRowOperator() : | < MOD: "MOD" > | < MODIFIES: "MODIFIES" > | < MODULE: "MODULE" > +| < MONDAY: "MONDAY" > | < MONTH: "MONTH" > | < MONTHS: "MONTHS" > | < MORE_: "MORE" > @@ -7901,6 +7945,7 @@ SqlPostfixOperator PostfixRowOperator() : | < PROCEDURE: "PROCEDURE" > | < PUBLIC: "PUBLIC" > | < QUARTER: "QUARTER" > +| < QUARTERS: "QUARTERS" > | < RANGE: "RANGE" > | < RANK: "RANK" > | < READ: "READ" > @@ -7950,6 +7995,7 @@ SqlPostfixOperator PostfixRowOperator() : | < ROW_NUMBER: "ROW_NUMBER" > | < ROWS: "ROWS" > | < RUNNING: "RUNNING" > +| < SATURDAY: "SATURDAY" > | < SAVEPOINT: "SAVEPOINT" > | < SCALAR: "SCALAR" > | < SCALE: "SCALE" > @@ -8063,6 +8109,7 @@ SqlPostfixOperator PostfixRowOperator() : | < SUBSTRING_REGEX: "SUBSTRING_REGEX" > | < SUCCEEDS: "SUCCEEDS" > | < SUM: "SUM" > +| < SUNDAY: "SUNDAY" > | < SYMMETRIC: "SYMMETRIC" > | < SYSTEM: "SYSTEM" > | < SYSTEM_TIME: "SYSTEM_TIME" > @@ -8072,6 +8119,7 @@ SqlPostfixOperator PostfixRowOperator() : | < TABLESAMPLE: "TABLESAMPLE" > | < TEMPORARY: "TEMPORARY" > | < THEN: "THEN" > +| < THURSDAY: "THURSDAY" > | < TIES: "TIES" > | < TIME: "TIME" > | < TIME_DIFF: "TIME_DIFF" > @@ -8105,6 +8153,7 @@ SqlPostfixOperator PostfixRowOperator() : | < TRIM_ARRAY: "TRIM_ARRAY" > | < TRUE: "TRUE" > | < TRUNCATE: "TRUNCATE" > +| < TUESDAY: "TUESDAY" > | < TUMBLE: "TUMBLE" > | < TYPE: "TYPE" > | < UESCAPE: "UESCAPE" > @@ -8142,7 +8191,9 @@ SqlPostfixOperator PostfixRowOperator() : | < VERSION: "VERSION" > | < VERSIONING: "VERSIONING" > | < VIEW: "VIEW" > +| < WEDNESDAY: "WEDNESDAY" > | < WEEK: "WEEK" > +| < WEEKS: "WEEKS" > | < WHEN: "WHEN" > | < WHENEVER: "WHENEVER" > | < WHERE: "WHERE" > 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 c4845a7d29..35d563e23e 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 @@ -2181,6 +2181,7 @@ public class RexImpTable { "timeUnitRange"); switch (timeUnitRange) { case YEAR: + case ISOYEAR: case QUARTER: case MONTH: case WEEK: diff --git a/core/src/main/java/org/apache/calcite/rel/type/TimeFrames.java b/core/src/main/java/org/apache/calcite/rel/type/TimeFrames.java index 8eb4bd13a0..f69e66f3c6 100644 --- a/core/src/main/java/org/apache/calcite/rel/type/TimeFrames.java +++ b/core/src/main/java/org/apache/calcite/rel/type/TimeFrames.java @@ -19,12 +19,14 @@ package org.apache.calcite.rel.type; import org.apache.calcite.avatica.util.DateTimeUtils; import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.avatica.util.TimeUnitRange; +import org.apache.calcite.linq4j.Ord; import org.apache.calcite.util.MonotonicSupplier; import org.apache.calcite.util.Pair; import org.apache.calcite.util.TimestampString; import org.apache.commons.math3.fraction.BigFraction; +import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; import com.google.common.collect.ImmutableMultimap; import com.google.common.collect.Iterables; @@ -36,6 +38,7 @@ import java.util.Calendar; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashMap; +import java.util.List; import java.util.Map; import java.util.Set; import java.util.function.Supplier; @@ -51,8 +54,19 @@ public class TimeFrames { private TimeFrames() { } + /** The names of the frames that are WEEK starting on each week day. + * Entry 0 is "WEEK_SUNDAY" and entry 6 is "WEEK_SATURDAY". */ + public static final List<String> WEEK_FRAME_NAMES = + ImmutableList.of("WEEK_SUNDAY", + "WEEK_MONDAY", + "WEEK_TUESDAY", + "WEEK_WEDNESDAY", + "WEEK_THURSDAY", + "WEEK_FRIDAY", + "WEEK_SATURDAY"); + /** The core time frame set. Includes the time frames for all Avatica time - * units plus ISOWEEK: + * units plus ISOWEEK and week offset for each week day: * * <ul> * <li>SECOND, and multiples MINUTE, HOUR, DAY, WEEK (starts on a Sunday), @@ -60,6 +74,8 @@ public class TimeFrames { * quotients DOY, DOW; * <li>MONTH, and multiples QUARTER, YEAR, DECADE, CENTURY, MILLENNIUM; * <li>ISOYEAR, and sub-unit ISOWEEK (starts on a Monday), quotient ISODOW; + * <li>WEEK(<i>weekday</i>) with <i>weekday</i> being one of + * SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY. * </ul> * * <p>Does not include EPOCH. @@ -94,12 +110,18 @@ public class TimeFrames { b.addSub("ISOWEEK", false, 7, TimeUnit.DAY.name(), new TimestampString(1970, 1, 5, 0, 0, 0)); // a monday + // Add "WEEK(SUNDAY)" through "WEEK(SATURDAY)" + Ord.forEach(WEEK_FRAME_NAMES, (frameName, i) -> + b.addSub(frameName, false, 7, + "DAY", new TimestampString(1970, 1, 4 + i, 0, 0, 0))); + b.addQuotient(TimeUnit.DOY, TimeUnit.DAY, TimeUnit.YEAR); b.addQuotient(TimeUnit.DOW, TimeUnit.DAY, TimeUnit.WEEK); b.addQuotient(TimeUnit.ISODOW.name(), TimeUnit.DAY.name(), "ISOWEEK"); b.addRollup(TimeUnit.DAY, TimeUnit.MONTH); b.addRollup("ISOWEEK", TimeUnit.ISOYEAR.name()); + return b; } 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 8e82dd9e80..6d36db0b3e 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 @@ -16,7 +16,6 @@ */ package org.apache.calcite.sql.fun; -import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.SqlAggFunction; @@ -43,13 +42,10 @@ import org.apache.calcite.sql.type.SqlTypeTransforms; import org.apache.calcite.util.Litmus; import org.apache.calcite.util.Optionality; -import com.google.common.collect.ImmutableSet; - import org.checkerframework.checker.nullness.qual.Nullable; import java.util.ArrayList; import java.util.List; -import java.util.Set; import static org.apache.calcite.sql.fun.SqlLibrary.BIG_QUERY; import static org.apache.calcite.sql.fun.SqlLibrary.CALCITE; @@ -672,31 +668,6 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE); - 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.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(); - /** The "TIMESTAMP_ADD(timestamp, interval)" function (BigQuery), the * two-argument variant of the built-in * {@link SqlStdOperatorTable#TIMESTAMP_ADD TIMESTAMPADD} function, which has @@ -745,7 +716,7 @@ public abstract class SqlLibraryOperators { SqlBasicFunction.create("DATE_TRUNC", ReturnTypes.DATE_NULLABLE, OperandTypes.sequence("'DATE_TRUNC(<DATE>, <DATETIME_INTERVAL>)'", - OperandTypes.DATE, OperandTypes.interval(DATE_UNITS)), + OperandTypes.DATE, OperandTypes.dateInterval()), SqlFunctionCategory.TIMEDATE); /** The "TIME_SUB(time, interval)" function (BigQuery); @@ -767,7 +738,7 @@ public abstract class SqlLibraryOperators { SqlBasicFunction.create("TIME_TRUNC", ReturnTypes.TIME_NULLABLE, OperandTypes.sequence("'TIME_TRUNC(<TIME>, <DATETIME_INTERVAL>)'", - OperandTypes.TIME, OperandTypes.interval(TIME_UNITS)), + OperandTypes.TIME, OperandTypes.timeInterval()), SqlFunctionCategory.TIMEDATE); /** The "TIMESTAMP_SUB(timestamp, interval)" function (BigQuery); @@ -790,7 +761,7 @@ public abstract class SqlLibraryOperators { ReturnTypes.TIMESTAMP_NULLABLE, OperandTypes.sequence( "'TIMESTAMP_TRUNC(<TIMESTAMP>, <DATETIME_INTERVAL>)'", - OperandTypes.TIMESTAMP, OperandTypes.interval(TIMESTAMP_UNITS)), + OperandTypes.TIMESTAMP, OperandTypes.timestampInterval()), SqlFunctionCategory.TIMEDATE); /** The "TIMESTAMP_SECONDS(bigint)" function; returns a TIMESTAMP value diff --git a/core/src/main/java/org/apache/calcite/sql/type/IntervalOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/IntervalOperandTypeChecker.java index 9d99b5b574..f86299ebbf 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/IntervalOperandTypeChecker.java +++ b/core/src/main/java/org/apache/calcite/sql/type/IntervalOperandTypeChecker.java @@ -16,7 +16,6 @@ */ package org.apache.calcite.sql.type; -import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlNode; @@ -24,17 +23,17 @@ import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.util.Static; import org.apache.calcite.util.Util; -import com.google.common.collect.ImmutableSet; +import java.util.function.Predicate; /** * Parameter type-checking strategy whether the operand must be an interval. */ public class IntervalOperandTypeChecker implements SqlSingleOperandTypeChecker { - private final ImmutableSet<TimeUnitRange> unitSet; + private final Predicate<SqlIntervalQualifier> predicate; - IntervalOperandTypeChecker(ImmutableSet<TimeUnitRange> unitSet) { - this.unitSet = unitSet; + IntervalOperandTypeChecker(Predicate<SqlIntervalQualifier> predicate) { + this.predicate = predicate; } @Override public boolean checkSingleOperandType(SqlCallBinding callBinding, @@ -42,7 +41,7 @@ public class IntervalOperandTypeChecker implements SqlSingleOperandTypeChecker { final SqlNode operand = callBinding.operand(iFormalOperand); if (operand instanceof SqlIntervalQualifier) { final SqlIntervalQualifier interval = (SqlIntervalQualifier) operand; - if (unitSet.contains(interval.timeUnitRange)) { + if (predicate.test(interval)) { return true; } if (throwOnFailure) { 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 a2bc22178b..82f813c10d 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,6 +20,7 @@ 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.SqlLiteral; import org.apache.calcite.sql.SqlNode; @@ -39,6 +40,7 @@ import java.math.BigDecimal; import java.util.ArrayList; import java.util.Arrays; import java.util.List; +import java.util.Set; import java.util.function.BiFunction; import java.util.function.Function; import java.util.function.IntFunction; @@ -66,6 +68,39 @@ import static org.apache.calcite.util.Static.RESOURCE; * @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() { } @@ -102,7 +137,37 @@ public abstract class OperandTypes { */ public static SqlSingleOperandTypeChecker interval( Iterable<TimeUnitRange> ranges) { - return new IntervalOperandTypeChecker(ImmutableSet.copyOf(ranges)); + final Set<TimeUnitRange> set = ImmutableSet.copyOf(ranges); + return new IntervalOperandTypeChecker(intervalQualifier -> + set.contains(intervalQualifier.timeUnitRange)); + } + + /** + * Creates a checker for DATE intervals (YEAR, WEEK, ISOWEEK, + * WEEK_WEDNESDAY, etc.) + */ + public static SqlSingleOperandTypeChecker dateInterval() { + return new IntervalOperandTypeChecker(intervalQualifier -> + DATE_UNITS.contains(intervalQualifier.timeUnitRange) + || WEEK_FRAMES.contains(intervalQualifier.timeFrameName)); + } + + /** + * Creates a checker for TIME intervals (HOUR, SECOND, etc.) + */ + public static SqlSingleOperandTypeChecker timeInterval() { + return new IntervalOperandTypeChecker(intervalQualifier -> + TIME_UNITS.contains(intervalQualifier.timeUnitRange)); + } + + /** + * Creates a checker for TIMESTAMP intervals (YEAR, WEEK, ISOWEEK, + * WEEK_WEDNESDAY, HOUR, SECOND, etc.) + */ + public static SqlSingleOperandTypeChecker timestampInterval() { + return new IntervalOperandTypeChecker(intervalQualifier -> + TIMESTAMP_UNITS.contains(intervalQualifier.timeUnitRange) + || WEEK_FRAMES.contains(intervalQualifier.timeFrameName)); } /** @@ -797,7 +862,6 @@ public abstract class OperandTypes { } }; - /** * Checker for record just has one field. */ diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 4dc72e96ae..508f896b56 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -4408,6 +4408,36 @@ public class SqlValidatorTest extends SqlValidatorTestCase { invalidCodes.forEach(invalidConsumer); } + /** Checks parsing of built-in functions that accept time unit + * Checks WEEK(WEEKDAY) + * <p>Override if your parser supports more such functions. */ + @Test void checkWeekdayCustomTimeFrames() { + SqlValidatorFixture f = fixture() + .withOperatorTable(operatorTableFor(SqlLibrary.BIG_QUERY)); + + // Check that each valid code passes each query that it should. + final String ds = "DATE '2022-12-25'"; + Consumer<String> validConsumer = weekday -> { + f.withSql("select date_trunc(" + ds + ", " + weekday + ")").ok(); + }; + validConsumer.accept("WEEK"); + validConsumer.accept("WEEK(SUNDAY)"); + validConsumer.accept("WEEK(MONDAY)"); + validConsumer.accept("WEEK(TUESDAY)"); + validConsumer.accept("WEEK(WEDNESDAY)"); + validConsumer.accept("WEEK(THURSDAY)"); + validConsumer.accept("WEEK(FRIDAY)"); + validConsumer.accept("WEEK(SUNDAY)"); + + // Check that each invalid code fails each query that it should. + Consumer<String> invalidConsumer = weekday -> { + String errorMessage = "'" + weekday + "' is not a valid time frame"; + f.withSql("select date_trunc(" + ds + ", ^" + weekday + "^)") + .fails(errorMessage); + }; + invalidConsumer.accept("A"); + } + public void checkWinFuncExpWithWinClause( String sql, String expectedMsgPattern) { diff --git a/core/src/test/java/org/apache/calcite/test/TimeFrameTest.java b/core/src/test/java/org/apache/calcite/test/TimeFrameTest.java index 046b94ffd6..e3f6f959fa 100644 --- a/core/src/test/java/org/apache/calcite/test/TimeFrameTest.java +++ b/core/src/test/java/org/apache/calcite/test/TimeFrameTest.java @@ -229,6 +229,9 @@ public class TimeFrameTest { f.checkDateFloor("1970-08-03", f.isoWeek, is("1970-08-03")); // monday f.checkDateFloor("1970-08-04", f.isoWeek, is("1970-08-03")); // tuesday + f.checkDateFloor("1970-08-04", "WEEK_MONDAY", is("1970-08-03")); // tuesday + f.checkDateFloor("1970-08-04", "WEEK_TUESDAY", is("1970-08-04")); // tuesday + f.checkTimestampFloor("1970-01-01 01:23:45", HOUR, 0, is("1970-01-01 01:00:00")); f.checkTimestampFloor("1970-01-01 01:23:45", MINUTE, @@ -577,6 +580,13 @@ public class TimeFrameTest { unixDateToString(outDate), matcher); } + void checkDateFloor(String in, String timeFrameName, Matcher<String> matcher) { + int inDate = dateStringToUnixDate(in); + int outDate = timeFrameSet.floorDate(inDate, timeFrameSet.get(timeFrameName)); + assertThat("floor(" + in + " to " + timeFrameName + ")", + unixDateToString(outDate), matcher); + } + void checkTimestampFloor(String in, TimeUnit unit, int precision, Matcher<String> matcher) { long inTs = timestampStringToUnixDate(in); diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 5d8534a9fc..65c4a0eaa7 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -627,6 +627,7 @@ FOUND, FRAC_SECOND, **FRAME_ROW**, **FREE**, +**FRIDAY**, **FROM**, **FULL**, **FUNCTION**, @@ -746,6 +747,7 @@ MINVALUE, **MOD**, **MODIFIES**, **MODULE**, +**MONDAY**, **MONTH**, MONTHS, MORE, @@ -843,6 +845,7 @@ PRIVILEGES, **PROCEDURE**, PUBLIC, QUARTER, +QUARTERS, **RANGE**, **RANK**, READ, @@ -892,6 +895,7 @@ ROUTINE_SCHEMA, ROW_COUNT, **ROW_NUMBER**, **RUNNING**, +**SATURDAY**, **SAVEPOINT**, SCALAR, SCALE, @@ -1004,6 +1008,7 @@ SUBSTITUTE, **SUBSTRING_REGEX**, **SUCCEEDS**, **SUM**, +**SUNDAY**, **SYMMETRIC**, **SYSTEM**, **SYSTEM_TIME**, @@ -1013,6 +1018,7 @@ SUBSTITUTE, TABLE_NAME, TEMPORARY, **THEN**, +**THURSDAY**, TIES, **TIME**, **TIMESTAMP**, @@ -1046,6 +1052,7 @@ TRIGGER_SCHEMA, **TRIM_ARRAY**, **TRUE**, **TRUNCATE**, +**TUESDAY**, TUMBLE, TYPE, **UESCAPE**, @@ -1083,7 +1090,9 @@ UTF8, VERSION, **VERSIONING**, VIEW, +**WEDNESDAY**, WEEK, +WEEKS, **WHEN**, **WHENEVER**, **WHERE**, @@ -1166,9 +1175,9 @@ Note: * GEOMETRY is allowed only in certain [conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#allowGeometry--). * Interval literals may only use time units - YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. In certain + YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE and SECOND. In certain [conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#allowPluralTimeUnits--), - we also allow their plurals, YEARS, MONTHS, DAYS, HOURS, MINUTES and SECONDS. + we also allow their plurals, YEARS, QUARTERS, MONTHS, WEEKS, DAYS, HOURS, MINUTES and SECONDS. ### Non-scalar types diff --git a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java index 223a2b0bd7..735d3c296a 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -269,6 +269,7 @@ public class SqlParserTest { "FOUND", "92", "99", "FRAME_ROW", "2014", "c", "FREE", "99", "2003", "2011", "2014", "c", + "FRIDAY", "c", "FROM", "92", "99", "2003", "2011", "2014", "c", "FULL", "92", "99", "2003", "2011", "2014", "c", "FUNCTION", "92", "99", "2003", "2011", "2014", "c", @@ -360,6 +361,7 @@ public class SqlParserTest { "MOD", "2011", "2014", "c", "MODIFIES", "99", "2003", "2011", "2014", "c", "MODULE", "92", "99", "2003", "2011", "2014", "c", + "MONDAY", "c", "MONTH", "92", "99", "2003", "2011", "2014", "c", "MULTISET", "2003", "2011", "2014", "c", "NAMES", "92", "99", @@ -464,6 +466,7 @@ public class SqlParserTest { "ROWS", "92", "99", "2003", "2011", "2014", "c", "ROW_NUMBER", "2011", "2014", "c", "RUNNING", "2014", "c", + "SATURDAY", "c", "SAVEPOINT", "99", "2003", "2011", "2014", "c", "SCHEMA", "92", "99", "SCOPE", "99", "2003", "2011", "2014", "c", @@ -508,6 +511,7 @@ public class SqlParserTest { "SUBSTRING_REGEX", "2011", "2014", "c", "SUCCEEDS", "2014", "c", "SUM", "92", "2011", "2014", "c", + "SUNDAY", "c", "SYMMETRIC", "99", "2003", "2011", "2014", "c", "SYSTEM", "99", "2003", "2011", "2014", "c", "SYSTEM_TIME", "2014", "c", @@ -516,6 +520,7 @@ public class SqlParserTest { "TABLESAMPLE", "2003", "2011", "2014", "c", "TEMPORARY", "92", "99", "THEN", "92", "99", "2003", "2011", "2014", "c", + "THURSDAY", "c", "TIME", "92", "99", "2003", "2011", "2014", "c", "TIMESTAMP", "92", "99", "2003", "2011", "2014", "c", "TIMEZONE_HOUR", "92", "99", "2003", "2011", "2014", "c", @@ -533,6 +538,7 @@ public class SqlParserTest { "TRIM_ARRAY", "2011", "2014", "c", "TRUE", "92", "99", "2003", "2011", "2014", "c", "TRUNCATE", "2011", "2014", "c", + "TUESDAY", "c", "UESCAPE", "2011", "2014", "c", "UNDER", "99", "UNDO", "92", "99", "2003", @@ -559,6 +565,7 @@ public class SqlParserTest { "VERSIONING", "2011", "2014", "c", "VERSIONS", "2011", "VIEW", "92", "99", + "WEDNESDAY", "c", "WHEN", "92", "99", "2003", "2011", "2014", "c", "WHENEVER", "92", "99", "2003", "2011", "2014", "c", "WHERE", "92", "99", "2003", "2011", "2014", "c", @@ -5865,6 +5872,125 @@ public class SqlParserTest { .ok("(MAP[])"); } + /** + * Runs tests for INTERVAL... WEEK that should pass both parser and + * validator. A substantially identical set of tests exists in + * SqlValidatorTest, and any changes here should be synchronized there. + * Similarly, any changes to tests here should be echoed appropriately to + * each of the other 12 subTestIntervalXXXPositive() tests. + */ + public void subTestIntervalWeekPositive() { + // default precision + expr("interval '1' week") + .ok("INTERVAL '1' WEEK"); + expr("interval '99' week") + .ok("INTERVAL '99' WEEK"); + + // explicit precision equal to default + expr("interval '1' week(2)") + .ok("INTERVAL '1' WEEK(2)"); + expr("interval '99' week(2)") + .ok("INTERVAL '99' WEEK(2)"); + + // max precision + expr("interval '2147483647' week(10)") + .ok("INTERVAL '2147483647' WEEK(10)"); + + // min precision + expr("interval '0' week(1)") + .ok("INTERVAL '0' WEEK(1)"); + + // alternate precision + expr("interval '1234' week(4)") + .ok("INTERVAL '1234' WEEK(4)"); + + // sign + expr("interval '+1' week") + .ok("INTERVAL '+1' WEEK"); + expr("interval '-1' week") + .ok("INTERVAL '-1' WEEK"); + expr("interval +'1' week") + .ok("INTERVAL '1' WEEK"); + expr("interval +'+1' week") + .ok("INTERVAL '+1' WEEK"); + expr("interval +'-1' week") + .ok("INTERVAL '-1' WEEK"); + expr("interval -'1' week") + .ok("INTERVAL -'1' WEEK"); + expr("interval -'+1' week") + .ok("INTERVAL -'+1' WEEK"); + expr("interval -'-1' week") + .ok("INTERVAL -'-1' WEEK"); + } + + /** + * Runs tests for INTERVAL... QUARTER that should pass both parser and + * validator. A substantially identical set of tests exists in + * SqlValidatorTest, and any changes here should be synchronized there. + * Similarly, any changes to tests here should be echoed appropriately to + * each of the other 12 subTestIntervalXXXPositive() tests. + */ + public void subTestIntervalQuarterPositive() { + // default precision + expr("interval '1' quarter") + .ok("INTERVAL '1' QUARTER"); + expr("interval '99' quarter") + .ok("INTERVAL '99' QUARTER"); + + // explicit precision equal to default + expr("interval '1' quarter(2)") + .ok("INTERVAL '1' QUARTER(2)"); + expr("interval '99' quarter(2)") + .ok("INTERVAL '99' QUARTER(2)"); + + // max precision + expr("interval '2147483647' quarter(10)") + .ok("INTERVAL '2147483647' QUARTER(10)"); + + // min precision + expr("interval '0' quarter(1)") + .ok("INTERVAL '0' QUARTER(1)"); + + // alternate precision + expr("interval '1234' quarter(4)") + .ok("INTERVAL '1234' QUARTER(4)"); + + // sign + expr("interval '+1' quarter") + .ok("INTERVAL '+1' QUARTER"); + expr("interval '-1' quarter") + .ok("INTERVAL '-1' QUARTER"); + expr("interval +'1' quarter") + .ok("INTERVAL '1' QUARTER"); + expr("interval +'+1' quarter") + .ok("INTERVAL '+1' QUARTER"); + expr("interval +'-1' quarter") + .ok("INTERVAL '-1' QUARTER"); + expr("interval -'1' quarter") + .ok("INTERVAL -'1' QUARTER"); + expr("interval -'+1' quarter") + .ok("INTERVAL -'+1' QUARTER"); + expr("interval -'-1' quarter") + .ok("INTERVAL -'-1' QUARTER"); + } + + public void subTestIntervalPlural() { + expr("interval '+2' seconds") + .ok("INTERVAL '+2' SECOND"); + expr("interval '+2' hours") + .ok("INTERVAL '+2' HOUR"); + expr("interval '+2' days") + .ok("INTERVAL '+2' DAY"); + expr("interval '+2' weeks") + .ok("INTERVAL '+2' WEEK"); + expr("interval '+2' quarters") + .ok("INTERVAL '+2' QUARTER"); + expr("interval '+2' months") + .ok("INTERVAL '+2' MONTH"); + expr("interval '+2' years") + .ok("INTERVAL '+2' YEAR"); + } + /** * Runs tests for INTERVAL... YEAR that should pass both parser and * validator. A substantially identical set of tests exists in @@ -7553,6 +7679,9 @@ public class SqlParserTest { subTestIntervalMinutePositive(); subTestIntervalMinuteToSecondPositive(); subTestIntervalSecondPositive(); + subTestIntervalWeekPositive(); + subTestIntervalQuarterPositive(); + subTestIntervalPlural(); subTestIntervalYearFailsValidation(); subTestIntervalYearToMonthFailsValidation(); @@ -7582,8 +7711,12 @@ public class SqlParserTest { + " \"MINUTES\" \\.\\.\\.\n" + " \"MONTH\" \\.\\.\\.\n" + " \"MONTHS\" \\.\\.\\.\n" + + " \"QUARTER\" \\.\\.\\.\n" + + " \"QUARTERS\" \\.\\.\\.\n" + " \"SECOND\" \\.\\.\\.\n" + " \"SECONDS\" \\.\\.\\.\n" + + " \"WEEK\" \\.\\.\\.\n" + + " \"WEEKS\" \\.\\.\\.\n" + " \"YEAR\" \\.\\.\\.\n" + " \"YEARS\" \\.\\.\\.\n" + " "); @@ -7948,8 +8081,6 @@ public class SqlParserTest { .fails(ANY); expr("INTERVAL '10' ^DECADE^") .fails(ANY); - expr("INTERVAL '4' ^QUARTER^") - .fails(ANY); } /** Tests that plural time units are allowed when not in strict mode. */ @@ -8094,7 +8225,7 @@ public class SqlParserTest { .fails("(?s)Encountered \"to\".*"); } - /** Tests that EXTRACT, FLOOR, CEIL functions accept abbreviations for + /** Tests that EXTRACT, FLOOR, CEIL, DATE_TRUNC functions accept abbreviations for * time units (such as "Y" for "YEAR") when configured via * {@link Config#timeUnitCodes()}. */ @Test protected void testTimeUnitCodes() { @@ -8128,6 +8259,11 @@ public class SqlParserTest { expr("ceiling(d to microsecond)").ok("CEIL(`D` TO MICROSECOND)"); expr("extract(nanosecond from d)").ok("EXTRACT(NANOSECOND FROM `D`)"); expr("extract(microsecond from d)").ok("EXTRACT(MICROSECOND FROM `D`)"); + + // As for FLOOR, so for DATE_TRUNC. + expr("date_trunc(d , year)").ok("DATE_TRUNC(`D`, YEAR)"); + expr("date_trunc(d , y)").ok("DATE_TRUNC(`D`, `Y`)"); + expr("date_trunc(d , week(tuesday))").ok("DATE_TRUNC(`D`, `WEEK_TUESDAY`)"); } @Test void testGeometry() { 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 a93771e3a0..30c8aa2694 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -8286,9 +8286,24 @@ public class SqlOperatorTest { 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 2 week)", + "2016-02-10 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 2 weeks)", + "2016-02-10 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 quarter)", + "2015-11-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + f.checkScalar("timestamp_sub(timestamp '2016-02-24 12:42:25', interval 1 quarters)", + "2015-11-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"); @@ -8345,9 +8360,28 @@ public class SqlOperatorTest { 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 1 week)", + "2016-02-17", + "DATE NOT NULL"); + + f.checkScalar("date_sub(date '2016-02-24', interval 2 weeks)", + "2016-02-10", + "DATE NOT NULL"); + + f.checkScalar("date_sub(date '2020-10-17', interval 0 week)", + "2020-10-17", + "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 1 quarter)", + "2015-11-24", + "DATE NOT NULL"); + + f.checkScalar("date_sub(date '2016-02-24', interval 2 quarters)", + "2015-08-24", + "DATE NOT NULL"); + f.checkScalar("date_sub(date '2016-02-24', interval 5 year)", "2011-02-24", "DATE NOT NULL"); @@ -8515,12 +8549,30 @@ public class SqlOperatorTest { "2015-02-19", "DATE NOT NULL"); f.checkScalar("date_trunc(date '2015-02-19', week)", "2015-02-15", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', isoweek)", + "2015-02-16", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(sunday))", + "2015-02-15", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(monday))", + "2015-02-16", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(tuesday))", + "2015-02-17", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(wednesday))", + "2015-02-18", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(thursday))", + "2015-02-19", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(friday))", + "2015-02-13", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', week(saturday))", + "2015-02-14", "DATE NOT NULL"); f.checkScalar("date_trunc(date '2015-02-19', month)", "2015-02-01", "DATE NOT NULL"); f.checkScalar("date_trunc(date '2015-02-19', quarter)", "2015-01-01", "DATE NOT NULL"); f.checkScalar("date_trunc(date '2015-02-19', year)", "2015-01-01", "DATE NOT NULL"); + f.checkScalar("date_trunc(date '2015-02-19', isoyear)", + "2014-12-29", "DATE NOT NULL"); } @Test void testDenseRankFunc() {