Repository: calcite Updated Branches: refs/heads/master 868d64140 -> 7a56eb63d
[CALCITE-1644] SUBSET sub-clause of MATCH_RECOGNIZE (Zhiqiang-He) Close apache/calcite#445 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/7a56eb63 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/7a56eb63 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/7a56eb63 Branch: refs/heads/master Commit: 7a56eb63dfbd3310d14c85270f667eb3f8fa6aa3 Parents: 868d641 Author: Zhiqiang-He <[email protected]> Authored: Wed May 10 11:28:26 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Mon May 15 22:33:38 2017 -0400 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 43 ++++++- .../java/org/apache/calcite/rel/core/Match.java | 31 +++-- .../apache/calcite/rel/core/RelFactories.java | 7 +- .../calcite/rel/logical/LogicalMatch.java | 17 ++- .../calcite/rel/rel2sql/RelToSqlConverter.java | 16 ++- .../apache/calcite/runtime/CalciteResource.java | 1 + .../apache/calcite/sql/SqlMatchRecognize.java | 27 +++- .../calcite/sql/validate/SqlValidatorImpl.java | 21 ++++ .../calcite/sql2rel/SqlToRelConverter.java | 18 ++- .../rel/rel2sql/RelToSqlConverterTest.java | 125 +++++++++++++++++++ .../calcite/sql/parser/SqlParserTest.java | 73 +++++++++++ .../calcite/test/SqlValidatorMatchTest.java | 25 ++++ site/_docs/reference.md | 11 +- 13 files changed, 382 insertions(+), 33 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 6b63d28..83ed172 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -2508,6 +2508,7 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : final SqlNode after; final SqlParserPos pos; final SqlNode var; + SqlNodeList subsetList = SqlNodeList.EMPTY; SqlLiteral isStrictStarts = SqlLiteral.createBoolean(false, getPos()); SqlLiteral isStrictEnds = SqlLiteral.createBoolean(false, getPos()); } @@ -2561,12 +2562,16 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : { isStrictEnds = SqlLiteral.createBoolean(false, getPos()); } ) <RPAREN> + [ + <SUBSET> + subsetList = SubsetDefinitionCommaList(getPos()) + ] <DEFINE> patternDefList = PatternDefinitionCommaList(getPos()) <RPAREN> { return new SqlMatchRecognize(startPos.plus(getPos()), tableRef, pattern, isStrictStarts, isStrictEnds, patternDefList, measureList, - after); + after, subsetList); } } @@ -2761,6 +2766,42 @@ SqlNode PatternPrimary() : } } +SqlNodeList SubsetDefinitionCommaList(final SqlParserPos pos) : +{ + SqlNode e; + final List<SqlNode> eList = new ArrayList<SqlNode>(); +} +{ + e = SubsetDefinition() { + eList.add(e); + } + ( + <COMMA> + e = SubsetDefinition() { + eList.add(e); + } + )* + { + return new SqlNodeList(eList, pos.plus(getPos())); + } +} + +SqlNode SubsetDefinition() : +{ + final SqlNode var; + final SqlNodeList varList; +} +{ + var = SimpleIdentifier() + <EQ> + <LPAREN> + varList = ExpressionCommaList(null, ExprContext.ACCEPT_NON_QUERY) + <RPAREN> { + return SqlStdOperatorTable.EQUALS.createCall( + var.getParserPosition().plus(getPos()), var, varList); + } +} + SqlNodeList PatternDefinitionCommaList(SqlParserPos pos) : { SqlNode e; http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 ae5215d..920a6e2 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 @@ -63,13 +63,13 @@ public abstract class Match extends SingleRel { protected final ImmutableMap<String, RexNode> patternDefinitions; protected final Set<RexMRAggCall> aggregateCalls; protected final Map<String, SortedSet<RexMRAggCall>> aggregateCallsPreVar; + protected final ImmutableMap<String, SortedSet<String>> subsets; //~ Constructors ----------------------------------------------- /** * Creates a Match. - * - * @param cluster Cluster + * @param cluster Cluster * @param traitSet Trait set * @param input Input relational expression * @param pattern Regular expression that defines pattern variables @@ -78,12 +78,14 @@ public abstract class Match extends SingleRel { * @param patternDefinitions Pattern definitions * @param measures Measure definitions * @param after After match definitions + * @param subsets Subsets of pattern variables * @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, - RexNode after, RelDataType rowType) { + RexNode after, Map<String, ? extends SortedSet<String>> subsets, + RelDataType rowType) { super(cluster, traitSet, input); this.pattern = Preconditions.checkNotNull(pattern); Preconditions.checkArgument(patternDefinitions.size() > 0); @@ -93,6 +95,7 @@ public abstract class Match extends SingleRel { this.rowType = Preconditions.checkNotNull(rowType); this.measures = ImmutableMap.copyOf(measures); this.after = Preconditions.checkNotNull(after); + this.subsets = copyMap(subsets); final AggregateFinder aggregateFinder = new AggregateFinder(); for (RexNode rex : this.patternDefinitions.values()) { @@ -109,15 +112,16 @@ public abstract class Match extends SingleRel { aggregateCalls = ImmutableSortedSet.copyOf(aggregateFinder.aggregateCalls); aggregateCallsPreVar = - copy(aggregateFinder.aggregateCallsPerVar); + copyMap(aggregateFinder.aggregateCallsPerVar); } - /** Creates an immutable copy of a map of sorted sets. */ + /** Creates an immutable map of a map of sorted sets. */ private static <K extends Comparable<K>, V> - ImmutableSortedMap<K, SortedSet<V>> copy(Map<K, SortedSet<V>> map) { + ImmutableSortedMap<K, SortedSet<V>> copyMap( + Map<K, ? extends SortedSet<V>> map) { final ImmutableSortedMap.Builder<K, SortedSet<V>> b = ImmutableSortedMap.naturalOrder(); - for (Map.Entry<K, SortedSet<V>> e : map.entrySet()) { + for (Map.Entry<K, ? extends SortedSet<V>> e : map.entrySet()) { b.put(e.getKey(), ImmutableSortedSet.copyOf(e.getValue())); } return b.build(); @@ -149,10 +153,15 @@ public abstract class Match extends SingleRel { return patternDefinitions; } + public ImmutableMap<String, SortedSet<String>> getSubsets() { + return subsets; + } + public abstract Match copy(RelNode input, RexNode pattern, - boolean strictStart, boolean strictEnd, - Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, RelDataType rowType); + boolean strictStart, boolean strictEnd, + Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, + RexNode after, Map<String, ? extends SortedSet<String>> subsets, + RelDataType rowType); @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { if (getInputs().equals(inputs) @@ -161,7 +170,7 @@ public abstract class Match extends SingleRel { } return copy(inputs.get(0), pattern, strictStart, strictEnd, - patternDefinitions, measures, after, rowType); + patternDefinitions, measures, after, subsets, rowType); } @Override public RelWriter explainTerms(RelWriter pw) { http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 5404666..7cefe9f 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 @@ -49,6 +49,7 @@ import com.google.common.collect.ImmutableList; import java.util.List; import java.util.Map; import java.util.Set; +import java.util.TreeSet; /** * Contains factory interface and default implementation for creating various @@ -397,7 +398,7 @@ public class RelFactories { RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, RelDataType rowType); + RexNode after, Map<String, TreeSet<String>> subsets, RelDataType rowType); } /** @@ -408,9 +409,9 @@ public class RelFactories { public RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, RelDataType rowType) { + RexNode after, Map<String, TreeSet<String>> subsets, RelDataType rowType) { return LogicalMatch.create(input, pattern, strictStart, strictEnd, - patternDefinitions, measures, after, rowType); + patternDefinitions, measures, after, subsets, rowType); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 62a9814..2fc751e 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 @@ -25,6 +25,8 @@ import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rex.RexNode; import java.util.Map; +import java.util.SortedSet; +import java.util.TreeSet; /** * Sub-class of {@link Match} @@ -44,14 +46,16 @@ public class LogicalMatch extends Match { * @param patternDefinitions Pattern definitions * @param measures Measure definitions * @param after After match definitions + * @param subsets Subset 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, - RexNode after, RelDataType rowType) { + RexNode after, Map<String, ? extends SortedSet<String>> subsets, + RelDataType rowType) { super(cluster, traitSet, input, pattern, strictStart, strictEnd, - patternDefinitions, measures, after, rowType); + patternDefinitions, measures, after, subsets, rowType); } /** @@ -60,11 +64,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, - RexNode after, RelDataType rowType) { + RexNode after, Map<String, TreeSet<String>> subsets, 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, after, rowType); + strictStart, strictEnd, patternDefinitions, measures, after, subsets, rowType); } //~ Methods ------------------------------------------------------ @@ -72,11 +76,12 @@ 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, - RexNode after, RelDataType rowType) { + RexNode after, Map<String, ? extends SortedSet<String>> subsets, + RelDataType rowType) { final RelTraitSet traitSet = getCluster().traitSetOf(Convention.NONE); return new LogicalMatch(getCluster(), traitSet, input, pattern, strictStart, strictEnd, patternDefinitions, measures, - after, rowType); + after, subsets, rowType); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 cec0e03..4a07e55 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 @@ -71,6 +71,7 @@ import java.util.ArrayList; import java.util.List; import java.util.Locale; import java.util.Map; +import java.util.SortedSet; /** * Utility to convert relational expressions to SQL abstract syntax tree. @@ -395,6 +396,18 @@ public class RelToSqlConverter extends SqlImplementor final SqlLiteral strictStart = SqlLiteral.createBoolean(e.isStrictStart(), POS); final SqlLiteral strictEnd = SqlLiteral.createBoolean(e.isStrictEnd(), POS); + final SqlNodeList subsetList = new SqlNodeList(POS); + for (Map.Entry<String, SortedSet<String>> entry : e.getSubsets().entrySet()) { + SqlNode left = new SqlIdentifier(entry.getKey(), POS); + List<SqlNode> rhl = Lists.newArrayList(); + for (String right : entry.getValue()) { + rhl.add(new SqlIdentifier(right, POS)); + } + subsetList.add( + SqlStdOperatorTable.EQUALS.createCall(POS, left, + new SqlNodeList(rhl, POS))); + } + final SqlNodeList measureList = new SqlNodeList(POS); for (Map.Entry<String, RexNode> entry : e.getMeasures().entrySet()) { final String alias = entry.getKey(); @@ -410,7 +423,8 @@ public class RelToSqlConverter extends SqlImplementor } final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, - pattern, strictStart, strictEnd, patternDefList, measureList, after); + pattern, strictStart, strictEnd, patternDefList, measureList, after, + subsetList); return result(matchRecognize, Expressions.list(Clause.FROM), e, null); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 5cb45dc..e785a15 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -698,6 +698,7 @@ public interface CalciteResource { @BaseMessage("Unknown pattern ''{0}''") ExInst<SqlValidatorException> unknownPattern(String call); + } // End CalciteResource.java http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 7fdc765..dfc87f8 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java @@ -39,6 +39,7 @@ public class SqlMatchRecognize extends SqlCall { 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 int OPERAND_SUBSET = 7; public static final SqlPrefixOperator SKIP_TO_FIRST = new SqlPrefixOperator("SKIP TO FIRST", SqlKind.SKIP_TO_FIRST, 20, null, @@ -57,11 +58,12 @@ public class SqlMatchRecognize extends SqlCall { private SqlNodeList patternDefList; private SqlNodeList measureList; private SqlNode after; + private SqlNodeList subsetList; /** Creates a SqlMatchRecognize. */ public SqlMatchRecognize(SqlParserPos pos, SqlNode tableRef, SqlNode pattern, SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList, - SqlNodeList measureList, SqlNode after) { + SqlNodeList measureList, SqlNode after, SqlNodeList subsetList) { super(pos); this.tableRef = Preconditions.checkNotNull(tableRef); this.pattern = Preconditions.checkNotNull(pattern); @@ -71,6 +73,7 @@ public class SqlMatchRecognize extends SqlCall { Preconditions.checkArgument(patternDefList.size() > 0); this.measureList = Preconditions.checkNotNull(measureList); this.after = after; + this.subsetList = subsetList; } // ~ Methods @@ -85,7 +88,7 @@ public class SqlMatchRecognize extends SqlCall { @Override public List<SqlNode> getOperandList() { return ImmutableNullableList.of(tableRef, pattern, strictStart, strictEnd, - patternDefList, measureList, after); + patternDefList, measureList, after, subsetList); } @Override public void unparse(SqlWriter writer, int leftPrec, @@ -121,6 +124,9 @@ public class SqlMatchRecognize extends SqlCall { case OPERAND_AFTER: after = operand; break; + case OPERAND_SUBSET: + subsetList = (SqlNodeList) operand; + break; default: throw new AssertionError(i); } @@ -154,6 +160,10 @@ public class SqlMatchRecognize extends SqlCall { return after; } + public SqlNodeList getSubsetList() { + return subsetList; + } + /** * Options for {@code AFTER MATCH} clause. */ @@ -200,11 +210,12 @@ public class SqlMatchRecognize extends SqlCall { SqlParserPos pos, SqlNode... operands) { assert functionQualifier == null; - assert operands.length == 6; + assert operands.length == 8; return new SqlMatchRecognize(pos, operands[0], operands[1], (SqlLiteral) operands[2], (SqlLiteral) operands[3], - (SqlNodeList) operands[4], (SqlNodeList) operands[5], operands[6]); + (SqlNodeList) operands[4], (SqlNodeList) operands[5], operands[6], + (SqlNodeList) operands[7]); } @Override public <R> void acceptCall( @@ -271,6 +282,14 @@ public class SqlMatchRecognize extends SqlCall { } writer.endList(patternFrame); + if (pattern.subsetList != null && pattern.subsetList.size() > 0) { + writer.newlineAndIndent(); + writer.sep("SUBSET"); + SqlWriter.Frame subsetFrame = writer.startList("", ""); + pattern.subsetList.unparse(writer, 0, 0); + writer.endList(subsetFrame); + } + writer.newlineAndIndent(); writer.sep("DEFINE"); http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 499cdc9..65d53a2 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 @@ -4505,6 +4505,27 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { validateDefinitions(matchRecognize, scope); + SqlNodeList subsets = matchRecognize.getSubsetList(); + if (subsets != null && subsets.size() > 0) { + for (SqlNode node : subsets) { + List<SqlNode> operands = ((SqlCall) node).getOperandList(); + String leftString = ((SqlIdentifier) operands.get(0)).getSimple(); + if (scope.getPatternVars().contains(leftString)) { + throw newValidationError(operands.get(0), + RESOURCE.patternVarAlreadyDefined(leftString)); + } + scope.addPatternVar(leftString); + for (SqlNode right : (SqlNodeList) operands.get(1)) { + SqlIdentifier id = (SqlIdentifier) right; + if (!scope.getPatternVars().contains(id.getSimple())) { + throw newValidationError(id, + RESOURCE.unknownPattern(id.getSimple())); + } + scope.addPatternVar(id.getSimple()); + } + } + } + // validate AFTER ... SKIP TO final SqlNode skipTo = matchRecognize.getAfter(); if (skipTo instanceof SqlCall) { http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 cc949e2..d5fc1c0 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2120,6 +2120,22 @@ public class SqlToRelConverter { }; final RexNode patternNode = pattern.accept(patternVarVisitor); + // convert subset + final SqlNodeList subsets = matchRecognize.getSubsetList(); + final Map<String, TreeSet<String>> subsetMap = Maps.newHashMap(); + for (SqlNode node : subsets) { + List<SqlNode> operands = ((SqlCall) node).getOperandList(); + SqlIdentifier left = (SqlIdentifier) operands.get(0); + patternVarsSet.add(left.getSimple()); + SqlNodeList rights = (SqlNodeList) operands.get(1); + final TreeSet<String> list = new TreeSet<String>(); + for (SqlNode right : rights) { + assert right instanceof SqlIdentifier; + list.add(((SqlIdentifier) right).getSimple()); + } + subsetMap.put(left.getSimple(), list); + } + SqlNode afterMatch = matchRecognize.getAfter(); if (afterMatch == null) { afterMatch = @@ -2173,7 +2189,7 @@ public class SqlToRelConverter { matchRecognize.getStrictStart().booleanValue(), matchRecognize.getStrictEnd().booleanValue(), definitionNodes.build(), measureNodes.build(), after, - rowType); + subsetMap, rowType); bb.setRoot(rel, false); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/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 0357c6b..af122d5 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 @@ -1349,6 +1349,131 @@ public class RelToSqlConverterTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizeSubset1() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " after match skip to down\n" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down)\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" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\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 testMatchRecognizeSubset2() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " measures STRT.\"net_weight\" as start_nw," + + " LAST(DOWN.\"net_weight\") as bottom_nw," + + " AVG(STDN.\"net_weight\") as avg_stdn" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") " + + "MATCH_RECOGNIZE(\n" + + "MEASURES " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "FINAL (SUM(\"STDN\".\"net_weight\") / " + + "COUNT(\"STDN\".\"net_weight\")) AS \"AVG_STDN\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(\"UP\".\"net_weight\", 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizeSubset3() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " measures STRT.\"net_weight\" as start_nw," + + " LAST(DOWN.\"net_weight\") as bottom_nw," + + " SUM(STDN.\"net_weight\") as avg_stdn" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") " + + "MATCH_RECOGNIZE(\n" + + "MEASURES " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "FINAL SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(\"UP\".\"net_weight\", 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizeSubset4() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " measures STRT.\"net_weight\" as start_nw," + + " LAST(DOWN.\"net_weight\") as bottom_nw," + + " SUM(STDN.\"net_weight\") as avg_stdn" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") " + + "MATCH_RECOGNIZE(\n" + + "MEASURES " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "FINAL SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(\"UP\".\"net_weight\", 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/7a56eb63/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 613191d..d6e8d75 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 @@ -7758,6 +7758,79 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizeSubset1() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down)" + + " 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" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "SUBSET (`STDN` = (`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); + } + + @Test public void testMatchRecognizeSubset2() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " measures STRT.ts as start_ts," + + " LAST(DOWN.ts) as bottom_ts," + + " AVG(stdn.price) as stdn_avg" + + " pattern (strt down+ up+)\n" + + " subset stdn = (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" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "SUBSET (`STDN` = (`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); + } + + @Test public void testMatchRecognizeSubset3() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " measures STRT.ts as start_ts," + + " LAST(DOWN.ts) as bottom_ts," + + " AVG(stdn.price) as stdn_avg" + + " pattern (strt down+ up+)\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" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\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/7a56eb63/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 c0fca1c..ef3558f 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorMatchTest.java @@ -195,6 +195,31 @@ public class SqlValidatorMatchTest extends SqlValidatorTestCase { .ok(); } + @Test public void testMatchRecognizeSubset() throws Exception { + final String sql = "select *\n" + + "from emp match_recognize (\n" + + " pattern (strt down+ up+)\n" + + " subset stdn = (^strt1^, down)\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("Unknown pattern 'STRT1'"); + } + + @Test public void testMatchRecognizeSubset2() throws Exception { + final String sql = "select *\n" + + "from emp match_recognize (\n" + + " pattern (strt down+ up+)\n" + + " subset ^strt^ = (strt, down)\n" + + " define\n" + + " down as down.sal < PREV(down.sal),\n" + + " up as up.sal > prev(up.sal)\n" + + " ) mr"; + sql(sql) + .fails("Pattern variable 'STRT' has already been defined"); + } } // End SqlValidatorMatchTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/7a56eb63/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 15c3c0d..88a3bb0 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1654,10 +1654,13 @@ matchRecognize: | SKIP TO variable ) ] PATTERN '(' pattern ')' - [ SUBSET variable [, variable ]* ] + [ SUBSET subsetItem [, subsetItem ]* ] DEFINE variable AS condition [, variable AS condition ]* ')' +subsetItem: + variable = '(' variable [, variable ]* ')' + measureColumn: expression AS alias @@ -1695,8 +1698,4 @@ and *minRepeat* and *maxRepeat* are non-negative integers. The following clauses are not implemented: * `PARTITION BY` -* `ORDER BY` -* `MEASURES` -* `ON ROW PER MATCH`, `ALL ROWS PER MATCH` -* `AFTER MATCH` -* `SUBSET` +* `ORDER BY` \ No newline at end of file
