http://git-wip-us.apache.org/repos/asf/calcite/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/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/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/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/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/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/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/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/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/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/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`
