[CALCITE-1911] In MATCH_RECOGNIZE, support WITHIN sub-clause (Dian Fu) Close apache/calcite#509
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/dfe251d7 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/dfe251d7 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/dfe251d7 Branch: refs/heads/master Commit: dfe251d7e5136ce93b1172ad1822bd908e953c86 Parents: 1830040 Author: Dian Fu <[email protected]> Authored: Mon Aug 7 21:39:15 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Wed Aug 23 11:20:35 2017 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 8 +- .../java/org/apache/calcite/rel/core/Match.java | 29 ++++--- .../apache/calcite/rel/core/RelFactories.java | 13 +-- .../calcite/rel/logical/LogicalMatch.java | 41 ++++++---- .../calcite/rel/rel2sql/RelToSqlConverter.java | 9 ++- .../calcite/rel/rel2sql/SqlImplementor.java | 6 ++ .../java/org/apache/calcite/rex/RexLiteral.java | 80 +++++++++++++++++-- .../apache/calcite/runtime/CalciteResource.java | 9 +++ .../calcite/sql/SqlIntervalQualifier.java | 2 +- .../apache/calcite/sql/SqlMatchRecognize.java | 20 ++++- .../calcite/sql/validate/SqlValidatorImpl.java | 30 +++++++ .../calcite/sql2rel/SqlToRelConverter.java | 10 ++- .../calcite/runtime/CalciteResource.properties | 3 + .../rel/rel2sql/RelToSqlConverterTest.java | 83 ++++++++++++++++++-- .../calcite/sql/parser/SqlParserTest.java | 28 +++++++ .../calcite/test/SqlValidatorMatchTest.java | 25 ++++++ site/_docs/reference.md | 10 ++- 17 files changed, 354 insertions(+), 52 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/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 a913800..fae4fd1 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -2449,6 +2449,7 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : SqlNodeList partitionList = SqlNodeList.EMPTY; SqlNodeList orderList = SqlNodeList.EMPTY; SqlNode pattern; + SqlLiteral interval; SqlNodeList patternDefList; final SqlNode after; SqlParserPos pos; @@ -2528,6 +2529,11 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : { isStrictEnds = SqlLiteral.createBoolean(false, getPos()); } ) <RPAREN> + ( + <WITHIN> interval = IntervalLiteral() + | + { interval = null; } + ) [ <SUBSET> subsetList = SubsetDefinitionCommaList(span()) @@ -2537,7 +2543,7 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : <RPAREN> { return new SqlMatchRecognize(s.end(this), tableRef, pattern, isStrictStarts, isStrictEnds, patternDefList, measureList, - after, subsetList, rowsPerMatch, partitionList, orderList); + after, subsetList, rowsPerMatch, partitionList, orderList, interval); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rel/core/Match.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/Match.java b/core/src/main/java/org/apache/calcite/rel/core/Match.java index ca05ff8..13c34bf 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Match.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Match.java @@ -67,6 +67,7 @@ public abstract class Match extends SingleRel { protected final ImmutableMap<String, SortedSet<String>> subsets; protected final List<RexNode> partitionKeys; protected final RelCollation orderKeys; + protected final RexNode interval; //~ Constructors ----------------------------------------------- @@ -76,6 +77,7 @@ public abstract class Match extends SingleRel { * @param cluster Cluster * @param traitSet Trait set * @param input Input relational expression + * @param rowType Row type * @param pattern Regular expression that defines pattern variables * @param strictStart Whether it is a strict start pattern * @param strictEnd Whether it is a strict end pattern @@ -86,27 +88,29 @@ public abstract class Match extends SingleRel { * @param allRows Whether all rows per match (false means one row per match) * @param partitionKeys Partition by columns * @param orderKeys Order by columns - * @param rowType Row type + * @param interval Interval definition, null if WITHIN clause is not defined */ - protected Match(RelOptCluster cluster, RelTraitSet traitSet, - RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, + protected Match(RelOptCluster cluster, RelTraitSet traitSet, RelNode input, + RelDataType rowType, RexNode pattern, + boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, boolean allRows, List<RexNode> partitionKeys, RelCollation orderKeys, - RelDataType rowType) { + RexNode interval) { super(cluster, traitSet, input); + this.rowType = Preconditions.checkNotNull(rowType); this.pattern = Preconditions.checkNotNull(pattern); Preconditions.checkArgument(patternDefinitions.size() > 0); this.strictStart = strictStart; this.strictEnd = strictEnd; this.patternDefinitions = ImmutableMap.copyOf(patternDefinitions); - this.rowType = Preconditions.checkNotNull(rowType); this.measures = ImmutableMap.copyOf(measures); this.after = Preconditions.checkNotNull(after); this.subsets = copyMap(subsets); this.allRows = allRows; this.partitionKeys = ImmutableList.copyOf(partitionKeys); this.orderKeys = Preconditions.checkNotNull(orderKeys); + this.interval = interval; final AggregateFinder aggregateFinder = new AggregateFinder(); for (RexNode rex : this.patternDefinitions.values()) { @@ -180,12 +184,16 @@ public abstract class Match extends SingleRel { return orderKeys; } - public abstract Match copy(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, + public RexNode getInterval() { + return interval; + } + + public abstract Match copy(RelNode input, RelDataType rowType, + RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, boolean allRows, List<RexNode> partitionKeys, RelCollation orderKeys, - RelDataType rowType); + RexNode interval); @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { if (getInputs().equals(inputs) @@ -193,9 +201,9 @@ public abstract class Match extends SingleRel { return this; } - return copy(inputs.get(0), pattern, strictStart, strictEnd, + return copy(inputs.get(0), rowType, pattern, strictStart, strictEnd, patternDefinitions, measures, after, subsets, allRows, - partitionKeys, orderKeys, rowType); + partitionKeys, orderKeys, interval); } @Override public RelWriter explainTerms(RelWriter pw) { @@ -208,6 +216,7 @@ public abstract class Match extends SingleRel { .item("pattern", getPattern()) .item("isStrictStarts", isStrictStart()) .item("isStrictEnds", isStrictEnd()) + .itemIf("interval", getInterval(), getInterval() != null) .item("subsets", getSubsets().values().asList()) .item("patternDefinitions", getPatternDefinitions().values().asList()) .item("inputFields", getInput().getRowType().getFieldNames()); http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java index 47c2425..e7e5f72 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java +++ b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java @@ -396,10 +396,10 @@ public class RelFactories { public interface MatchFactory { /** Creates a {@link Match}. */ RelNode createMatchRecognize(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, + RelDataType rowType, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, - List<RexNode> partitionKeys, RelCollation orderKeys, RelDataType rowType); + List<RexNode> partitionKeys, RelCollation orderKeys, RexNode interval); } /** @@ -408,12 +408,13 @@ public class RelFactories { */ private static class MatchFactoryImpl implements MatchFactory { public RelNode createMatchRecognize(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, + RelDataType rowType, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, - List<RexNode> partitionKeys, RelCollation orderKeys, RelDataType rowType) { - return LogicalMatch.create(input, pattern, strictStart, strictEnd, - patternDefinitions, measures, after, subsets, allRows, partitionKeys, orderKeys, rowType); + List<RexNode> partitionKeys, RelCollation orderKeys, RexNode interval) { + return LogicalMatch.create(input, rowType, pattern, strictStart, + strictEnd, patternDefinitions, measures, after, subsets, allRows, + partitionKeys, orderKeys, interval); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java index 406b40f..f0e3729 100644 --- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java +++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java @@ -21,6 +21,7 @@ import org.apache.calcite.plan.RelOptCluster; import org.apache.calcite.plan.RelTraitSet; import org.apache.calcite.rel.RelCollation; import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.RelShuttle; import org.apache.calcite.rel.core.Match; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rex.RexNode; @@ -42,6 +43,7 @@ public class LogicalMatch extends Match { * @param cluster cluster * @param traitSet Trait set * @param input Input relational expression + * @param rowType Row type * @param pattern Regular Expression defining pattern variables * @param strictStart Whether it is a strict start pattern * @param strictEnd Whether it is a strict end pattern @@ -52,45 +54,54 @@ public class LogicalMatch extends Match { * @param allRows Whether all rows per match (false means one row per match) * @param partitionKeys Partition by columns * @param orderKeys Order by columns - * @param rowType Row type + * @param interval Interval definition, null if WITHIN clause is not defined */ private LogicalMatch(RelOptCluster cluster, RelTraitSet traitSet, - RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, + RelNode input, RelDataType rowType, RexNode pattern, + boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, boolean allRows, List<RexNode> partitionKeys, RelCollation orderKeys, - RelDataType rowType) { - super(cluster, traitSet, input, pattern, strictStart, strictEnd, + RexNode interval) { + super(cluster, traitSet, input, rowType, pattern, strictStart, strictEnd, patternDefinitions, measures, after, subsets, allRows, partitionKeys, - orderKeys, rowType); + orderKeys, interval); } /** * Creates a LogicalMatch. */ - public static LogicalMatch create(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, + public static LogicalMatch create(RelNode input, RelDataType rowType, + RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, - List<RexNode> partitionKeys, RelCollation orderKeys, RelDataType rowType) { + List<RexNode> partitionKeys, RelCollation orderKeys, RexNode interval) { final RelOptCluster cluster = input.getCluster(); final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE); - return new LogicalMatch(cluster, traitSet, input, pattern, + return new LogicalMatch(cluster, traitSet, input, rowType, pattern, strictStart, strictEnd, patternDefinitions, measures, after, subsets, - allRows, partitionKeys, orderKeys, rowType); + allRows, partitionKeys, orderKeys, interval); } //~ Methods ------------------------------------------------------ - @Override public Match copy(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, + @Override public Match copy(RelNode input, RelDataType rowType, + RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, - boolean allRows, List<RexNode> partitionKeys, RelCollation orderKeys, RelDataType rowType) { + boolean allRows, List<RexNode> partitionKeys, RelCollation orderKeys, + RexNode interval) { final RelTraitSet traitSet = getCluster().traitSetOf(Convention.NONE); return new LogicalMatch(getCluster(), traitSet, - input, pattern, strictStart, strictEnd, patternDefinitions, measures, - after, subsets, allRows, partitionKeys, orderKeys, rowType); + input, + rowType, + pattern, strictStart, strictEnd, patternDefinitions, measures, + after, subsets, allRows, partitionKeys, orderKeys, + interval); + } + + @Override public RelNode accept(RelShuttle shuttle) { + return shuttle.visit(this); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 39587a0..8de2c41 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -48,6 +48,7 @@ import org.apache.calcite.sql.SqlDelete; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlInsert; +import org.apache.calcite.sql.SqlIntervalLiteral; import org.apache.calcite.sql.SqlJoin; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlMatchRecognize; @@ -428,6 +429,12 @@ public class RelToSqlConverter extends SqlImplementor final SqlLiteral strictStart = SqlLiteral.createBoolean(e.isStrictStart(), POS); final SqlLiteral strictEnd = SqlLiteral.createBoolean(e.isStrictEnd(), POS); + RexLiteral rexInterval = (RexLiteral) e.getInterval(); + SqlIntervalLiteral interval = null; + if (rexInterval != null) { + interval = (SqlIntervalLiteral) context.toSql(null, rexInterval); + } + final SqlNodeList subsetList = new SqlNodeList(POS); for (Map.Entry<String, SortedSet<String>> entry : e.getSubsets().entrySet()) { SqlNode left = new SqlIdentifier(entry.getKey(), POS); @@ -456,7 +463,7 @@ public class RelToSqlConverter extends SqlImplementor final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, pattern, strictStart, strictEnd, patternDefList, measureList, after, - subsetList, rowsPerMatch, partitionList, orderByList); + subsetList, rowsPerMatch, partitionList, orderByList, interval); return result(matchRecognize, Expressions.list(Clause.FROM), e, null); } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java index 285cfe4..d227310 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java @@ -564,6 +564,12 @@ public abstract class SqlImplementor { case BOOLEAN: return SqlLiteral.createBoolean(literal.getValueAs(Boolean.class), POS); + case INTERVAL_YEAR_MONTH: + case INTERVAL_DAY_TIME: + final boolean negative = literal.getValueAs(Boolean.class); + return SqlLiteral.createInterval(negative ? -1 : 1, + literal.getValueAs(String.class), + literal.getType().getIntervalQualifier(), POS); case DATE: return SqlLiteral.createDate(literal.getValueAs(DateString.class), POS); http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/rex/RexLiteral.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexLiteral.java b/core/src/main/java/org/apache/calcite/rex/RexLiteral.java index 674cdd8..8c4c732 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexLiteral.java +++ b/core/src/main/java/org/apache/calcite/rex/RexLiteral.java @@ -18,6 +18,7 @@ package org.apache.calcite.rex; import org.apache.calcite.avatica.util.ByteString; import org.apache.calcite.avatica.util.DateTimeUtils; +import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.SqlCollation; import org.apache.calcite.sql.SqlKind; @@ -25,6 +26,7 @@ import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserUtil; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.CompositeList; import org.apache.calcite.util.ConversionUtil; import org.apache.calcite.util.DateString; import org.apache.calcite.util.Litmus; @@ -35,6 +37,7 @@ import org.apache.calcite.util.TimestampString; import org.apache.calcite.util.Util; import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; import java.io.PrintWriter; import java.io.StringWriter; @@ -194,6 +197,10 @@ public class RexLiteral extends RexNode { */ private final SqlTypeName typeName; + + private static final ImmutableList<TimeUnit> TIME_UNITS = + ImmutableList.copyOf(TimeUnit.values()); + //~ Constructors ----------------------------------------------------------- /** @@ -344,6 +351,66 @@ public class RexLiteral extends RexNode { } } + /** Returns a list of the time units covered by an interval type such + * as HOUR TO SECOND. Adds MILLISECOND if the end is SECOND, to deal with + * fractional seconds. */ + private static List<TimeUnit> getTimeUnits(SqlTypeName typeName) { + final TimeUnit start = typeName.getStartUnit(); + final TimeUnit end = typeName.getEndUnit(); + final ImmutableList<TimeUnit> list = + TIME_UNITS.subList(start.ordinal(), end.ordinal() + 1); + if (end == TimeUnit.SECOND) { + return CompositeList.of(list, ImmutableList.of(TimeUnit.MILLISECOND)); + } + return list; + } + + private String intervalString(BigDecimal v) { + final List<TimeUnit> timeUnits = getTimeUnits(type.getSqlTypeName()); + final StringBuilder b = new StringBuilder(); + for (TimeUnit timeUnit : timeUnits) { + final BigDecimal[] result = v.divideAndRemainder(timeUnit.multiplier); + if (b.length() > 0) { + b.append(timeUnit.separator); + } + final int width = b.length() == 0 ? -1 : width(timeUnit); // don't pad 1st + pad(b, result[0].toString(), width); + v = result[1]; + } + if (Util.last(timeUnits) == TimeUnit.MILLISECOND) { + while (b.toString().matches(".*\\.[0-9]*0")) { + if (b.toString().endsWith(".0")) { + b.setLength(b.length() - 2); // remove ".0" + } else { + b.setLength(b.length() - 1); // remove "0" + } + } + } + return b.toString(); + } + + private static void pad(StringBuilder b, String s, int width) { + if (width >= 0) { + for (int i = s.length(); i < width; i++) { + b.append('0'); + } + } + b.append(s); + } + + private static int width(TimeUnit timeUnit) { + switch (timeUnit) { + case MILLISECOND: + return 3; + case HOUR: + case MINUTE: + case SECOND: + return 2; + default: + return -1; + } + } + /** * Prints the value this literal as a Java string constant. */ @@ -780,10 +847,6 @@ public class RexLiteral extends RexNode { case INTERVAL_YEAR: case INTERVAL_YEAR_MONTH: case INTERVAL_MONTH: - if (clazz == Integer.class) { - return clazz.cast(((BigDecimal) value).intValue()); - } - break; case INTERVAL_DAY: case INTERVAL_DAY_HOUR: case INTERVAL_DAY_MINUTE: @@ -794,8 +857,15 @@ public class RexLiteral extends RexNode { case INTERVAL_MINUTE: case INTERVAL_MINUTE_SECOND: case INTERVAL_SECOND: - if (clazz == Long.class) { + if (clazz == Integer.class) { + return clazz.cast(((BigDecimal) value).intValue()); + } else if (clazz == Long.class) { return clazz.cast(((BigDecimal) value).longValue()); + } else if (clazz == String.class) { + return clazz.cast(intervalString(getValueAs(BigDecimal.class).abs())); + } else if (clazz == Boolean.class) { + // return whether negative + return clazz.cast(getValueAs(BigDecimal.class).signum() < 0); } break; } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java index ad389d1..261b41c 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -708,6 +708,15 @@ public interface CalciteResource { @BaseMessage("Unknown pattern ''{0}''") ExInst<SqlValidatorException> unknownPattern(String call); + @BaseMessage("Interval must be non-negative ''{0}''") + ExInst<SqlValidatorException> intervalMustBeNonNegative(String call); + + @BaseMessage("Must contain an ORDER BY clause when WITHIN is used") + ExInst<SqlValidatorException> cannotUseWithinWithoutOrderBy(); + + @BaseMessage("First column of ORDER BY must be of type TIMESTAMP") + ExInst<SqlValidatorException> firstColumnOfOrderByMustBeTimestamp(); + @BaseMessage("Extended columns not allowed under the current SQL conformance level") ExInst<SqlValidatorException> extendNotAllowed(); http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java index fa1c45f..05c99ab 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java @@ -368,7 +368,7 @@ public class SqlIntervalQualifier extends SqlNode { /** * @return 1 or -1 */ - private int getIntervalSign(String value) { + public int getIntervalSign(String value) { int sign = 1; // positive until proven otherwise if (!Util.isNullOrEmpty(value)) { http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java index 9c8dcef..bb52e62 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java @@ -43,6 +43,7 @@ public class SqlMatchRecognize extends SqlCall { public static final int OPERAND_ROWS_PER_MATCH = 8; public static final int OPERAND_PARTITION_BY = 9; public static final int OPERAND_ORDER_BY = 10; + public static final int OPERAND_INTERVAL = 11; public static final SqlPrefixOperator SKIP_TO_FIRST = new SqlPrefixOperator("SKIP TO FIRST", SqlKind.SKIP_TO_FIRST, 20, null, @@ -65,13 +66,14 @@ public class SqlMatchRecognize extends SqlCall { private SqlLiteral rowsPerMatch; private SqlNodeList partitionList; private SqlNodeList orderList; + private SqlLiteral interval; /** Creates a SqlMatchRecognize. */ public SqlMatchRecognize(SqlParserPos pos, SqlNode tableRef, SqlNode pattern, SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList, SqlNodeList measureList, SqlNode after, SqlNodeList subsetList, SqlLiteral rowsPerMatch, SqlNodeList partitionList, - SqlNodeList orderList) { + SqlNodeList orderList, SqlLiteral interval) { super(pos); this.tableRef = Preconditions.checkNotNull(tableRef); this.pattern = Preconditions.checkNotNull(pattern); @@ -87,6 +89,7 @@ public class SqlMatchRecognize extends SqlCall { this.rowsPerMatch = rowsPerMatch; this.partitionList = Preconditions.checkNotNull(partitionList); this.orderList = Preconditions.checkNotNull(orderList); + this.interval = interval; } // ~ Methods @@ -151,6 +154,9 @@ public class SqlMatchRecognize extends SqlCall { case OPERAND_ORDER_BY: orderList = (SqlNodeList) operand; break; + case OPERAND_INTERVAL: + interval = (SqlLiteral) operand; + break; default: throw new AssertionError(i); } @@ -200,6 +206,10 @@ public class SqlMatchRecognize extends SqlCall { return orderList; } + public SqlLiteral getInterval() { + return interval; + } + /** * Options for {@code ROWS PER MATCH}. */ @@ -268,13 +278,13 @@ public class SqlMatchRecognize extends SqlCall { SqlParserPos pos, SqlNode... operands) { assert functionQualifier == null; - assert operands.length == 11; + assert operands.length == 12; return new SqlMatchRecognize(pos, operands[0], operands[1], (SqlLiteral) operands[2], (SqlLiteral) operands[3], (SqlNodeList) operands[4], (SqlNodeList) operands[5], operands[6], (SqlNodeList) operands[7], (SqlLiteral) operands[8], - (SqlNodeList) operands[9], (SqlNodeList) operands[10]); + (SqlNodeList) operands[9], (SqlNodeList) operands[10], (SqlLiteral) operands[11]); } @Override public <R> void acceptCall( @@ -362,6 +372,10 @@ public class SqlMatchRecognize extends SqlCall { writer.sep("$"); } writer.endList(patternFrame); + if (pattern.interval != null) { + writer.sep("WITHIN"); + pattern.interval.unparse(writer, 0, 0); + } if (pattern.subsetList != null && pattern.subsetList.size() > 0) { writer.newlineAndIndent(); http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java ---------------------------------------------------------------------- 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 0440129..c6de052 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 @@ -4815,6 +4815,36 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { PatternVarVisitor visitor = new PatternVarVisitor(scope); pattern.accept(visitor); + SqlLiteral interval = matchRecognize.getInterval(); + if (interval != null) { + interval.validate(this, scope); + if (((SqlIntervalLiteral) interval).signum() < 0) { + throw newValidationError(interval, + RESOURCE.intervalMustBeNonNegative(interval.toValue())); + } + if (orderBy == null || orderBy.size() == 0) { + throw newValidationError(interval, + RESOURCE.cannotUseWithinWithoutOrderBy()); + } + + SqlNode firstOrderByColumn = orderBy.getList().get(0); + SqlIdentifier identifier; + if (firstOrderByColumn instanceof SqlBasicCall) { + identifier = (SqlIdentifier) ((SqlBasicCall) firstOrderByColumn).getOperands()[0]; + } else { + identifier = (SqlIdentifier) firstOrderByColumn; + } + RelDataType firstOrderByColumnType = deriveType(scope, identifier); + if (firstOrderByColumnType.getSqlTypeName() != SqlTypeName.TIMESTAMP) { + throw newValidationError(interval, + RESOURCE.firstColumnOfOrderByMustBeTimestamp()); + } + + SqlNode expand = expand(interval, scope); + RelDataType type = deriveType(scope, expand); + setValidatedNodeType(interval, type); + } + validateDefinitions(matchRecognize, scope); SqlNodeList subsets = matchRecognize.getSubsetList(); http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index d4d6fc7..990db38 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2168,6 +2168,12 @@ public class SqlToRelConverter { }; final RexNode patternNode = pattern.accept(patternVarVisitor); + SqlLiteral interval = matchRecognize.getInterval(); + RexNode intervalNode = null; + if (interval != null) { + intervalNode = matchBb.convertLiteral(interval); + } + // convert subset final SqlNodeList subsets = matchRecognize.getSubsetList(); final Map<String, TreeSet<String>> subsetMap = Maps.newHashMap(); @@ -2238,10 +2244,10 @@ public class SqlToRelConverter { RelFactories.DEFAULT_MATCH_FACTORY; final RelNode rel = factory.createMatchRecognize(input, patternNode, - matchRecognize.getStrictStart().booleanValue(), + rowType, matchRecognize.getStrictStart().booleanValue(), matchRecognize.getStrictEnd().booleanValue(), definitionNodes.build(), measureNodes.build(), after, - subsetMap, allRows, partitionKeys, orders, rowType); + subsetMap, allRows, partitionKeys, orders, intervalNode); bb.setRoot(rel, false); } http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties ---------------------------------------------------------------------- diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties index 4dd7c38..52168b0 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -230,6 +230,9 @@ PatternFunctionVariableCheck=Multiple pattern variables in ''{0}'' FunctionMatchRecognizeOnly=Function ''{0}'' can only be used in MATCH_RECOGNIZE PatternFunctionNullCheck=Null parameters in ''{0}'' UnknownPattern=Unknown pattern ''{0}'' +IntervalMustBeNonNegative=Interval must be non-negative ''{0}'' +CannotUseWithinWithoutOrderBy=Must contain an ORDER BY clause when WITHIN is used +FirstColumnOfOrderByMustBeTimestamp=First column of ORDER BY must be of type TIMESTAMP ExtendNotAllowed=Extended columns not allowed under the current SQL conformance level RolledUpNotAllowed=Rolled up column ''{0}'' is not allowed in {1} # End CalciteResource.properties http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index 15725ec..d0749b9 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -690,22 +690,67 @@ public class RelToSqlConverterTest { @Test public void testLiteral() { checkLiteral("DATE '1978-05-02'"); + checkLiteral2("DATE '1978-5-2'", "DATE '1978-05-02'"); checkLiteral("TIME '12:34:56'"); checkLiteral("TIME '12:34:56.78'"); + checkLiteral2("TIME '1:4:6.080'", "TIME '01:04:06.080'"); checkLiteral("TIMESTAMP '1978-05-02 12:34:56.78'"); + checkLiteral2("TIMESTAMP '1978-5-2 2:4:6.80'", + "TIMESTAMP '1978-05-02 02:04:06.80'"); checkLiteral("'I can''t explain'"); checkLiteral("''"); checkLiteral("TRUE"); checkLiteral("123"); checkLiteral("123.45"); checkLiteral("-123.45"); - } - - private void checkLiteral(String s) { - sql("VALUES " + s) + checkLiteral("INTERVAL '1-2' YEAR TO MONTH"); + checkLiteral("INTERVAL -'1-2' YEAR TO MONTH"); + checkLiteral("INTERVAL '12-11' YEAR TO MONTH"); + checkLiteral("INTERVAL '1' YEAR"); + checkLiteral("INTERVAL '1' MONTH"); + checkLiteral("INTERVAL '12' DAY"); + checkLiteral("INTERVAL -'12' DAY"); + checkLiteral2("INTERVAL '1 2' DAY TO HOUR", + "INTERVAL '1 02' DAY TO HOUR"); + checkLiteral2("INTERVAL '1 2:10' DAY TO MINUTE", + "INTERVAL '1 02:10' DAY TO MINUTE"); + checkLiteral2("INTERVAL '1 2:00' DAY TO MINUTE", + "INTERVAL '1 02:00' DAY TO MINUTE"); + checkLiteral2("INTERVAL '1 2:34:56' DAY TO SECOND", + "INTERVAL '1 02:34:56' DAY TO SECOND"); + checkLiteral2("INTERVAL '1 2:34:56.789' DAY TO SECOND", + "INTERVAL '1 02:34:56.789' DAY TO SECOND"); + checkLiteral2("INTERVAL '1 2:34:56.78' DAY TO SECOND", + "INTERVAL '1 02:34:56.78' DAY TO SECOND"); + checkLiteral2("INTERVAL '1 2:34:56.078' DAY TO SECOND", + "INTERVAL '1 02:34:56.078' DAY TO SECOND"); + checkLiteral2("INTERVAL -'1 2:34:56.078' DAY TO SECOND", + "INTERVAL -'1 02:34:56.078' DAY TO SECOND"); + checkLiteral2("INTERVAL '1 2:3:5.070' DAY TO SECOND", + "INTERVAL '1 02:03:05.07' DAY TO SECOND"); + checkLiteral("INTERVAL '1:23' HOUR TO MINUTE"); + checkLiteral("INTERVAL '1:02' HOUR TO MINUTE"); + checkLiteral("INTERVAL -'1:02' HOUR TO MINUTE"); + checkLiteral("INTERVAL '1:23:45' HOUR TO SECOND"); + checkLiteral("INTERVAL '1:03:05' HOUR TO SECOND"); + checkLiteral("INTERVAL '1:23:45.678' HOUR TO SECOND"); + checkLiteral("INTERVAL '1:03:05.06' HOUR TO SECOND"); + checkLiteral("INTERVAL '12' MINUTE"); + checkLiteral("INTERVAL '12:34' MINUTE TO SECOND"); + checkLiteral("INTERVAL '12:34.567' MINUTE TO SECOND"); + checkLiteral("INTERVAL '12' SECOND"); + checkLiteral("INTERVAL '12.345' SECOND"); + } + + private void checkLiteral(String expression) { + checkLiteral2(expression, expression); + } + + private void checkLiteral2(String expression, String expected) { + sql("VALUES " + expression) .dialect(DatabaseProduct.HSQLDB.getDialect()) .ok("SELECT *\n" - + "FROM (VALUES (" + s + "))"); + + "FROM (VALUES (" + expected + "))"); } /** Test case for @@ -1898,6 +1943,34 @@ public class RelToSqlConverterTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizeWithin() { + final String sql = "select *\n" + + " from \"employee\" match_recognize\n" + + " (\n" + + " order by \"hire_date\"\n" + + " ALL ROWS PER MATCH\n" + + " pattern (strt down+ up+) within interval '3:12:22.123' hour to second\n" + + " define\n" + + " down as down.\"salary\" < PREV(down.\"salary\"),\n" + + " up as up.\"salary\" > prev(up.\"salary\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"employee\") " + + "MATCH_RECOGNIZE(\n" + + "ORDER BY \"hire_date\"\n" + + "ALL ROWS PER MATCH\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +) WITHIN INTERVAL '3:12:22.123' HOUR TO SECOND\n" + + "DEFINE " + + "\"DOWN\" AS \"DOWN\".\"salary\" < " + + "PREV(\"DOWN\".\"salary\", 1), " + + "\"UP\" AS \"UP\".\"salary\" > " + + "PREV(\"UP\".\"salary\", 1))"; + sql(sql).ok(expected); + } + /** Fluid interface to run tests. */ private static class Sql { private CalciteAssert.SchemaSpec schemaSpec; http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/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 30f15e6..90e03c4 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 @@ -8012,6 +8012,34 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizeWithin() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " order by rowtime\n" + + " measures STRT.ts as start_ts,\n" + + " LAST(DOWN.ts) as bottom_ts,\n" + + " AVG(stdn.price) as stdn_avg\n" + + " pattern (strt down+ up+) within interval '3' second\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.price < PREV(down.price),\n" + + " up as up.price > prev(up.price)\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM `T` MATCH_RECOGNIZE(\n" + + "ORDER BY `ROWTIME`\n" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +))) WITHIN INTERVAL '3' SECOND\n" + + "SUBSET (`STDN` = (`STRT`, `DOWN`)), (`STDN2` = (`STRT`, `DOWN`))\n" + + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + //~ Inner Interfaces ------------------------------------------------------- /** http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java index ef3558f..efdd965 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java @@ -220,6 +220,31 @@ public class SqlValidatorMatchTest extends SqlValidatorTestCase { sql(sql) .fails("Pattern variable 'STRT' has already been defined"); } + + @Test public void testMatchRecognizeWithin() throws Exception { + final String sql = "select *\n" + + "from emp match_recognize (\n" + + " pattern (strt down+ up+) within ^interval '3:10' minute to second^\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("Must contain an ORDER BY clause when WITHIN is used"); + } + + @Test public void testMatchRecognizeWithin2() throws Exception { + final String sql = "select *\n" + + "from emp match_recognize (\n" + + " order by sal\n" + + " pattern (strt down+ up+) within ^interval '3:10' minute to second^\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("First column of ORDER BY must be of type TIMESTAMP"); + } } // End SqlValidatorMatchTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/dfe251d7/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index a2d9f75..cbe48a6 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -959,8 +959,8 @@ name will have been converted to upper case also. ### Scalar types -| Data type | Description | Range and examples | -|:----------- |:------------------------- |:---------------------| +| Data type | Description | Range and example literals +|:----------- |:------------------------- |:-------------------------- | BOOLEAN | Logical values | Values: TRUE, FALSE, UNKNOWN | TINYINT | 1 byte signed integer | Range is -128 to 127 | SMALLINT | 2 byte signed integer | Range is -32768 to 32767 @@ -978,7 +978,7 @@ name will have been converted to upper case also. | TIME | Time of day | Example: TIME '20:17:40' | 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 +| INTERVAL timeUnit [ TO timeUnit ] | Date time interval | Examples: INTERVAL '1-5' YEAR TO MONTH, INTERVAL '45' DAY, INTERVAL '1 2:34:56.789' DAY TO SECOND Where: @@ -1678,6 +1678,7 @@ matchRecognize: | SKIP TO variable ) ] PATTERN '(' pattern ')' + [ WITHIN intervalLiteral ] [ SUBSET subsetItem [, subsetItem ]* ] DEFINE variable AS condition [, variable AS condition ]* ')' @@ -1714,6 +1715,9 @@ patternQuantifier: | '??' | '{' { [ minRepeat ], [ maxRepeat ] } '}' ['?'] | '{' repeat '}' + +intervalLiteral: + INTERVAL 'string' timeUnit [ TO timeUnit ] {% endhighlight %} In *patternQuantifier*, *repeat* is a positive integer,
