Repository: calcite Updated Branches: refs/heads/branch-1.14 1e0472b7b -> 5410e79aa (forced update)
[CALCITE-1943] Add back NavigationExpander and NavigationReplacer in SqlValidatorImpl (Dian Fu) It will replace clauses such as A.price with PREV(A.price, 0) and makes the implementation of RexVisitor.visitPatternFieldRef(RexPatternFieldRef) more unified. Otherwise, it is difficult to implement this method. If it returns the specified field, then the navigation such as PREV(A.price, 1) becomes impossible; if not, then comparisons such as A.price > PREV(A.price, 1) becomes meaningless. Close apache/calcite#516 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/d3a7c0d7 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/d3a7c0d7 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/d3a7c0d7 Branch: refs/heads/branch-1.14 Commit: d3a7c0d794cb9d0079fb37999dcd0dd6992f2041 Parents: a050ff5 Author: Dian Fu <[email protected]> Authored: Tue Aug 15 17:19:00 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Tue Aug 29 10:11:17 2017 -0700 ---------------------------------------------------------------------- .../calcite/sql/validate/SqlValidatorImpl.java | 166 ++++++++----- .../rel/rel2sql/RelToSqlConverterTest.java | 238 +++++++++---------- .../calcite/test/SqlToRelConverterTest.java | 21 ++ .../calcite/test/SqlToRelConverterTest.xml | 54 ++++- 4 files changed, 285 insertions(+), 194 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/d3a7c0d7/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 5509b9f..68106a9 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 @@ -31,6 +31,8 @@ import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.rel.type.RelRecordType; import org.apache.calcite.rex.RexBuilder; import org.apache.calcite.rex.RexNode; +import org.apache.calcite.rex.RexPatternFieldRef; +import org.apache.calcite.rex.RexVisitor; import org.apache.calcite.runtime.CalciteContextException; import org.apache.calcite.runtime.CalciteException; import org.apache.calcite.runtime.Feature; @@ -4908,7 +4910,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { aliases.add(alias); SqlNode expand = expand(measure, scope); - validateMeasures(expand, allRows); + expand = navigationInMeasure(expand, allRows); setOriginal(expand, measure); inferUnknownTypes(unknownType, scope, expand); @@ -4933,9 +4935,22 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return fields; } - private void validateMeasures(SqlNode node, boolean allRows) { + private SqlNode navigationInMeasure(SqlNode node, boolean allRows) { final Set<String> prefix = node.accept(new PatternValidator(true)); Util.discard(prefix); + final List<SqlNode> ops = ((SqlCall) node).getOperandList(); + + final SqlOperator defaultOp = + allRows ? SqlStdOperatorTable.RUNNING : SqlStdOperatorTable.FINAL; + final SqlNode op0 = ops.get(0); + if (!isRunningOrFinal(op0.getKind()) + || !allRows && op0.getKind() == SqlKind.RUNNING) { + SqlNode newNode = defaultOp.createCall(SqlParserPos.ZERO, op0); + node = SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, newNode, ops.get(1)); + } + + node = new NavigationExpander().go(node); + return node; } private void validateDefinitions(SqlMatchRecognize mr, @@ -4956,7 +4971,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { for (SqlNode item : mr.getPatternDefList().getList()) { final String alias = alias(item); SqlNode expand = expand(item, scope); - validateDefine(expand, alias); + expand = navigationInDefine(expand, alias); setOriginal(expand, item); inferUnknownTypes(booleanType, scope, expand); @@ -4992,10 +5007,15 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return identifier.getSimple(); } - /** Checks that all pattern variables within a function are the same. */ - private void validateDefine(SqlNode node, String alpha) { + /** Checks that all pattern variables within a function are the same, + * and canonizes expressions such as {@code PREV(B.price)} to + * {@code LAST(B.price, 0)}. */ + private SqlNode navigationInDefine(SqlNode node, String alpha) { Set<String> prefix = node.accept(new PatternValidator(false)); Util.discard(prefix); + node = new NavigationExpander().go(node); + node = new NavigationReplacer(alpha).go(node); + return node; } public void validateAggregateParams(SqlCall aggCall, SqlNode filter, @@ -5764,44 +5784,35 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { * Modify the nodes in navigation function * such as FIRST, LAST, PREV AND NEXT. */ - private class NavigationModifier extends SqlBasicVisitor<SqlNode> { - @Override public SqlNode visit(SqlLiteral literal) { - return literal; - } - - @Override public SqlNode visit(SqlIntervalQualifier intervalQualifier) { - return intervalQualifier; - } - - @Override public SqlNode visit(SqlDataTypeSpec type) { - return type; - } - - @Override public SqlNode visit(SqlDynamicParam param) { - return param; - } - + private static class NavigationModifier extends SqlShuttle { public SqlNode go(SqlNode node) { return node.accept(this); } } /** - * Expand navigation expression : - * eg: PREV(A.price + A.amount) to PREV(A.price) + PREV(A.amount) - * eg: FIRST(A.price * 2) to FIST(A.PRICE) * 2 + * Shuttle that expands navigation expressions in a MATCH_RECOGNIZE clause. + * + * <p>Examples: + * + * <ul> + * <li>{@code PREV(A.price + A.amount)} → + * {@code PREV(A.price) + PREV(A.amount)} + * + * <li>{@code FIRST(A.price * 2)} → {@code FIRST(A.PRICE) * 2} + * </ul> */ - private class NavigationExpander extends NavigationModifier { - SqlOperator currentOperator; - SqlNode currentOffset; + private static class NavigationExpander extends NavigationModifier { + final SqlOperator op; + final SqlNode offset; NavigationExpander() { - + this(null, null); } NavigationExpander(SqlOperator operator, SqlNode offset) { - this.currentOffset = offset; - this.currentOperator = operator; + this.offset = offset; + this.op = operator; } @Override public SqlNode visit(SqlCall call) { @@ -5826,33 +5837,59 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { innerOperands.get(0), offset); } } - return inner.accept(new NavigationExpander(call.getOperator(), offset)); + SqlNode newInnerNode = + inner.accept(new NavigationExpander(call.getOperator(), offset)); + if (op != null) { + newInnerNode = op.createCall(SqlParserPos.ZERO, newInnerNode, + this.offset); + } + return newInnerNode; } - for (SqlNode node : operands) { - SqlNode newNode = node.accept(new NavigationExpander()); - if (currentOperator != null) { - newNode = currentOperator.createCall(SqlParserPos.ZERO, newNode, currentOffset); + if (operands.size() > 0) { + for (SqlNode node : operands) { + if (node != null) { + SqlNode newNode = node.accept(new NavigationExpander()); + if (op != null) { + newNode = op.createCall(SqlParserPos.ZERO, newNode, offset); + } + newOperands.add(newNode); + } else { + newOperands.add(null); + } + } + return call.getOperator().createCall(SqlParserPos.ZERO, newOperands); + } else { + if (op == null) { + return call; + } else { + return op.createCall(SqlParserPos.ZERO, call, offset); } - newOperands.add(newNode); } - return call.getOperator().createCall(SqlParserPos.ZERO, newOperands); } @Override public SqlNode visit(SqlIdentifier id) { - if (currentOperator == null) { + if (op == null) { return id; } else { - return currentOperator.createCall(SqlParserPos.ZERO, id, currentOffset); + return op.createCall(SqlParserPos.ZERO, id, offset); } } } /** - * Replace {@code A as A.price > PREV(B.price)} - * with {@code PREV(A.price, 0) > last(B.price, 0)}. + * Shuttle that replaces {@code A as A.price > PREV(B.price)} with + * {@code PREV(A.price, 0) > LAST(B.price, 0)}. + * + * <p>Replacing {@code A.price} with {@code PREV(A.price, 0)} makes the + * implementation of + * {@link RexVisitor#visitPatternFieldRef(RexPatternFieldRef)} more unified. + * Otherwise, it's difficult to implement this method. If it returns the + * specified field, then the navigation such as {@code PREV(A.price, 1)} + * becomes impossible; if not, then comparisons such as + * {@code A.price > PREV(A.price, 1)} become meaningless. */ - private class NavigationReplacer extends NavigationModifier { + private static class NavigationReplacer extends NavigationModifier { private final String alpha; NavigationReplacer(String alpha) { @@ -5867,19 +5904,16 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return call; } - List<SqlNode> operands = call.getOperandList(); switch (kind) { case PREV: - String name = ((SqlIdentifier) operands.get(0)).names.get(0); - return name.equals(alpha) ? call - : SqlStdOperatorTable.LAST.createCall(SqlParserPos.ZERO, operands); - default: - List<SqlNode> newOperands = new ArrayList<>(); - for (SqlNode op : operands) { - newOperands.add(op.accept(this)); + final List<SqlNode> operands = call.getOperandList(); + if (operands.get(0) instanceof SqlIdentifier) { + String name = ((SqlIdentifier) operands.get(0)).names.get(0); + return name.equals(alpha) ? call + : SqlStdOperatorTable.LAST.createCall(SqlParserPos.ZERO, operands); } - return call.getOperator().createCall(SqlParserPos.ZERO, newOperands); } + return super.visit(call); } @Override public SqlNode visit(SqlIdentifier id) { @@ -5959,10 +5993,12 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } for (SqlNode node : operands) { - vars.addAll( - node.accept( - new PatternValidator(isMeasure, firstLastCount, prevNextCount, - aggregateCount))); + if (node != null) { + vars.addAll( + node.accept( + new PatternValidator(isMeasure, firstLastCount, prevNextCount, + aggregateCount))); + } } if (isSingle) { @@ -5974,13 +6010,17 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } break; default: - if (vars.isEmpty()) { - throw newValidationError(call, - Static.RESOURCE.patternFunctionNullCheck(call.toString())); - } - if (vars.size() != 1) { - throw newValidationError(call, - Static.RESOURCE.patternFunctionVariableCheck(call.toString())); + if (operands.size() == 0 + || !(operands.get(0) instanceof SqlCall) + || ((SqlCall) operands.get(0)).getOperator() != SqlStdOperatorTable.CLASSIFIER) { + if (vars.isEmpty()) { + throw newValidationError(call, + Static.RESOURCE.patternFunctionNullCheck(call.toString())); + } + if (vars.size() != 1) { + throw newValidationError(call, + Static.RESOURCE.patternFunctionVariableCheck(call.toString())); + } } break; } http://git-wip-us.apache.org/repos/asf/calcite/blob/d3a7c0d7/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 d0749b9..b72edfc 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 @@ -993,9 +993,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1016,9 +1016,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1039,9 +1039,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1062,9 +1062,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1085,9 +1085,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" * \"UP\" ?)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1108,9 +1108,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" -} \"UP\" ?)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1131,9 +1131,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { 2 } \"UP\" { 3, })\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1154,9 +1154,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { , 2 } \"UP\" { 3, 5 })\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1177,9 +1177,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" + -} {- \"UP\" * -})\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1203,9 +1203,9 @@ public class RelToSqlConverterTest { + "(\"A\" \"B\" \"C\" | \"A\" \"C\" \"B\" | \"B\" \"A\" \"C\" " + "| \"B\" \"C\" \"A\" | \"C\" \"A\" \"B\" | \"C\" \"B\" \"A\")\n" + "DEFINE " - + "\"A\" AS \"A\".\"net_weight\" < PREV(\"A\".\"net_weight\", 1), " - + "\"B\" AS \"B\".\"net_weight\" > PREV(\"B\".\"net_weight\", 1), " - + "\"C\" AS \"C\".\"net_weight\" < PREV(\"C\".\"net_weight\", 1))"; + + "\"A\" AS PREV(\"A\".\"net_weight\", 0) < PREV(\"A\".\"net_weight\", 1), " + + "\"B\" AS PREV(\"B\".\"net_weight\", 0) > PREV(\"B\".\"net_weight\", 1), " + + "\"C\" AS PREV(\"C\".\"net_weight\", 0) < PREV(\"C\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1225,9 +1225,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1248,9 +1248,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"net_weight\""; sql(sql).ok(expected); @@ -1289,9 +1289,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"net_weight\""; sql(sql).ok(expected); @@ -1313,10 +1313,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1336,9 +1336,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "FIRST(\"DOWN\".\"net_weight\", 0), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "LAST(\"UP\".\"net_weight\", 0))"; sql(sql).ok(expected); } @@ -1359,10 +1359,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "LAST(\"UP\".\"net_weight\" + \"UP\".\"gross_weight\", 0))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "LAST(\"UP\".\"net_weight\", 0) + LAST(\"UP\".\"gross_weight\", 0))"; sql(sql).ok(expected); } @@ -1383,11 +1383,11 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "PREV(LAST(\"UP\".\"net_weight\" + " - + "\"UP\".\"gross_weight\", 0), 3))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(LAST(\"UP\".\"net_weight\", 0) + " + + "LAST(\"UP\".\"gross_weight\", 0), 3))"; sql(sql).ok(expected); } @@ -1411,18 +1411,18 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "MATCH_NUMBER () AS \"MATCH_NUM\", " - + "CLASSIFIER() AS \"VAR_MATCH\", " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "FINAL MATCH_NUMBER () AS \"MATCH_NUM\", " + + "FINAL CLASSIFIER() AS \"VAR_MATCH\", " + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1445,16 +1445,16 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1477,16 +1477,16 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "RUNNING LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1509,17 +1509,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL COUNT(\"UP\".\"net_weight\") AS \"UP_CNT\", " + "FINAL COUNT(\"*\".\"net_weight\") AS \"DOWN_CNT\", " - + "RUNNING COUNT(\"*\".\"net_weight\") AS \"RUNNING_CNT\"\n" + + "FINAL (RUNNING COUNT(\"*\".\"net_weight\")) AS \"RUNNING_CNT\"\n" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1543,17 +1543,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " - + "LAST(\"UP\".\"net_weight\", 0) AS \"UP_CNT\", " - + "SUM(\"DOWN\".\"net_weight\") / " - + "COUNT(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n" + + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"UP_CNT\", " + + "FINAL (SUM(\"DOWN\".\"net_weight\") / " + + "COUNT(\"DOWN\".\"net_weight\")) AS \"DOWN_CNT\"\n" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1576,17 +1576,17 @@ public class RelToSqlConverterTest { + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + "MEASURES " - + "FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1609,17 +1609,17 @@ public class RelToSqlConverterTest { + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + "MEASURES " - + "FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"START_NW\", \"UP_CNT\""; sql(sql).ok(expected); @@ -1642,10 +1642,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1666,10 +1666,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP PAST LAST ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1689,10 +1689,10 @@ public class RelToSqlConverterTest { + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO FIRST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" - + "DEFINE \"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "DEFINE \"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1713,10 +1713,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1737,10 +1737,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1763,10 +1763,10 @@ public class RelToSqlConverterTest { + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " - + "NEXT(\"UP\".\"net_weight\", 1))"; + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "NEXT(PREV(\"UP\".\"net_weight\", 0), 1))"; sql(sql).ok(expected); } @@ -1789,18 +1789,18 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "SUM(\"STDN\".\"net_weight\") / " - + "COUNT(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1824,17 +1824,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1858,17 +1858,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1893,17 +1893,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1928,17 +1928,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "\"STRT\".\"net_weight\" AS \"START_NW\", " - + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " - + "SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "RUNNING \"STRT\".\"net_weight\" AS \"START_NW\", " + + "RUNNING LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "RUNNING SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + "ALL ROWS PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + "DEFINE " - + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS \"UP\".\"net_weight\" > " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1964,9 +1964,9 @@ public class RelToSqlConverterTest { + "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\" < " + + "\"DOWN\" AS PREV(\"DOWN\".\"salary\", 0) < " + "PREV(\"DOWN\".\"salary\", 1), " - + "\"UP\" AS \"UP\".\"salary\" > " + + "\"UP\" AS PREV(\"UP\".\"salary\", 0) > " + "PREV(\"UP\".\"salary\", 1))"; sql(sql).ok(expected); } http://git-wip-us.apache.org/repos/asf/calcite/blob/d3a7c0d7/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index 75dfe77..ca2623c 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -2653,6 +2653,27 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test public void testPrevClassifier() { + final String sql = "SELECT *\n" + + "FROM emp\n" + + "MATCH_RECOGNIZE (\n" + + " MEASURES\n" + + " STRT.mgr AS start_mgr,\n" + + " LAST(DOWN.mgr) AS up_days,\n" + + " LAST(UP.mgr) AS total_days\n" + + " PATTERN (STRT DOWN? UP+)\n" + + " DEFINE\n" + + " DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n" + + " UP AS CASE\n" + + " WHEN PREV(CLASSIFIER()) = 'STRT'\n" + + " THEN UP.mgr > 15\n" + + " ELSE\n" + + " UP.mgr > 20\n" + + " END\n" + + ") AS T"; + sql(sql).ok(); + } + /** * Visitor that checks that every {@link RelNode} in a tree is valid. * http://git-wip-us.apache.org/repos/asf/calcite/blob/d3a7c0d7/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index c5ba797..f96583d 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -2390,7 +2390,7 @@ LogicalProject(DEPTNO=[$1], EXPR$1=[CASE($3, 1, 0)], EXPR$2=[$4], EXPR$3=[CASE($ <Resource name="plan"> <![CDATA[ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC, 0]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC, 0]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2402,7 +2402,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ( partition by job, sal order by job asc, sal desc - measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) + measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) define down as down.mgr < PREV(down.mgr), up as up.mgr > prev(up.mgr) @@ -2411,7 +2411,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <Resource name="plan"> <![CDATA[ LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4], BOTTOM_NW=[$5], END_NW=[$6]) - LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[JOB, SAL, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[JOB, SAL, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2423,7 +2423,7 @@ LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4] ( partition by job order by sal - measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) + measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) define down as down.mgr < PREV(down.mgr), up as up.mgr > prev(up.mgr) @@ -2432,7 +2432,7 @@ LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4] <Resource name="plan"> <![CDATA[ LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_NW=[$4], END_NW=[$5]) - LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2444,7 +2444,9 @@ LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_N ( partition by job order by sal - measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw ALL ROWS PER MATCH pattern (strt down+ up+) + measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw + ALL ROWS PER MATCH + pattern (strt down+ up+) define down as down.mgr < PREV(down.mgr), up as up.mgr > prev(up.mgr) @@ -2453,7 +2455,7 @@ LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_N <Resource name="plan"> <![CDATA[ LogicalProject(JOB=[$0], SAL=[$1], EMPNO=[$2], ENAME=[$3], MGR=[$4], HIREDATE=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], MATCH_NUM=[$9], VAR_MATCH=[$10], START_NW=[$11], BOTTOM_NW=[$12], END_NW=[$13]) - LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, SAL, EMPNO, ENAME, MGR, HIREDATE, COMM, DEPTNO, SLACKER, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[true], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, SAL, EMPNO, ENAME, MGR, HIREDATE, COMM, DEPTNO, SLACKER, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[true], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2473,7 +2475,7 @@ LogicalProject(JOB=[$0], SAL=[$1], EMPNO=[$2], ENAME=[$3], MGR=[$4], HIREDATE=[$ <Resource name="plan"> <![CDATA[ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), NEXT(PREV(UP.$3, 0), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2481,7 +2483,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <TestCase name="testMatchRecognizePrevDown"> <Resource name="sql"> <![CDATA[SELECT * -FROM emp +FROM tmp MATCH_RECOGNIZE ( MEASURES STRT.mgr AS start_mgr, @@ -2496,7 +2498,7 @@ MATCH_RECOGNIZE ( <Resource name="plan"> <![CDATA[ LogicalProject(START_MGR=[$0], UP_DAYS=[$1], TOTAL_DAYS=[$2]) - LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, UP_DAYS, TOTAL_DAYS]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(DOWN.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, UP_DAYS, TOTAL_DAYS]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), LAST(DOWN.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2520,7 +2522,7 @@ MATCH_RECOGNIZE ( <Resource name="plan"> <![CDATA[ LogicalProject(START_MGR=[$0], BOTTOM_MGR=[$1], END_MGR=[$2]) - LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, BOTTOM_MGR, END_MGR]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(LAST(DOWN.$3, 1), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, BOTTOM_MGR, END_MGR]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), PREV(LAST(DOWN.$3, 1), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -2541,7 +2543,35 @@ LogicalProject(START_MGR=[$0], BOTTOM_MGR=[$1], END_MGR=[$2]) <Resource name="plan"> <![CDATA[ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[SKIP TO LAST('DOWN')], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[[DOWN, STRT]]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[SKIP TO LAST('DOWN')], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[[DOWN, STRT]]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), >(PREV(UP.$3, 0), NEXT(PREV(UP.$3, 0), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testPrevClassifier"> + <Resource name="sql"> + <![CDATA[SELECT * +FROM tmp +MATCH_RECOGNIZE ( + MEASURES + STRT.mgr AS start_mgr, + LAST(DOWN.mgr) AS up_days, + LAST(UP.mgr) AS total_days + PATTERN (STRT DOWN? UP+) + DEFINE + DOWN AS DOWN.mgr < PREV(DOWN.mgr), + UP AS CASE + WHEN PREV(CLASSIFIER()) = 'STRT' + THEN UP.mgr > 15 + ELSE + UP.mgr > 20 + END +) AS T]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(START_MGR=[$0], UP_DAYS=[$1], TOTAL_DAYS=[$2]) + LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, UP_DAYS, TOTAL_DAYS]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 0, 1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), CASE(=(PREV(CLASSIFIER(), 1), 'STRT'), >(PREV(UP.$3, 0), 15), >(PREV(UP.$3, 0), 20))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource>
