Repository: calcite Updated Branches: refs/heads/master e9d0ca673 -> feff5964b
[CALCITE-715] Add PERIOD type constructor and period operators (CONTAINS, PRECEDES, etc.) Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/feff5964 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/feff5964 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/feff5964 Branch: refs/heads/master Commit: feff5964bf36d2e5aa311fcd7777b159aa4a8a1e Parents: c94a080 Author: Julian Hyde <[email protected]> Authored: Wed Apr 19 21:10:00 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Wed Apr 26 14:21:11 2017 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 170 +++++++++++-------- .../java/org/apache/calcite/sql/SqlKind.java | 41 ++++- .../calcite/sql/fun/SqlOverlapsOperator.java | 130 +++++--------- .../calcite/sql/fun/SqlStdOperatorTable.java | 20 ++- .../apache/calcite/sql/type/OperandTypes.java | 65 +++++++ .../sql2rel/StandardConvertletTable.java | 95 ++++++++--- .../calcite/sql/parser/SqlParserTest.java | 77 ++++++--- .../apache/calcite/sql/test/SqlAdvisorTest.java | 7 + .../calcite/sql/test/SqlOperatorBaseTest.java | 122 ++++++++++++- .../calcite/sql/test/SqlPrettyWriterTest.java | 2 +- .../apache/calcite/test/SqlValidatorTest.java | 153 ++++++++++++++++- core/src/test/resources/sql/misc.iq | 32 +++- site/_docs/reference.md | 155 ++++++++++++++++- 13 files changed, 856 insertions(+), 213 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index a6672db..6b63d28 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -621,13 +621,17 @@ JAVACODE boolean matchesPrefix(int[] seq, int[][] prefixes) * * <p>Postgres syntax for limit: * + * <blockquote><pre> * [ LIMIT { count | ALL } ] - * [ OFFSET start ] + * [ OFFSET start ]</pre> + * </blockquote> * * <p>SQL:2008 syntax for limit: * + * <blockquote><pre> * [ OFFSET start { ROW | ROWS } ] - * [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] + * [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]</pre> + * </blockquote> */ SqlNode OrderedQueryOrExpr(ExprContext exprContext) : { @@ -733,14 +737,18 @@ SqlNode ParenthesizedExpression(ExprContext exprContext) : * <p>REVIEW jvs 8-Feb-2004: There's a small hole in this production. It can be * used to construct something like * - * <code>WHERE x IN (select count(*) from t where c=d,5)</code>, + * <blockquote><pre> + * WHERE x IN (select count(*) from t where c=d,5)</pre> + * </blockquote> * - * which should be illegal. The above is interpreted as equivalent to + * <p>which should be illegal. The above is interpreted as equivalent to * - * <code>WHERE x IN ((select count(*) from t where c=d),5)</code>, + * <blockquote><pre> + * <code>WHERE x IN ((select count(*) from t where c=d),5)</pre> + * </blockquote> * - * which is a legal use of a sub-query. The only way to fix the hole is to be - * able to remember whether a subexpression was parenthesized or not, which + * <p>which is a legal use of a sub-query. The only way to fix the hole is to + * be able to remember whether a subexpression was parenthesized or not, which * means preserving parentheses in the SqlNode tree. This is probably * desirable anyway for use in purely syntactic parsing applications (e.g. SQL * pretty-printer). However, if this is done, it's important to also make @@ -2028,7 +2036,7 @@ SqlNode TableFunctionCall(SqlParserPos pos) : * Abstract production: * SqlNode ExtendedTableRef() * - * Allows parser to be extended with new types of table references. The + * <p>Allows parser to be extended with new types of table references. The * default implementation of this production is empty. */ @@ -3120,14 +3128,13 @@ List<Object> Expression2(ExprContext exprContext) : */ SqlNode Expression3(ExprContext exprContext) : { - SqlNode e; - SqlNodeList list; - SqlNodeList list1; - SqlNodeList list2; - SqlPrefixOperator op; - boolean rowSeen = false; - SqlParserPos pos; - SqlParserPos prefixRowOpPos; + final SqlNode e; + final SqlNodeList list; + final SqlNodeList list1; + final SqlNodeList list2; + final SqlOperator op; + final SqlParserPos pos; + SqlParserPos rowPos = null; } { LOOKAHEAD(2) @@ -3152,25 +3159,22 @@ SqlNode Expression3(ExprContext exprContext) : return SqlStdOperatorTable.ROW.createCall(list); } | - { - pos = getPos(); - } [ - <ROW> - { - pos = getPos(); rowSeen = true; - } + <ROW> { rowPos = getPos(); } ] list1 = ParenthesizedQueryOrCommaList(exprContext) { - if (rowSeen) { + pos = list1.getParserPosition(); + if (rowPos != null) { // interpret as row constructor - return SqlStdOperatorTable.ROW.createCall(pos, list1.toArray()); - + return SqlStdOperatorTable.ROW.createCall(rowPos.plus(pos), + list1.toArray()); } } [ + LOOKAHEAD(2) + /* TODO: ( - <OVERLAPS> + op = periodOperator() list2 = ParenthesizedQueryOrCommaList(exprContext) { if (list1.size() != 2 || list2.size() != 2) { @@ -3182,12 +3186,13 @@ SqlNode Expression3(ExprContext exprContext) : for (SqlNode node : list2) { list1.add(node); } - return SqlStdOperatorTable.OVERLAPS.createCall( + return op.createCall( list1.getParserPosition().plus(list2.getParserPosition()), list1.toArray()); } ) | + */ ( e = IntervalQualifier() { @@ -3217,11 +3222,29 @@ SqlNode Expression3(ExprContext exprContext) : return list1.get(0); } else { // interpret as row constructor - return SqlStdOperatorTable.ROW.createCall(pos, list1.toArray()); + return SqlStdOperatorTable.ROW.createCall( + pos.plus(list1.getParserPosition()), list1.toArray()); } } } +SqlOperator periodOperator() : +{ +} +{ + <OVERLAPS> { return SqlStdOperatorTable.OVERLAPS; } +| + <IMMEDIATELY> <PRECEDES> { return SqlStdOperatorTable.IMMEDIATELY_PRECEDES; } +| + <PRECEDES> { return SqlStdOperatorTable.PRECEDES; } +| + <IMMEDIATELY> <SUCCEEDS> { return SqlStdOperatorTable.IMMEDIATELY_SUCCEEDS; } +| + <SUCCEEDS> { return SqlStdOperatorTable.SUCCEEDS; } +| + <EQUALS> { return SqlStdOperatorTable.PERIOD_EQUALS; } +} + /** * Parses a COLLATE clause */ @@ -3260,6 +3283,8 @@ SqlNode AtomicRowExpression() : | e = MapConstructor() { return e; } | + e = PeriodConstructor() { return e; } + | // NOTE jvs 18-Jan-2005: use syntactic lookahead to discriminate // compound identifiers from function calls in which the function // name is a compound identifier @@ -3825,7 +3850,7 @@ SqlNode ArrayConstructor() : ) } -/** Parses an MAP constructor */ +/** Parses a MAP constructor */ SqlNode MapConstructor() : { SqlNodeList args; @@ -3860,6 +3885,23 @@ SqlNode MapConstructor() : ) } +/** Parses a PERIOD constructor */ +SqlNode PeriodConstructor() : +{ + final SqlNode e0, e1; + final SqlParserPos pos; +} +{ + <PERIOD> { pos = getPos(); } + <LPAREN> + e0 = Expression(ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + e1 = Expression(ExprContext.ACCEPT_SUB_QUERY) + <RPAREN> { + return SqlStdOperatorTable.ROW.createCall(pos.plus(getPos()), e0, e1); + } +} + /** * Parses an interval literal. */ @@ -5387,50 +5429,38 @@ SqlBinaryOperator BinaryRowOperator() : SqlBinaryOperator op; } { - <EQ> - { return SqlStdOperatorTable.EQUALS; } - | <GT> - { return SqlStdOperatorTable.GREATER_THAN; } - | <LT> - { return SqlStdOperatorTable.LESS_THAN; } - | <LE> - { return SqlStdOperatorTable.LESS_THAN_OR_EQUAL; } - | <GE> - { return SqlStdOperatorTable.GREATER_THAN_OR_EQUAL; } - | <NE> - { return SqlStdOperatorTable.NOT_EQUALS; } - | <NE2> - { + // <IN> is handled as a special case + <EQ> { return SqlStdOperatorTable.EQUALS; } + | <GT> { return SqlStdOperatorTable.GREATER_THAN; } + | <LT> { return SqlStdOperatorTable.LESS_THAN; } + | <LE> { return SqlStdOperatorTable.LESS_THAN_OR_EQUAL; } + | <GE> { return SqlStdOperatorTable.GREATER_THAN_OR_EQUAL; } + | <NE> { return SqlStdOperatorTable.NOT_EQUALS; } + | <NE2> { if (!this.conformance.isBangEqualAllowed()) { throw new ParseException(RESOURCE.bangEqualNotAllowed().str()); } return SqlStdOperatorTable.NOT_EQUALS; } - | <PLUS> - { return SqlStdOperatorTable.PLUS; } - | <MINUS> - { return SqlStdOperatorTable.MINUS; } - | <STAR> - { return SqlStdOperatorTable.MULTIPLY; } - | <SLASH> - { return SqlStdOperatorTable.DIVIDE; } - | <CONCAT> - { return SqlStdOperatorTable.CONCAT; } - | <AND> - { return SqlStdOperatorTable.AND; } - | <OR> - { return SqlStdOperatorTable.OR; } - | LOOKAHEAD(2) <IS> <DISTINCT> <FROM> - { return SqlStdOperatorTable.IS_DISTINCT_FROM; } - | <IS> <NOT> <DISTINCT> <FROM> - { return SqlStdOperatorTable.IS_NOT_DISTINCT_FROM; } - // <IN> is handled as a special case - | <MEMBER> <OF> - { return SqlStdOperatorTable.MEMBER_OF; } - | <SUBMULTISET> <OF> - { return SqlStdOperatorTable.SUBMULTISET_OF; } - | op = BinaryMultisetOperator() - { return op; } + | <PLUS> { return SqlStdOperatorTable.PLUS; } + | <MINUS> { return SqlStdOperatorTable.MINUS; } + | <STAR> { return SqlStdOperatorTable.MULTIPLY; } + | <SLASH> { return SqlStdOperatorTable.DIVIDE; } + | <CONCAT> { return SqlStdOperatorTable.CONCAT; } + | <AND> { return SqlStdOperatorTable.AND; } + | <OR> { return SqlStdOperatorTable.OR; } + | LOOKAHEAD(2) <IS> <DISTINCT> <FROM> { return SqlStdOperatorTable.IS_DISTINCT_FROM; } + | <IS> <NOT> <DISTINCT> <FROM> { return SqlStdOperatorTable.IS_NOT_DISTINCT_FROM; } + | <MEMBER> <OF> { return SqlStdOperatorTable.MEMBER_OF; } + | <SUBMULTISET> <OF> { return SqlStdOperatorTable.SUBMULTISET_OF; } + | <CONTAINS> { return SqlStdOperatorTable.CONTAINS; } + | <OVERLAPS> { return SqlStdOperatorTable.OVERLAPS; } + | <EQUALS> { return SqlStdOperatorTable.PERIOD_EQUALS; } + | <PRECEDES> { return SqlStdOperatorTable.PRECEDES; } + | <SUCCEEDS> { return SqlStdOperatorTable.SUCCEEDS; } + | <IMMEDIATELY> <PRECEDES> { return SqlStdOperatorTable.IMMEDIATELY_PRECEDES; } + | <IMMEDIATELY> <SUCCEEDS> { return SqlStdOperatorTable.IMMEDIATELY_SUCCEEDS; } + | op = BinaryMultisetOperator() { return op; } } /** @@ -5707,6 +5737,7 @@ SqlPostfixOperator PostfixRowOperator() : | < HOUR: "HOUR" > | < IDENTITY: "IDENTITY" > | < IMMEDIATE: "IMMEDIATE" > + | < IMMEDIATELY: "IMMEDIATELY" > | < IMPLEMENTATION: "IMPLEMENTATION" > | < IMPORT: "IMPORT" > | < IN: "IN" > @@ -6237,6 +6268,7 @@ String CommonNonReservedKeyWord() : | <GRANTED> | <HIERARCHY> | <IMMEDIATE> + | <IMMEDIATELY> | <IMPLEMENTATION> | <INCLUDING> | <INCREMENT> http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- 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 07164b3..4147c9a 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -332,11 +332,41 @@ public enum SqlKind { DOT, /** - * The "OVERLAPS" operator. + * The "OVERLAPS" operator for periods. */ OVERLAPS, /** + * The "CONTAINS" operator for periods. + */ + CONTAINS, + + /** + * The "PRECEDES" operator for periods. + */ + PRECEDES, + + /** + * The "IMMEDIATELY PRECEDES" operator for periods. + */ + IMMEDIATELY_PRECEDES("IMMEDIATELY PRECEDES"), + + /** + * The "SUCCEEDS" operator for periods. + */ + SUCCEEDS, + + /** + * The "IMMEDIATELY SUCCEEDS" operator for periods. + */ + IMMEDIATELY_SUCCEEDS("IMMEDIATELY SUCCEEDS"), + + /** + * The "EQUALS" operator for periods. + */ + PERIOD_EQUALS("EQUALS"), + + /** * The "LIKE" operator. */ LIKE, @@ -1084,6 +1114,15 @@ public enum SqlKind { /** Lower-case name. */ public final String lowerName = name().toLowerCase(Locale.ROOT); + public final String sql; + + SqlKind() { + sql = name(); + } + + SqlKind(String sql) { + this.sql = sql; + } /** Returns the kind that corresponds to this operator but in the opposite * direction. Or returns this, if this kind is not reversible. http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/java/org/apache/calcite/sql/fun/SqlOverlapsOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlOverlapsOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlOverlapsOperator.java index 941a06e..0ca5e54 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlOverlapsOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlOverlapsOperator.java @@ -17,20 +17,19 @@ package org.apache.calcite.sql.fun; import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlOperandCountRange; -import org.apache.calcite.sql.SqlSpecialOperator; import org.apache.calcite.sql.SqlUtil; 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.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; +import org.apache.calcite.sql.type.SqlSingleOperandTypeChecker; import org.apache.calcite.sql.type.SqlTypeUtil; -import org.apache.calcite.sql.validate.SqlValidator; -import org.apache.calcite.sql.validate.SqlValidatorScope; import com.google.common.collect.ImmutableList; @@ -38,52 +37,44 @@ import com.google.common.collect.ImmutableList; * SqlOverlapsOperator represents the SQL:1999 standard {@code OVERLAPS} * function. Determines whether two anchored time intervals overlap. */ -public class SqlOverlapsOperator extends SqlSpecialOperator { - //~ Static fields/initializers --------------------------------------------- - - private static final SqlWriter.FrameType FRAME_TYPE = - SqlWriter.FrameTypeEnum.create("OVERLAPS"); - +public class SqlOverlapsOperator extends SqlBinaryOperator { //~ Constructors ----------------------------------------------------------- - public SqlOverlapsOperator() { - super("OVERLAPS", - SqlKind.OVERLAPS, - 30, - true, - ReturnTypes.BOOLEAN_NULLABLE, + SqlOverlapsOperator(SqlKind kind) { + super(kind.sql, kind, 30, true, ReturnTypes.BOOLEAN_NULLABLE, InferTypes.FIRST_KNOWN, - null); + OperandTypes.sequence("'<PERIOD> " + kind.sql + " <PERIOD>'", + OperandTypes.PERIOD, OperandTypes.PERIOD)); } //~ Methods ---------------------------------------------------------------- - public void unparse( - SqlWriter writer, - SqlCall call, - int leftPrec, + @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { final SqlWriter.Frame frame = - writer.startList(FRAME_TYPE, "(", ")"); - call.operand(0).unparse(writer, leftPrec, rightPrec); - writer.sep(",", true); - call.operand(1).unparse(writer, leftPrec, rightPrec); - writer.sep(")", true); + writer.startList(SqlWriter.FrameTypeEnum.SIMPLE); + arg(writer, call, leftPrec, rightPrec, 0); writer.sep(getName()); - writer.sep("(", true); - call.operand(2).unparse(writer, leftPrec, rightPrec); - writer.sep(",", true); - call.operand(3).unparse(writer, leftPrec, rightPrec); + arg(writer, call, leftPrec, rightPrec, 1); writer.endList(frame); } - public SqlOperandCountRange getOperandCountRange() { - return SqlOperandCountRanges.of(4); + void arg(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec, int i) { + if (SqlUtil.isCallTo(call.operand(i), SqlStdOperatorTable.ROW)) { + SqlCall row = call.operand(i); + writer.keyword("PERIOD"); + writer.sep("(", true); + row.operand(0).unparse(writer, leftPrec, rightPrec); + writer.sep(",", true); + row.operand(1).unparse(writer, leftPrec, rightPrec); + writer.sep(")", true); + } else { + call.operand(i).unparse(writer, leftPrec, rightPrec); + } } - public String getSignatureTemplate(int operandsCount) { - assert 4 == operandsCount; - return "({1}, {2}) {0} ({3}, {4})"; + public SqlOperandCountRange getOperandCountRange() { + return SqlOperandCountRanges.of(2); } public String getAllowedSignatures(String opName) { @@ -108,69 +99,36 @@ public class SqlOverlapsOperator extends SqlSpecialOperator { return ret.toString(); } - public boolean checkOperandTypes( - SqlCallBinding callBinding, + public boolean checkOperandTypes(SqlCallBinding callBinding, boolean throwOnFailure) { - SqlValidator validator = callBinding.getValidator(); - SqlValidatorScope scope = callBinding.getScope(); - if (!OperandTypes.DATETIME.checkSingleOperandType( - callBinding, - callBinding.operand(0), - 0, - throwOnFailure)) { + if (!OperandTypes.PERIOD.checkSingleOperandType(callBinding, + callBinding.operand(0), 0, throwOnFailure)) { return false; } - if (!OperandTypes.DATETIME.checkSingleOperandType( - callBinding, - callBinding.operand(2), - 0, - throwOnFailure)) { - return false; + final SqlSingleOperandTypeChecker rightChecker; + switch (kind) { + case CONTAINS: + rightChecker = OperandTypes.PERIOD_OR_DATETIME; + break; + default: + rightChecker = OperandTypes.PERIOD; + break; } - - RelDataType t0 = validator.deriveType(scope, callBinding.operand(0)); - RelDataType t1 = validator.deriveType(scope, callBinding.operand(1)); - RelDataType t2 = validator.deriveType(scope, callBinding.operand(2)); - RelDataType t3 = validator.deriveType(scope, callBinding.operand(3)); - - // t0 must be comparable with t2 - if (!SqlTypeUtil.sameNamedType(t0, t2)) { - if (throwOnFailure) { - throw callBinding.newValidationSignatureError(); - } + if (!rightChecker.checkSingleOperandType(callBinding, + callBinding.operand(1), 0, throwOnFailure)) { return false; } - - if (SqlTypeUtil.isDatetime(t1)) { - // if t1 is of DATETIME, - // then t1 must be comparable with t0 - if (!SqlTypeUtil.sameNamedType(t0, t1)) { + final RelDataType t0 = callBinding.getOperandType(0); + final RelDataType t1 = callBinding.getOperandType(1); + if (!SqlTypeUtil.isDatetime(t1)) { + final RelDataType t00 = t0.getFieldList().get(0).getType(); + final RelDataType t10 = t1.getFieldList().get(0).getType(); + if (!SqlTypeUtil.sameNamedType(t00, t10)) { if (throwOnFailure) { throw callBinding.newValidationSignatureError(); } return false; } - } else if (!SqlTypeUtil.isInterval(t1)) { - if (throwOnFailure) { - throw callBinding.newValidationSignatureError(); - } - return false; - } - - if (SqlTypeUtil.isDatetime(t3)) { - // if t3 is of DATETIME, - // then t3 must be comparable with t2 - if (!SqlTypeUtil.sameNamedType(t2, t3)) { - if (throwOnFailure) { - throw callBinding.newValidationSignatureError(); - } - return false; - } - } else if (!SqlTypeUtil.isInterval(t3)) { - if (throwOnFailure) { - throw callBinding.newValidationSignatureError(); - } - return false; } return true; } http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- 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 8d385a6..bd63b91 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 @@ -1048,7 +1048,25 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlCollectionTableOperator("TABLE", SqlModality.RELATION); public static final SqlOverlapsOperator OVERLAPS = - new SqlOverlapsOperator(); + new SqlOverlapsOperator(SqlKind.OVERLAPS); + + public static final SqlOverlapsOperator CONTAINS = + new SqlOverlapsOperator(SqlKind.CONTAINS); + + public static final SqlOverlapsOperator PRECEDES = + new SqlOverlapsOperator(SqlKind.PRECEDES); + + public static final SqlOverlapsOperator IMMEDIATELY_PRECEDES = + new SqlOverlapsOperator(SqlKind.IMMEDIATELY_PRECEDES); + + public static final SqlOverlapsOperator SUCCEEDS = + new SqlOverlapsOperator(SqlKind.SUCCEEDS); + + public static final SqlOverlapsOperator IMMEDIATELY_SUCCEEDS = + new SqlOverlapsOperator(SqlKind.IMMEDIATELY_SUCCEEDS); + + public static final SqlOverlapsOperator PERIOD_EQUALS = + new SqlOverlapsOperator(SqlKind.PERIOD_EQUALS); public static final SqlSpecialOperator VALUES = new SqlValuesOperator(); http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java ---------------------------------------------------------------------- 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 ddddd08..15d3b70 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 @@ -233,6 +233,12 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker INTERVAL = family(SqlTypeFamily.DATETIME_INTERVAL); + public static final SqlSingleOperandTypeChecker PERIOD = + new PeriodOperandTypeChecker(); + + public static final SqlSingleOperandTypeChecker PERIOD_OR_DATETIME = + or(PERIOD, DATETIME); + public static final FamilyOperandTypeChecker INTERVAL_INTERVAL = family(SqlTypeFamily.DATETIME_INTERVAL, SqlTypeFamily.DATETIME_INTERVAL); @@ -621,6 +627,65 @@ public abstract class OperandTypes { return Consistency.NONE; } }; + + /** Operand type checker that accepts period types: + * PERIOD (DATETIME, DATETIME) + * PERIOD (DATETIME, INTERVAL) + * [ROW] (DATETIME, DATETIME) + * [ROW] (DATETIME, INTERVAL) */ + private static class PeriodOperandTypeChecker + implements SqlSingleOperandTypeChecker { + public boolean checkSingleOperandType(SqlCallBinding callBinding, + SqlNode node, int iFormalOperand, boolean throwOnFailure) { + assert 0 == iFormalOperand; + RelDataType type = + callBinding.getValidator().deriveType(callBinding.getScope(), node); + boolean valid = false; + if (type.isStruct() && type.getFieldList().size() == 2) { + final RelDataType t0 = type.getFieldList().get(0).getType(); + final RelDataType t1 = type.getFieldList().get(1).getType(); + if (SqlTypeUtil.isDatetime(t0)) { + if (SqlTypeUtil.isDatetime(t1)) { + // t0 must be comparable with t1; (DATE, TIMESTAMP) is not valid + if (SqlTypeUtil.sameNamedType(t0, t1)) { + valid = true; + } + } else if (SqlTypeUtil.isInterval(t1)) { + valid = true; + } + } + } + + if (!valid && throwOnFailure) { + throw callBinding.newValidationSignatureError(); + } + return valid; + } + + public boolean checkOperandTypes(SqlCallBinding callBinding, + boolean throwOnFailure) { + return checkSingleOperandType(callBinding, callBinding.operand(0), 0, + throwOnFailure); + } + + public SqlOperandCountRange getOperandCountRange() { + return SqlOperandCountRanges.of(1); + } + + public String getAllowedSignatures(SqlOperator op, String opName) { + return SqlUtil.getAliasedSignature(op, opName, + ImmutableList.of("PERIOD (DATETIME, INTERVAL)", + "PERIOD (DATETIME, DATETIME)")); + } + + public boolean isOptional(int i) { + return false; + } + + public Consistency getConsistency() { + return Consistency.NONE; + } + } } // End OperandTypes.java http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java ---------------------------------------------------------------------- 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 90f1ffc..3d9975c 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -32,7 +32,6 @@ import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexRangeRef; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.sql.SqlAggFunction; -import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDataTypeSpec; @@ -70,6 +69,7 @@ import org.apache.calcite.sql.type.SqlTypeFamily; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeUtil; import org.apache.calcite.sql.validate.SqlValidator; +import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; import com.google.common.collect.ImmutableList; @@ -365,6 +365,10 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { return rexBuilder.makeCall(SqlStdOperatorTable.OR, a0, a1); } + private RexNode eq(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, a0, a1); + } + private RexNode ge(RexBuilder rexBuilder, RexNode a0, RexNode a1) { return rexBuilder.makeCall(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, a0, a1); @@ -1192,35 +1196,76 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { SqlCall call) { // for intervals [t0, t1] overlaps [t2, t3], we can find if the // intervals overlaps by: ~(t1 < t2 or t3 < t0) - final SqlNode[] operands = ((SqlBasicCall) call).getOperands(); - assert operands.length == 4; - final SqlParserPos pos = call.getParserPosition(); - final RelDataType t1 = cx.getValidator().getValidatedNodeType(operands[1]); - if (SqlTypeUtil.isInterval(t1)) { - // make t1 = t0 + t1 when t1 is an interval. - operands[1] = plus(pos, operands[0], operands[1]); - } - final RelDataType t3 = cx.getValidator().getValidatedNodeType(operands[3]); - if (SqlTypeUtil.isInterval(t3)) { - // make t3 = t2 + t3 when t3 is an interval. - operands[3] = plus(pos, operands[2], operands[3]); - } + assert call.getOperandList().size() == 2; - final RexNode r0 = cx.convertExpression(operands[0]); - final RexNode r1 = cx.convertExpression(operands[1]); - final RexNode r2 = cx.convertExpression(operands[2]); - final RexNode r3 = cx.convertExpression(operands[3]); + final Pair<RexNode, RexNode> left = + convertOverlapsOperand(cx, call.getParserPosition(), call.operand(0)); + final RexNode r0 = left.left; + final RexNode r1 = left.right; + final Pair<RexNode, RexNode> right = + convertOverlapsOperand(cx, call.getParserPosition(), call.operand(1)); + final RexNode r2 = right.left; + final RexNode r3 = right.right; // Sort end points into start and end, such that (s0 <= e0) and (s1 <= e1). final RexBuilder rexBuilder = cx.getRexBuilder(); - final RexNode s0 = case_(rexBuilder, le(rexBuilder, r0, r1), r0, r1); - final RexNode e0 = case_(rexBuilder, le(rexBuilder, r0, r1), r1, r0); - final RexNode s1 = case_(rexBuilder, le(rexBuilder, r2, r3), r2, r3); - final RexNode e1 = case_(rexBuilder, le(rexBuilder, r2, r3), r3, r2); + RexNode leftSwap = le(rexBuilder, r0, r1); + final RexNode s0 = case_(rexBuilder, leftSwap, r0, r1); + final RexNode e0 = case_(rexBuilder, leftSwap, r1, r0); + RexNode rightSwap = le(rexBuilder, r2, r3); + final RexNode s1 = case_(rexBuilder, rightSwap, r2, r3); + final RexNode e1 = case_(rexBuilder, rightSwap, r3, r2); // (e0 >= s1) AND (e1 >= s0) - return and(rexBuilder, - ge(rexBuilder, e0, s1), - ge(rexBuilder, e1, s0)); + switch (op.kind) { + case OVERLAPS: + return and(rexBuilder, + ge(rexBuilder, e0, s1), + ge(rexBuilder, e1, s0)); + case CONTAINS: + return and(rexBuilder, + le(rexBuilder, s0, s1), + ge(rexBuilder, e0, e1)); + case PERIOD_EQUALS: + return and(rexBuilder, + eq(rexBuilder, s0, s1), + eq(rexBuilder, e0, e1)); + case PRECEDES: + return le(rexBuilder, e0, s1); + case IMMEDIATELY_PRECEDES: + return eq(rexBuilder, e0, s1); + case SUCCEEDS: + return ge(rexBuilder, s0, e1); + case IMMEDIATELY_SUCCEEDS: + return eq(rexBuilder, s0, e1); + default: + throw new AssertionError(op); + } + } + + private Pair<RexNode, RexNode> convertOverlapsOperand(SqlRexContext cx, + SqlParserPos pos, SqlNode operand) { + final SqlNode a0; + final SqlNode a1; + switch (operand.getKind()) { + case ROW: + a0 = ((SqlCall) operand).operand(0); + final SqlNode a10 = ((SqlCall) operand).operand(1); + final RelDataType t1 = cx.getValidator().getValidatedNodeType(a10); + if (SqlTypeUtil.isInterval(t1)) { + // make t1 = t0 + t1 when t1 is an interval. + a1 = plus(pos, a0, a10); + } else { + a1 = a10; + } + break; + default: + a0 = operand; + a1 = operand; + } + + final RexNode r0 = cx.convertExpression(a0); + final RexNode r1 = cx.convertExpression(a1); + return Pair.of(r0, r1); } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java ---------------------------------------------------------------------- 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 6fdb98d..613191d 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 @@ -1011,30 +1011,46 @@ public class SqlParserTest { + "FROM `SALES`.`DEPTS`) AS `T`"); } - @Test public void testOverlaps() { - checkExp( - "(x,xx) overlaps (y,yy)", - "((`X`, `XX`) OVERLAPS (`Y`, `YY`))"); + @Test public void testPeriod() { + // We don't have a PERIOD constructor currently; + // ROW constructor is sufficient for now. + checkExp("period (date '1969-01-05', interval '2-3' year to month)", + "(ROW(DATE '1969-01-05', INTERVAL '2-3' YEAR TO MONTH))"); + } - checkExp( - "(x,xx) overlaps (y,yy) or false", - "(((`X`, `XX`) OVERLAPS (`Y`, `YY`)) OR FALSE)"); + @Test public void testOverlaps() { + final String[] ops = { + "overlaps", "equals", "precedes", "succeeds", + "immediately precedes", "immediately succeeds" + }; + final String[] periods = {"period ", ""}; + for (String period : periods) { + for (String op : ops) { + checkPeriodPredicate(new Checker(op, period)); + } + } + } - checkExp( - "true and not (x,xx) overlaps (y,yy) or false", - "((TRUE AND (NOT ((`X`, `XX`) OVERLAPS (`Y`, `YY`)))) OR FALSE)"); + void checkPeriodPredicate(Checker checker) { + checker.checkExp("$p(x,xx) $op $p(y,yy)", + "(PERIOD (`X`, `XX`) $op PERIOD (`Y`, `YY`))"); - checkExpFails( - "^(x,xx,xxx) overlaps (y,yy)^ or false", - "(?s).*Illegal overlaps expression.*"); + checker.checkExp( + "$p(x,xx) $op $p(y,yy) or false", + "((PERIOD (`X`, `XX`) $op PERIOD (`Y`, `YY`)) OR FALSE)"); - checkExpFails( - "true or ^(x,xx,xxx) overlaps (y,yy,yyy)^ or false", - "(?s).*Illegal overlaps expression.*"); + checker.checkExp( + "true and not $p(x,xx) $op $p(y,yy) or false", + "((TRUE AND (NOT (PERIOD (`X`, `XX`) $op PERIOD (`Y`, `YY`)))) OR FALSE)"); - checkExpFails( - "^(x,xx) overlaps (y,yy,yyy)^ or false", - "(?s).*Illegal overlaps expression.*"); + if (checker.period.isEmpty()) { + checker.checkExp("$p(x,xx,xxx) $op $p(y,yy) or false", + "((PERIOD (`X`, `XX`) $op PERIOD (`Y`, `YY`)) OR FALSE)"); + } else { + // 3-argument rows are valid in the parser, rejected by the validator + checker.checkExpFails("$p(x,xx^,^xxx) $op $p(y,yy) or false", + "(?s).*Encountered \",\" at .*"); + } } @Test public void testIsDistinctFrom() { @@ -7971,6 +7987,29 @@ public class SqlParserTest { return this; } } + + /** Runs tests on period operators such as OVERLAPS, IMMEDIATELY PRECEDES. */ + private class Checker { + final String op; + final String period; + + Checker(String op, String period) { + this.op = op; + this.period = period; + } + + public void checkExp(String sql, String expected) { + SqlParserTest.this.checkExp( + sql.replace("$op", op).replace("$p", period), + expected.replace("$op", op.toUpperCase(Locale.ROOT))); + } + + public void checkExpFails(String sql, String expected) { + SqlParserTest.this.checkExpFails( + sql.replace("$op", op).replace("$p", period), + expected.replace("$op", op)); + } + } } // End SqlParserTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java index 356709b..085de9b 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java @@ -179,6 +179,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { "KEYWORD(OCTET_LENGTH)", "KEYWORD(OVERLAY)", "KEYWORD(PERCENT_RANK)", + "KEYWORD(PERIOD)", "KEYWORD(POSITION)", "KEYWORD(POWER)", "KEYWORD(PREV)", @@ -258,6 +259,9 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { "KEYWORD(>=)", "KEYWORD(AND)", "KEYWORD(BETWEEN)", + "KEYWORD(CONTAINS)", + "KEYWORD(EQUALS)", + "KEYWORD(IMMEDIATELY)", "KEYWORD(IN)", "KEYWORD(IS)", "KEYWORD(LIKE)", @@ -265,8 +269,11 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { "KEYWORD(MULTISET)", "KEYWORD(NOT)", "KEYWORD(OR)", + "KEYWORD(OVERLAPS)", + "KEYWORD(PRECEDES)", "KEYWORD(SIMILAR)", "KEYWORD(SUBMULTISET)", + "KEYWORD(SUCCEEDS)", "KEYWORD([)", "KEYWORD(||)"); http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index ff3c0e2..d3c4920 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -2353,7 +2353,15 @@ public abstract class SqlOperatorBaseTest { "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (cast(null as timestamp), interval '1 2:3:4.5' day to second)"); } - @Test public void testOverlapsEtc() { + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-715";>[CALCITE-715] + * Add PERIOD type constructor and period operators (CONTAINS, PRECEDES, + * etc.)</a>. + * + * <p>Tests OVERLAP and similar period operators CONTAINS, EQUALS, PRECEDES, + * SUCCEEDS, IMMEDIATELY PRECEDES, IMMEDIATELY SUCCEEDS for DATE, TIME and + * TIMESTAMP values. */ + @Test public void testPeriodOperators() { String[] times = { "TIME '01:00:00'", "TIME '02:00:00'", @@ -2378,6 +2386,7 @@ public abstract class SqlOperatorBaseTest { } private void checkOverlaps(OverlapChecker c) { + c.isTrue("($0,$0) OVERLAPS ($0,$0)"); c.isFalse("($0,$1) OVERLAPS ($2,$3)"); c.isTrue("($0,$1) OVERLAPS ($1,$2)"); c.isTrue("($0,$2) OVERLAPS ($1,$3)"); @@ -2387,6 +2396,117 @@ public abstract class SqlOperatorBaseTest { c.isTrue("($2,$3) OVERLAPS ($0,$2)"); c.isTrue("($2,$3) OVERLAPS ($2,$0)"); c.isTrue("($3,$2) OVERLAPS ($2,$0)"); + c.isTrue("($0,$2) OVERLAPS ($2,$0)"); + c.isTrue("($0,$3) OVERLAPS ($1,$3)"); + c.isTrue("($0,$3) OVERLAPS ($3,$3)"); + + c.isTrue("($0,$0) CONTAINS ($0,$0)"); + c.isFalse("($0,$1) CONTAINS ($2,$3)"); + c.isFalse("($0,$1) CONTAINS ($1,$2)"); + c.isFalse("($0,$2) CONTAINS ($1,$3)"); + c.isFalse("($0,$2) CONTAINS ($3,$1)"); + c.isFalse("($2,$0) CONTAINS ($3,$1)"); + c.isFalse("($3,$2) CONTAINS ($1,$0)"); + c.isFalse("($2,$3) CONTAINS ($0,$2)"); + c.isFalse("($2,$3) CONTAINS ($2,$0)"); + c.isFalse("($3,$2) CONTAINS ($2,$0)"); + c.isTrue("($0,$2) CONTAINS ($2,$0)"); + c.isTrue("($0,$3) CONTAINS ($1,$3)"); + c.isTrue("($0,$3) CONTAINS ($3,$3)"); + c.isTrue("($3,$0) CONTAINS ($3,$3)"); + c.isTrue("($3,$0) CONTAINS ($0,$0)"); + + c.isTrue("($0,$0) CONTAINS $0"); + c.isTrue("($3,$0) CONTAINS $0"); + c.isTrue("($3,$0) CONTAINS $1"); + c.isTrue("($3,$0) CONTAINS $2"); + c.isTrue("($3,$0) CONTAINS $3"); + c.isTrue("($0,$3) CONTAINS $0"); + c.isTrue("($0,$3) CONTAINS $1"); + c.isTrue("($0,$3) CONTAINS $2"); + c.isTrue("($0,$3) CONTAINS $3"); + c.isFalse("($1,$3) CONTAINS $0"); + c.isFalse("($1,$2) CONTAINS $3"); + + c.isTrue("($0,$0) EQUALS ($0,$0)"); + c.isFalse("($0,$1) EQUALS ($2,$3)"); + c.isFalse("($0,$1) EQUALS ($1,$2)"); + c.isFalse("($0,$2) EQUALS ($1,$3)"); + c.isFalse("($0,$2) EQUALS ($3,$1)"); + c.isFalse("($2,$0) EQUALS ($3,$1)"); + c.isFalse("($3,$2) EQUALS ($1,$0)"); + c.isFalse("($2,$3) EQUALS ($0,$2)"); + c.isFalse("($2,$3) EQUALS ($2,$0)"); + c.isFalse("($3,$2) EQUALS ($2,$0)"); + c.isTrue("($0,$2) EQUALS ($2,$0)"); + c.isFalse("($0,$3) EQUALS ($1,$3)"); + c.isFalse("($0,$3) EQUALS ($3,$3)"); + c.isFalse("($3,$0) EQUALS ($3,$3)"); + c.isFalse("($3,$0) EQUALS ($0,$0)"); + + c.isTrue("($0,$0) PRECEDES ($0,$0)"); + c.isTrue("($0,$1) PRECEDES ($2,$3)"); + c.isTrue("($0,$1) PRECEDES ($1,$2)"); + c.isFalse("($0,$2) PRECEDES ($1,$3)"); + c.isFalse("($0,$2) PRECEDES ($3,$1)"); + c.isFalse("($2,$0) PRECEDES ($3,$1)"); + c.isFalse("($3,$2) PRECEDES ($1,$0)"); + c.isFalse("($2,$3) PRECEDES ($0,$2)"); + c.isFalse("($2,$3) PRECEDES ($2,$0)"); + c.isFalse("($3,$2) PRECEDES ($2,$0)"); + c.isFalse("($0,$2) PRECEDES ($2,$0)"); + c.isFalse("($0,$3) PRECEDES ($1,$3)"); + c.isTrue("($0,$3) PRECEDES ($3,$3)"); + c.isTrue("($3,$0) PRECEDES ($3,$3)"); + c.isFalse("($3,$0) PRECEDES ($0,$0)"); + + c.isTrue("($0,$0) SUCCEEDS ($0,$0)"); + c.isFalse("($0,$1) SUCCEEDS ($2,$3)"); + c.isFalse("($0,$1) SUCCEEDS ($1,$2)"); + c.isFalse("($0,$2) SUCCEEDS ($1,$3)"); + c.isFalse("($0,$2) SUCCEEDS ($3,$1)"); + c.isFalse("($2,$0) SUCCEEDS ($3,$1)"); + c.isTrue("($3,$2) SUCCEEDS ($1,$0)"); + c.isTrue("($2,$3) SUCCEEDS ($0,$2)"); + c.isTrue("($2,$3) SUCCEEDS ($2,$0)"); + c.isTrue("($3,$2) SUCCEEDS ($2,$0)"); + c.isFalse("($0,$2) SUCCEEDS ($2,$0)"); + c.isFalse("($0,$3) SUCCEEDS ($1,$3)"); + c.isFalse("($0,$3) SUCCEEDS ($3,$3)"); + c.isFalse("($3,$0) SUCCEEDS ($3,$3)"); + c.isTrue("($3,$0) SUCCEEDS ($0,$0)"); + + c.isTrue("($0,$0) IMMEDIATELY PRECEDES ($0,$0)"); + c.isFalse("($0,$1) IMMEDIATELY PRECEDES ($2,$3)"); + c.isTrue("($0,$1) IMMEDIATELY PRECEDES ($1,$2)"); + c.isFalse("($0,$2) IMMEDIATELY PRECEDES ($1,$3)"); + c.isFalse("($0,$2) IMMEDIATELY PRECEDES ($3,$1)"); + c.isFalse("($2,$0) IMMEDIATELY PRECEDES ($3,$1)"); + c.isFalse("($3,$2) IMMEDIATELY PRECEDES ($1,$0)"); + c.isFalse("($2,$3) IMMEDIATELY PRECEDES ($0,$2)"); + c.isFalse("($2,$3) IMMEDIATELY PRECEDES ($2,$0)"); + c.isFalse("($3,$2) IMMEDIATELY PRECEDES ($2,$0)"); + c.isFalse("($0,$2) IMMEDIATELY PRECEDES ($2,$0)"); + c.isFalse("($0,$3) IMMEDIATELY PRECEDES ($1,$3)"); + c.isTrue("($0,$3) IMMEDIATELY PRECEDES ($3,$3)"); + c.isTrue("($3,$0) IMMEDIATELY PRECEDES ($3,$3)"); + c.isFalse("($3,$0) IMMEDIATELY PRECEDES ($0,$0)"); + + c.isTrue("($0,$0) IMMEDIATELY SUCCEEDS ($0,$0)"); + c.isFalse("($0,$1) IMMEDIATELY SUCCEEDS ($2,$3)"); + c.isFalse("($0,$1) IMMEDIATELY SUCCEEDS ($1,$2)"); + c.isFalse("($0,$2) IMMEDIATELY SUCCEEDS ($1,$3)"); + c.isFalse("($0,$2) IMMEDIATELY SUCCEEDS ($3,$1)"); + c.isFalse("($2,$0) IMMEDIATELY SUCCEEDS ($3,$1)"); + c.isFalse("($3,$2) IMMEDIATELY SUCCEEDS ($1,$0)"); + c.isTrue("($2,$3) IMMEDIATELY SUCCEEDS ($0,$2)"); + c.isTrue("($2,$3) IMMEDIATELY SUCCEEDS ($2,$0)"); + c.isTrue("($3,$2) IMMEDIATELY SUCCEEDS ($2,$0)"); + c.isFalse("($0,$2) IMMEDIATELY SUCCEEDS ($2,$0)"); + c.isFalse("($0,$3) IMMEDIATELY SUCCEEDS ($1,$3)"); + c.isFalse("($0,$3) IMMEDIATELY SUCCEEDS ($3,$3)"); + c.isFalse("($3,$0) IMMEDIATELY SUCCEEDS ($3,$3)"); + c.isTrue("($3,$0) IMMEDIATELY SUCCEEDS ($0,$0)"); } @Test public void testLessThanOperator() { http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/java/org/apache/calcite/sql/test/SqlPrettyWriterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlPrettyWriterTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlPrettyWriterTest.java index e414b0e..83d5a7e 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlPrettyWriterTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlPrettyWriterTest.java @@ -275,7 +275,7 @@ public class SqlPrettyWriterTest { assertExprPrintsTo( true, "(x,xx) overlaps (y,yy) or x is not null", - "(`X`, `XX`) OVERLAPS (`Y`, `YY`) OR `X` IS NOT NULL"); + "PERIOD (`X`, `XX`) OVERLAPS PERIOD (`Y`, `YY`) OR `X` IS NOT NULL"); } @Test public void testUnion() { http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- 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 fe9261d..95fdef0 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -4778,7 +4778,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase { checkExpFails("false and ^1 in (date '2012-01-02', date '2012-01-04')^", ERR_IN_OPERANDS_INCOMPATIBLE); checkExpFails( - "1 > 5 ^or (1, 2) in (3, 4)^", + "1 > 5 or ^(1, 2) in (3, 4)^", ERR_IN_OPERANDS_INCOMPATIBLE); } @@ -6727,8 +6727,11 @@ public class SqlValidatorTest extends SqlValidatorTestCase { } @Test public void testOverlaps() { - checkExpType( - "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', date '1-2-3')", + checkExpType("(date '1-2-3', date '1-2-3')\n" + + " overlaps (date '1-2-3', date '1-2-3')", + "BOOLEAN NOT NULL"); + checkExpType("period (date '1-2-3', date '1-2-3')\n" + + " overlaps period (date '1-2-3', date '1-2-3')", "BOOLEAN NOT NULL"); checkExp( "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', interval '1' year)"); @@ -6739,13 +6742,143 @@ public class SqlValidatorTest extends SqlValidatorTestCase { checkWholeExpFails( "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (time '4:5:6', interval '1 2:3:4.5' day to second)", - "(?s).*Cannot apply 'OVERLAPS' to arguments of type '.<TIMESTAMP.0.>, <TIMESTAMP.0.>. OVERLAPS .<TIME.0.>, <INTERVAL DAY TO SECOND>.*"); + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); checkWholeExpFails( "(time '4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (time '4:5:6', interval '1 2:3:4.5' day to second)", - "(?s).*Cannot apply 'OVERLAPS' to arguments of type '.<TIME.0.>, <TIMESTAMP.0.>. OVERLAPS .<TIME.0.>, <INTERVAL DAY TO SECOND>.'.*"); + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); checkWholeExpFails( "(time '4:5:6', time '4:5:6' ) overlaps (time '4:5:6', date '1-2-3')", - "(?s).*Cannot apply 'OVERLAPS' to arguments of type '.<TIME.0.>, <TIME.0.>. OVERLAPS .<TIME.0.>, <DATE>.'.*"); + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); + checkWholeExpFails("1 overlaps 2", + "(?s).*Cannot apply 'OVERLAPS' to arguments of type " + + "'<INTEGER> OVERLAPS <INTEGER>'\\. Supported form.*"); + + checkExpType("true\n" + + "or (date '1-2-3', date '1-2-3')\n" + + " overlaps (date '1-2-3', date '1-2-3')\n" + + "or false", + "BOOLEAN NOT NULL"); + // row with 3 arguments as left argument to overlaps + checkExpFails("true\n" + + "or ^(date '1-2-3', date '1-2-3', date '1-2-3')\n" + + " overlaps (date '1-2-3', date '1-2-3')^\n" + + "or false", + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); + // row with 3 arguments as right argument to overlaps + checkExpFails("true\n" + + "or ^(date '1-2-3', date '1-2-3')\n" + + " overlaps (date '1-2-3', date '1-2-3', date '1-2-3')^\n" + + "or false", + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); + checkExpFails("^period (date '1-2-3', date '1-2-3')\n" + + " overlaps (date '1-2-3', date '1-2-3', date '1-2-3')^", + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); + checkExpFails("true\n" + + "or ^(1, 2) overlaps (2, 3)^\n" + + "or false", + "(?s).*Cannot apply 'OVERLAPS' to arguments of type .*"); + + // Other operators with similar syntax + String[] ops = { + "overlaps", "contains", "equals", "precedes", "succeeds", + "immediately precedes", "immediately succeeds" + }; + for (String op : ops) { + checkExpType("period (date '1-2-3', date '1-2-3')\n" + + " " + op + " period (date '1-2-3', date '1-2-3')", + "BOOLEAN NOT NULL"); + checkExpType("(date '1-2-3', date '1-2-3')\n" + + " " + op + " (date '1-2-3', date '1-2-3')", + "BOOLEAN NOT NULL"); + } + } + + @Test public void testContains() { + final String cannotApply = + "(?s).*Cannot apply 'CONTAINS' to arguments of type .*"; + + checkExpType("(date '1-2-3', date '1-2-3')\n" + + " contains (date '1-2-3', date '1-2-3')", + "BOOLEAN NOT NULL"); + checkExpType("period (date '1-2-3', date '1-2-3')\n" + + " contains period (date '1-2-3', date '1-2-3')", + "BOOLEAN NOT NULL"); + checkExp("(date '1-2-3', date '1-2-3')\n" + + " contains (date '1-2-3', interval '1' year)"); + checkExp("(time '1:2:3', interval '1' second)\n" + + " contains (time '23:59:59', time '1:2:3')"); + checkExp("(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6')\n" + + " contains (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)"); + + // period contains point + checkExp("(date '1-2-3', date '1-2-3')\n" + + " contains date '1-2-3'"); + // same, with "period" keyword + checkExp("period (date '1-2-3', date '1-2-3')\n" + + " contains date '1-2-3'"); + // point contains period + checkWholeExpFails("date '1-2-3'\n" + + " contains (date '1-2-3', date '1-2-3')", + cannotApply); + // same, with "period" keyword + checkWholeExpFails("date '1-2-3'\n" + + " contains period (date '1-2-3', date '1-2-3')", + cannotApply); + // point contains point + checkWholeExpFails("date '1-2-3' contains date '1-2-3'", + cannotApply); + + checkWholeExpFails("(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' )\n" + + " contains (time '4:5:6', interval '1 2:3:4.5' day to second)", + cannotApply); + checkWholeExpFails("(time '4:5:6', timestamp '1-2-3 4:5:6' )\n" + + " contains (time '4:5:6', interval '1 2:3:4.5' day to second)", + cannotApply); + checkWholeExpFails("(time '4:5:6', time '4:5:6' )\n" + + " contains (time '4:5:6', date '1-2-3')", + cannotApply); + checkWholeExpFails("1 contains 2", + cannotApply); + // row with 3 arguments + checkExpFails("true\n" + + "or ^(date '1-2-3', date '1-2-3', date '1-2-3')\n" + + " contains (date '1-2-3', date '1-2-3')^\n" + + "or false", + cannotApply); + + checkExpType("true\n" + + "or (date '1-2-3', date '1-2-3')\n" + + " contains (date '1-2-3', date '1-2-3')\n" + + "or false", + "BOOLEAN NOT NULL"); + // second argument is a point + checkExpType("true\n" + + "or (date '1-2-3', date '1-2-3')\n" + + " contains date '1-2-3'\n" + + "or false", + "BOOLEAN NOT NULL"); + // first argument may be null, so result may be null + checkExpType("true\n" + + "or (date '1-2-3',\n" + + " case 1 when 2 then date '1-2-3' else null end)\n" + + " contains date '1-2-3'\n" + + "or false", + "BOOLEAN"); + // second argument may be null, so result may be null + checkExpType("true\n" + + "or (date '1-2-3', date '1-2-3')\n" + + " contains case 1 when 1 then date '1-2-3' else null end\n" + + "or false", + "BOOLEAN"); + checkExpFails("true\n" + + "or ^period (date '1-2-3', date '1-2-3')\n" + + " contains period (date '1-2-3', time '4:5:6')^\n" + + "or false", + cannotApply); + checkExpFails("true\n" + + "or ^(1, 2) contains (2, 3)^\n" + + "or false", + cannotApply); } @Test public void testExtract() { @@ -7994,11 +8127,17 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "= left\n" + "> left\n" + ">= left\n" + + "CONTAINS left\n" + + "EQUALS left\n" + + "IMMEDIATELY PRECEDES left\n" + + "IMMEDIATELY SUCCEEDS left\n" + "IS DISTINCT FROM left\n" + "IS NOT DISTINCT FROM left\n" + "MEMBER OF left\n" - + "OVERLAPS -\n" + + "OVERLAPS left\n" + + "PRECEDES left\n" + "SUBMULTISET OF left\n" + + "SUCCEEDS left\n" + "\n" + "IS A SET post\n" + "IS FALSE post\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/core/src/test/resources/sql/misc.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index 956fa10..0ba3335 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -620,11 +620,41 @@ select count(*) as c from "everyTypes" where "utilDate" = TIMESTAMP '1970-01-01 !ok +# [CALCITE-715] Add PERIOD type constructor and period operators (CONTAINS, PRECEDES, etc.) +select period("sqlDate", INTERVAL '1' YEAR) as p +from "everyTypes"; ++------------+ +| P | ++------------+ +| {0, 12} | +| {null, 12} | ++------------+ +(2 rows) + +!ok + +!use foodmart + +select count(*) as c +from "customer" +where period ("birthdate", DATE '1970-02-05') contains DATE '1964-01-01'; +EnumerableAggregate(group=[{}], C=[COUNT()]) + EnumerableCalc(expr#0..28=[{inputs}], expr#29=[1970-02-05], expr#30=[<=($t16, $t29)], expr#31=[CASE($t30, $t16, $t29)], expr#32=[1964-01-01], expr#33=[<=($t31, $t32)], expr#34=[CASE($t30, $t29, $t16)], expr#35=[>=($t34, $t32)], expr#36=[AND($t33, $t35)], proj#0..28=[{exprs}], $condition=[$t36]) + EnumerableTableScan(table=[[foodmart2, customer]]) +!plan ++------+ +| C | ++------+ +| 7918 | ++------+ +(1 row) + +!ok + # [CALCITE-346] Add commutative join rule # # 3-way join that does not require bushy join. Best plan is: sales_fact_1997 as # left-most leaf, then customer (with filter), then product. -!use foodmart select * from "sales_fact_1997" as s join "customer" as c using ("customer_id") http://git-wip-us.apache.org/repos/asf/calcite/blob/feff5964/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index c942c14..5e64c70 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -22,6 +22,61 @@ limitations under the License. {% endcomment %} --> +<style> +.container { + width: 400px; + height: 26px; +} +.gray { + width: 60px; + height: 26px; + background: gray; + float: left; +} +.r15 { + width: 40px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 10px; +} +.r12 { + width: 10px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 10px; +} +.r13 { + width: 20px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 10px; +} +.r2 { + width: 2px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 20px; +} +.r24 { + width: 20px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 20px; +} +.r35 { + width: 20px; + height: 6px; + background: yellow; + margin-top: 4px; + margin-left: 30px; +} +</style> + The page describes the SQL dialect recognized by Calcite's default SQL parser. ## Grammar @@ -454,6 +509,7 @@ HIERARCHY, **HOUR**, **IDENTITY**, IMMEDIATE, +IMMEDIATELY, IMPLEMENTATION, **IMPORT**, **IN**, @@ -910,7 +966,6 @@ name will have been converted to upper case also. | TIMESTAMP [ WITHOUT TIME ZONE ] | Date and time | Example: TIMESTAMP '1969-07-20 20:17:40' | TIMESTAMP WITH TIME ZONE | Date and time with time zone | Example: TIMESTAMP '1969-07-20 20:17:40 America/Los Angeles' | INTERVAL timeUnit [ TO timeUnit ] | Date time interval | Examples: INTERVAL '1:5' YEAR TO MONTH, INTERVAL '45' DAY -| Anchored interval | Date time interval | Example: (DATE '1969-07-20', DATE '1972-08-29') Where: @@ -949,7 +1004,7 @@ The operator precedence and associativity, highest to lowest. | + - (unary plus, minus) | right | * / | left | + - | left -| BETWEEN, IN, LIKE, SIMILAR | - +| BETWEEN, IN, LIKE, SIMILAR, OVERLAPS, CONTAINS etc. | - | < > = <= >= <> != | left | IS NULL, IS FALSE, IS NOT TRUE etc. | - | NOT | right @@ -1151,6 +1206,102 @@ Not implemented: See also: UNNEST relational operator converts a collection to a relation. +### Period predicates + +<table> + <tr> + <th>Operator syntax</th> + <th>Description</th> + </tr> + <tr> + <td>period1 CONTAINS dateTime</td> + <td> + <div class="container"> + <div class="gray"><div class="r15"></div><div class="r2"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 CONTAINS period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r15"></div><div class="r24"></div></div> + <div class="gray"><div class="r15"></div><div class="r13"></div></div> + <div class="gray"><div class="r15"></div><div class="r35"></div></div> + <div class="gray"><div class="r15"></div><div class="r15"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 OVERLAPS period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r15"></div><div class="r24"></div></div> + <div class="gray"><div class="r15"></div><div class="r13"></div></div> + <div class="gray"><div class="r15"></div><div class="r35"></div></div> + <div class="gray"><div class="r15"></div><div class="r15"></div></div> + <div class="gray"><div class="r24"></div><div class="r15"></div></div> + <div class="gray"><div class="r13"></div><div class="r15"></div></div> + <div class="gray"><div class="r35"></div><div class="r15"></div></div> + <div class="gray"><div class="r24"></div><div class="r13"></div></div> + <div class="gray"><div class="r13"></div><div class="r24"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 EQUALS period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r15"></div><div class="r15"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 PRECEDES period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r12"></div><div class="r35"></div></div> + <div class="gray"><div class="r13"></div><div class="r35"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 IMMEDIATELY PRECEDES period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r13"></div><div class="r35"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 SUCCEEDS period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r35"></div><div class="r12"></div></div> + <div class="gray"><div class="r35"></div><div class="r13"></div></div> + </div> + </td> + </tr> + <tr> + <td>period1 IMMEDIATELY SUCCEEDS period2</td> + <td> + <div class="container"> + <div class="gray"><div class="r35"></div><div class="r13"></div></div> + </div> + </td> + </tr> +</table> + +Where *period1* and *period2* are period expressions: + +{% highlight sql %} +period: + (dateTime, dateTime) + | (dateTime, interval) + | PERIOD (dateTime, dateTime) + | PERIOD (dateTime, interval) +{% endhighlight %} + ### JDBC function escape #### Numeric
