[CALCITE-1643] AFTER MATCH sub-clause of MATCH_RECOGNIZE clause (Zhiqiang-He)
Close apache/calcite#429 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c850e227 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c850e227 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c850e227 Branch: refs/heads/master Commit: c850e227db583a697c8cc46585b33274b63ed2d0 Parents: d97c14c Author: Zhiqiang-He <[email protected]> Authored: Fri Apr 14 20:58:21 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Mon Apr 24 18:26:09 2017 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 38 ++++- .../java/org/apache/calcite/rel/core/Match.java | 15 +- .../apache/calcite/rel/core/RelFactories.java | 6 +- .../calcite/rel/logical/LogicalMatch.java | 13 +- .../calcite/rel/rel2sql/RelToSqlConverter.java | 14 +- .../java/org/apache/calcite/sql/SqlKind.java | 14 +- .../apache/calcite/sql/SqlMatchRecognize.java | 58 +++++++- .../calcite/sql2rel/SqlToRelConverter.java | 39 ++++-- .../rel/rel2sql/RelToSqlConverterTest.java | 139 +++++++++++++++++++ .../calcite/sql/parser/SqlParserTest.java | 105 ++++++++++++++ .../apache/calcite/test/SqlValidatorTest.java | 63 ++++++--- site/_docs/reference.md | 1 + 12 files changed, 458 insertions(+), 47 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 64d5936..a6672db 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -2497,6 +2497,9 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : SqlNodeList measureList = SqlNodeList.EMPTY; SqlNode pattern; SqlNodeList patternDefList; + final SqlNode after; + final SqlParserPos pos; + final SqlNode var; SqlLiteral isStrictStarts = SqlLiteral.createBoolean(false, getPos()); SqlLiteral isStrictEnds = SqlLiteral.createBoolean(false, getPos()); } @@ -2506,6 +2509,36 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : <MEASURES> measureList = MeasureColumnCommaList(getPos()) ] + ( + <AFTER> { pos = getPos(); } <MATCH> <SKIP_> + ( + <TO> + ( + LOOKAHEAD(2) + <NEXT> <ROW> { + after = SqlMatchRecognize.AfterOption.SKIP_TO_NEXT_ROW + .symbol(pos.plus(getPos())); + } + | + <FIRST> var = SimpleIdentifier() { + after = SqlMatchRecognize.SKIP_TO_FIRST.createCall( + pos.plus(var.getParserPosition()), var); + } + | + [ <LAST> ] var = SimpleIdentifier() { + after = SqlMatchRecognize.SKIP_TO_LAST.createCall( + pos.plus(var.getParserPosition()), var); + } + ) + | + <PAST> <LAST> <ROW> { + after = SqlMatchRecognize.AfterOption.SKIP_PAST_LAST_ROW + .symbol(pos.plus(getPos())); + } + ) + | + { after = null; } + ) <PATTERN> <LPAREN> ( @@ -2524,7 +2557,8 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : patternDefList = PatternDefinitionCommaList(getPos()) <RPAREN> { return new SqlMatchRecognize(startPos.plus(getPos()), tableRef, - pattern, isStrictStarts, isStrictEnds, patternDefList, measureList); + pattern, isStrictStarts, isStrictEnds, patternDefList, measureList, + after); } } @@ -5814,6 +5848,7 @@ SqlPostfixOperator PostfixRowOperator() : | < PARTITION: "PARTITION" > | < PASCAL: "PASCAL" > | < PASSTHROUGH: "PASSTHROUGH" > + | < PAST: "PAST" > | < PATH: "PATH" > | < PATTERN: "PATTERN" > | < PER: "PER" > @@ -6261,6 +6296,7 @@ String CommonNonReservedKeyWord() : | <PARTIAL> | <PASCAL> | <PASSTHROUGH> + | <PAST> | <PATH> | <PLACING> | <PLAN> http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 84ef106..ae5215d 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 @@ -59,6 +59,7 @@ public abstract class Match extends SingleRel { protected final RexNode pattern; protected final boolean strictStart; protected final boolean strictEnd; + protected final RexNode after; protected final ImmutableMap<String, RexNode> patternDefinitions; protected final Set<RexMRAggCall> aggregateCalls; protected final Map<String, SortedSet<RexMRAggCall>> aggregateCallsPreVar; @@ -76,20 +77,22 @@ public abstract class Match extends SingleRel { * @param strictEnd Whether it is a strict end pattern * @param patternDefinitions Pattern definitions * @param measures Measure definitions + * @param after After match definitions * @param rowType Row type */ protected Match(RelOptCluster cluster, RelTraitSet traitSet, RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType) { + RexNode after, RelDataType rowType) { super(cluster, traitSet, input); this.pattern = Preconditions.checkNotNull(pattern); Preconditions.checkArgument(patternDefinitions.size() > 0); this.strictStart = strictStart; this.strictEnd = strictEnd; this.patternDefinitions = ImmutableMap.copyOf(patternDefinitions); - this.rowType = rowType; + this.rowType = Preconditions.checkNotNull(rowType); this.measures = ImmutableMap.copyOf(measures); + this.after = Preconditions.checkNotNull(after); final AggregateFinder aggregateFinder = new AggregateFinder(); for (RexNode rex : this.patternDefinitions.values()) { @@ -126,6 +129,10 @@ public abstract class Match extends SingleRel { return measures; } + public RexNode getAfter() { + return after; + } + public RexNode getPattern() { return pattern; } @@ -145,7 +152,7 @@ public abstract class Match extends SingleRel { public abstract Match copy(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType); + RexNode after, RelDataType rowType); @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { if (getInputs().equals(inputs) @@ -154,7 +161,7 @@ public abstract class Match extends SingleRel { } return copy(inputs.get(0), pattern, strictStart, strictEnd, - patternDefinitions, measures, rowType); + patternDefinitions, measures, after, rowType); } @Override public RelWriter explainTerms(RelWriter pw) { http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 10f9a10..5404666 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 @@ -397,7 +397,7 @@ public class RelFactories { RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType); + RexNode after, RelDataType rowType); } /** @@ -408,9 +408,9 @@ public class RelFactories { public RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType) { + RexNode after, RelDataType rowType) { return LogicalMatch.create(input, pattern, strictStart, strictEnd, - patternDefinitions, measures, rowType); + patternDefinitions, measures, after, rowType); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 524e8a5..62a9814 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 @@ -43,14 +43,15 @@ public class LogicalMatch extends Match { * @param strictEnd Whether it is a strict end pattern * @param patternDefinitions Pattern definitions * @param measures Measure definitions + * @param after After match definitions * @param rowType Row type */ public LogicalMatch(RelOptCluster cluster, RelTraitSet traitSet, RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType) { + RexNode after, RelDataType rowType) { super(cluster, traitSet, input, pattern, strictStart, strictEnd, - patternDefinitions, measures, rowType); + patternDefinitions, measures, after, rowType); } /** @@ -59,11 +60,11 @@ public class LogicalMatch extends Match { public static LogicalMatch create(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType) { + RexNode after, RelDataType rowType) { final RelOptCluster cluster = input.getCluster(); final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE); return new LogicalMatch(cluster, traitSet, input, pattern, - strictStart, strictEnd, patternDefinitions, measures, rowType); + strictStart, strictEnd, patternDefinitions, measures, after, rowType); } //~ Methods ------------------------------------------------------ @@ -71,11 +72,11 @@ public class LogicalMatch extends Match { @Override public Match copy(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RelDataType rowType) { + RexNode after, RelDataType rowType) { final RelTraitSet traitSet = getCluster().traitSetOf(Convention.NONE); return new LogicalMatch(getCluster(), traitSet, input, pattern, strictStart, strictEnd, patternDefinitions, measures, - rowType); + after, rowType); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 ecb75a0..cec0e03 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 @@ -35,6 +35,7 @@ import org.apache.calcite.rel.core.TableScan; import org.apache.calcite.rel.core.Union; import org.apache.calcite.rel.core.Values; import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexLocalRef; import org.apache.calcite.rex.RexNode; @@ -378,6 +379,17 @@ public class RelToSqlConverter extends SqlImplementor SqlNode tableRef = x.asQueryOrValues(); + final SqlNode after; + if (e.getAfter() instanceof RexLiteral) { + SqlMatchRecognize.AfterOption value = (SqlMatchRecognize.AfterOption) + ((RexLiteral) e.getAfter()).getValue2(); + after = SqlLiteral.createSymbol(value, POS); + } else { + RexCall call = (RexCall) e.getAfter(); + String operand = RexLiteral.stringValue(call.getOperands().get(0)); + after = call.getOperator().createCall(POS, new SqlIdentifier(operand, POS)); + } + RexNode rexPattern = e.getPattern(); final SqlNode pattern = context.toSql(null, rexPattern); final SqlLiteral strictStart = SqlLiteral.createBoolean(e.isStrictStart(), POS); @@ -398,7 +410,7 @@ public class RelToSqlConverter extends SqlImplementor } final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, - pattern, strictStart, strictEnd, patternDefList, measureList); + pattern, strictStart, strictEnd, patternDefList, measureList, after); return result(matchRecognize, Expressions.list(Clause.FROM), e, null); } http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 8f49591..7a33772 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -469,6 +469,18 @@ public enum SqlKind { MATCH_NUMBER, + /** + * The "SKIP TO FIRST" qualifier of restarting point in a MATCH_RECOGNIZE + * clause. + */ + SKIP_TO_FIRST, + + /** + * The "SKIP TO LAST" qualifier of restarting point in a MATCH_RECOGNIZE + * clause. + */ + SKIP_TO_LAST, + // postfix operators /** @@ -1026,7 +1038,7 @@ public enum SqlKind { TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT, LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY, NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE, - VALUES, WITH, WITH_ITEM), + VALUES, WITH, WITH_ITEM, SKIP_TO_FIRST, SKIP_TO_LAST), AGGREGATE, DML, DDL)); /** http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 300e886..7fdc765 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java @@ -29,7 +29,7 @@ import java.util.List; import javax.annotation.Nonnull; /** - * SqlNode for Match_recognize clause + * SqlNode for MATCH_RECOGNIZE clause. */ public class SqlMatchRecognize extends SqlCall { public static final int OPERAND_TABLE_REF = 0; @@ -38,6 +38,15 @@ public class SqlMatchRecognize extends SqlCall { public static final int OPERAND_STRICT_END = 3; public static final int OPERAND_PATTERN_DEFINES = 4; public static final int OPERAND_MEASURES = 5; + public static final int OPERAND_AFTER = 6; + + public static final SqlPrefixOperator SKIP_TO_FIRST = + new SqlPrefixOperator("SKIP TO FIRST", SqlKind.SKIP_TO_FIRST, 20, null, + null, null); + + public static final SqlPrefixOperator SKIP_TO_LAST = + new SqlPrefixOperator("SKIP TO LAST", SqlKind.SKIP_TO_LAST, 20, null, + null, null); //~ Instance fields ------------------------------------------- @@ -47,11 +56,12 @@ public class SqlMatchRecognize extends SqlCall { private SqlLiteral strictEnd; private SqlNodeList patternDefList; private SqlNodeList measureList; + private SqlNode after; /** Creates a SqlMatchRecognize. */ public SqlMatchRecognize(SqlParserPos pos, SqlNode tableRef, SqlNode pattern, SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList, - SqlNodeList measureList) { + SqlNodeList measureList, SqlNode after) { super(pos); this.tableRef = Preconditions.checkNotNull(tableRef); this.pattern = Preconditions.checkNotNull(pattern); @@ -60,6 +70,7 @@ public class SqlMatchRecognize extends SqlCall { this.patternDefList = Preconditions.checkNotNull(patternDefList); Preconditions.checkArgument(patternDefList.size() > 0); this.measureList = Preconditions.checkNotNull(measureList); + this.after = after; } // ~ Methods @@ -74,7 +85,7 @@ public class SqlMatchRecognize extends SqlCall { @Override public List<SqlNode> getOperandList() { return ImmutableNullableList.of(tableRef, pattern, strictStart, strictEnd, - patternDefList, measureList); + patternDefList, measureList, after); } @Override public void unparse(SqlWriter writer, int leftPrec, @@ -107,6 +118,9 @@ public class SqlMatchRecognize extends SqlCall { case OPERAND_MEASURES: measureList = Preconditions.checkNotNull((SqlNodeList) operand); break; + case OPERAND_AFTER: + after = operand; + break; default: throw new AssertionError(i); } @@ -136,6 +150,36 @@ public class SqlMatchRecognize extends SqlCall { return measureList; } + public SqlNode getAfter() { + return after; + } + + /** + * Options for {@code AFTER MATCH} clause. + */ + public enum AfterOption { + SKIP_TO_NEXT_ROW("SKIP TO NEXT ROW"), + SKIP_PAST_LAST_ROW("SKIP PAST LAST ROW"); + + private final String sql; + + AfterOption(String sql) { + this.sql = sql; + } + + @Override public String toString() { + return sql; + } + + /** + * Creates a parse-tree node representing an occurrence of this symbol + * at a particular position in the parsed text. + */ + public SqlLiteral symbol(SqlParserPos pos) { + return SqlLiteral.createSymbol(this, pos); + } + } + /** * An operator describing a MATCH_RECOGNIZE specification. */ @@ -160,7 +204,7 @@ public class SqlMatchRecognize extends SqlCall { return new SqlMatchRecognize(pos, operands[0], operands[1], (SqlLiteral) operands[2], (SqlLiteral) operands[3], - (SqlNodeList) operands[4], (SqlNodeList) operands[5]); + (SqlNodeList) operands[4], (SqlNodeList) operands[5], operands[6]); } @Override public <R> void acceptCall( @@ -208,6 +252,12 @@ public class SqlMatchRecognize extends SqlCall { writer.endList(measureFrame); } + if (pattern.after != null) { + writer.newlineAndIndent(); + writer.sep("AFTER MATCH"); + pattern.after.unparse(writer, 0, 0); + } + writer.newlineAndIndent(); writer.sep("PATTERN"); http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 573214e..abf3fca 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2082,12 +2082,12 @@ public class SqlToRelConverter { final SqlValidatorNamespace ns = validator.getNamespace(matchRecognize); final SqlValidatorScope scope = validator.getMatchRecognizeScope(matchRecognize); - final Blackboard mrBlackBoard = createBlackboard(scope, null, false); + final Blackboard matchBb = createBlackboard(scope, null, false); final RelDataType rowType = ns.getRowType(); // convert inner query, could be a table name or a derived table SqlNode expr = matchRecognize.getTableRef(); - convertFrom(mrBlackBoard, expr); - final RelNode input = mrBlackBoard.root; + convertFrom(matchBb, expr); + final RelNode input = matchBb.root; // convert pattern final Set<String> patternVarsSet = new HashSet<>(); @@ -2120,7 +2120,29 @@ public class SqlToRelConverter { }; final RexNode patternNode = pattern.accept(patternVarVisitor); - mrBlackBoard.setPatternVarRef(true); + SqlNode afterMatch = matchRecognize.getAfter(); + if (afterMatch == null) { + afterMatch = + SqlMatchRecognize.AfterOption.SKIP_TO_NEXT_ROW.symbol(SqlParserPos.ZERO); + } + + final RexNode after; + if (afterMatch instanceof SqlCall) { + List<SqlNode> operands = ((SqlCall) afterMatch).getOperandList(); + SqlOperator operator = ((SqlCall) afterMatch).getOperator(); + assert operands.size() == 1; + SqlIdentifier id = (SqlIdentifier) operands.get(0); + assert patternVarsSet.contains(id.getSimple()) + : id.getSimple() + " not defined in pattern"; + RexNode rex = rexBuilder.makeLiteral(id.getSimple()); + after = + rexBuilder.makeCall(validator.getUnknownType(), operator, + ImmutableList.of(rex)); + } else { + after = matchBb.convertExpression(afterMatch); + } + + matchBb.setPatternVarRef(true); // convert measures final ImmutableMap.Builder<String, RexNode> measureNodes = @@ -2128,7 +2150,7 @@ public class SqlToRelConverter { for (SqlNode measure : matchRecognize.getMeasureList()) { List<SqlNode> operands = ((SqlCall) measure).getOperandList(); String alias = ((SqlIdentifier) operands.get(1)).getSimple(); - RexNode rex = mrBlackBoard.convertExpression(operands.get(0)); + RexNode rex = matchBb.convertExpression(operands.get(0)); measureNodes.put(alias, rex); } @@ -2138,11 +2160,11 @@ public class SqlToRelConverter { for (SqlNode def : matchRecognize.getPatternDefList()) { List<SqlNode> operands = ((SqlCall) def).getOperandList(); String alias = ((SqlIdentifier) operands.get(1)).getSimple(); - RexNode rex = mrBlackBoard.convertExpression(operands.get(0)); + RexNode rex = matchBb.convertExpression(operands.get(0)); definitionNodes.put(alias, rex); } - mrBlackBoard.setPatternVarRef(false); + matchBb.setPatternVarRef(false); final RelFactories.MatchFactory factory = RelFactories.DEFAULT_MATCH_FACTORY; @@ -2150,8 +2172,7 @@ public class SqlToRelConverter { factory.createMatchRecognize(input, patternNode, matchRecognize.getStrictStart().booleanValue(), matchRecognize.getStrictEnd().booleanValue(), - definitionNodes.build(), - measureNodes.build(), + definitionNodes.build(), measureNodes.build(), after, rowType); bb.setRoot(rel, false); } http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/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 15c8063..0357c6b 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 @@ -627,6 +627,7 @@ public class RelToSqlConverterTest { String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -648,6 +649,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -669,6 +671,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -690,6 +693,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -711,6 +715,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" * \"UP\" ?)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -732,6 +737,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" -} \"UP\" ?)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -754,6 +760,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { 2 } \"UP\" { 3, })\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -775,6 +782,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { , 2 } \"UP\" { 3, 5 })\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -796,6 +804,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" + -} {- \"UP\" * -})\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -818,6 +827,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"A\" \"B\" \"C\" | \"A\" \"C\" \"B\" | \"B\" \"A\" \"C\" " + "| \"B\" \"C\" \"A\" | \"C\" \"A\" \"B\" | \"C\" \"B\" \"A\")\n" @@ -840,6 +850,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -861,6 +872,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -900,6 +912,7 @@ public class RelToSqlConverterTest { + "WHERE \"customer\".\"city\" = 'San Francisco' " + "AND \"product_class\".\"product_department\" = 'Snacks') " + "MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -922,6 +935,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" @@ -943,6 +957,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) " @@ -964,6 +979,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -986,6 +1002,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1016,6 +1033,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1046,6 +1064,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1076,6 +1095,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL (RUNNING LAST(\"DOWN\".\"net_weight\", 0)) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1106,6 +1126,7 @@ public class RelToSqlConverterTest { + "FINAL COUNT(\"UP\".\"net_weight\") AS \"UP_CNT\", " + "FINAL COUNT(\"*\".\"net_weight\") AS \"DOWN_CNT\", " + "FINAL (RUNNING COUNT(\"*\".\"net_weight\")) AS \"RUNNING_CNT\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1138,6 +1159,7 @@ public class RelToSqlConverterTest { + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL (SUM(\"DOWN\".\"net_weight\") / COUNT(\"DOWN\".\"net_weight\")) " + "AS \"DOWN_CNT\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " @@ -1168,6 +1190,7 @@ public class RelToSqlConverterTest { + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1199,6 +1222,7 @@ public class RelToSqlConverterTest { + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1210,6 +1234,121 @@ public class RelToSqlConverterTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizePatternSkip1() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip to next row\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" + + " < PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)" + + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip2() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip past last row\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP PAST LAST ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" + + " < PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)" + + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip3() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip to FIRST down\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO FIRST \"DOWN\"\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" + + " < PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)" + + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip4() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip to last down\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" + + " < PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)" + + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip5() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip to down\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)" + + " < PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)" + + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 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/c850e227/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 37c6c3a..05089bf 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 @@ -7636,6 +7636,111 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizePatternSkip1() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " after match skip to next row\n" + + " pattern (strt down+ up+)\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" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "DEFINE " + + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip2() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " after match skip past last row\n" + + " pattern (strt down+ up+)\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" + + "AFTER MATCH SKIP PAST LAST ROW\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "DEFINE " + + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip3() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " after match skip to FIRST down\n" + + " pattern (strt down+ up+)\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" + + "AFTER MATCH SKIP TO FIRST `DOWN`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "DEFINE " + + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip4() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " after match skip to LAST down\n" + + " pattern (strt down+ up+)\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" + + "AFTER MATCH SKIP TO LAST `DOWN`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "DEFINE " + + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizePatternSkip5() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " after match skip to down\n" + + " pattern (strt down+ up+)\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" + + "AFTER MATCH SKIP TO LAST `DOWN`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\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/c850e227/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 94551f9..fe9261d 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -9225,11 +9225,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { } @Test public void testMatchRecognizeDefines4() throws Exception { - final String sql = "select * \n" - + " from emp match_recognize \n" - + " (\n" + final String sql = "select *\n" + + " from emp match_recognize (\n" + " pattern (strt down+ up+)\n" - + " define \n" + + " define\n" + " down as down.sal < PREV(down.sal),\n" + " up as up.sal > FIRST(^PREV(up.sal)^)\n" + " ) mr"; @@ -9238,11 +9237,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { } @Test public void testMatchRecognizeDefines5() throws Exception { - final String sql = "select * \n" - + " from emp match_recognize \n" - + " (\n" + final String sql = "select *\n" + + " from emp match_recognize (\n" + " pattern (strt down+ up+)\n" - + " define \n" + + " define\n" + " down as down.sal < PREV(down.sal),\n" + " up as up.sal > FIRST(^FIRST(up.sal)^)\n" + " ) mr"; @@ -9251,11 +9249,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { } @Test public void testMatchRecognizeDefines6() throws Exception { - final String sql = "select * \n" - + " from emp match_recognize \n" - + " (\n" + final String sql = "select *\n" + + " from emp match_recognize (\n" + " pattern (strt down+ up+)\n" - + " define \n" + + " define\n" + " down as down.sal < PREV(down.sal),\n" + " up as up.sal > ^COUNT(down.sal, up.sal)^\n" + " ) mr"; @@ -9653,12 +9650,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { @Test public void testMatchRecognizeMeasures1() throws Exception { final String sql = "select *\n" - + " from emp match_recognize\n" - + " (\n" - + " measures " - + " STRT.sal as start_sal," - + " ^LAST(null)^ as bottom_sal," - + " LAST(up.ts) as end_sal" + + " from emp match_recognize (\n" + + " measures STRT.sal as start_sal," + + " ^LAST(null)^ as bottom_sal," + + " LAST(up.ts) as end_sal" + " pattern (strt down+ up+)\n" + " define\n" + " down as down.sal < PREV(down.sal),\n" @@ -9668,6 +9663,38 @@ public class SqlValidatorTest extends SqlValidatorTestCase { .fails("Null parameters in 'LAST\\(NULL, 0\\)'"); } + @Test public void testMatchRecognizeSkipTo1() throws Exception { + final String sql = "select *\n" + + " from emp match_recognize (\n" + + " after match skip to ^null^\n" + + " measures\n" + + " STRT.sal as start_sal,\n" + + " LAST(up.ts) as end_sal\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("(?s).*Encountered \"to null\" at .*"); + } + + @Test public void testMatchRecognizeSkipTo2() throws Exception { + final String sql = "select *\n" + + " from emp match_recognize (\n" + + " after match skip to ^no_exists^\n" + + " measures\n" + + " STRT.sal as start_sal," + + " LAST(up.ts) as end_sal" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("(?s).*Encountered \"measures\" at .*"); + } + } // End SqlValidatorTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/c850e227/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index d4d64f2..c942c14 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -596,6 +596,7 @@ PARTIAL, **PARTITION**, PASCAL, PASSTHROUGH, +PAST, PATH, **PATTERN**, **PER**,
