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 03:36:03 UTC
[calcite] branch main updated (ecb27b9f92 -> 13ef58b506)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
from ecb27b9f92 [CALCITE-5493] Time zone tests in SqlFunctions should pass in Europe/London
new c4c0bf344b [CALCITE-5491] Allow TIME and DATE to be args for TIMESTAMPDIFF
new 13ef58b506 [CALCITE-5450] Allow 'WEEK(weekday)' time frame as argument to functions such as EXTRACT, DATE_TRUNC; allow WEEK and QUARTER in interval literals
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
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 +----
.../calcite/sql/fun/SqlStdOperatorTable.java | 4 +-
.../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 | 73 ++++++++++-
14 files changed, 471 insertions(+), 95 deletions(-)
[calcite] 02/02: [CALCITE-5450] Allow 'WEEK(weekday)' time frame as argument to functions such as EXTRACT, DATE_TRUNC; allow WEEK and QUARTER in interval literals
Posted by jh...@apache.org.
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 <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; 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 <ta...@google.com>
Co-authored-by: Oliver Lee <ol...@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() {
[calcite] 01/02: [CALCITE-5491] Allow TIME and DATE to be args for TIMESTAMPDIFF
Posted by jh...@apache.org.
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 c4c0bf344b02cb7065a8d9539b4f6cda9a9e6f50
Author: Sergey Nuyanzin <sn...@gmail.com>
AuthorDate: Mon Jan 23 23:05:53 2023 +0100
[CALCITE-5491] Allow TIME and DATE to be args for TIMESTAMPDIFF
This fixes a regression introduced by [CALCITE-5423].
Close apache/calcite#3041
---
.../apache/calcite/sql/fun/SqlStdOperatorTable.java | 4 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 21 +++++++++++++++++++--
2 files changed, 21 insertions(+), 4 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 950a103680..e5604775f7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1903,8 +1903,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
/** The <code>TIMESTAMPDIFF</code> function. */
public static final SqlFunction TIMESTAMP_DIFF =
new SqlTimestampDiffFunction("TIMESTAMPDIFF",
- OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.TIMESTAMP,
- SqlTypeFamily.TIMESTAMP));
+ OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATETIME,
+ SqlTypeFamily.DATETIME));
/**
* Use of the <code>IN_FENNEL</code> operator forces the argument to be
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 0a9bf1f46f..a93771e3a0 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -78,6 +78,8 @@ import org.checkerframework.checker.nullness.qual.Nullable;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Tag;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
import org.slf4j.Logger;
import java.math.BigDecimal;
@@ -8129,8 +8131,11 @@ public class SqlOperatorTest {
isNullValue(), "INTEGER"));
}
- @Test void testTimestampDiff() {
- final SqlOperatorFixture f = fixture();
+ @ValueSource(booleans = {true, false})
+ @ParameterizedTest(name = "CoercionEnabled: {0}")
+ void testTimestampDiff(boolean coercionEnabled) {
+ final SqlOperatorFixture f = fixture()
+ .withValidatorConfig(c -> c.withTypeCoercionEnabled(coercionEnabled));
f.setFor(SqlStdOperatorTable.TIMESTAMP_DIFF, VmName.EXPAND);
HOUR_VARIANTS.forEach(s ->
f.checkScalar("timestampdiff(" + s + ", "
@@ -8211,6 +8216,18 @@ public class SqlOperatorTest {
f.checkScalar("timestampdiff(" + s + ", "
+ "date '2019-09-01', date '2016-08-01')",
"-37", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestampdiff(" + s + ", "
+ + "time '12:42:25', time '12:42:25')",
+ "0", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestampdiff(" + s + ", "
+ + "time '12:42:25', date '2016-06-14')",
+ "-1502389", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestampdiff(" + s + ", "
+ + "date '2016-06-14', time '12:42:25')",
+ "1502389", "INTEGER NOT NULL"));
DAY_VARIANTS.forEach(s ->
f.checkScalar("timestampdiff(" + s + ", "
+ "date '2016-06-15', date '2016-06-14')",