Repository: calcite Updated Branches: refs/heads/master dac513ab4 -> 65d95e679
[CALCITE-1871] Nesting LAST within PREV is not parsed correctly for MATCH_RECOGNIZE Close apache/calcite#494 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/65d95e67 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/65d95e67 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/65d95e67 Branch: refs/heads/master Commit: 65d95e6792c002d70d826b049cf16a748fe3109d Parents: dac513a Author: Zhiqiang-He <[email protected]> Authored: Sat Jul 8 15:34:11 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Sat Jul 8 10:03:23 2017 -0700 ---------------------------------------------------------------------- .../calcite/sql/validate/SqlValidatorImpl.java | 28 +- .../rel/rel2sql/RelToSqlConverterTest.java | 346 +++++++++---------- .../calcite/test/SqlToRelConverterTest.java | 33 ++ .../calcite/test/SqlToRelConverterTest.xml | 55 ++- 4 files changed, 264 insertions(+), 198 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/65d95e67/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 0c2f379..78ce39b 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 @@ -4595,7 +4595,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { aliases.add(alias); SqlNode expand = expand(measure, scope); - expand = navigationInMeasure(expand, allRows); + validateMeasures(expand, allRows); setOriginal(expand, measure); inferUnknownTypes(unknownType, scope, expand); @@ -4620,20 +4620,9 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return fields; } - private SqlNode navigationInMeasure(SqlNode node, boolean allRows) { + private void validateMeasures(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)); - } - return node; } private void validateDefinitions(SqlMatchRecognize mr, @@ -4654,7 +4643,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { for (SqlNode item : mr.getPatternDefList().getList()) { final String alias = alias(item); SqlNode expand = expand(item, scope); - expand = navigationInDefine(expand, alias); + validateDefine(expand, alias); setOriginal(expand, item); inferUnknownTypes(booleanType, scope, expand); @@ -4682,21 +4671,18 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { mr.setOperand(SqlMatchRecognize.OPERAND_PATTERN_DEFINES, list); } + /** Returns the alias of a "expr AS alias" expression. */ private static String alias(SqlNode item) { assert item instanceof SqlCall; + assert item.getKind() == SqlKind.AS; final SqlIdentifier identifier = ((SqlCall) item).operand(1); return identifier.getSimple(); } - /** - * check all pattern var within one function is the same - */ - private SqlNode navigationInDefine(SqlNode node, String alpha) { + /** Checks that all pattern variables within a function are the same. */ + private void validateDefine(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, http://git-wip-us.apache.org/repos/asf/calcite/blob/65d95e67/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 4b46e7a..d1a1b87 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 @@ -805,9 +805,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -828,9 +828,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -851,9 +851,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -874,9 +874,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -897,9 +897,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" * \"UP\" ?)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -920,11 +920,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" -} \"UP\" ?)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; - sql(sql).ok(expected); } @@ -944,9 +943,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { 2 } \"UP\" { 3, })\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -967,9 +966,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { , 2 } \"UP\" { 3, 5 })\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -990,9 +989,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" + -} {- \"UP\" * -})\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1016,9 +1015,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 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))"; + + "\"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))"; sql(sql).ok(expected); } @@ -1038,9 +1037,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1061,9 +1060,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"net_weight\""; sql(sql).ok(expected); @@ -1102,9 +1101,9 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"net_weight\""; sql(sql).ok(expected); @@ -1126,10 +1125,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1149,10 +1148,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) " - + "< FIRST(\"DOWN\".\"net_weight\", 0), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) " - + "> LAST(\"UP\".\"net_weight\", 0))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "FIRST(\"DOWN\".\"net_weight\", 0), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "LAST(\"UP\".\"net_weight\", 0))"; sql(sql).ok(expected); } @@ -1172,10 +1171,10 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " - + "LAST(\"UP\".\"net_weight\", 0) + LAST(\"UP\".\"gross_weight\", 0))"; + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "LAST(\"UP\".\"net_weight\" + \"UP\".\"gross_weight\", 0))"; sql(sql).ok(expected); } @@ -1196,10 +1195,11 @@ public class RelToSqlConverterTest { + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " - + "LAST(\"UP\".\"net_weight\", 0) + LAST(\"UP\".\"gross_weight\", 0))"; + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "PREV(LAST(\"UP\".\"net_weight\" + " + + "\"UP\".\"gross_weight\", 0), 3))"; sql(sql).ok(expected); } @@ -1223,18 +1223,18 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "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" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1257,16 +1257,16 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "\"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" + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1289,16 +1289,16 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "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" + + "\"STRT\".\"net_weight\" AS \"START_NW\", " + + "RUNNING LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1320,17 +1320,18 @@ public class RelToSqlConverterTest { + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" - + "MEASURES FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "MEASURES " + + "\"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL COUNT(\"UP\".\"net_weight\") AS \"UP_CNT\", " + "FINAL COUNT(\"*\".\"net_weight\") AS \"DOWN_CNT\", " - + "FINAL (RUNNING COUNT(\"*\".\"net_weight\")) AS \"RUNNING_CNT\"\n" + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1354,17 +1355,17 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\") " + "MATCH_RECOGNIZE(\n" + "MEASURES " - + "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" + + "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" + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " - + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1387,17 +1388,17 @@ public class RelToSqlConverterTest { + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + "MEASURES " - + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " - + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + + "FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1420,17 +1421,17 @@ public class RelToSqlConverterTest { + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + "MEASURES " - + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " - + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + + "FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))\n" + "ORDER BY \"START_NW\", \"UP_CNT\""; sql(sql).ok(expected); @@ -1453,10 +1454,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1477,10 +1478,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1500,11 +1501,10 @@ public class RelToSqlConverterTest { + "ONE ROW PER MATCH\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))"; + + "DEFINE \"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1525,10 +1525,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1549,10 +1549,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1575,10 +1575,10 @@ public class RelToSqlConverterTest { + "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))"; + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "NEXT(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1601,18 +1601,18 @@ public class RelToSqlConverterTest { + "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" + + "\"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" + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1636,17 +1636,17 @@ public class RelToSqlConverterTest { + "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" + + "\"STRT\".\"net_weight\" AS \"START_NW\", " + + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @@ -1670,88 +1670,88 @@ public class RelToSqlConverterTest { + "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" + + "\"STRT\".\"net_weight\" AS \"START_NW\", " + + "LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "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 PREV(\"DOWN\".\"net_weight\", 0) < " + + "\"DOWN\" AS \"DOWN\".\"net_weight\" < " + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "\"UP\" AS \"UP\".\"net_weight\" > " + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @Test public void testMatchRecognizeRowsPerMatch1() { 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" - + " ONE ROW PER MATCH\n" - + " 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"; + + " 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" + + " ONE ROW PER MATCH\n" + + " 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" - + "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 PREV(\"DOWN\".\"net_weight\", 0) < " - + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " - + "PREV(\"UP\".\"net_weight\", 1))"; + + "FROM (SELECT *\n" + + "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" + + "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\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } @Test public void testMatchRecognizeRowsPerMatch2() { 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" - + " ALL ROWS PER MATCH\n" - + " 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"; + + " 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" + + " ALL ROWS PER MATCH\n" + + " 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 " - + "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 PREV(\"DOWN\".\"net_weight\", 0) < " - + "PREV(\"DOWN\".\"net_weight\", 1), " - + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " - + "PREV(\"UP\".\"net_weight\", 1))"; + + "FROM (SELECT *\n" + + "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" + + "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\" < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS \"UP\".\"net_weight\" > " + + "PREV(\"UP\".\"net_weight\", 1))"; sql(sql).ok(expected); } http://git-wip-us.apache.org/repos/asf/calcite/blob/65d95e67/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 f7b730c..d921f6a 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -2569,6 +2569,39 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test public void testMRPrevLast() { + final String sql = "SELECT *\n" + + "FROM emp\n" + + "MATCH_RECOGNIZE (\n" + + " MEASURES\n" + + " STRT.mgr AS start_mgr,\n" + + " LAST(DOWN.mgr) AS bottom_mgr,\n" + + " LAST(UP.mgr) AS end_mgr\n" + + " ONE ROW PER MATCH\n" + + " PATTERN (STRT DOWN+ UP+)\n" + + " DEFINE\n" + + " DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n" + + " UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)\n" + + ") AS T"; + sql(sql).ok(); + } + + @Test public void testMRPrevDown() { + 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 UP.mgr > PREV(DOWN.mgr)\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/65d95e67/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 35cc58d..c14c85a 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -4743,7 +4743,7 @@ LogicalProject(A=[$0], B=[$1]) <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=[[<(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]]) + 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]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -4764,7 +4764,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <Resource name="plan"> <![CDATA[ LogicalProject(MATCH_NUM=[$0], VAR_MATCH=[$1], START_NW=[$2], BOTTOM_NW=[$3], END_NW=[$4]) - LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[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]]) + LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[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]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -4784,7 +4784,7 @@ LogicalProject(MATCH_NUM=[$0], VAR_MATCH=[$1], START_NW=[$2], BOTTOM_NW=[$3], EN <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=[[<(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]]) + 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]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -4805,7 +4805,54 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <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=[[<(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]]) + 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]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testMRPrevLast"> + <Resource name="sql"> + <![CDATA[SELECT * +FROM emp +MATCH_RECOGNIZE ( + MEASURES + STRT.mgr AS start_mgr, + LAST(DOWN.mgr) AS bottom_mgr, + LAST(UP.mgr) AS end_mgr + ONE ROW PER MATCH\n + PATTERN (STRT DOWN+ UP+) + DEFINE + DOWN AS DOWN.mgr < PREV(DOWN.mgr), + UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1) +) AS T;]]> + </Resource> + <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]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testMRPrevDown"> + <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 UP.mgr > PREV(DOWN.mgr) +) 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', 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]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource>
