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:45 UTC
[calcite] 04/05: [CALCITE-5495] Allow WEEK and QUARTER in INTERVAL literals
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 016dcd46ee90022904720fa9c51db8e15dcef4e9
Author: Tanner Clary <ta...@google.com>
AuthorDate: Wed Jan 11 17:42:24 2023 +0000
[CALCITE-5495] Allow WEEK and QUARTER in INTERVAL literals
---
core/src/main/codegen/default_config.fmpp | 2 +
core/src/main/codegen/templates/Parser.jj | 28 +++++
.../apache/calcite/sql/SqlIntervalQualifier.java | 76 ++++++++++++
site/_docs/reference.md | 2 +
.../apache/calcite/sql/parser/SqlParserTest.java | 6 +-
.../java/org/apache/calcite/test/IntervalTest.java | 135 +++++++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 15 +++
7 files changed, 262 insertions(+), 2 deletions(-)
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..b9db2b83ce 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()
@@ -7901,6 +7927,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < PROCEDURE: "PROCEDURE" >
| < PUBLIC: "PUBLIC" >
| < QUARTER: "QUARTER" >
+| < QUARTERS: "QUARTERS" >
| < RANGE: "RANGE" >
| < RANK: "RANK" >
| < READ: "READ" >
@@ -8143,6 +8170,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < VERSIONING: "VERSIONING" >
| < VIEW: "VIEW" >
| < WEEK: "WEEK" >
+| < WEEKS: "WEEKS" >
| < WHEN: "WHEN" >
| < WHENEVER: "WHENEVER" >
| < WHERE: "WHERE" >
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
index 1bf7980b95..4c20fea592 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
@@ -594,6 +594,76 @@ public class SqlIntervalQualifier extends SqlNode {
}
}
+ /**
+ * Validates an INTERVAL literal against a QUARTER interval qualifier.
+ *
+ * @throws org.apache.calcite.runtime.CalciteContextException if the interval
+ * value is illegal
+ */
+ private int[] evaluateIntervalLiteralAsQuarter(
+ RelDataTypeSystem typeSystem, int sign,
+ String value,
+ String originalValue,
+ SqlParserPos pos) {
+ BigDecimal quarter;
+
+ // validate as QUARTER(startPrecision), e.g. 'MM'
+ String intervalPattern = "(\\d+)";
+
+ Matcher m = Pattern.compile(intervalPattern).matcher(value);
+ if (m.matches()) {
+ // Break out field values
+ try {
+ quarter = parseField(m, 1);
+ } catch (NumberFormatException e) {
+ throw invalidValueException(pos, originalValue);
+ }
+
+ // Validate individual fields
+ checkLeadFieldInRange(typeSystem, sign, quarter, TimeUnit.QUARTER, pos);
+
+ // package values up for return
+ return fillIntervalValueArray(sign, ZERO, quarter);
+ } else {
+ throw invalidValueException(pos, originalValue);
+ }
+ }
+
+ /**
+ * Validates an INTERVAL literal against a WEEK interval qualifier.
+ *
+ * @throws org.apache.calcite.runtime.CalciteContextException if the interval
+ * value is illegal
+ */
+ private int[] evaluateIntervalLiteralAsWeek(
+ RelDataTypeSystem typeSystem, int sign,
+ String value,
+ String originalValue,
+ SqlParserPos pos) {
+ BigDecimal week;
+
+ // validate as WEEK(startPrecision), e.g. 'MM'
+ String intervalPattern = "(\\d+)";
+
+ Matcher m = Pattern.compile(intervalPattern).matcher(value);
+ if (m.matches()) {
+ // Break out field values
+ try {
+ week = parseField(m, 1);
+ } catch (NumberFormatException e) {
+ throw invalidValueException(pos, originalValue);
+ }
+
+ // Validate individual fields
+ checkLeadFieldInRange(typeSystem, sign, week, TimeUnit.WEEK, pos);
+
+ // package values up for return
+ return fillIntervalValueArray(sign, ZERO, week);
+ } else {
+ throw invalidValueException(pos, originalValue);
+ }
+ }
+
/**
* Validates an INTERVAL literal against a DAY interval qualifier.
*
@@ -1149,6 +1219,12 @@ public class SqlIntervalQualifier extends SqlNode {
case MONTH:
return evaluateIntervalLiteralAsMonth(typeSystem, sign, value, value0,
pos);
+ case QUARTER:
+ return evaluateIntervalLiteralAsQuarter(typeSystem, sign, value, value0,
+ pos);
+ case WEEK:
+ return evaluateIntervalLiteralAsWeek(typeSystem, sign, value, value0,
+ pos);
case DAY:
return evaluateIntervalLiteralAsDay(typeSystem, sign, value, value0, pos);
case DAY_TO_HOUR:
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 5d8534a9fc..011890b412 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -843,6 +843,7 @@ PRIVILEGES,
**PROCEDURE**,
PUBLIC,
QUARTER,
+QUARTERS,
**RANGE**,
**RANK**,
READ,
@@ -1084,6 +1085,7 @@ VERSION,
**VERSIONING**,
VIEW,
WEEK,
+WEEKS,
**WHEN**,
**WHENEVER**,
**WHERE**,
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 d6e449055f..00dcddc4f4 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
@@ -5954,8 +5954,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"
+ " ");
@@ -6320,8 +6324,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. */
diff --git a/testkit/src/main/java/org/apache/calcite/test/IntervalTest.java b/testkit/src/main/java/org/apache/calcite/test/IntervalTest.java
index 1f6d5ed30d..1c808db319 100644
--- a/testkit/src/main/java/org/apache/calcite/test/IntervalTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/IntervalTest.java
@@ -44,6 +44,9 @@ public class IntervalTest {
subTestIntervalMinutePositive();
subTestIntervalMinuteToSecondPositive();
subTestIntervalSecondPositive();
+ subTestIntervalWeekPositive();
+ subTestIntervalQuarterPositive();
+ subTestIntervalPlural();
// Tests that should pass parser but fail validator
subTestIntervalYearNegative();
@@ -960,6 +963,138 @@ public class IntervalTest {
+ "INTERVAL YEAR\\(0\\) TO MONTH");
}
+ /**
+ * 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
+ f.expr("INTERVAL '1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL '99' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+
+ // explicit precision equal to default
+ f.expr("INTERVAL '1' WEEK(2)")
+ .columnType("INTERVAL WEEK(2) NOT NULL");
+ f.expr("INTERVAL '99' WEEK(2)")
+ .columnType("INTERVAL WEEK(2) NOT NULL");
+
+ // max precision
+ f.expr("INTERVAL '2147483647' WEEK(10)")
+ .columnType("INTERVAL WEEK(10) NOT NULL");
+
+ // min precision
+ f.expr("INTERVAL '0' WEEK(1)")
+ .columnType("INTERVAL WEEK(1) NOT NULL");
+
+ // alternate precision
+ f.expr("INTERVAL '1234' WEEK(4)")
+ .columnType("INTERVAL WEEK(4) NOT NULL");
+
+ // sign
+ f.expr("INTERVAL '+1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL '-1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL +'1' WEEK")
+ .assertParse("INTERVAL '1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL +'+1' WEEK")
+ .assertParse("INTERVAL '+1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL +'-1' WEEK")
+ .assertParse("INTERVAL '-1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL -'1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL -'+1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL -'-1' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ }
+
+ /**
+ * 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
+ f.expr("INTERVAL '1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL '99' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+
+ // explicit precision equal to default
+ f.expr("INTERVAL '1' QUARTER(2)")
+ .columnType("INTERVAL QUARTER(2) NOT NULL");
+ f.expr("INTERVAL '99' QUARTER(2)")
+ .columnType("INTERVAL QUARTER(2) NOT NULL");
+
+ // max precision
+ f.expr("INTERVAL '2147483647' QUARTER(10)")
+ .columnType("INTERVAL QUARTER(10) NOT NULL");
+
+ // min precision
+ f.expr("INTERVAL '0' QUARTER(1)")
+ .columnType("INTERVAL QUARTER(1) NOT NULL");
+
+ // alternate precision
+ f.expr("INTERVAL '1234' QUARTER(4)")
+ .columnType("INTERVAL QUARTER(4) NOT NULL");
+
+ // sign
+ f.expr("INTERVAL '+1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL '-1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL +'1' QUARTER")
+ .assertParse("INTERVAL '1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL +'+1' QUARTER")
+ .assertParse("INTERVAL '+1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL +'-1' QUARTER")
+ .assertParse("INTERVAL '-1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL -'1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL -'+1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL -'-1' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ }
+
+ public void subTestIntervalPlural() {
+ f.expr("INTERVAL '+2' SECONDS")
+ .assertParse("INTERVAL '+2' SECOND")
+ .columnType("INTERVAL SECOND NOT NULL");
+ f.expr("INTERVAL '+2' HOURS")
+ .assertParse("INTERVAL '+2' HOUR")
+ .columnType("INTERVAL HOUR NOT NULL");
+ f.expr("INTERVAL '+2' DAYS")
+ .assertParse("INTERVAL '+2' DAY")
+ .columnType("INTERVAL DAY NOT NULL");
+ f.expr("INTERVAL '+2' WEEKS")
+ .assertParse("INTERVAL '+2' WEEK")
+ .columnType("INTERVAL WEEK NOT NULL");
+ f.expr("INTERVAL '+2' QUARTERS")
+ .assertParse("INTERVAL '+2' QUARTER")
+ .columnType("INTERVAL QUARTER NOT NULL");
+ f.expr("INTERVAL '+2' MONTHS")
+ .assertParse("INTERVAL '+2' MONTH")
+ .columnType("INTERVAL MONTH NOT NULL");
+ f.expr("INTERVAL '+2' YEARS")
+ .assertParse("INTERVAL '+2' YEAR")
+ .columnType("INTERVAL YEAR NOT NULL");
+ }
+
/**
* Runs tests for INTERVAL... MONTH that should pass parser but fail
* validator. A substantially identical set of tests exists in
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..1e5734be39 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,15 @@ 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 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 year)",
"2015-02-24 12:42:25",
"TIMESTAMP(0) NOT NULL");
@@ -8345,9 +8351,18 @@ 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 '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 5 year)",
"2011-02-24",
"DATE NOT NULL");