This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 03c76a7d2b896042ab417ddc36f1849f874ad3dd Author: Julian Hyde <[email protected]> AuthorDate: Mon Jul 20 13:33:06 2020 -0700 [CALCITE-4134] Interval expressions --- core/src/main/codegen/templates/Parser.jj | 159 +++++++++++++++++---- .../java/org/apache/calcite/sql/SqlDialect.java | 2 +- .../main/java/org/apache/calcite/sql/SqlKind.java | 5 +- .../java/org/apache/calcite/sql/SqlLiteral.java | 18 +++ .../main/java/org/apache/calcite/sql/SqlNode.java | 11 ++ .../calcite/sql/dialect/BigQuerySqlDialect.java | 6 +- .../apache/calcite/sql/dialect/Db2SqlDialect.java | 2 +- .../calcite/sql/dialect/MssqlSqlDialect.java | 2 +- .../calcite/sql/fun/SqlIntervalOperator.java | 83 +++++++++++ .../calcite/sql/fun/SqlStdOperatorTable.java | 6 + .../calcite/sql/type/SqlOperandTypeChecker.java | 2 + .../calcite/sql/type/SqlOperandTypeInference.java | 2 + .../calcite/sql/type/SqlReturnTypeInference.java | 4 +- .../apache/calcite/sql/type/SqlTypeTransform.java | 2 + .../calcite/sql/validate/SqlValidatorImpl.java | 3 +- .../calcite/sql2rel/ReflectiveConvertletTable.java | 2 +- .../calcite/sql2rel/SqlNodeToRexConverterImpl.java | 9 +- .../calcite/sql2rel/StandardConvertletTable.java | 20 ++- .../apache/calcite/sql/parser/SqlParserTest.java | 20 +++ .../apache/calcite/sql/test/AbstractSqlTester.java | 2 +- .../apache/calcite/test/SqlToRelConverterTest.java | 4 + .../org/apache/calcite/test/SqlValidatorTest.java | 149 +++++++++++-------- .../apache/calcite/test/SqlToRelConverterTest.xml | 12 +- core/src/test/resources/sql/misc.iq | 27 ++++ 24 files changed, 444 insertions(+), 108 deletions(-) diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 1d8465e..279fa38 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -3770,7 +3770,7 @@ SqlNode AtomicRowExpression() : } { ( - e = Literal() + e = LiteralOrIntervalExpression() | e = DynamicParam() | @@ -4007,6 +4007,10 @@ SqlDrop SqlDrop() : * Usually returns an SqlLiteral, but a continued string literal * is an SqlCall expression, which concatenates 2 or more string * literals; the validator reduces this. + * + * <p>If the context allows both literals and expressions, + * use {@link #LiteralOrIntervalExpression}, which requires less + * lookahead. */ SqlNode Literal() : { @@ -4014,6 +4018,20 @@ SqlNode Literal() : } { ( + e = NonIntervalLiteral() + | + e = IntervalLiteral() + ) + { return e; } +} + +/** Parses a literal that is not an interval literal. */ +SqlNode NonIntervalLiteral() : +{ + final SqlNode e; +} +{ + ( e = NumericLiteral() | e = StringLiteral() @@ -4021,8 +4039,6 @@ SqlNode Literal() : e = SpecialLiteral() | e = DateTimeLiteral() - | - e = IntervalLiteral() <#-- additional literal parser methods are included here --> <#list parser.literalParserMethods as method> | @@ -4032,8 +4048,25 @@ SqlNode Literal() : { return e; } +} - +/** Parses a literal or an interval expression. + * + * <p>We include them in the same production because it is difficult to + * distinguish interval literals from interval expression (both of which + * start with the {@code INTERVAL} keyword); this way, we can use less + * LOOKAHEAD. */ +SqlNode LiteralOrIntervalExpression() : +{ + final SqlNode e; +} +{ + ( + e = IntervalLiteralOrExpression() + | + e = NonIntervalLiteral() + ) + { return e; } } /** Parses a unsigned numeric literal */ @@ -4416,6 +4449,53 @@ SqlLiteral IntervalLiteral() : } } +/** Parses an interval literal (e.g. {@code INTERVAL '2:3' HOUR TO MINUTE}) + * or an interval expression (e.g. {@code INTERVAL emp.empno MINUTE} + * or {@code INTERVAL 3 MONTHS}). */ +SqlNode IntervalLiteralOrExpression() : +{ + final String p; + final SqlIntervalQualifier intervalQualifier; + int sign = 1; + final Span s; + SqlNode e; +} +{ + <INTERVAL> { s = span(); } + [ + <MINUS> { sign = -1; } + | + <PLUS> { sign = 1; } + ] + ( + // literal (with quoted string) + <QUOTED_STRING> { p = token.image; } + intervalQualifier = IntervalQualifier() { + return SqlParserUtil.parseIntervalLiteral(s.end(intervalQualifier), + sign, p, intervalQualifier); + } + | + // To keep parsing simple, any expressions besides numeric literal and + // identifiers must be enclosed in parentheses. + ( + <LPAREN> + e = Expression(ExprContext.ACCEPT_SUB_QUERY) + <RPAREN> + | + e = UnsignedNumericLiteral() + | + e = CompoundIdentifier() + ) + intervalQualifier = IntervalQualifierStart() { + if (sign == -1) { + e = SqlStdOperatorTable.UNARY_MINUS.createCall(e.getParserPosition(), e); + } + return SqlStdOperatorTable.INTERVAL.createCall(s.end(this), e, + intervalQualifier); + } + ) +} + TimeUnit Year() : { } @@ -4472,6 +4552,7 @@ TimeUnit Second() : SqlIntervalQualifier IntervalQualifier() : { + final Span s; final TimeUnit start; TimeUnit end = null; int startPrec = RelDataType.PRECISION_NOT_SPECIFIED; @@ -4479,27 +4560,28 @@ SqlIntervalQualifier IntervalQualifier() : } { ( - start = Year() [ <LPAREN> startPrec = UnsignedIntLiteral() <RPAREN> ] + start = Year() { s = span(); } startPrec = PrecisionOpt() [ LOOKAHEAD(2) <TO> end = Month() ] | - start = Month() [ <LPAREN> startPrec = UnsignedIntLiteral() <RPAREN> ] + start = Month() { s = span(); } startPrec = PrecisionOpt() | - start = Day() [ <LPAREN> startPrec = UnsignedIntLiteral() <RPAREN> ] - [ LOOKAHEAD(2) <TO> + start = Day() { s = span(); } startPrec = PrecisionOpt() + [ + LOOKAHEAD(2) <TO> ( end = Hour() | end = Minute() | - end = Second() - [ <LPAREN> secondFracPrec = UnsignedIntLiteral() <RPAREN> ] + end = Second() secondFracPrec = PrecisionOpt() ) ] | - start = Hour() [ <LPAREN> startPrec = UnsignedIntLiteral() <RPAREN> ] - [ LOOKAHEAD(2) <TO> + start = Hour() { s = span(); } startPrec = PrecisionOpt() + [ + LOOKAHEAD(2) <TO> ( end = Minute() | @@ -4508,26 +4590,54 @@ SqlIntervalQualifier IntervalQualifier() : ) ] | - start = Minute() [ <LPAREN> startPrec = UnsignedIntLiteral() <RPAREN> ] - [ LOOKAHEAD(2) <TO> - ( - end = Second() - [ <LPAREN> secondFracPrec = UnsignedIntLiteral() <RPAREN> ] - ) + start = Minute() { s = span(); } startPrec = PrecisionOpt() + [ + LOOKAHEAD(2) <TO> end = Second() + [ <LPAREN> secondFracPrec = UnsignedIntLiteral() <RPAREN> ] ] | - start = Second() + start = Second() { s = span(); } + [ + <LPAREN> startPrec = UnsignedIntLiteral() + [ <COMMA> secondFracPrec = UnsignedIntLiteral() ] + <RPAREN> + ] + ) + { + return new SqlIntervalQualifier(start, startPrec, end, secondFracPrec, + s.end(this)); + } +} + +/** Interval qualifier without 'TO unit'. */ +SqlIntervalQualifier IntervalQualifierStart() : +{ + final Span s; + final TimeUnit start; + int startPrec = RelDataType.PRECISION_NOT_SPECIFIED; + int secondFracPrec = RelDataType.PRECISION_NOT_SPECIFIED; +} +{ + ( + ( + start = Year() + | start = Month() + | start = Day() + | start = Hour() + | start = Minute() + ) + { s = span(); } + startPrec = PrecisionOpt() + | + start = Second() { s = span(); } [ <LPAREN> startPrec = UnsignedIntLiteral() [ <COMMA> secondFracPrec = UnsignedIntLiteral() ] <RPAREN> ] ) { - return new SqlIntervalQualifier(start, - startPrec, - end, - secondFracPrec, - getPos()); + return new SqlIntervalQualifier(start, startPrec, null, secondFracPrec, + s.end(this)); } } @@ -5260,7 +5370,6 @@ int PrecisionOpt() : int precision = -1; } { - LOOKAHEAD(2) <LPAREN> precision = UnsignedIntLiteral() <RPAREN> diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 6c19c1a..5dedfc8 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -526,7 +526,7 @@ public class SqlDialect { public void unparseSqlIntervalLiteral(SqlWriter writer, SqlIntervalLiteral literal, int leftPrec, int rightPrec) { SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); writer.keyword("INTERVAL"); if (interval.getSign() == -1) { writer.print("-"); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index adb3223..1fee9bf 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -349,6 +349,9 @@ public enum SqlKind { /** {@code CASE} expression. */ CASE, + /** {@code INTERVAL} expression. */ + INTERVAL, + /** {@code NULLIF} operator. */ NULLIF, @@ -1066,7 +1069,7 @@ public enum SqlKind { FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS, DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL, SELECT, JOIN, OTHER_FUNCTION, POSITION, CAST, TRIM, FLOOR, CEIL, - TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT, + TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT, INTERVAL, LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY, NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE, VALUES, WITH, WITH_ITEM, ITEM, SKIP_TO_FIRST, SKIP_TO_LAST, diff --git a/core/src/main/java/org/apache/calcite/sql/SqlLiteral.java b/core/src/main/java/org/apache/calcite/sql/SqlLiteral.java index bd80488..7ce5fa1 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlLiteral.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlLiteral.java @@ -252,6 +252,20 @@ public class SqlLiteral extends SqlNode { return value; } + /** + * Returns the value of this literal as a particular type. + * + * <p>The type might be the internal type, or other convenient types. + * For example, numeric literals' values are stored internally as + * {@link BigDecimal}, but other numeric types such as {@link Long} and + * {@link Double} are also allowed. + * + * @param clazz Desired value type + * @param <T> Value type + * @return Value of the literal + * + * @throws AssertionError if the value type is not supported + */ public <T> T getValueAs(Class<T> clazz) { if (clazz.isInstance(value)) { return clazz.cast(value); @@ -320,6 +334,8 @@ public class SqlLiteral extends SqlNode { return clazz.cast(BigDecimal.valueOf(getValueAs(Long.class))); } else if (clazz == TimeUnitRange.class) { return clazz.cast(valMonth.getIntervalQualifier().timeUnitRange); + } else if (clazz == SqlIntervalQualifier.class) { + return clazz.cast(valMonth.getIntervalQualifier()); } break; case INTERVAL_DAY: @@ -341,6 +357,8 @@ public class SqlLiteral extends SqlNode { return clazz.cast(BigDecimal.valueOf(getValueAs(Long.class))); } else if (clazz == TimeUnitRange.class) { return clazz.cast(valTime.getIntervalQualifier().timeUnitRange); + } else if (clazz == SqlIntervalQualifier.class) { + return clazz.cast(valTime.getIntervalQualifier()); } break; } diff --git a/core/src/main/java/org/apache/calcite/sql/SqlNode.java b/core/src/main/java/org/apache/calcite/sql/SqlNode.java index ec69cae..82bad71 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlNode.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlNode.java @@ -211,6 +211,17 @@ public abstract class SqlNode implements Cloneable { int leftPrec, int rightPrec); + public void unparseWithParentheses(SqlWriter writer, int leftPrec, + int rightPrec, boolean parentheses) { + if (parentheses) { + final SqlWriter.Frame frame = writer.startList("(", ")"); + unparse(writer, 0, 0); + writer.endList(frame); + } else { + unparse(writer, leftPrec, rightPrec); + } + } + public SqlParserPos getParserPosition() { return pos; } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java index af2e855..19276b1 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java @@ -164,10 +164,10 @@ public class BigQuerySqlDialect extends SqlDialect { } /** BigQuery interval syntax: INTERVAL int64 time_unit. */ - @Override public void unparseSqlIntervalLiteral( - SqlWriter writer, SqlIntervalLiteral literal, int leftPrec, int rightPrec) { + @Override public void unparseSqlIntervalLiteral(SqlWriter writer, + SqlIntervalLiteral literal, int leftPrec, int rightPrec) { SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); writer.keyword("INTERVAL"); if (interval.getSign() == -1) { writer.print("-"); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java index 27994af..9dbbda0 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java @@ -84,7 +84,7 @@ public class Db2SqlDialect extends SqlDialect { // If one operand is a timestamp, the other operand can be any of teh duration. SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); if (interval.getSign() == -1) { writer.print("-"); } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java index 547f9a9..f005175 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java @@ -270,7 +270,7 @@ public class MssqlSqlDialect extends SqlDialect { private void unparseSqlIntervalLiteralMssql( SqlWriter writer, SqlIntervalLiteral literal, int sign) { final SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); unparseSqlIntervalQualifier(writer, interval.getIntervalQualifier(), RelDataTypeSystem.DEFAULT); writer.sep(",", true); diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlIntervalOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlIntervalOperator.java new file mode 100644 index 0000000..e959b40 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlIntervalOperator.java @@ -0,0 +1,83 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.sql.fun; + +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlIdentifier; +import org.apache.calcite.sql.SqlInternalOperator; +import org.apache.calcite.sql.SqlIntervalQualifier; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.SqlLiteral; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlOperatorBinding; +import org.apache.calcite.sql.SqlWriter; +import org.apache.calcite.sql.type.InferTypes; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.SqlReturnTypeInference; +import org.apache.calcite.sql.type.SqlTypeTransforms; + +/** Interval expression. + * + * <p>Syntax: + * + * <blockquote><pre>INTERVAL numericExpression timeUnit + * + * timeUnit: YEAR | MONTH | DAY | HOUR | MINUTE | SECOND</pre></blockquote> + * + * <p>Compare with interval literal, whose syntax is + * {@code INTERVAL characterLiteral timeUnit [ TO timeUnit ]}. + */ +public class SqlIntervalOperator extends SqlInternalOperator { + private static final SqlReturnTypeInference RETURN_TYPE = + ((SqlReturnTypeInference) SqlIntervalOperator::returnType) + .andThen(SqlTypeTransforms.TO_NULLABLE); + + SqlIntervalOperator() { + super("INTERVAL", SqlKind.INTERVAL, 0, true, RETURN_TYPE, + InferTypes.ANY_NULLABLE, OperandTypes.NUMERIC_INTERVAL); + } + + private static RelDataType returnType(SqlOperatorBinding opBinding) { + final SqlIntervalQualifier intervalQualifier = + opBinding.getOperandLiteralValue(1, SqlIntervalQualifier.class); + return opBinding.getTypeFactory().createSqlIntervalType(intervalQualifier); + } + + @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, + int rightPrec) { + writer.keyword("INTERVAL"); + final SqlNode expression = call.operand(0); + final SqlIntervalQualifier intervalQualifier = call.operand(1); + expression.unparseWithParentheses(writer, leftPrec, rightPrec, + !(expression instanceof SqlLiteral + || expression instanceof SqlIdentifier + || expression.getKind() == SqlKind.MINUS_PREFIX + || writer.isAlwaysUseParentheses())); + assert intervalQualifier.timeUnitRange.endUnit == null; + intervalQualifier.unparse(writer, 0, 0); + } + + @Override public String getSignatureTemplate(int operandsCount) { + switch (operandsCount) { + case 2: + return "{0} {1} {2}"; // e.g. "INTERVAL <INTEGER> <INTERVAL HOUR>" + default: + throw new AssertionError(); + } + } +} 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 46f2208..77cff50 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 @@ -548,6 +548,12 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlDatetimePlusOperator(); /** + * Interval expression, '<code>INTERVAL n timeUnit</code>'. + */ + public static final SqlSpecialOperator INTERVAL = + new SqlIntervalOperator(); + + /** * Multiset {@code MEMBER OF}, which returns whether a element belongs to a * multiset. * diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java index 6e3452c..7f15371 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeChecker.java @@ -25,6 +25,8 @@ import org.apache.calcite.sql.SqlOperator; * * <p>This interface is an example of the * {@link org.apache.calcite.util.Glossary#STRATEGY_PATTERN strategy pattern}. + * + * @see OperandTypes */ public interface SqlOperandTypeChecker { //~ Methods ---------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeInference.java b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeInference.java index 7091ebb..e27524c 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeInference.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlOperandTypeInference.java @@ -21,6 +21,8 @@ import org.apache.calcite.sql.SqlCallBinding; /** * Strategy to infer unknown types of the operands of an operator call. + * + * @see InferTypes */ public interface SqlOperandTypeInference { //~ Methods ---------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlReturnTypeInference.java b/core/src/main/java/org/apache/calcite/sql/type/SqlReturnTypeInference.java index c9e31d2..16a80ea 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlReturnTypeInference.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlReturnTypeInference.java @@ -28,7 +28,9 @@ import org.apache.calcite.sql.SqlOperatorBinding; * {@link org.apache.calcite.util.Glossary#STRATEGY_PATTERN strategy pattern}. * This makes * sense because many operators have similar, straightforward strategies, such - * as to take the type of the first operand.</p> + * as to take the type of the first operand. + * + * @see ReturnTypes */ @FunctionalInterface public interface SqlReturnTypeInference { diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransform.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransform.java index 6bf80b2..ffdd968 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransform.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransform.java @@ -27,6 +27,8 @@ import org.apache.calcite.sql.SqlOperatorBinding; * * <p>This class is an example of the * {@link org.apache.calcite.util.Glossary#STRATEGY_PATTERN strategy pattern}. + * + * @see SqlTypeTransforms */ public interface SqlTypeTransform { //~ Methods ---------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index bf59a4f..a81f359 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -3088,8 +3088,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { case INTERVAL_SECOND: if (literal instanceof SqlIntervalLiteral) { SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) - literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); SqlIntervalQualifier intervalQualifier = interval.getIntervalQualifier(); diff --git a/core/src/main/java/org/apache/calcite/sql2rel/ReflectiveConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/ReflectiveConvertletTable.java index d8243c8..ffa05f0 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/ReflectiveConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/ReflectiveConvertletTable.java @@ -134,7 +134,7 @@ public class ReflectiveConvertletTable implements SqlRexConvertletTable { final SqlOperator op = call.getOperator(); // Is there a convertlet for this operator - // (e.g. SqlStdOperatorTable.plusOperator)? + // (e.g. SqlStdOperatorTable.PLUS)? convertlet = (SqlRexConvertlet) map.get(op); if (convertlet != null) { return convertlet; diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlNodeToRexConverterImpl.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlNodeToRexConverterImpl.java index a6b124a..c6104c4 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlNodeToRexConverterImpl.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlNodeToRexConverterImpl.java @@ -23,7 +23,6 @@ import org.apache.calcite.rex.RexBuilder; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; import org.apache.calcite.sql.SqlCall; -import org.apache.calcite.sql.SqlIntervalLiteral; import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlTimeLiteral; @@ -95,10 +94,7 @@ public class SqlNodeToRexConverterImpl implements SqlNodeToRexConverter { return rexBuilder.makeNullLiteral(type); } - BitString bitString; - SqlIntervalLiteral.IntervalValue intervalValue; - long l; - + final BitString bitString; switch (literal.getTypeName()) { case DECIMAL: // exact number @@ -152,8 +148,7 @@ public class SqlNodeToRexConverterImpl implements SqlNodeToRexConverter { case INTERVAL_MINUTE_SECOND: case INTERVAL_SECOND: SqlIntervalQualifier sqlIntervalQualifier = - literal.getValueAs(SqlIntervalLiteral.IntervalValue.class) - .getIntervalQualifier(); + literal.getValueAs(SqlIntervalQualifier.class); return rexBuilder.makeIntervalLiteral( literal.getValueAs(BigDecimal.class), sqlIntervalQualifier); diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index aaae32e..d1c9bd9 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -259,6 +259,9 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { registerOp(SqlStdOperatorTable.TIMESTAMP_DIFF, new TimestampDiffConvertlet()); + registerOp(SqlStdOperatorTable.INTERVAL, + StandardConvertletTable::convertInterval); + // Convert "element(<expr>)" to "$element_slice(<expr>)", if the // expression is a multiset of scalars. if (false) { @@ -294,6 +297,21 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { } } + /** Converts an interval expression to a numeric multiplied by an interval + * literal. */ + private static RexNode convertInterval(SqlRexContext cx, SqlCall call) { + // "INTERVAL n HOUR" becomes "n * INTERVAL '1' HOUR" + final SqlNode n = call.operand(0); + final SqlIntervalQualifier intervalQualifier = call.operand(1); + final SqlIntervalLiteral literal = + SqlLiteral.createInterval(1, "1", intervalQualifier, + call.getParserPosition()); + final SqlCall multiply = + SqlStdOperatorTable.MULTIPLY.createCall(call.getParserPosition(), n, + literal); + return cx.convertExpression(multiply); + } + //~ Methods ---------------------------------------------------------------- private RexNode or(RexBuilder rexBuilder, RexNode a0, RexNode a1) { @@ -546,7 +564,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { && call.operand(0) instanceof SqlIntervalLiteral) { final SqlIntervalLiteral literal = call.operand(0); SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) literal.getValue(); + literal.getValueAs(SqlIntervalLiteral.IntervalValue.class); BigDecimal val = interval.getIntervalQualifier().getStartUnit().multiplier; RexNode rexInterval = cx.convertExpression(literal); diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index 4fa3451..3c4bf76 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -7096,6 +7096,26 @@ public class SqlParserTest { .ok("INTERVAL '1:x:2' HOUR TO SECOND"); } + @Test void testIntervalExpression() { + expr("interval 0 day").ok("INTERVAL 0 DAY"); + expr("interval 0 days").ok("INTERVAL 0 DAY"); + expr("interval -10 days").ok("INTERVAL (- 10) DAY"); + expr("interval -10 days").ok("INTERVAL (- 10) DAY"); + // parser requires parentheses for expressions other than numeric + // literal or identifier + expr("interval 1 ^+^ x.y days") + .fails("(?s)Encountered \"\\+\" at .*"); + expr("interval (1 + x.y) days") + .ok("INTERVAL (1 + `X`.`Y`) DAY"); + expr("interval -x second(3)") + .ok("INTERVAL (- `X`) SECOND(3)"); + expr("interval -x.y second(3)") + .ok("INTERVAL (- `X`.`Y`) SECOND(3)"); + expr("interval 1 day ^to^ hour") + .fails("(?s)Encountered \"to\" at .*"); + expr("interval '1 1' day to hour").ok("INTERVAL '1 1' DAY TO HOUR"); + } + @Test void testIntervalOperators() { expr("-interval '1' day") .ok("(- INTERVAL '1' DAY)"); diff --git a/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java b/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java index a858170..4a6bbf1 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java +++ b/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java @@ -234,7 +234,7 @@ public abstract class AbstractSqlTester implements SqlTester, AutoCloseable { assertNotNull(node); SqlIntervalLiteral intervalLiteral = (SqlIntervalLiteral) node; SqlIntervalLiteral.IntervalValue interval = - (SqlIntervalLiteral.IntervalValue) intervalLiteral.getValue(); + intervalLiteral.getValueAs(SqlIntervalLiteral.IntervalValue.class); long l = interval.getIntervalQualifier().isYearMonth() ? SqlParserUtil.intervalToMonths(interval) diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index e05ef07..4bb6ed0 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -119,6 +119,10 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test void testIntervalExpression() { + sql("select interval mgr hour as h from emp").ok(); + } + @Test void testAliasList() { final String sql = "select a + b from (\n" + " select deptno, 1 as uno, name from dept\n" 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 48c2958..12cdca6 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -92,7 +92,7 @@ import static org.junit.jupiter.api.Assumptions.assumeTrue; * {@link org.apache.calcite.sql.test.SqlTester}. */ @LocaleEnUs -class SqlValidatorTest extends SqlValidatorTestCase { +public class SqlValidatorTest extends SqlValidatorTestCase { //~ Static fields/initializers --------------------------------------------- // CHECKSTYLE: IGNORE 1 @@ -1550,7 +1550,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("(?s).*Function '.fn HAHAHA.' is not defined.*"); } - @Test void testQuotedFunction() { + @Test public void testQuotedFunction() { if (false) { // REVIEW jvs 2-Feb-2005: I am disabling this test because I // removed the corresponding support from the parser. Where in the @@ -1822,7 +1822,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalYearPositive() { + void subTestIntervalYearPositive() { // default precision expr("INTERVAL '1' YEAR") .columnType("INTERVAL YEAR NOT NULL"); @@ -1873,7 +1873,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalYearToMonthPositive() { + void subTestIntervalYearToMonthPositive() { // default precision expr("INTERVAL '1-2' YEAR TO MONTH") .columnType("INTERVAL YEAR TO MONTH NOT NULL"); @@ -1928,7 +1928,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalMonthPositive() { + void subTestIntervalMonthPositive() { // default precision expr("INTERVAL '1' MONTH") .columnType("INTERVAL MONTH NOT NULL"); @@ -1979,7 +1979,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalDayPositive() { + void subTestIntervalDayPositive() { // default precision expr("INTERVAL '1' DAY") .columnType("INTERVAL DAY NOT NULL"); @@ -2023,7 +2023,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { .columnType("INTERVAL DAY NOT NULL"); } - public void subTestIntervalDayToHourPositive() { + void subTestIntervalDayToHourPositive() { // default precision expr("INTERVAL '1 2' DAY TO HOUR") .columnType("INTERVAL DAY TO HOUR NOT NULL"); @@ -2078,7 +2078,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalDayToMinutePositive() { + void subTestIntervalDayToMinutePositive() { // default precision expr("INTERVAL '1 2:3' DAY TO MINUTE") .columnType("INTERVAL DAY TO MINUTE NOT NULL"); @@ -2133,7 +2133,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalDayToSecondPositive() { + void subTestIntervalDayToSecondPositive() { // default precision expr("INTERVAL '1 2:3:4' DAY TO SECOND") .columnType("INTERVAL DAY TO SECOND NOT NULL"); @@ -2202,7 +2202,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalHourPositive() { + void subTestIntervalHourPositive() { // default precision expr("INTERVAL '1' HOUR") .columnType("INTERVAL HOUR NOT NULL"); @@ -2253,7 +2253,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalHourToMinutePositive() { + void subTestIntervalHourToMinutePositive() { // default precision expr("INTERVAL '2:3' HOUR TO MINUTE") .columnType("INTERVAL HOUR TO MINUTE NOT NULL"); @@ -2308,7 +2308,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalHourToSecondPositive() { + void subTestIntervalHourToSecondPositive() { // default precision expr("INTERVAL '2:3:4' HOUR TO SECOND") .columnType("INTERVAL HOUR TO SECOND NOT NULL"); @@ -2377,7 +2377,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalMinutePositive() { + void subTestIntervalMinutePositive() { // default precision expr("INTERVAL '1' MINUTE") .columnType("INTERVAL MINUTE NOT NULL"); @@ -2428,7 +2428,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalMinuteToSecondPositive() { + void subTestIntervalMinuteToSecondPositive() { // default precision expr("INTERVAL '2:4' MINUTE TO SECOND") .columnType("INTERVAL MINUTE TO SECOND NOT NULL"); @@ -2497,7 +2497,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXPositive() tests. */ - public void subTestIntervalSecondPositive() { + void subTestIntervalSecondPositive() { // default precision expr("INTERVAL '1' SECOND") .columnType("INTERVAL SECOND NOT NULL"); @@ -2558,7 +2558,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalYearNegative() { + void subTestIntervalYearNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' YEAR") .fails("Illegal interval literal format '-' for INTERVAL YEAR.*"); @@ -2595,14 +2595,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { + "YEAR\\(10\\) field"); // precision > maximum - expr("INTERVAL '1' YEAR(11^)^") + expr("INTERVAL '1' ^YEAR(11)^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL YEAR\\(11\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' YEAR(0^)^") + expr("INTERVAL '0' ^YEAR(0)^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL YEAR\\(0\\)"); } @@ -2614,7 +2614,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalYearToMonthNegative() { + void subTestIntervalYearToMonthNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' YEAR TO MONTH") .fails("Illegal interval literal format '-' for INTERVAL YEAR TO MONTH"); @@ -2660,14 +2660,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Illegal interval literal format '1-12' for INTERVAL YEAR TO MONTH.*"); // precision > maximum - expr("INTERVAL '1-1' YEAR(11) TO ^MONTH^") + expr("INTERVAL '1-1' ^YEAR(11) TO MONTH^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL YEAR\\(11\\) TO MONTH"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0-0' YEAR(0) TO ^MONTH^") + expr("INTERVAL '0-0' ^YEAR(0) TO MONTH^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL YEAR\\(0\\) TO MONTH"); } @@ -2679,7 +2679,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalMonthNegative() { + void subTestIntervalMonthNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' MONTH") .fails("Illegal interval literal format '-' for INTERVAL MONTH.*"); @@ -2714,14 +2714,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Interval field value -2,147,483,648 exceeds precision of MONTH\\(10\\) field.*"); // precision > maximum - expr("INTERVAL '1' MONTH(11^)^") + expr("INTERVAL '1' ^MONTH(11)^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL MONTH\\(11\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' MONTH(0^)^") + expr("INTERVAL '0' ^MONTH(0)^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL MONTH\\(0\\)"); } @@ -2733,7 +2733,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalDayNegative() { + void subTestIntervalDayNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' DAY") .fails("Illegal interval literal format '-' for INTERVAL DAY.*"); @@ -2772,14 +2772,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { + "DAY\\(10\\) field.*"); // precision > maximum - expr("INTERVAL '1' DAY(11^)^") + expr("INTERVAL '1' ^DAY(11)^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL DAY\\(11\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' DAY(0^)^") + expr("INTERVAL '0' ^DAY(0)^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL DAY\\(0\\)"); } @@ -2791,7 +2791,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalDayToHourNegative() { + void subTestIntervalDayToHourNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' DAY TO HOUR") .fails("Illegal interval literal format '-' for INTERVAL DAY TO HOUR"); @@ -2838,14 +2838,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Illegal interval literal format '1 24' for INTERVAL DAY TO HOUR.*"); // precision > maximum - expr("INTERVAL '1 1' DAY(11) TO ^HOUR^") + expr("INTERVAL '1 1' ^DAY(11) TO HOUR^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL DAY\\(11\\) TO HOUR"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0 0' DAY(0) TO ^HOUR^") + expr("INTERVAL '0 0' ^DAY(0) TO HOUR^") .fails("Interval leading field precision '0' out of range for INTERVAL DAY\\(0\\) TO HOUR"); } @@ -2856,7 +2856,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalDayToMinuteNegative() { + void subTestIntervalDayToMinuteNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL ' :' DAY TO MINUTE") .fails("Illegal interval literal format ' :' for INTERVAL DAY TO MINUTE"); @@ -2920,14 +2920,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Illegal interval literal format '1 1:60' for INTERVAL DAY TO MINUTE.*"); // precision > maximum - expr("INTERVAL '1 1:1' DAY(11) TO ^MINUTE^") + expr("INTERVAL '1 1:1' ^DAY(11) TO MINUTE^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL DAY\\(11\\) TO MINUTE"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0 0' DAY(0) TO ^MINUTE^") + expr("INTERVAL '0 0' ^DAY(0) TO MINUTE^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL DAY\\(0\\) TO MINUTE"); } @@ -2939,7 +2939,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalDayToSecondNegative() { + void subTestIntervalDayToSecondNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL ' ::' DAY TO SECOND") .fails("Illegal interval literal format ' ::' for INTERVAL DAY TO SECOND"); @@ -3041,20 +3041,20 @@ class SqlValidatorTest extends SqlValidatorTestCase { + "INTERVAL DAY TO SECOND\\(3\\).*"); // precision > maximum - expr("INTERVAL '1 1' DAY(11) TO ^SECOND^") + expr("INTERVAL '1 1' ^DAY(11) TO SECOND^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL DAY\\(11\\) TO SECOND"); - expr("INTERVAL '1 1' DAY TO SECOND(10^)^") + expr("INTERVAL '1 1' ^DAY TO SECOND(10)^") .fails("Interval fractional second precision '10' out of range for " + "INTERVAL DAY TO SECOND\\(10\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0 0:0:0' DAY(0) TO ^SECOND^") + expr("INTERVAL '0 0:0:0' ^DAY(0) TO SECOND^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL DAY\\(0\\) TO SECOND"); - expr("INTERVAL '0 0:0:0' DAY TO SECOND(0^)^") + expr("INTERVAL '0 0:0:0' ^DAY TO SECOND(0)^") .fails("Interval fractional second precision '0' out of range for " + "INTERVAL DAY TO SECOND\\(0\\)"); } @@ -3066,7 +3066,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalHourNegative() { + void subTestIntervalHourNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' HOUR") .fails("Illegal interval literal format '-' for INTERVAL HOUR.*"); @@ -3110,14 +3110,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { + "HOUR\\(10\\) field.*"); // precision > maximum - expr("INTERVAL '1' HOUR(11^)^") + expr("INTERVAL '1' ^HOUR(11)^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL HOUR\\(11\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' HOUR(0^)^") + expr("INTERVAL '0' ^HOUR(0)^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL HOUR\\(0\\)"); } @@ -3129,7 +3129,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalHourToMinuteNegative() { + void subTestIntervalHourToMinuteNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL ':' HOUR TO MINUTE") .fails("Illegal interval literal format ':' for INTERVAL HOUR TO MINUTE"); @@ -3175,14 +3175,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Illegal interval literal format '1:60' for INTERVAL HOUR TO MINUTE.*"); // precision > maximum - expr("INTERVAL '1:1' HOUR(11) TO ^MINUTE^") + expr("INTERVAL '1:1' ^HOUR(11) TO MINUTE^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL HOUR\\(11\\) TO MINUTE"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0:0' HOUR(0) TO ^MINUTE^") + expr("INTERVAL '0:0' ^HOUR(0) TO MINUTE^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL HOUR\\(0\\) TO MINUTE"); } @@ -3194,7 +3194,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalHourToSecondNegative() { + void subTestIntervalHourToSecondNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '::' HOUR TO SECOND") .fails("Illegal interval literal format '::' for INTERVAL HOUR TO SECOND"); @@ -3270,20 +3270,20 @@ class SqlValidatorTest extends SqlValidatorTestCase { + "INTERVAL HOUR TO SECOND\\(3\\).*"); // precision > maximum - expr("INTERVAL '1:1:1' HOUR(11) TO ^SECOND^") + expr("INTERVAL '1:1:1' ^HOUR(11) TO SECOND^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL HOUR\\(11\\) TO SECOND"); - expr("INTERVAL '1:1:1' HOUR TO SECOND(10^)^") + expr("INTERVAL '1:1:1' ^HOUR TO SECOND(10)^") .fails("Interval fractional second precision '10' out of range for " + "INTERVAL HOUR TO SECOND\\(10\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0:0:0' HOUR(0) TO ^SECOND^") + expr("INTERVAL '0:0:0' ^HOUR(0) TO SECOND^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL HOUR\\(0\\) TO SECOND"); - expr("INTERVAL '0:0:0' HOUR TO SECOND(0^)^") + expr("INTERVAL '0:0:0' ^HOUR TO SECOND(0)^") .fails("Interval fractional second precision '0' out of range for " + "INTERVAL HOUR TO SECOND\\(0\\)"); } @@ -3295,7 +3295,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalMinuteNegative() { + void subTestIntervalMinuteNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL '-' MINUTE") .fails("Illegal interval literal format '-' for INTERVAL MINUTE.*"); @@ -3332,14 +3332,14 @@ class SqlValidatorTest extends SqlValidatorTestCase { .fails("Interval field value -2,147,483,648 exceeds precision of MINUTE\\(10\\) field.*"); // precision > maximum - expr("INTERVAL '1' MINUTE(11^)^") + expr("INTERVAL '1' ^MINUTE(11)^") .fails("Interval leading field precision '11' out of range for " + "INTERVAL MINUTE\\(11\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' MINUTE(0^)^") + expr("INTERVAL '0' ^MINUTE(0)^") .fails("Interval leading field precision '0' out of range for " + "INTERVAL MINUTE\\(0\\)"); } @@ -3351,7 +3351,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalMinuteToSecondNegative() { + void subTestIntervalMinuteToSecondNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL ':' MINUTE TO SECOND") .fails("Illegal interval literal format ':' for INTERVAL MINUTE TO SECOND"); @@ -3414,20 +3414,20 @@ class SqlValidatorTest extends SqlValidatorTestCase { + " INTERVAL MINUTE TO SECOND\\(3\\).*"); // precision > maximum - expr("INTERVAL '1:1' MINUTE(11) TO ^SECOND^") + expr("INTERVAL '1:1' ^MINUTE(11) TO SECOND^") .fails("Interval leading field precision '11' out of range for" + " INTERVAL MINUTE\\(11\\) TO SECOND"); - expr("INTERVAL '1:1' MINUTE TO SECOND(10^)^") + expr("INTERVAL '1:1' ^MINUTE TO SECOND(10)^") .fails("Interval fractional second precision '10' out of range for" + " INTERVAL MINUTE TO SECOND\\(10\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0:0' MINUTE(0) TO ^SECOND^") + expr("INTERVAL '0:0' ^MINUTE(0) TO SECOND^") .fails("Interval leading field precision '0' out of range for" + " INTERVAL MINUTE\\(0\\) TO SECOND"); - expr("INTERVAL '0:0' MINUTE TO SECOND(0^)^") + expr("INTERVAL '0:0' ^MINUTE TO SECOND(0)^") .fails("Interval fractional second precision '0' out of range for" + " INTERVAL MINUTE TO SECOND\\(0\\)"); } @@ -3439,7 +3439,7 @@ class SqlValidatorTest extends SqlValidatorTestCase { * Similarly, any changes to tests here should be echoed appropriately to * each of the other 12 subTestIntervalXXXNegative() tests. */ - public void subTestIntervalSecondNegative() { + void subTestIntervalSecondNegative() { // Qualifier - field mismatches wholeExpr("INTERVAL ':' SECOND") .fails("Illegal interval literal format ':' for INTERVAL SECOND.*"); @@ -3491,20 +3491,20 @@ class SqlValidatorTest extends SqlValidatorTestCase { + " INTERVAL SECOND\\(2, 9\\).*"); // precision > maximum - expr("INTERVAL '1' SECOND(11^)^") + expr("INTERVAL '1' ^SECOND(11)^") .fails("Interval leading field precision '11' out of range for" + " INTERVAL SECOND\\(11\\)"); - expr("INTERVAL '1.1' SECOND(1, 10^)^") + expr("INTERVAL '1.1' ^SECOND(1, 10)^") .fails("Interval fractional second precision '10' out of range for" + " INTERVAL SECOND\\(1, 10\\)"); // precision < minimum allowed) // note: parser will catch negative values, here we // just need to check for 0 - expr("INTERVAL '0' SECOND(0^)^") + expr("INTERVAL '0' ^SECOND(0)^") .fails("Interval leading field precision '0' out of range for" + " INTERVAL SECOND\\(0\\)"); - expr("INTERVAL '0' SECOND(1, 0^)^") + expr("INTERVAL '0' ^SECOND(1, 0)^") .fails("Interval fractional second precision '0' out of range for" + " INTERVAL SECOND\\(1, 0\\)"); } @@ -3583,6 +3583,31 @@ class SqlValidatorTest extends SqlValidatorTestCase { .columnType("INTERVAL MONTH(3) NOT NULL"); } + @Test void testIntervalExpression() { + expr("interval 1 hour").columnType("INTERVAL HOUR NOT NULL"); + expr("interval (2 + 3) month").columnType("INTERVAL MONTH NOT NULL"); + expr("interval (cast(null as integer)) year").columnType("INTERVAL YEAR"); + expr("interval (cast(null as integer)) year(2)") + .columnType("INTERVAL YEAR(2)"); + expr("interval (date '1970-01-01') hour").withWhole(true) + .fails("Cannot apply 'INTERVAL' to arguments of type " + + "'INTERVAL <DATE> <INTERVAL HOUR>'\\. Supported form\\(s\\): " + + "'INTERVAL <NUMERIC> <DATETIME_INTERVAL>'"); + expr("interval (nullif(true, true)) hour").withWhole(true) + .fails("Cannot apply 'INTERVAL' to arguments of type " + + "'INTERVAL <BOOLEAN> <INTERVAL HOUR>'\\. Supported form\\(s\\): " + + "'INTERVAL <NUMERIC> <DATETIME_INTERVAL>'"); + expr("interval (interval '1' day) hour").withWhole(true) + .fails("Cannot apply 'INTERVAL' to arguments of type " + + "'INTERVAL <INTERVAL DAY> <INTERVAL HOUR>'\\. " + + "Supported form\\(s\\): " + + "'INTERVAL <NUMERIC> <DATETIME_INTERVAL>'"); + sql("select interval empno hour as h from emp") + .columnType("INTERVAL HOUR NOT NULL"); + sql("select interval emp.mgr hour as h from emp") + .columnType("INTERVAL HOUR"); + } + @Test void testIntervalOperators() { expr("interval '1' hour + TIME '8:8:8'") .columnType("TIME(0) NOT NULL"); diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index 53ef390..178e2e6 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -2339,6 +2339,17 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ]]> </Resource> </TestCase> + <TestCase name="testIntervalExpression"> + <Resource name="sql"> + <![CDATA[select interval mgr hour as h from emp]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(H=[*($3, 3600000:INTERVAL HOUR)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testLateralDecorrelate"> <Resource name="sql"> <![CDATA[select * from emp, @@ -3743,7 +3754,6 @@ LogicalProject(DEPTNO=[$7]) ]]> </Resource> </TestCase> - <TestCase name="testSubQueryAggregateFunctionFollowedBySimpleOperation"> <Resource name="sql"> <![CDATA[select deptno diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index d30fd56..0980e04 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -1394,6 +1394,33 @@ from "scott".dept; !ok +# [CALCITE-4091] Interval expressions +select empno, mgr, date '1970-01-01' + interval empno day as d, + timestamp '1970-01-01 00:00:00' + interval (mgr / 100) minute as ts +from "scott".emp +order by empno; ++-------+------+------------+---------------------+ +| EMPNO | MGR | D | TS | ++-------+------+------------+---------------------+ +| 7369 | 7902 | 1990-03-06 | 1970-01-01 01:19:00 | +| 7499 | 7698 | 1990-07-14 | 1970-01-01 01:16:00 | +| 7521 | 7698 | 1990-08-05 | 1970-01-01 01:16:00 | +| 7566 | 7839 | 1990-09-19 | 1970-01-01 01:18:00 | +| 7654 | 7698 | 1990-12-16 | 1970-01-01 01:16:00 | +| 7698 | 7839 | 1991-01-29 | 1970-01-01 01:18:00 | +| 7782 | 7839 | 1991-04-23 | 1970-01-01 01:18:00 | +| 7788 | 7566 | 1991-04-29 | 1970-01-01 01:15:00 | +| 7839 | | 1991-06-19 | | +| 7844 | 7698 | 1991-06-24 | 1970-01-01 01:16:00 | +| 7876 | 7788 | 1991-07-26 | 1970-01-01 01:17:00 | +| 7900 | 7698 | 1991-08-19 | 1970-01-01 01:16:00 | +| 7902 | 7566 | 1991-08-21 | 1970-01-01 01:15:00 | +| 7934 | 7782 | 1991-09-22 | 1970-01-01 01:17:00 | ++-------+------+------------+---------------------+ +(14 rows) + +!ok + # [CALCITE-1486] Invalid "Invalid literal" error for complex expression select 8388608/(60+27.39); +-------------------+
