You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/01/25 07:32:46 UTC
[calcite] 05/05: [CALCITE-5450] Allow 'WEEK(weekday)' time frame as argument to functions such as EXTRACT, DATE_TRUNC
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 d9eeba4422960832053dac45a4d3efcb22e24528
Author: Tanner Clary <ta...@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
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.
---
babel/src/test/resources/sql/big-query.iq | 30 +++++--
core/src/main/codegen/templates/Parser.jj | 95 ++++++++++++++--------
.../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 | 11 ++-
.../apache/calcite/sql/parser/SqlParserTest.java | 14 +++-
.../org/apache/calcite/test/SqlOperatorTest.java | 30 +++++++
12 files changed, 270 insertions(+), 89 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/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index b9db2b83ce..0514b815e2 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -5023,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.
*/
@@ -7712,6 +7728,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < FRAC_SECOND: "FRAC_SECOND" >
| < FRAME_ROW: "FRAME_ROW" >
| < FREE: "FREE" >
+| < FRIDAY: "FRIDAY" >
| < FROM: "FROM" > { beforeTableName(); }
| < FULL: "FULL" >
| < FUNCTION: "FUNCTION" >
@@ -7830,6 +7847,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < MOD: "MOD" >
| < MODIFIES: "MODIFIES" >
| < MODULE: "MODULE" >
+| < MONDAY: "MONDAY" >
| < MONTH: "MONTH" >
| < MONTHS: "MONTHS" >
| < MORE_: "MORE" >
@@ -7977,6 +7995,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < ROW_NUMBER: "ROW_NUMBER" >
| < ROWS: "ROWS" >
| < RUNNING: "RUNNING" >
+| < SATURDAY: "SATURDAY" >
| < SAVEPOINT: "SAVEPOINT" >
| < SCALAR: "SCALAR" >
| < SCALE: "SCALE" >
@@ -8090,6 +8109,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < SUBSTRING_REGEX: "SUBSTRING_REGEX" >
| < SUCCEEDS: "SUCCEEDS" >
| < SUM: "SUM" >
+| < SUNDAY: "SUNDAY" >
| < SYMMETRIC: "SYMMETRIC" >
| < SYSTEM: "SYSTEM" >
| < SYSTEM_TIME: "SYSTEM_TIME" >
@@ -8099,6 +8119,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < TABLESAMPLE: "TABLESAMPLE" >
| < TEMPORARY: "TEMPORARY" >
| < THEN: "THEN" >
+| < THURSDAY: "THURSDAY" >
| < TIES: "TIES" >
| < TIME: "TIME" >
| < TIME_DIFF: "TIME_DIFF" >
@@ -8132,6 +8153,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < TRIM_ARRAY: "TRIM_ARRAY" >
| < TRUE: "TRUE" >
| < TRUNCATE: "TRUNCATE" >
+| < TUESDAY: "TUESDAY" >
| < TUMBLE: "TUMBLE" >
| < TYPE: "TYPE" >
| < UESCAPE: "UESCAPE" >
@@ -8169,6 +8191,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < VERSION: "VERSION" >
| < VERSIONING: "VERSIONING" >
| < VIEW: "VIEW" >
+| < WEDNESDAY: "WEDNESDAY" >
| < WEEK: "WEEK" >
| < WEEKS: "WEEKS" >
| < WHEN: "WHEN" >
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 05d3789076..7fcf6cf5a1 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -2708,6 +2708,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 011890b412..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,
@@ -893,6 +895,7 @@ ROUTINE_SCHEMA,
ROW_COUNT,
**ROW_NUMBER**,
**RUNNING**,
+**SATURDAY**,
**SAVEPOINT**,
SCALAR,
SCALE,
@@ -1005,6 +1008,7 @@ SUBSTITUTE,
**SUBSTRING_REGEX**,
**SUCCEEDS**,
**SUM**,
+**SUNDAY**,
**SYMMETRIC**,
**SYSTEM**,
**SYSTEM_TIME**,
@@ -1014,6 +1018,7 @@ SUBSTITUTE,
TABLE_NAME,
TEMPORARY,
**THEN**,
+**THURSDAY**,
TIES,
**TIME**,
**TIMESTAMP**,
@@ -1047,6 +1052,7 @@ TRIGGER_SCHEMA,
**TRIM_ARRAY**,
**TRUE**,
**TRUNCATE**,
+**TUESDAY**,
TUMBLE,
TYPE,
**UESCAPE**,
@@ -1084,6 +1090,7 @@ UTF8,
VERSION,
**VERSIONING**,
VIEW,
+**WEDNESDAY**,
WEEK,
WEEKS,
**WHEN**,
@@ -1168,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 00dcddc4f4..9b5fc4aca2 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
@@ -270,6 +270,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",
@@ -361,6 +362,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",
@@ -465,6 +467,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",
@@ -509,6 +512,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",
@@ -517,6 +521,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",
@@ -534,6 +539,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",
@@ -560,6 +566,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",
@@ -6468,7 +6475,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() {
@@ -6502,6 +6509,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 1e5734be39..3d03cd2cb3 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8289,12 +8289,18 @@ public class SqlOperatorTest {
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");
@@ -8354,6 +8360,9 @@ public class SqlOperatorTest {
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");
@@ -8363,6 +8372,9 @@ public class SqlOperatorTest {
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");
@@ -8530,12 +8542,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() {