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 b8ccac153a5be6db72fcbcbda983a83d40b7932b Author: Tanner Clary <tannercl...@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");