calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mm...@apache.org
Subject [01/16] calcite git commit: [CALCITE-1943] Add back NavigationExpander and NavigationReplacer in SqlValidatorImpl (Dian Fu) [Forced Update!]
Date Tue, 05 Sep 2017 14:36:44 GMT
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 <fudian.fd@alibaba-inc.com>
Authored: Tue Aug 15 17:19:00 2017 +0800
Committer: Julian Hyde <jhyde@apache.org>
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)} &rarr;
+   *   {@code PREV(A.price) + PREV(A.amount)}
+   *
+   *   <li>{@code FIRST(A.price * 2)} &rarr; {@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>


Mime
View raw message