You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by el...@apache.org on 2017/03/30 21:37:57 UTC
[07/50] [abbrv] calcite-avatica git commit: [CALCITE-1641] Add parser
and validator support for MATCH_RECOGNIZE (Zhiqiang-He)
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/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 eb2ea3b..5ae0def 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
@@ -50,6 +50,7 @@ import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlJoin;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlMatchRecognize;
import org.apache.calcite.sql.SqlMerge;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -74,6 +75,7 @@ import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandTypeInference;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.sql.util.SqlShuttle;
import org.apache.calcite.sql.util.SqlVisitor;
import org.apache.calcite.util.BitString;
@@ -90,6 +92,7 @@ import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
@@ -964,6 +967,10 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
return orderScopes.get(select);
}
+ public SqlValidatorScope getMatchRecognizeScope(SqlMatchRecognize node) {
+ return scopes.get(node);
+ }
+
public SqlValidatorScope getJoinScope(SqlNode node) {
return scopes.get(stripAs(node));
}
@@ -1788,6 +1795,37 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
return true;
}
+ private void registerMatchRecognize(
+ SqlValidatorScope parentScope,
+ SqlValidatorScope usingScope,
+ SqlMatchRecognize call,
+ SqlNode enclosingNode,
+ String alias,
+ boolean forceNullable) {
+
+ final MatchRecognizeNamespace matchRecognizeNamespace =
+ createMatchRecognizeNameSpace(call, enclosingNode);
+ registerNamespace(usingScope, alias, matchRecognizeNamespace, forceNullable);
+
+ final MatchRecognizeScope matchRecognizeScope =
+ new MatchRecognizeScope(parentScope, call);
+ scopes.put(call, matchRecognizeScope);
+
+ // parse input query
+ SqlNode expr = call.getTableRef();
+ SqlNode newExpr = registerFrom(usingScope, matchRecognizeScope, expr,
+ expr, null, null, forceNullable);
+ if (expr != newExpr) {
+ call.setOperand(0, newExpr);
+ }
+ }
+
+ protected MatchRecognizeNamespace createMatchRecognizeNameSpace(
+ SqlMatchRecognize call,
+ SqlNode enclosingNode) {
+ return new MatchRecognizeNamespace(this, call, enclosingNode);
+ }
+
/**
* Registers a new namespace, and adds it as a child of its parent scope.
* Derived class can override this method to tinker with namespaces as they
@@ -1872,6 +1910,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
case UNNEST:
case OTHER_FUNCTION:
case COLLECTION_TABLE:
+ case MATCH_RECOGNIZE:
// give this anonymous construct a name since later
// query processing stages rely on it
@@ -1925,7 +1964,10 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
false);
}
return node;
-
+ case MATCH_RECOGNIZE:
+ registerMatchRecognize(parentScope, usingScope,
+ (SqlMatchRecognize) node, enclosingNode, alias, forceNullable);
+ return node;
case TABLESAMPLE:
call = (SqlCall) node;
expr = call.operand(0);
@@ -4244,6 +4286,85 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
inWindow = false;
}
+ @Override public void validateMatchRecognize(SqlCall call) {
+ SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
+ final MatchRecognizeScope scope =
+ (MatchRecognizeScope) getMatchRecognizeScope(matchRecognize);
+
+ final MatchRecognizeNamespace ns =
+ getNamespace(call).unwrap(MatchRecognizeNamespace.class);
+ assert ns.rowType == null;
+
+ // retrieve pattern variables used in pattern and subset
+ SqlNode pattern = matchRecognize.getPattern();
+ PatternVarVisitor visitor = new PatternVarVisitor(scope);
+ pattern.accept(visitor);
+
+ validateDefinitions(matchRecognize, scope);
+ ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType());
+ }
+
+ private void validateDefinitions(SqlMatchRecognize mr,
+ MatchRecognizeScope scope) {
+ final Set<String> aliases = new HashSet<>();
+ for (SqlNode item : mr.getPatternDefList().getList()) {
+ final String alias = alias(item);
+ if (!aliases.add(alias)) {
+ throw newValidationError(item,
+ Static.RESOURCE.PatternVarAlreadyDefined(alias));
+ }
+ scope.addPatternVar(alias);
+ }
+
+ final List<SqlNode> sqlNodes = new ArrayList<>();
+ for (SqlNode item : mr.getPatternDefList().getList()) {
+ final String alias = alias(item);
+ SqlNode expand = expand(item, scope);
+ expand = navigationInDefine(expand, alias);
+ setOriginal(expand, item);
+
+ inferUnknownTypes(booleanType, scope, expand);
+ expand.validate(this, scope);
+
+ // Some extra work need required here.
+ // In PREV, NEXT, FINAL and LAST, only one pattern variable is allowed.
+ sqlNodes.add(
+ SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, expand,
+ new SqlIdentifier(alias, SqlParserPos.ZERO)));
+
+ final RelDataType type = deriveType(scope, expand);
+ if (!SqlTypeUtil.inBooleanFamily(type)) {
+ throw newValidationError(expand, RESOURCE.condMustBeBoolean("DEFINE"));
+ }
+ setValidatedNodeType(item, type);
+ }
+
+ SqlNodeList list =
+ new SqlNodeList(sqlNodes, mr.getPatternDefList().getParserPosition());
+ inferUnknownTypes(unknownType, scope, list);
+ for (SqlNode node : list) {
+ validateExpr(node, scope);
+ }
+ mr.setOperand(SqlMatchRecognize.OPERAND_PATTERN_DEFINES, list);
+ }
+
+ private static String alias(SqlNode item) {
+ assert item instanceof SqlCall;
+ 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) {
+ 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,
SqlValidatorScope scope) {
// For "agg(expr)", expr cannot itself contain aggregate function
@@ -4411,6 +4532,30 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
throw new UnsupportedOperationException();
}
+ private static boolean isPhysicalNavigation(SqlKind kind) {
+ return kind == SqlKind.PREV || kind == SqlKind.NEXT;
+ }
+
+ private static boolean isLogicalNavigation(SqlKind kind) {
+ return kind == SqlKind.FIRST || kind == SqlKind.LAST;
+ }
+
+ private static boolean isAggregation(SqlKind kind) {
+ return kind == SqlKind.SUM || kind == SqlKind.SUM0
+ || kind == SqlKind.AVG || kind == SqlKind.COUNT
+ || kind == SqlKind.MAX || kind == SqlKind.MIN;
+ }
+
+ private static boolean isRunningOrFinal(SqlKind kind) {
+ return kind == SqlKind.RUNNING || kind == SqlKind.FINAL;
+ }
+
+ private static boolean isSingleVarRequired(SqlKind kind) {
+ return isPhysicalNavigation(kind)
+ || isLogicalNavigation(kind)
+ || isAggregation(kind);
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
@@ -4492,6 +4637,49 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
/**
+ * retrieve pattern variables defined
+ */
+ private class PatternVarVisitor implements SqlVisitor<Void> {
+ private MatchRecognizeScope scope;
+ public PatternVarVisitor(MatchRecognizeScope scope) {
+ this.scope = scope;
+ }
+
+ @Override public Void visit(SqlLiteral literal) {
+ return null;
+ }
+
+ @Override public Void visit(SqlCall call) {
+ for (int i = 0; i < call.getOperandList().size(); i++) {
+ call.getOperandList().get(i).accept(this);
+ }
+ return null;
+ }
+
+ @Override public Void visit(SqlNodeList nodeList) {
+ throw Util.needToImplement(nodeList);
+ }
+
+ @Override public Void visit(SqlIdentifier id) {
+ Preconditions.checkArgument(id.isSimple());
+ scope.addPatternVar(id.getSimple());
+ return null;
+ }
+
+ @Override public Void visit(SqlDataTypeSpec type) {
+ throw Util.needToImplement(type);
+ }
+
+ @Override public Void visit(SqlDynamicParam param) {
+ throw Util.needToImplement(param);
+ }
+
+ @Override public Void visit(SqlIntervalQualifier intervalQualifier) {
+ throw Util.needToImplement(intervalQualifier);
+ }
+ }
+
+ /**
* Visitor which derives the type of a given {@link SqlNode}.
*
* <p>Each method must return the derived type. This visitor is basically a
@@ -4824,6 +5012,256 @@ 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;
+ }
+
+ 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
+ */
+ private class NavigationExpander extends NavigationModifier {
+ SqlOperator currentOperator;
+ SqlNode currentOffset;
+
+ public NavigationExpander() {
+
+ }
+
+ public NavigationExpander(SqlOperator operator, SqlNode offset) {
+ this.currentOffset = offset;
+ this.currentOperator = operator;
+ }
+
+ @Override public SqlNode visit(SqlCall call) {
+ SqlKind kind = call.getKind();
+ List<SqlNode> operands = call.getOperandList();
+ List<SqlNode> newOperands = new ArrayList<>();
+ if (isLogicalNavigation(kind) || isPhysicalNavigation(kind)) {
+ SqlNode inner = operands.get(0);
+ SqlNode offset = operands.get(1);
+
+ // merge two straight prev/next, update offset
+ if (isPhysicalNavigation(kind)) {
+ SqlKind innerKind = inner.getKind();
+ if (isPhysicalNavigation(innerKind)) {
+ List<SqlNode> innerOperands = ((SqlCall) inner).getOperandList();
+ SqlNode innerOffset = innerOperands.get(1);
+ SqlOperator newOperator = innerKind == kind
+ ? SqlStdOperatorTable.PLUS : SqlStdOperatorTable.MINUS;
+ offset = newOperator.createCall(SqlParserPos.ZERO,
+ offset, innerOffset);
+ inner = call.getOperator().createCall(SqlParserPos.ZERO,
+ innerOperands.get(0), offset);
+ }
+ }
+ return inner.accept(new NavigationExpander(call.getOperator(), offset));
+ }
+
+ for (SqlNode node : operands) {
+ SqlNode newNode = node.accept(new NavigationExpander());
+ if (currentOperator != null) {
+ newNode = currentOperator.createCall(SqlParserPos.ZERO, newNode, currentOffset);
+ }
+ newOperands.add(newNode);
+ }
+ return call.getOperator().createCall(SqlParserPos.ZERO, newOperands);
+ }
+
+ @Override public SqlNode visit(SqlIdentifier id) {
+ if (currentOperator == null) {
+ return id;
+ } else {
+ return currentOperator.createCall(SqlParserPos.ZERO, id, currentOffset);
+ }
+ }
+ }
+
+ /**
+ * Replace {@code A as A.price > PREV(B.price)}
+ * with {@code PREV(A.price, 0) > last(B.price, 0)}.
+ */
+ private class NavigationReplacer extends NavigationModifier {
+ private final String alpha;
+
+ public NavigationReplacer(String alpha) {
+ this.alpha = alpha;
+ }
+
+ @Override public SqlNode visit(SqlCall call) {
+ SqlKind kind = call.getKind();
+ if (isLogicalNavigation(kind)
+ || isAggregation(kind)
+ || isRunningOrFinal(kind)) {
+ 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));
+ }
+ return call.getOperator().createCall(SqlParserPos.ZERO, newOperands);
+ }
+ }
+
+ @Override public SqlNode visit(SqlIdentifier id) {
+ if (id.isSimple()) {
+ return id;
+ }
+ SqlOperator operator = id.names.get(0).equals(alpha)
+ ? SqlStdOperatorTable.PREV : SqlStdOperatorTable.LAST;
+
+ return operator.createCall(SqlParserPos.ZERO, id,
+ SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO));
+ }
+ }
+
+ /**
+ * Within one navigation function, the pattern var should be same
+ */
+ private class PatternValidator extends SqlBasicVisitor<Set<String>> {
+ private final boolean isMeasure;
+ int firstLastCount;
+ int prevNextCount;
+ int aggregateCount;
+
+ PatternValidator(boolean isMeasure) {
+ this(isMeasure, 0, 0, 0);
+ }
+
+ PatternValidator(boolean isMeasure, int firstLastCount, int prevNextCount,
+ int aggregateCount) {
+ this.isMeasure = isMeasure;
+ this.firstLastCount = firstLastCount;
+ this.prevNextCount = prevNextCount;
+ this.aggregateCount = aggregateCount;
+ }
+
+ @Override public Set<String> visit(SqlCall call) {
+ boolean isSingle = false;
+ Set<String> vars = new HashSet<>();
+ SqlKind kind = call.getKind();
+ List<SqlNode> operands = call.getOperandList();
+
+ if (isSingleVarRequired(kind)) {
+ isSingle = true;
+ if (isPhysicalNavigation(kind)) {
+ if (isMeasure) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternPrevFunctionInMeasure(call.toString()));
+ }
+ if (firstLastCount != 0) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternPrevFunctionOrder(call.toString()));
+ }
+ prevNextCount++;
+ } else if (isLogicalNavigation(kind)) {
+ if (firstLastCount != 0) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternPrevFunctionOrder(call.toString()));
+ }
+ firstLastCount++;
+ } else if (isAggregation(kind)) {
+ // cannot apply aggregation in PREV/NEXT, FIRST/LAST
+ if (firstLastCount != 0 || prevNextCount != 0) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternAggregationInNavigation(call.toString()));
+ }
+ if (kind == SqlKind.COUNT && call.getOperandList().size() > 1) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternCountFunctionArg());
+ }
+ aggregateCount++;
+ }
+ }
+
+ if (isRunningOrFinal(kind) && isMeasure) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternRunningFunctionInDefine(call.toString()));
+ }
+
+ for (SqlNode node : operands) {
+ vars.addAll(
+ node.accept(
+ new PatternValidator(isMeasure, firstLastCount, prevNextCount,
+ aggregateCount)));
+ }
+
+ if (isSingle) {
+ switch (kind) {
+ case COUNT:
+ if (vars.size() > 1) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternFunctionVariableCheck(call.toString()));
+ }
+ break;
+ default:
+ if (vars.size() != 1) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternCountFunctionArg());
+ }
+ break;
+ }
+ }
+ return vars;
+ }
+
+ @Override public Set<String> visit(SqlIdentifier identifier) {
+ boolean check = prevNextCount > 0 || firstLastCount > 0 || aggregateCount > 0;
+ Set<String> vars = new HashSet<>();
+ if (identifier.names.size() > 1 && check) {
+ vars.add(identifier.names.get(0));
+ }
+ return vars;
+ }
+
+ @Override public Set<String> visit(SqlLiteral literal) {
+ return ImmutableSet.of();
+ }
+
+ @Override public Set<String> visit(SqlIntervalQualifier qualifier) {
+ return ImmutableSet.of();
+ }
+
+ @Override public Set<String> visit(SqlDataTypeSpec type) {
+ return ImmutableSet.of();
+ }
+
+ @Override public Set<String> visit(SqlDynamicParam param) {
+ return ImmutableSet.of();
+ }
+ }
+
//~ Enums ------------------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
index 0579d4a..e6e0ee0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
@@ -35,6 +35,7 @@ import org.apache.calcite.rel.logical.LogicalCorrelate;
import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalIntersect;
import org.apache.calcite.rel.logical.LogicalJoin;
+import org.apache.calcite.rel.logical.LogicalMatch;
import org.apache.calcite.rel.logical.LogicalMinus;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalSort;
@@ -673,6 +674,10 @@ public class RelStructuredTypeFlattener implements ReflectiveVisitor {
rewriteGeneric(rel);
}
+ public void rewriteRel(LogicalMatch rel) {
+ rewriteGeneric(rel);
+ }
+
/** Generates expressions that reference the flattened input fields from
* a given row type. */
private void flattenInputs(List<RelDataTypeField> fieldList, RexNode prefix,
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index f6e20e1..e832f19 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -107,6 +107,7 @@ import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlJoin;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlMatchRecognize;
import org.apache.calcite.sql.SqlMerge;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -185,6 +186,7 @@ import java.util.Collections;
import java.util.Deque;
import java.util.EnumSet;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
@@ -1903,6 +1905,10 @@ public class SqlToRelConverter {
final SqlCall call;
final SqlNode[] operands;
switch (from.getKind()) {
+ case MATCH_RECOGNIZE:
+ convertMatchRecognize(bb, (SqlCall) from);
+ return;
+
case AS:
convertFrom(bb, ((SqlCall) from).operand(0));
return;
@@ -2075,6 +2081,74 @@ public class SqlToRelConverter {
}
}
+ protected void convertMatchRecognize(Blackboard bb, SqlCall call) {
+ final SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
+ final SqlValidatorNamespace ns = validator.getNamespace(matchRecognize);
+ final SqlValidatorScope scope = validator.getMatchRecognizeScope(matchRecognize);
+
+ final Blackboard mrBlackBoard = createBlackboard(scope, null, false);
+ final RelDataType rowType = ns.getRowType();
+ // convert inner query, could be a table name or a derived table
+ SqlNode expr = matchRecognize.getTableRef();
+ convertFrom(mrBlackBoard, expr);
+ final RelNode input = mrBlackBoard.root;
+
+ // convert pattern
+ final Set<String> patternVarsSet = new HashSet<>();
+ SqlNode pattern = matchRecognize.getPattern();
+ final SqlBasicVisitor<RexNode> patternVarVisitor =
+ new SqlBasicVisitor<RexNode>() {
+ @Override public RexNode visit(SqlCall call) {
+ List<SqlNode> operands = call.getOperandList();
+ List<RexNode> newOperands = Lists.newArrayList();
+ for (SqlNode node : operands) {
+ newOperands.add(node.accept(this));
+ }
+ return rexBuilder.makeCall(
+ validator.getUnknownType(), call.getOperator(), newOperands);
+ }
+
+ @Override public RexNode visit(SqlIdentifier id) {
+ assert id.isSimple();
+ patternVarsSet.add(id.getSimple());
+ return rexBuilder.makeLiteral(id.getSimple());
+ }
+
+ @Override public RexNode visit(SqlLiteral literal) {
+ if (literal instanceof SqlNumericLiteral) {
+ return rexBuilder.makeExactLiteral(BigDecimal.valueOf(literal.intValue(true)));
+ } else {
+ return rexBuilder.makeLiteral(literal.booleanValue());
+ }
+ }
+ };
+ final RexNode patternNode = pattern.accept(patternVarVisitor);
+
+ mrBlackBoard.setPatternVarRef(true);
+
+ // convert definitions
+ final ImmutableMap.Builder<String, RexNode> definitionNodes =
+ ImmutableMap.builder();
+ for (SqlNode def : matchRecognize.getPatternDefList()) {
+ List<SqlNode> operands = ((SqlCall) def).getOperandList();
+ String alias = ((SqlIdentifier) operands.get(1)).getSimple();
+ RexNode rex = mrBlackBoard.convertExpression(operands.get(0));
+ definitionNodes.put(alias, rex);
+ }
+
+ mrBlackBoard.setPatternVarRef(false);
+
+ final RelFactories.MatchFactory factory =
+ RelFactories.DEFAULT_MATCH_FACTORY;
+ final RelNode rel =
+ factory.createMatchRecognize(input, patternNode,
+ matchRecognize.getStrictStart().booleanValue(),
+ matchRecognize.getStrictEnd().booleanValue(),
+ definitionNodes.build(),
+ rowType);
+ bb.setRoot(rel, false);
+ }
+
protected void convertCollectionTable(
Blackboard bb,
SqlCall call) {
@@ -3302,6 +3376,11 @@ public class SqlToRelConverter {
return bb.convertExpression(call);
}
+ String pv = null;
+ if (bb.isPatternVarRef && identifier.names.size() > 1) {
+ pv = identifier.names.get(0);
+ }
+
final SqlQualified qualified;
if (bb.scope != null) {
qualified = bb.scope.fullyQualify(identifier);
@@ -3695,6 +3774,8 @@ public class SqlToRelConverter {
private final Map<CorrelationId, RexFieldAccess> mapCorrelateToRex =
new HashMap<>();
+ private boolean isPatternVarRef = false;
+
final List<RelNode> cursors = new ArrayList<>();
/**
@@ -3749,6 +3830,10 @@ public class SqlToRelConverter {
this.top = top;
}
+ public void setPatternVarRef(boolean isVarRef) {
+ this.isPatternVarRef = isVarRef;
+ }
+
public RexNode register(
RelNode rel,
JoinRelType joinType) {
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 94795bb..7d6a313 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -257,7 +257,14 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return cx.convertExpression(expanded);
}
});
-
+ registerOp(
+ SqlStdOperatorTable.PATTERN_DEFINE_AS,
+ new SqlRexConvertlet() {
+ public RexNode convertCall(SqlRexContext cx, SqlCall call) {
+ SqlNode expanded = call.operand(0);
+ return cx.convertExpression(expanded);
+ }
+ });
// "SQRT(x)" is equivalent to "POWER(x, .5)"
registerOp(
SqlStdOperatorTable.SQRT,
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
----------------------------------------------------------------------
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index ed3d96b..792ba2a 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -215,4 +215,11 @@ MinusNotAllowed=MINUS is not allowed under the current SQL conformance level
SelectMissingFrom=SELECT must have a FROM clause
GroupFunctionMustAppearInGroupByClause=Group function ''{0}'' can only appear in GROUP BY clause
AuxiliaryWithoutMatchingGroupCall=Call to auxiliary group function ''{0}'' must have matching call to group function ''{1}'' in GROUP BY clause
+PatternVarAlreadyDefined=Pattern variable ''{0}'' has already been defined
+PatternPrevFunctionInMeasure=Cannot use PREV/NEXT in MEASURE ''{0}''
+PatternPrevFunctionOrder=Cannot nest PREV/NEXT under LAST/FIRST ''{0}''
+PatternAggregationInNavigation=Cannot use aggregation in navigation ''{0}''
+PatternCountFunctionArg=Invalid number of parameters to COUNT method
+PatternRunningFunctionInDefine=Cannot use RUNNING/FINAL in DEFINE ''{0}''
+PatternFunctionVariableCheck=Multiple pattern variables in ''{0}''
# End CalciteResource.properties
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/test/java/org/apache/calcite/prepare/LookupOperatorOverloadsTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/prepare/LookupOperatorOverloadsTest.java b/core/src/test/java/org/apache/calcite/prepare/LookupOperatorOverloadsTest.java
index ddc997d..f843e51 100644
--- a/core/src/test/java/org/apache/calcite/prepare/LookupOperatorOverloadsTest.java
+++ b/core/src/test/java/org/apache/calcite/prepare/LookupOperatorOverloadsTest.java
@@ -44,6 +44,7 @@ import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
+import static org.apache.calcite.sql.SqlFunctionCategory.MATCH_RECOGNIZE;
import static org.apache.calcite.sql.SqlFunctionCategory.USER_DEFINED_CONSTRUCTOR;
import static org.apache.calcite.sql.SqlFunctionCategory.USER_DEFINED_FUNCTION;
import static org.apache.calcite.sql.SqlFunctionCategory.USER_DEFINED_PROCEDURE;
@@ -95,7 +96,7 @@ public class LookupOperatorOverloadsTest {
}
}
check(cats, USER_DEFINED_TABLE_FUNCTION,
- USER_DEFINED_TABLE_SPECIFIC_FUNCTION);
+ USER_DEFINED_TABLE_SPECIFIC_FUNCTION, MATCH_RECOGNIZE);
}
@Test public void testIsSpecific() throws SQLException {
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/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 52bcab5..b6d4780 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
@@ -574,6 +574,356 @@ public class RelToSqlConverterTest {
.ok(expected);
}
+ @Test public void testMatchRecognizePatternExpression() {
+ String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n"
+ + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n"
+ + " ) mr";
+ String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression2() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+$)\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"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" + $)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression3() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (^strt down+ up+)\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"
+ + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression4() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (^strt down+ up+$)\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"
+ + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" + $)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression5() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down* up?)\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"
+ + "PATTERN (\"STRT\" \"DOWN\" * \"UP\" ?)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression6() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt {-down-} up?)\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"
+ + "PATTERN (\"STRT\" {- \"DOWN\" -} \"UP\" ?)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression7() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down{2} up{3,})\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"
+ + "PATTERN (\"STRT\" \"DOWN\" { 2 } \"UP\" { 3, })\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression8() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down{,2} up{3,5})\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"
+ + "PATTERN (\"STRT\" \"DOWN\" { , 2 } \"UP\" { 3, 5 })\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression9() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt {-down+-} {-up*-})\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"
+ + "PATTERN (\"STRT\" {- \"DOWN\" + -} {- \"UP\" * -})\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression10() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (A B C | A C B | B A C | B C A | C A B | C B A)\n"
+ + " define\n"
+ + " A as A.\"net_weight\" < PREV(A.\"net_weight\"),\n"
+ + " B as B.\"net_weight\" > PREV(B.\"net_weight\"),\n"
+ + " C as C.\"net_weight\" < PREV(C.\"net_weight\")\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"A\" \"B\" \"C\" | \"A\" \"C\" \"B\" "
+ + "| \"B\" \"A\" \"C\" | \"B\" \"C\" \"A\" "
+ + "| \"C\" \"A\" \"B\" | \"C\" \"B\" \"A\")\n"
+ + "DEFINE "
+ + "\"A\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"B\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1), "
+ + "\"C\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression11() {
+ final String sql = "select *\n"
+ + " from (select * from \"product\") match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\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"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression12() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n"
+ + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n"
+ + " ) mr order by MR.\"net_weight\"";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))\n"
+ + "ORDER BY \"net_weight\"";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizePatternExpression13() {
+ final String sql = "select *\n"
+ + " from (\n"
+ + "select *\n"
+ + "from \"sales_fact_1997\" as s\n"
+ + "join \"customer\" as c using (\"customer_id\")\n"
+ + "join \"product\" as p using (\"product_id\")\n"
+ + "join \"product_class\" as pc using (\"product_class_id\")\n"
+ + "where c.\"city\" = 'San Francisco'\n"
+ + "and pc.\"product_department\" = 'Snacks'"
+ + ") match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n"
+ + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n"
+ + " ) mr order by MR.\"net_weight\"";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ + "INNER JOIN \"foodmart\".\"customer\" "
+ + "ON \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n"
+ + "INNER JOIN \"foodmart\".\"product\" "
+ + "ON \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\"\n"
+ + "INNER JOIN \"foodmart\".\"product_class\" "
+ + "ON \"product\".\"product_class_id\" = \"product_class\".\"product_class_id\"\n"
+ + "WHERE \"customer\".\"city\" = 'San Francisco' "
+ + "AND \"product_class\".\"product_department\" = 'Snacks') "
+ + "MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > PREV(\"net_weight\", 1))\n"
+ + "ORDER BY \"net_weight\"";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n"
+ + " up as up.\"net_weight\" > NEXT(up.\"net_weight\")\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > NEXT(PREV(\"net_weight\", 0), 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause2() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < FIRST(down.\"net_weight\"),\n"
+ + " up as up.\"net_weight\" > LAST(up.\"net_weight\")\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < FIRST(\"net_weight\", 0), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > LAST(\"net_weight\", 0))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause3() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\",1),\n"
+ + " up as up.\"net_weight\" > LAST(up.\"net_weight\" + up.\"gross_weight\")\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > "
+ + "LAST(\"net_weight\", 0) + LAST(\"gross_weight\", 0))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause4() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.\"net_weight\" < PREV(down.\"net_weight\",1),\n"
+ + " up as up.\"net_weight\" > "
+ + "PREV(LAST(up.\"net_weight\" + up.\"gross_weight\"),3)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"net_weight\", 0) < PREV(\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"net_weight\", 0) > "
+ + "LAST(\"net_weight\", 0) + LAST(\"gross_weight\", 0))";
+ sql(sql).ok(expected);
+ }
+
/** Fluid interface to run tests. */
private static class Sql {
private CalciteAssert.SchemaSpec schemaSpec;
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 9c47a4f..b3fa2fc 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -190,6 +190,7 @@ public class SqlParserTest {
"DEFAULT", "92", "99", "2003", "2011", "c",
"DEFERRABLE", "92", "99",
"DEFERRED", "92", "99",
+ "DEFINE", "c",
"DELETE", "92", "99", "2003", "2011", "c",
"DENSE_RANK", "2011", "c",
"DEPTH", "99",
@@ -303,6 +304,7 @@ public class SqlParserTest {
"LOWER", "92", "2011", "c",
"MAP", "99",
"MATCH", "92", "99", "2003", "2011", "c",
+ "MATCH_RECOGNIZE", "c",
"MAX", "92", "2011", "c",
"MAX_CARDINALITY", "2011",
"MEMBER", "2003", "2011", "c",
@@ -357,15 +359,18 @@ public class SqlParserTest {
"PARTIAL", "92", "99",
"PARTITION", "99", "2003", "2011", "c",
"PATH", "92", "99",
+ "PATTERN", "c",
"PERCENTILE_CONT", "2011", "c",
"PERCENTILE_DISC", "2011", "c",
"PERCENT_RANK", "2011", "c",
+ "PERMUTE", "c",
"POSITION", "92", "2011", "c",
"POSITION_REGEX", "2011",
"POWER", "2011", "c",
"PRECISION", "92", "99", "2003", "2011", "c",
"PREPARE", "92", "99", "2003", "2011", "c",
"PRESERVE", "92", "99",
+ "PREV", "c",
"PRIMARY", "92", "99", "2003", "2011", "c",
"PRIOR", "92", "99",
"PRIVILEGES", "92", "99",
@@ -407,6 +412,7 @@ public class SqlParserTest {
"ROW", "99", "2003", "2011", "c",
"ROWS", "92", "99", "2003", "2011", "c",
"ROW_NUMBER", "2011", "c",
+ "RUNNING", "c",
"SAVEPOINT", "99", "2003", "2011", "c",
"SCHEMA", "92", "99",
"SCOPE", "99", "2003", "2011", "c",
@@ -7104,6 +7110,292 @@ public class SqlParserTest {
+ "VALUES (ROW(1, (CURRENT VALUE FOR `MY_SEQ`)))");
}
+ @Test public void testMatchRecognize1() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize2() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+$)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)) $)\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize3() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (^strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (^ ((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize4() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (^strt down+ up+$)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (^ ((`STRT` (`DOWN` +)) (`UP` +)) $)\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize5() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down* up?)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` *)) (`UP` ?)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize6() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt {-down-} up?)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` ({- `DOWN` -})) (`UP` ?)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize7() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down{2} up{3,})\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` { 2 })) (`UP` { 3, })))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize8() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down{,2} up{3,5})\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` { , 2 })) (`UP` { 3, 5 })))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize9() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt {-down+-} {-up*-})\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > prev(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` ({- (`DOWN` +) -})) ({- (`UP` *) -})))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize10() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern ( A B C | A C B | B A C | B C A | C A B | C B A)\n"
+ + " define\n"
+ + " A as A.price > PREV(A.price),\n"
+ + " B as B.price < prev(B.price),\n"
+ + " C as C.price > prev(C.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN ((((((((`A` `B`) `C`) | ((`A` `C`) `B`)) | ((`B` `A`) `C`)) "
+ + "| ((`B` `C`) `A`)) | ((`C` `A`) `B`)) | ((`C` `B`) `A`)))\n"
+ + "DEFINE "
+ + "`A` AS (`A`.`PRICE` > (PREV(`A`.`PRICE`, 1))), "
+ + "`B` AS (`B`.`PRICE` < (PREV(`B`.`PRICE`, 1))), "
+ + "`C` AS (`C`.`PRICE` > (PREV(`C`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognize11() {
+ final String sql = "select *\n"
+ + " from t match_recognize (\n"
+ + " pattern ( \"a\" \"b c\")\n"
+ + " define\n"
+ + " \"A\" as A.price > PREV(A.price),\n"
+ + " \"b c\" as \"b c\".foo\n"
+ + " ) as mr(c1, c2) join e as x on foo = baz";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN ((`a` `b c`))\n"
+ + "DEFINE `A` AS (`A`.`PRICE` > (PREV(`A`.`PRICE`, 1))),"
+ + " `b c` AS `b c`.`FOO`) AS `MR` (`C1`, `C2`)\n"
+ + "INNER JOIN `E` AS `X` ON (`FOO` = `BAZ`)";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " up as up.price > NEXT(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (NEXT(`UP`.`PRICE`, 1)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause2() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < FIRST(down.price),\n"
+ + " up as up.price > LAST(up.price)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (FIRST(`DOWN`.`PRICE`, 0))), "
+ + "`UP` AS (`UP`.`PRICE` > (LAST(`UP`.`PRICE`, 0)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause3() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price,1),\n"
+ + " up as up.price > LAST(up.price + up.TAX)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (LAST((`UP`.`PRICE` + `UP`.`TAX`), 0)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeDefineClause4() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price,1),\n"
+ + " up as up.price > PREV(LAST(up.price + up.TAX),3)\n"
+ + " ) mr";
+ final String expected = "SELECT *\n"
+ + "FROM `T` MATCH_RECOGNIZE(\n"
+ + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n"
+ + "DEFINE "
+ + "`DOWN` AS (`DOWN`.`PRICE` < (PREV(`DOWN`.`PRICE`, 1))), "
+ + "`UP` AS (`UP`.`PRICE` > (PREV((LAST((`UP`.`PRICE` + `UP`.`TAX`), 0)), 3)))"
+ + ") AS `MR`";
+ sql(sql).ok(expected);
+ }
+
//~ Inner Interfaces -------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 76bfc51..24aae33 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -175,11 +175,13 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(PERCENT_RANK)",
"KEYWORD(POSITION)",
"KEYWORD(POWER)",
+ "KEYWORD(PREV)",
"KEYWORD(RANK)",
"KEYWORD(REGR_SXX)",
"KEYWORD(REGR_SYY)",
"KEYWORD(ROW)",
"KEYWORD(ROW_NUMBER)",
+ "KEYWORD(RUNNING)",
"KEYWORD(SECOND)",
"KEYWORD(SESSION_USER)",
"KEYWORD(SPECIFIC)",
@@ -301,6 +303,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(HAVING)",
"KEYWORD(LEFT)",
"KEYWORD(EXCEPT)",
+ "KEYWORD(MATCH_RECOGNIZE)",
"KEYWORD(MINUS)",
"KEYWORD(JOIN)",
"KEYWORD(WINDOW)",
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 938d4c7..f218064 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7936,12 +7936,19 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "DEFAULT -\n"
+ "ITEM -\n"
+ "NEXT_VALUE -\n"
+ + "PATTERN_EXCLUDE -\n"
+ + "PATTERN_PERMUTE -\n"
+ "\n"
+ + "PATTERN_QUANTIFIER -\n"
+ + "\n"
+ + " left\n"
+ "$LiteralChain -\n"
+ "+ pre\n"
+ "- pre\n"
+ ". left\n"
+ "\n"
+ + "| left\n"
+ + "\n"
+ "* left\n"
+ "/ left\n"
+ "/INT left\n"
@@ -7997,6 +8004,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "AS -\n"
+ "DESC post\n"
+ "OVER left\n"
+ + "PATTERN_DEFINE_AS -\n"
+ "TABLESAMPLE -\n"
+ "\n"
+ "INTERSECT left\n"
@@ -8935,6 +8943,103 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "from orders\n"
+ "group by session(rowtime, interval '1' hour)").ok();
}
+
+ /** Tries to create a calls to some internal operators in
+ * MATCH_RECOGNIZE. Should fail. */
+ @Test public void testMatchRecognizeInternals() throws Exception {
+ sql("values ^pattern_define_as(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^pattern_exclude(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^\"|\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ if (TODO) {
+ // FINAL and other functions should not be visible outside of
+ // MATCH_RECOGNIZE
+ sql("values ^\"FINAL\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^\"RUNNING\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^\"FIRST\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^\"LAST\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ sql("values ^\"PREV\"(1, 2)^")
+ .fails("No match found for function signature .*");
+ }
+ }
+
+ @Test public void testMatchRecognizeDefines() throws Exception {
+ final String sql = "select *\n"
+ + " from emp match_recognize (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.sal < PREV(down.sal),\n"
+ + " up as up.sal > PREV(up.sal)\n"
+ + " ) mr";
+ sql(sql).ok();
+ }
+
+ @Test public void testMatchRecognizeDefines2() throws Exception {
+ final String sql = "select *\n"
+ + " from t match_recognize (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define\n"
+ + " down as down.price < PREV(down.price),\n"
+ + " ^down as up.price > PREV(up.price)^\n"
+ + " ) mr";
+ sql(sql).fails("Pattern variable 'DOWN' has already been defined");
+ }
+
+ @Test public void testMatchRecognizeDefines3() throws Exception {
+ final String sql = "select *\n"
+ + " from emp match_recognize (\n"
+ + " pattern (strt down+up+)\n"
+ + " define\n"
+ + " down as down.sal < PREV(down.sal),\n"
+ + " up as up.sal > PREV(up.sal)\n"
+ + " ) mr";
+ sql(sql).ok();
+ }
+
+ @Test public void testMatchRecognizeDefines4() throws Exception {
+ final String sql = "select * \n"
+ + " from emp match_recognize \n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define \n"
+ + " down as down.sal < PREV(down.sal),\n"
+ + " up as up.sal > FIRST(^PREV(up.sal)^)\n"
+ + " ) mr";
+ sql(sql)
+ .fails("Cannot nest PREV/NEXT under LAST/FIRST 'PREV\\(`UP`\\.`SAL`, 1\\)'");
+ }
+
+ @Test public void testMatchRecognizeDefines5() throws Exception {
+ final String sql = "select * \n"
+ + " from emp match_recognize \n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define \n"
+ + " down as down.sal < PREV(down.sal),\n"
+ + " up as up.sal > FIRST(^FIRST(up.sal)^)\n"
+ + " ) mr";
+ sql(sql)
+ .fails("Cannot nest PREV/NEXT under LAST/FIRST 'FIRST\\(`UP`\\.`SAL`, 0\\)'");
+ }
+
+ @Test public void testMatchRecognizeDefines6() throws Exception {
+ final String sql = "select * \n"
+ + " from emp match_recognize \n"
+ + " (\n"
+ + " pattern (strt down+ up+)\n"
+ + " define \n"
+ + " down as down.sal < PREV(down.sal),\n"
+ + " up as up.sal > ^COUNT(down.sal, up.sal)^\n"
+ + " ) mr";
+ sql(sql)
+ .fails("Invalid number of parameters to COUNT method");
+ }
}
// End SqlValidatorTest.java
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/4e103825/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 53d00c5..f5f4f6e 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -139,6 +139,7 @@ joinCondition:
tableReference:
tablePrimary
+ [ matchRecognize ]
[ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
tablePrimary:
@@ -359,6 +360,7 @@ DECADE,
DEFAULTS,
DEFERRABLE,
DEFERRED,
+**DEFINE**,
DEFINED,
DEFINER,
DEGREE,
@@ -495,6 +497,7 @@ M,
MAP,
**MATCH**,
MATCHED,
+**MATCH_RECOGNIZE**,
**MAX**,
MAXVALUE,
**MEMBER**,
@@ -572,9 +575,11 @@ PARTIAL,
PASCAL,
PASSTHROUGH,
PATH,
+**PATTERN**,
**PERCENTILE_CONT**,
**PERCENTILE_DISC**,
**PERCENT_RANK**,
+**PERMUTE**,
PLACING,
PLAN,
PLI,
@@ -584,6 +589,7 @@ PRECEDING,
**PRECISION**,
**PREPARE**,
PRESERVE,
+**PREV**,
**PRIMARY**,
PRIOR,
PRIVILEGES,
@@ -635,6 +641,7 @@ ROUTINE_SCHEMA,
**ROWS**,
ROW_COUNT,
**ROW_NUMBER**,
+**RUNNING**,
**SAVEPOINT**,
SCALE,
SCHEMA,
@@ -929,8 +936,8 @@ The operator precedence and associativity, highest to lowest.
| string1 NOT LIKE string2 [ ESCAPE string3 ] | Whether *string1* does not match pattern *string2*
| string1 SIMILAR TO string2 [ ESCAPE string3 ] | Whether *string1* matches regular expression *string2*
| string1 NOT SIMILAR TO string2 [ ESCAPE string3 ] | Whether *string1* does not match regular expression *string2*
-| value IN (value [, value]* ) | Whether *value* is equal to a value in a list
-| value NOT IN (value [, value]* ) | Whether *value* is not equal to every value in a list
+| value IN (value [, value]*) | Whether *value* is equal to a value in a list
+| value NOT IN (value [, value]*) | Whether *value* is not equal to every value in a list
| value IN (sub-query) | Whether *value* is equal to a row returned by *sub-query*
| value NOT IN (sub-query) | Whether *value* is not equal to every row returned by *sub-query*
| EXISTS (sub-query) | Whether *sub-query* returns at least one row
@@ -1075,7 +1082,7 @@ Not implemented:
| CASE value<br/>WHEN value1 [, value11 ]* THEN result1<br/>[ WHEN valueN [, valueN1 ]* THEN resultN ]*<br/>[ ELSE resultZ ]<br/> END | Simple case
| CASE<br/>WHEN condition1 THEN result1<br/>[ WHEN conditionN THEN resultN ]*<br/>[ ELSE resultZ ]<br/>END | Searched case
| NULLIF(value, value) | Returns NULL if the values are the same.<br/><br/>For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5.
-| COALESCE(value, value [, value ]* ) | Provides a value if the first value is null.<br/><br/>For example, <code>COALESCE(NULL, 5)</code> returns 5.
+| COALESCE(value, value [, value ]*) | Provides a value if the first value is null.<br/><br/>For example, <code>COALESCE(NULL, 5)</code> returns 5.
### Type conversion
@@ -1087,8 +1094,8 @@ Not implemented:
| Operator syntax | Description
|:--------------- |:-----------
-| ROW (value [, value]* ) | Creates a row from a list of values.
-| (value [, value]* ) | Creates a row from a list of values.
+| ROW (value [, value ]*) | Creates a row from a list of values.
+| (value [, value ]* ) | Creates a row from a list of values.
| map '[' key ']' | Returns the element of a map with a particular key.
| array '[' index ']' | Returns the element at a particular location in an array.
| ARRAY '[' value [, value ]* ']' | Creates an array from a list of values.
@@ -1205,8 +1212,8 @@ Syntax:
{% highlight sql %}
aggregateCall:
- agg( [ DISTINCT ] value [, value]* ) [ FILTER ( WHERE condition ) ]
- | agg(*) [ FILTER ( WHERE condition ) ]
+ agg( [ ALL | DISTINCT ] value [, value ]*) [ FILTER (WHERE condition) ]
+ | agg(*) [ FILTER (WHERE condition) ]
{% endhighlight %}
If `FILTER` is present, the aggregate function only considers rows for which
@@ -1217,17 +1224,17 @@ passed to the aggregate function.
| Operator syntax | Description
|:---------------------------------- |:-----------
-| COLLECT( [ DISTINCT ] value) | Returns a multiset of the values
-| COUNT( [ DISTINCT ] value [, value]* ) | Returns the number of input rows for which *value* is not null (wholly not null if *value* is composite)
+| COLLECT( [ ALL | DISTINCT ] value) | Returns a multiset of the values
+| COUNT( [ ALL | DISTINCT ] value [, value ]*) | Returns the number of input rows for which *value* is not null (wholly not null if *value* is composite)
| COUNT(*) | Returns the number of input rows
-| AVG( [ DISTINCT ] numeric) | Returns the average (arithmetic mean) of *numeric* across all input values
-| SUM( [ DISTINCT ] numeric) | Returns the sum of *numeric* across all input values
-| MAX( [ DISTINCT ] value) | Returns the maximum value of *value* across all input values
-| MIN( [ DISTINCT ] value) | Returns the minimum value of *value* across all input values
-| STDDEV_POP( [ DISTINCT ] numeric) | Returns the population standard deviation of *numeric* across all input values
-| STDDEV_SAMP( [ DISTINCT ] numeric) | Returns the sample standard deviation of *numeric* across all input values
-| VAR_POP( [ DISTINCT ] value) | Returns the population variance (square of the population standard deviation) of *numeric* across all input values
-| VAR_SAMP( [ DISTINCT ] numeric) | Returns the sample variance (square of the sample standard deviation) of *numeric* across all input values
+| AVG( [ ALL | DISTINCT ] numeric) | Returns the average (arithmetic mean) of *numeric* across all input values
+| SUM( [ ALL | DISTINCT ] numeric) | Returns the sum of *numeric* across all input values
+| MAX( [ ALL | DISTINCT ] value) | Returns the maximum value of *value* across all input values
+| MIN( [ ALL | DISTINCT ] value) | Returns the minimum value of *value* across all input values
+| STDDEV_POP( [ ALL | DISTINCT ] numeric) | Returns the population standard deviation of *numeric* across all input values
+| STDDEV_SAMP( [ ALL | DISTINCT ] numeric) | Returns the sample standard deviation of *numeric* across all input values
+| VAR_POP( [ ALL | DISTINCT ] value) | Returns the population variance (square of the population standard deviation) of *numeric* across all input values
+| VAR_SAMP( [ ALL | DISTINCT ] numeric) | Returns the sample variance (square of the sample standard deviation) of *numeric* across all input values
| COVAR_POP(numeric1, numeric2) | Returns the population covariance of the pair (*numeric1*, *numeric2*) across all input values
| COVAR_SAMP(numeric1, numeric2) | Returns the sample covariance of the pair (*numeric1*, *numeric2*) across all input values
| REGR_SXX(numeric1, numeric2) | Returns the sum of squares of the dependent expression in a linear regression model
@@ -1247,7 +1254,7 @@ Not implemented:
| Operator syntax | Description
|:----------------------------------------- |:-----------
-| COUNT(value [, value ]* ) OVER window | Returns the number of rows in *window* for which *value* is not null (wholly not null if *value* is composite)
+| COUNT(value [, value ]*) OVER window | Returns the number of rows in *window* for which *value* is not null (wholly not null if *value* is composite)
| COUNT(*) OVER window | Returns the number of rows in *window*
| AVG(numeric) OVER window | Returns the average (arithmetic mean) of *numeric* across all values in *window*
| SUM(numeric) OVER window | Returns the sum of *numeric* across all values in *window*
@@ -1275,9 +1282,9 @@ Not implemented:
| Operator syntax | Description
|:-------------------- |:-----------
-| GROUPING(expression [, expression ] * ) | Returns a bit vector of the given grouping expressions
+| GROUPING(expression [, expression ]*) | Returns a bit vector of the given grouping expressions
| GROUP_ID() | Returns an integer that uniquely identifies the combination of grouping keys
-| GROUPING_ID(expression [, expression ] * ) | Synonym for `GROUPING`
+| GROUPING_ID(expression [, expression ]*) | Synonym for `GROUPING`
### Grouped window functions
@@ -1425,4 +1432,75 @@ Here are some examples:
* `f(c => 3, d => 1, a => 0)` is equivalent to `f(0, NULL, 3, 1, NULL)`;
* `f(c => 3, d => 1)` is not legal, because you have not specified a value for
`a` and `a` is not optional.
+```
+### MATCH_RECOGNIZE
+
+`MATCH_RECOGNIZE` is a SQL extension for recognizing sequences of
+events in complex event processing (CEP).
+
+It is experimental in Calcite, and yet not fully implemented.
+
+#### Syntax
+
+{% highlight sql %}
+matchRecognize:
+ MATCH_RECOGNIZE '('
+ [ PARTITION BY expression [, expression ]* ]
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ MEASURES measureColumn [, measureColumn ]* ]
+ [ ON ROW PER MATCH | ALL ROWS PER MATCH ]
+ [ AFTER MATCH
+ ( SKIP TO NEXT ROW
+ | SKIP PAST LAST ROW
+ | SKIP TO FIRST variable
+ | SKIP TO LAST variable
+ | SKIP TO variable )
+ ]
+ PATTERN '(' pattern ')'
+ [ SUBSET variable [, variable ]* ]
+ DEFINE variable AS condition [, variable AS condition ]*
+ ')'
+
+measureColumn:
+ expression AS alias
+
+pattern:
+ patternTerm ['|' patternTerm ]*
+
+patternTerm:
+ patternFactor [ patternFactor ]*
+
+patternFactor:
+ patternPrimary [ patternQuantifier ]
+
+patternPrimary:
+ variable
+ | '$'
+ | '^'
+ | '(' [ pattern ] ')'
+ | '{-' pattern '-}'
+ | PERMUTE '(' pattern [, pattern ]* ')'
+
+patternQuantifier:
+ '*'
+ | '*?'
+ | '+'
+ | '+?'
+ | '?'
+ | '??'
+ | '{' { [ minRepeat ], [ maxRepeat ] } '}' ['?']
+ | '{' repeat '}'
+{% endhighlight %}
+
+In *patternQuantifier*, *repeat* is a positive integer,
+and *minRepeat* and *maxRepeat* are non-negative integers.
+
+The following clauses are not implemented:
+
+* `PARTITION BY`
+* `ORDER BY`
+* `MEASURES`
+* `ON ROW PER MATCH`, `ALL ROWS PER MATCH`
+* `AFTER MATCH`
+* `SUBSET`