You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/04/02 05:25:58 UTC
calcite git commit: [CALCITE-1642] Support MEASURES clause in
MATCH_RECOGNIZE (Zhiqiang-He)
Repository: calcite
Updated Branches:
refs/heads/master 5289d343f -> 4d20d62d4
[CALCITE-1642] Support MEASURES clause in MATCH_RECOGNIZE (Zhiqiang-He)
Close apache/calcite#400
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/4d20d62d
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/4d20d62d
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/4d20d62d
Branch: refs/heads/master
Commit: 4d20d62d4d752ce4de29a002ec313a758a7cee6e
Parents: 5289d34
Author: Zhiqiang-He <ab...@qq.com>
Authored: Mon Mar 13 19:11:19 2017 +0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Sat Apr 1 20:43:28 2017 -0700
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 52 ++-
.../java/org/apache/calcite/rel/core/Match.java | 96 ++++--
.../apache/calcite/rel/core/RelFactories.java | 14 +-
.../calcite/rel/logical/LogicalMatch.java | 17 +-
.../calcite/rel/rel2sql/RelToSqlConverter.java | 28 +-
.../calcite/rel/rel2sql/SqlImplementor.java | 13 +
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../java/org/apache/calcite/sql/SqlKind.java | 1 +
.../apache/calcite/sql/SqlMatchRecognize.java | 50 ++-
.../calcite/sql/fun/SqlStdOperatorTable.java | 30 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 74 ++++-
.../calcite/sql2rel/SqlToRelConverter.java | 25 +-
.../calcite/runtime/CalciteResource.properties | 1 +
.../rel/rel2sql/RelToSqlConverterTest.java | 325 ++++++++++++++++---
.../calcite/sql/parser/SqlParserTest.java | 211 ++++++++++--
.../calcite/sql/parser/SqlUnParserTest.java | 2 +-
.../apache/calcite/test/SqlValidatorTest.java | 23 +-
site/_docs/reference.md | 1 +
18 files changed, 797 insertions(+), 169 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index c30794d..9b04cb5 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -2494,7 +2494,7 @@ SqlNode OrderItem() :
SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) :
{
final SqlParserPos startPos;
- SqlParserPos pos;
+ SqlNodeList measureList = SqlNodeList.EMPTY;
SqlNode pattern;
SqlNodeList patternDefList;
SqlLiteral isStrictStarts = SqlLiteral.createBoolean(false, getPos());
@@ -2502,6 +2502,10 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) :
}
{
<MATCH_RECOGNIZE> { startPos = getPos(); } <LPAREN>
+ [
+ <MEASURES>
+ measureList = MeasureColumnCommaList(getPos())
+ ]
<PATTERN>
<LPAREN>
(
@@ -2516,11 +2520,45 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) :
{ isStrictEnds = SqlLiteral.createBoolean(false, getPos()); }
)
<RPAREN>
- <DEFINE> { pos = getPos(); }
- patternDefList = PatternDefinitionCommaList(pos)
+ <DEFINE>
+ patternDefList = PatternDefinitionCommaList(getPos())
<RPAREN> {
return new SqlMatchRecognize(startPos.plus(getPos()), tableRef,
- pattern, isStrictStarts, isStrictEnds, patternDefList);
+ pattern, isStrictStarts, isStrictEnds, patternDefList, measureList);
+ }
+}
+
+SqlNodeList MeasureColumnCommaList(SqlParserPos pos) :
+{
+ SqlNode e;
+ final List<SqlNode> eList = new ArrayList<SqlNode>();
+}
+{
+ e = MeasureColumn() {
+ eList.add(e);
+ }
+ (
+ <COMMA>
+ e = MeasureColumn() {
+ eList.add(e);
+ }
+ )*
+ {
+ return new SqlNodeList(eList, pos.plusAll(eList));
+ }
+}
+
+SqlNode MeasureColumn() :
+{
+ SqlNode e;
+ SqlIdentifier alias;
+}
+{
+ e = Expression(ExprContext.ACCEPT_NON_QUERY)
+ <AS>
+ alias = SimpleIdentifier()
+ {
+ return SqlStdOperatorTable.AS.createCall(e.getParserPosition().plus(getPos()), e, alias);
}
}
@@ -2688,9 +2726,6 @@ SqlNodeList PatternDefinitionCommaList(SqlParserPos pos) :
}
{
e = PatternDefinition() {
- if (pos == null) {
- pos = e.getParserPosition();
- }
eList.add(e);
}
(
@@ -2700,7 +2735,7 @@ SqlNodeList PatternDefinitionCommaList(SqlParserPos pos) :
}
)*
{
- return new SqlNodeList(eList, pos.plus(getPos()));
+ return new SqlNodeList(eList, pos.plusAll(eList));
}
}
@@ -5676,6 +5711,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < MATCH_RECOGNIZE: "MATCH_RECOGNIZE">
| < MAX: "MAX" >
| < MAXVALUE: "MAXVALUE" >
+ | < MEASURES: "MEASURES" >
| < MEMBER: "MEMBER" >
| < MERGE: "MERGE" >
| < MESSAGE_LENGTH: "MESSAGE_LENGTH" >
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/rel/core/Match.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Match.java b/core/src/main/java/org/apache/calcite/rel/core/Match.java
index 4e81fd6..84ef106 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Match.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Match.java
@@ -36,6 +36,7 @@ import org.apache.calcite.sql.fun.SqlSumEmptyIsZeroAggFunction;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSortedMap;
import com.google.common.collect.ImmutableSortedSet;
import java.util.HashSet;
@@ -43,6 +44,7 @@ import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.SortedSet;
+import java.util.TreeMap;
import java.util.TreeSet;
/**
@@ -52,12 +54,14 @@ import java.util.TreeSet;
*/
public abstract class Match extends SingleRel {
//~ Instance fields ---------------------------------------------
+ private static final String STAR = "*";
protected final ImmutableMap<String, RexNode> measures;
protected final RexNode pattern;
protected final boolean strictStart;
protected final boolean strictEnd;
protected final ImmutableMap<String, RexNode> patternDefinitions;
protected final Set<RexMRAggCall> aggregateCalls;
+ protected final Map<String, SortedSet<RexMRAggCall>> aggregateCallsPreVar;
//~ Constructors -----------------------------------------------
@@ -71,11 +75,13 @@ public abstract class Match extends SingleRel {
* @param strictStart Whether it is a strict start pattern
* @param strictEnd Whether it is a strict end pattern
* @param patternDefinitions Pattern definitions
+ * @param measures Measure definitions
* @param rowType Row type
*/
protected Match(RelOptCluster cluster, RelTraitSet traitSet,
RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd,
- Map<String, RexNode> patternDefinitions, RelDataType rowType) {
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType) {
super(cluster, traitSet, input);
this.pattern = Preconditions.checkNotNull(pattern);
Preconditions.checkArgument(patternDefinitions.size() > 0);
@@ -83,7 +89,7 @@ public abstract class Match extends SingleRel {
this.strictEnd = strictEnd;
this.patternDefinitions = ImmutableMap.copyOf(patternDefinitions);
this.rowType = rowType;
- this.measures = ImmutableMap.of();
+ this.measures = ImmutableMap.copyOf(measures);
final AggregateFinder aggregateFinder = new AggregateFinder();
for (RexNode rex : this.patternDefinitions.values()) {
@@ -91,15 +97,31 @@ public abstract class Match extends SingleRel {
aggregateFinder.go((RexCall) rex);
}
}
+
+ for (RexNode rex : this.measures.values()) {
+ if (rex instanceof RexCall) {
+ aggregateFinder.go((RexCall) rex);
+ }
+ }
+
aggregateCalls = ImmutableSortedSet.copyOf(aggregateFinder.aggregateCalls);
+ aggregateCallsPreVar =
+ copy(aggregateFinder.aggregateCallsPerVar);
}
- //~ Methods --------------------------------------------------
-
- public Set<RexMRAggCall> getAggregateCalls() {
- return aggregateCalls;
+ /** Creates an immutable copy of a map of sorted sets. */
+ private static <K extends Comparable<K>, V>
+ ImmutableSortedMap<K, SortedSet<V>> copy(Map<K, SortedSet<V>> map) {
+ final ImmutableSortedMap.Builder<K, SortedSet<V>> b =
+ ImmutableSortedMap.naturalOrder();
+ for (Map.Entry<K, SortedSet<V>> e : map.entrySet()) {
+ b.put(e.getKey(), ImmutableSortedSet.copyOf(e.getValue()));
+ }
+ return b.build();
}
+ //~ Methods --------------------------------------------------
+
public ImmutableMap<String, RexNode> getMeasures() {
return measures;
}
@@ -122,21 +144,17 @@ public abstract class Match extends SingleRel {
public abstract Match copy(RelNode input, RexNode pattern,
boolean strictStart, boolean strictEnd,
- Map<String, RexNode> patternDefinitions, RelDataType rowType);
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType);
- @Override public RelNode copy(
- RelTraitSet traitSet,
- List<RelNode> inputs) {
+ @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) {
if (getInputs().equals(inputs)
&& traitSet == getTraitSet()) {
return this;
}
- return copy(
- inputs.get(0),
- pattern, strictStart, strictEnd,
- patternDefinitions,
- rowType);
+ return copy(inputs.get(0), pattern, strictStart, strictEnd,
+ patternDefinitions, measures, rowType);
}
@Override public RelWriter explainTerms(RelWriter pw) {
@@ -156,12 +174,13 @@ public abstract class Match extends SingleRel {
return pw;
}
-
/**
* Find aggregate functions in operands.
*/
private static class AggregateFinder extends RexVisitorImpl {
final SortedSet<RexMRAggCall> aggregateCalls = new TreeSet<>();
+ final Map<String, SortedSet<RexMRAggCall>> aggregateCallsPerVar =
+ new TreeMap<>();
AggregateFinder() {
super(true);
@@ -193,6 +212,28 @@ public abstract class Match extends SingleRel {
call.getType(), call.getOperands(), aggregateCalls.size());
aggregateCalls.add(aggCall);
Set<String> pv = new PatternVarFinder().go(call.getOperands());
+ if (pv.size() == 0) {
+ pv.add(STAR);
+ }
+ for (String alpha : pv) {
+ final SortedSet<RexMRAggCall> set;
+ if (aggregateCallsPerVar.containsKey(alpha)) {
+ set = aggregateCallsPerVar.get(alpha);
+ } else {
+ set = new TreeSet<>();
+ aggregateCallsPerVar.put(alpha, set);
+ }
+ boolean update = true;
+ for (RexMRAggCall rex : set) {
+ if (rex.toString().equals(aggCall.toString())) {
+ update = false;
+ break;
+ }
+ }
+ if (update) {
+ set.add(aggCall);
+ }
+ }
}
return null;
}
@@ -241,32 +282,21 @@ public abstract class Match extends SingleRel {
/**
* Aggregate calls in match recognize.
*/
- public static class RexMRAggCall extends RexCall implements Comparable<RexMRAggCall> {
+ public static final class RexMRAggCall extends RexCall
+ implements Comparable<RexMRAggCall> {
public final int ordinal;
- public RexMRAggCall(
- SqlAggFunction aggFun,
+
+ RexMRAggCall(SqlAggFunction aggFun,
RelDataType type,
List<RexNode> operands,
int ordinal) {
super(type, aggFun, operands);
this.ordinal = ordinal;
- digest = computeDigest();
- }
-
- public String computeDigest() {
- return super.computeDigest(false);
+ digest = toString(); // can compute here because class is final
}
@Override public int compareTo(RexMRAggCall o) {
- if (o.computeDigest() == null) {
- return 0;
- }
-
- if (computeDigest() == null) {
- return 1;
- }
-
- return o.computeDigest().compareTo(computeDigest());
+ return toString().compareTo(o.toString());
}
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
index 7dad9a8..10f9a10 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
@@ -395,8 +395,9 @@ public class RelFactories {
public interface MatchFactory {
/** Creates a {@link Match}. */
RelNode createMatchRecognize(RelNode input, RexNode pattern,
- boolean strictStarts, boolean strictEnds,
- Map<String, RexNode> patternDefinitions, RelDataType rowType);
+ boolean strictStart, boolean strictEnd,
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType);
}
/**
@@ -405,10 +406,11 @@ public class RelFactories {
*/
private static class MatchFactoryImpl implements MatchFactory {
public RelNode createMatchRecognize(RelNode input, RexNode pattern,
- boolean strictStarts, boolean strictEnds,
- Map<String, RexNode> patternDefinitions, RelDataType rowType) {
- return LogicalMatch.create(input, pattern, strictStarts, strictEnds,
- patternDefinitions, rowType);
+ boolean strictStart, boolean strictEnd,
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType) {
+ return LogicalMatch.create(input, pattern, strictStart, strictEnd,
+ patternDefinitions, measures, rowType);
}
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java
index 46d7755..524e8a5 100644
--- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java
+++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java
@@ -42,13 +42,15 @@ public class LogicalMatch extends Match {
* @param strictStart Whether it is a strict start pattern
* @param strictEnd Whether it is a strict end pattern
* @param patternDefinitions Pattern definitions
+ * @param measures Measure definitions
* @param rowType Row type
*/
public LogicalMatch(RelOptCluster cluster, RelTraitSet traitSet,
RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd,
- Map<String, RexNode> patternDefinitions, RelDataType rowType) {
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType) {
super(cluster, traitSet, input, pattern, strictStart, strictEnd,
- patternDefinitions, rowType);
+ patternDefinitions, measures, rowType);
}
/**
@@ -56,21 +58,24 @@ public class LogicalMatch extends Match {
*/
public static LogicalMatch create(RelNode input, RexNode pattern,
boolean strictStart, boolean strictEnd,
- Map<String, RexNode> patternDefinitions, RelDataType rowType) {
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType) {
final RelOptCluster cluster = input.getCluster();
final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE);
return new LogicalMatch(cluster, traitSet, input, pattern,
- strictStart, strictEnd, patternDefinitions, rowType);
+ strictStart, strictEnd, patternDefinitions, measures, rowType);
}
//~ Methods ------------------------------------------------------
@Override public Match copy(RelNode input, RexNode pattern,
boolean strictStart, boolean strictEnd,
- Map<String, RexNode> patternDefinitions, RelDataType rowType) {
+ Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures,
+ RelDataType rowType) {
final RelTraitSet traitSet = getCluster().traitSetOf(Convention.NONE);
return new LogicalMatch(getCluster(), traitSet,
- input, pattern, strictStart, strictEnd, patternDefinitions, rowType);
+ input, pattern, strictStart, strictEnd, patternDefinitions, measures,
+ rowType);
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index baf093a..ecb75a0 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -41,6 +41,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.sql.JoinConditionType;
import org.apache.calcite.sql.JoinType;
+import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDelete;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlIdentifier;
@@ -382,20 +383,30 @@ public class RelToSqlConverter extends SqlImplementor
final SqlLiteral strictStart = SqlLiteral.createBoolean(e.isStrictStart(), POS);
final SqlLiteral strictEnd = SqlLiteral.createBoolean(e.isStrictEnd(), POS);
+ final SqlNodeList measureList = new SqlNodeList(POS);
+ for (Map.Entry<String, RexNode> entry : e.getMeasures().entrySet()) {
+ final String alias = entry.getKey();
+ final SqlNode sqlNode = context.toSql(null, entry.getValue());
+ measureList.add(as(sqlNode, alias));
+ }
+
final SqlNodeList patternDefList = new SqlNodeList(POS);
for (Map.Entry<String, RexNode> entry : e.getPatternDefinitions().entrySet()) {
- String alias = entry.getKey();
- SqlNode sqlNode = context.toSql(null, entry.getValue());
- patternDefList.add(
- SqlStdOperatorTable.AS.createCall(POS, sqlNode,
- new SqlIdentifier(alias, POS)));
+ final String alias = entry.getKey();
+ final SqlNode sqlNode = context.toSql(null, entry.getValue());
+ patternDefList.add(as(sqlNode, alias));
}
- final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, pattern,
- strictStart, strictEnd, patternDefList);
+ final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef,
+ pattern, strictStart, strictEnd, patternDefList, measureList);
return result(matchRecognize, Expressions.list(Clause.FROM), e, null);
}
+ private SqlCall as(SqlNode e, String alias) {
+ return SqlStdOperatorTable.AS.createCall(POS, e,
+ new SqlIdentifier(alias, POS));
+ }
+
@Override public void addSelect(List<SqlNode> selectList, SqlNode node,
RelDataType rowType) {
String name = rowType.getFieldNames().get(selectList.size());
@@ -405,8 +416,7 @@ public class RelToSqlConverter extends SqlImplementor
// Put it in ordinalMap
ordinalMap.put(lowerName, node);
} else if (alias == null || !alias.equals(name)) {
- node = SqlStdOperatorTable.AS.createCall(
- POS, node, new SqlIdentifier(name, POS));
+ node = as(node, name);
}
selectList.add(node);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index f3287f0..181bfc7 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -31,6 +31,7 @@ import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexLocalRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexOver;
+import org.apache.calcite.rex.RexPatternFieldRef;
import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.rex.RexWindow;
import org.apache.calcite.rex.RexWindowBound;
@@ -515,6 +516,17 @@ public abstract class SqlImplementor {
case INPUT_REF:
return field(((RexInputRef) rex).getIndex());
+ case PATTERN_INPUT_REF:
+ final RexPatternFieldRef ref = (RexPatternFieldRef) rex;
+ String pv = ref.getAlpha();
+ SqlNode refNode = field(ref.getIndex());
+ final SqlIdentifier id = (SqlIdentifier) refNode;
+ if (id.names.size() > 1) {
+ return id.setName(0, pv);
+ } else {
+ return new SqlIdentifier(ImmutableList.of(pv, id.names.get(0)), POS);
+ }
+
case LITERAL:
final RexLiteral literal = (RexLiteral) rex;
if (literal.getTypeName() == SqlTypeName.SYMBOL) {
@@ -551,6 +563,7 @@ public abstract class SqlImplementor {
default:
throw new AssertionError(literal + ": " + literal.getTypeName());
}
+
case CASE:
final RexCall caseCall = (RexCall) rex;
final List<SqlNode> caseNodeList =
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 40774d0..a912969 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -692,6 +692,9 @@ public interface CalciteResource {
@BaseMessage("Function ''{0}'' can only be used in MATCH_RECOGNIZE")
ExInst<SqlValidatorException> FunctionMatchRecognizeOnly(String call);
+
+ @BaseMessage("Null parameters in ''{0}''")
+ ExInst<SqlValidatorException> PatternFunctionNullCheck(String call);
}
// End CalciteResource.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/sql/SqlKind.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 454e07f..8f49591 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -1020,6 +1020,7 @@ public enum SqlKind {
EnumSet.complementOf(
concat(
EnumSet.of(AS, ARGUMENT_ASSIGNMENT, DEFAULT,
+ RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
SELECT, JOIN, OTHER_FUNCTION, CAST, TRIM, FLOOR, CEIL,
TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT,
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java
index 770ec61..300e886 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java
@@ -23,7 +23,10 @@ import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.util.ImmutableNullableList;
+import com.google.common.base.Preconditions;
+
import java.util.List;
+import javax.annotation.Nonnull;
/**
* SqlNode for Match_recognize clause
@@ -34,6 +37,7 @@ public class SqlMatchRecognize extends SqlCall {
public static final int OPERAND_STRICT_START = 2;
public static final int OPERAND_STRICT_END = 3;
public static final int OPERAND_PATTERN_DEFINES = 4;
+ public static final int OPERAND_MEASURES = 5;
//~ Instance fields -------------------------------------------
@@ -42,20 +46,20 @@ public class SqlMatchRecognize extends SqlCall {
private SqlLiteral strictStart;
private SqlLiteral strictEnd;
private SqlNodeList patternDefList;
+ private SqlNodeList measureList;
/** Creates a SqlMatchRecognize. */
public SqlMatchRecognize(SqlParserPos pos, SqlNode tableRef, SqlNode pattern,
- SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList) {
+ SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList,
+ SqlNodeList measureList) {
super(pos);
- this.tableRef = tableRef;
- this.pattern = pattern;
+ this.tableRef = Preconditions.checkNotNull(tableRef);
+ this.pattern = Preconditions.checkNotNull(pattern);
this.strictStart = strictStart;
this.strictEnd = strictEnd;
- this.patternDefList = patternDefList;
-
- assert tableRef != null;
- assert pattern != null;
- assert patternDefList != null && patternDefList.size() > 0;
+ this.patternDefList = Preconditions.checkNotNull(patternDefList);
+ Preconditions.checkArgument(patternDefList.size() > 0);
+ this.measureList = Preconditions.checkNotNull(measureList);
}
// ~ Methods
@@ -70,7 +74,7 @@ public class SqlMatchRecognize extends SqlCall {
@Override public List<SqlNode> getOperandList() {
return ImmutableNullableList.of(tableRef, pattern, strictStart, strictEnd,
- patternDefList);
+ patternDefList, measureList);
}
@Override public void unparse(SqlWriter writer, int leftPrec,
@@ -85,7 +89,7 @@ public class SqlMatchRecognize extends SqlCall {
@Override public void setOperand(int i, SqlNode operand) {
switch (i) {
case OPERAND_TABLE_REF:
- tableRef = operand;
+ tableRef = Preconditions.checkNotNull(operand);
break;
case OPERAND_PATTERN:
pattern = operand;
@@ -97,14 +101,18 @@ public class SqlMatchRecognize extends SqlCall {
strictEnd = (SqlLiteral) operand;
break;
case OPERAND_PATTERN_DEFINES:
- patternDefList = (SqlNodeList) operand;
+ patternDefList = Preconditions.checkNotNull((SqlNodeList) operand);
+ Preconditions.checkArgument(patternDefList.size() > 0);
+ break;
+ case OPERAND_MEASURES:
+ measureList = Preconditions.checkNotNull((SqlNodeList) operand);
break;
default:
throw new AssertionError(i);
}
}
- public SqlNode getTableRef() {
+ @Nonnull public SqlNode getTableRef() {
return tableRef;
}
@@ -120,10 +128,14 @@ public class SqlMatchRecognize extends SqlCall {
return strictEnd;
}
- public SqlNodeList getPatternDefList() {
+ @Nonnull public SqlNodeList getPatternDefList() {
return patternDefList;
}
+ @Nonnull public SqlNodeList getMeasureList() {
+ return measureList;
+ }
+
/**
* An operator describing a MATCH_RECOGNIZE specification.
*/
@@ -144,11 +156,11 @@ public class SqlMatchRecognize extends SqlCall {
SqlParserPos pos,
SqlNode... operands) {
assert functionQualifier == null;
- assert operands.length == 5;
+ assert operands.length == 6;
return new SqlMatchRecognize(pos, operands[0], operands[1],
(SqlLiteral) operands[2], (SqlLiteral) operands[3],
- (SqlNodeList) operands[4]);
+ (SqlNodeList) operands[4], (SqlNodeList) operands[5]);
}
@Override public <R> void acceptCall(
@@ -188,6 +200,14 @@ public class SqlMatchRecognize extends SqlCall {
pattern.tableRef.unparse(writer, 0, 0);
final SqlWriter.Frame mrFrame = writer.startFunCall("MATCH_RECOGNIZE");
+ if (pattern.measureList != null && pattern.measureList.size() > 0) {
+ writer.newlineAndIndent();
+ writer.sep("MEASURES");
+ final SqlWriter.Frame measureFrame = writer.startList("", "");
+ pattern.measureList.unparse(writer, 0, 0);
+ writer.endList(measureFrame);
+ }
+
writer.newlineAndIndent();
writer.sep("PATTERN");
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 7c00a2e..8d385a6 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -727,6 +727,26 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
null,
null);
+ /** {@code FINAL} function to be used within {@code MATCH_RECOGNIZE}. */
+ public static final SqlPrefixOperator FINAL =
+ new SqlPrefixOperator(
+ "FINAL",
+ SqlKind.FINAL,
+ 80,
+ ReturnTypes.ARG0_NULLABLE,
+ null,
+ OperandTypes.ANY);
+
+ /** {@code RUNNING} function to be used within {@code MATCH_RECOGNIZE}. */
+ public static final SqlPrefixOperator RUNNING =
+ new SqlPrefixOperator(
+ "RUNNING",
+ SqlKind.RUNNING,
+ 80,
+ ReturnTypes.ARG0_NULLABLE,
+ null,
+ OperandTypes.ANY);
+
//-------------------------------------------------------------
// AGGREGATE OPERATORS
//-------------------------------------------------------------
@@ -1398,16 +1418,6 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
new SqlBaseContextVariable("PI", ReturnTypes.DOUBLE,
SqlFunctionCategory.NUMERIC);
- /** {@code FINAL} function to be used within {@code MATCH_RECOGNIZE}. */
- public static final SqlFunction FINAL =
- new SqlFunction("FINAL", SqlKind.FINAL, ReturnTypes.ARG0_NULLABLE, null,
- OperandTypes.ANY, SqlFunctionCategory.MATCH_RECOGNIZE);
-
- /** {@code RUNNING} function to be used within {@code MATCH_RECOGNIZE}. */
- public static final SqlFunction RUNNING =
- new SqlFunction("RUNNING", SqlKind.RUNNING, ReturnTypes.ARG0_NULLABLE,
- null, OperandTypes.ANY, SqlFunctionCategory.MATCH_RECOGNIZE);
-
/** {@code FIRST} function to be used within {@code MATCH_RECOGNIZE}. */
public static final SqlFunction FIRST =
new SqlFunction("FIRST", SqlKind.FIRST, ReturnTypes.ARG0_NULLABLE,
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 6e3d017..ff7c38b 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
@@ -4484,7 +4484,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
@Override public void validateMatchRecognize(SqlCall call) {
- SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
+ final SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
final MatchRecognizeScope scope =
(MatchRecognizeScope) getMatchRecognizeScope(matchRecognize);
@@ -4498,7 +4498,67 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
pattern.accept(visitor);
validateDefinitions(matchRecognize, scope);
- ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType());
+
+ List<Map.Entry<String, RelDataType>> fields =
+ validateMeasure(matchRecognize, scope);
+ final RelDataType rowType = typeFactory.createStructType(fields);
+ if (matchRecognize.getMeasureList().size() == 0) {
+ ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType());
+ } else {
+ ns.setType(rowType);
+ }
+ }
+
+ private List<Map.Entry<String, RelDataType>> validateMeasure(SqlMatchRecognize mr,
+ MatchRecognizeScope scope) {
+ final List<String> aliases = new ArrayList<>();
+ final List<SqlNode> sqlNodes = new ArrayList<>();
+ final SqlNodeList measures = mr.getMeasureList();
+ final List<Map.Entry<String, RelDataType>> fields = new ArrayList<>();
+
+ for (SqlNode measure : measures) {
+ assert measure instanceof SqlCall;
+ final String alias = deriveAlias(measure, aliases.size());
+ aliases.add(alias);
+
+ SqlNode expand = expand(measure, scope);
+ expand = navigationInMeasure(expand);
+ setOriginal(expand, measure);
+
+ inferUnknownTypes(unknownType, scope, expand);
+ final RelDataType type = deriveType(scope, expand);
+ setValidatedNodeType(measure, type);
+
+ fields.add(Pair.of(alias, type));
+ sqlNodes.add(
+ SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, expand,
+ new SqlIdentifier(alias, SqlParserPos.ZERO)));
+ }
+
+ SqlNodeList list = new SqlNodeList(sqlNodes, measures.getParserPosition());
+ inferUnknownTypes(unknownType, scope, list);
+
+ for (SqlNode node : list) {
+ validateExpr(node, scope);
+ }
+
+ mr.setOperand(SqlMatchRecognize.OPERAND_MEASURES, list);
+
+ return fields;
+ }
+
+ private SqlNode navigationInMeasure(SqlNode node) {
+ Set<String> prefix = node.accept(new PatternValidator(true));
+ Util.discard(prefix);
+ List<SqlNode> ops = ((SqlCall) node).getOperandList();
+
+ SqlOperator defaultOp = SqlStdOperatorTable.FINAL;
+ if (!isRunningOrFinal(ops.get(0).getKind())
+ || ops.get(0).getKind() == SqlKind.RUNNING) {
+ SqlNode newNode = defaultOp.createCall(SqlParserPos.ZERO, ops.get(0));
+ node = SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, newNode, ops.get(1));
+ }
+ return node;
}
private void validateDefinitions(SqlMatchRecognize mr,
@@ -5409,7 +5469,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
}
- if (isRunningOrFinal(kind) && isMeasure) {
+ if (isRunningOrFinal(kind) && !isMeasure) {
throw newValidationError(call,
Static.RESOURCE.PatternRunningFunctionInDefine(call.toString()));
}
@@ -5426,13 +5486,17 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
case COUNT:
if (vars.size() > 1) {
throw newValidationError(call,
- Static.RESOURCE.PatternFunctionVariableCheck(call.toString()));
+ Static.RESOURCE.PatternCountFunctionArg());
}
break;
default:
+ if (vars.isEmpty()) {
+ throw newValidationError(call,
+ Static.RESOURCE.PatternFunctionNullCheck(call.toString()));
+ }
if (vars.size() != 1) {
throw newValidationError(call,
- Static.RESOURCE.PatternCountFunctionArg());
+ Static.RESOURCE.PatternFunctionVariableCheck(call.toString()));
}
break;
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 18a1e39..5aa5ec8 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -52,6 +52,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;
@@ -78,6 +79,7 @@ import org.apache.calcite.rex.RexFieldCollation;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexPatternFieldRef;
import org.apache.calcite.rex.RexRangeRef;
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexSubQuery;
@@ -141,6 +143,7 @@ import org.apache.calcite.sql.validate.AggregatingSelectScope;
import org.apache.calcite.sql.validate.CollectNamespace;
import org.apache.calcite.sql.validate.DelegatingScope;
import org.apache.calcite.sql.validate.ListScope;
+import org.apache.calcite.sql.validate.MatchRecognizeScope;
import org.apache.calcite.sql.validate.ParameterScope;
import org.apache.calcite.sql.validate.SelectScope;
import org.apache.calcite.sql.validate.SqlMonotonicity;
@@ -2109,6 +2112,16 @@ public class SqlToRelConverter {
mrBlackBoard.setPatternVarRef(true);
+ // convert measures
+ final ImmutableMap.Builder<String, RexNode> measureNodes =
+ ImmutableMap.builder();
+ for (SqlNode measure : matchRecognize.getMeasureList()) {
+ List<SqlNode> operands = ((SqlCall) measure).getOperandList();
+ String alias = ((SqlIdentifier) operands.get(1)).getSimple();
+ RexNode rex = mrBlackBoard.convertExpression(operands.get(0));
+ measureNodes.put(alias, rex);
+ }
+
// convert definitions
final ImmutableMap.Builder<String, RexNode> definitionNodes =
ImmutableMap.builder();
@@ -2128,6 +2141,7 @@ public class SqlToRelConverter {
matchRecognize.getStrictStart().booleanValue(),
matchRecognize.getStrictEnd().booleanValue(),
definitionNodes.build(),
+ measureNodes.build(),
rowType);
bb.setRoot(rel, false);
}
@@ -3426,12 +3440,19 @@ public class SqlToRelConverter {
e = rexBuilder.makeFieldAccess(e, i);
} else {
final boolean caseSensitive = true; // name already fully-qualified
- e = rexBuilder.makeFieldAccess(e, name, caseSensitive);
+ if (identifier.isStar() && bb.scope instanceof MatchRecognizeScope) {
+ e = rexBuilder.makeFieldAccess(e, 0);
+ } else {
+ e = rexBuilder.makeFieldAccess(e, name, caseSensitive);
+ }
}
}
if (e instanceof RexInputRef) {
// adjust the type to account for nulls introduced by outer joins
e = adjustInputRef(bb, (RexInputRef) e);
+ if (pv != null) {
+ e = RexPatternFieldRef.of(pv, (RexInputRef) e);
+ }
}
if (e0.left instanceof RexCorrelVariable) {
@@ -4151,7 +4172,7 @@ public class SqlToRelConverter {
int[] start,
List<Pair<RelNode, Integer>> relOffsetList) {
for (RelNode rel : rels) {
- if (leaves.contains(rel)) {
+ if (leaves.contains(rel) || rel instanceof LogicalMatch) {
relOffsetList.add(
Pair.of(rel, start[0]));
start[0] += rel.getRowType().getFieldCount();
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 4fb7c9a..45a9b70 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -225,4 +225,5 @@ PatternCountFunctionArg=Invalid number of parameters to COUNT method
PatternRunningFunctionInDefine=Cannot use RUNNING/FINAL in DEFINE ''{0}''
PatternFunctionVariableCheck=Multiple pattern variables in ''{0}''
FunctionMatchRecognizeOnly=Function ''{0}'' can only be used in MATCH_RECOGNIZE
+PatternFunctionNullCheck=Null parameters in ''{0}''
# End CalciteResource.properties
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 34b9303..15c8063 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
@@ -629,8 +629,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -648,8 +650,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -667,8 +671,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -686,8 +692,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -705,8 +713,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -724,8 +734,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -744,8 +756,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -763,8 +777,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -782,8 +798,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -800,13 +818,13 @@ public class RelToSqlConverterTest {
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"
+ + "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))";
+ + "\"A\" AS PREV(\"A\".\"net_weight\", 0) < PREV(\"A\".\"net_weight\", 1), "
+ + "\"B\" AS PREV(\"B\".\"net_weight\", 0) > PREV(\"B\".\"net_weight\", 1), "
+ + "\"C\" AS PREV(\"C\".\"net_weight\", 0) < PREV(\"C\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -824,8 +842,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
sql(sql).ok(expected);
}
@@ -843,8 +863,10 @@ public class RelToSqlConverterTest {
+ "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"
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))\n"
+ "ORDER BY \"net_weight\"";
sql(sql).ok(expected);
}
@@ -880,8 +902,10 @@ public class RelToSqlConverterTest {
+ "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"
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))\n"
+ "ORDER BY \"net_weight\"";
sql(sql).ok(expected);
}
@@ -900,8 +924,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0)"
+ + " < PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0)"
+ + " > NEXT(PREV(\"UP\".\"net_weight\", 0), 1))";
sql(sql).ok(expected);
}
@@ -919,8 +945,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) "
+ + "< FIRST(\"DOWN\".\"net_weight\", 0), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) "
+ + "> LAST(\"UP\".\"net_weight\", 0))";
sql(sql).ok(expected);
}
@@ -938,9 +966,10 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "LAST(\"UP\".\"net_weight\", 0) + LAST(\"UP\".\"gross_weight\", 0))";
sql(sql).ok(expected);
}
@@ -959,9 +988,225 @@ public class RelToSqlConverterTest {
+ "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))";
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "LAST(\"UP\".\"net_weight\", 0) + LAST(\"UP\".\"gross_weight\", 0))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures1() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures STRT.\"net_weight\" as start_nw,"
+ + " LAST(DOWN.\"net_weight\") as bottom_nw,"
+ + " LAST(up.\"net_weight\") as end_nw"
+ + " 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"
+ + "MEASURES "
+ + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", "
+ + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", "
+ + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures2() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures STRT.\"net_weight\" as start_nw,"
+ + " FINAL LAST(DOWN.\"net_weight\") as bottom_nw,"
+ + " LAST(up.\"net_weight\") as end_nw"
+ + " 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"
+ + "MEASURES "
+ + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", "
+ + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", "
+ + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures3() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures STRT.\"net_weight\" as start_nw,"
+ + " RUNNING LAST(DOWN.\"net_weight\") as bottom_nw,"
+ + " LAST(up.\"net_weight\") as end_nw"
+ + " 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"
+ + "MEASURES "
+ + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", "
+ + "FINAL (RUNNING LAST(\"DOWN\".\"net_weight\", 0)) AS \"BOTTOM_NW\", "
+ + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures4() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures STRT.\"net_weight\" as start_nw,"
+ + " FINAL COUNT(up.\"net_weight\") as up_cnt,"
+ + " FINAL COUNT(\"net_weight\") as down_cnt,"
+ + " RUNNING COUNT(\"net_weight\") as running_cnt"
+ + " 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"
+ + "MEASURES FINAL \"STRT\".\"net_weight\" AS \"START_NW\", "
+ + "FINAL COUNT(\"UP\".\"net_weight\") AS \"UP_CNT\", "
+ + "FINAL COUNT(\"*\".\"net_weight\") AS \"DOWN_CNT\", "
+ + "FINAL (RUNNING COUNT(\"*\".\"net_weight\")) AS \"RUNNING_CNT\"\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures5() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FIRST(STRT.\"net_weight\") as start_nw,"
+ + " LAST(UP.\"net_weight\") as up_cnt,"
+ + " AVG(DOWN.\"net_weight\") as down_cnt"
+ + " 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"
+ + "MEASURES "
+ + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", "
+ + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"UP_CNT\", "
+ + "FINAL (SUM(\"DOWN\".\"net_weight\") / COUNT(\"DOWN\".\"net_weight\")) "
+ + "AS \"DOWN_CNT\"\n"
+ + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures6() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FIRST(STRT.\"net_weight\") as start_nw,"
+ + " LAST(DOWN.\"net_weight\") as up_cnt,"
+ + " FINAL SUM(DOWN.\"net_weight\") as down_cnt"
+ + " 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"
+ + "MEASURES "
+ + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", "
+ + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", "
+ + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n"
+ + "PATTERN "
+ + "(\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))";
+ sql(sql).ok(expected);
+ }
+
+ @Test public void testMatchRecognizeMeasures7() {
+ final String sql = "select *\n"
+ + " from \"product\" match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FIRST(STRT.\"net_weight\") as start_nw,"
+ + " LAST(DOWN.\"net_weight\") as up_cnt,"
+ + " FINAL SUM(DOWN.\"net_weight\") as down_cnt"
+ + " 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 start_nw, up_cnt";
+
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n"
+ + "MEASURES "
+ + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", "
+ + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", "
+ + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n"
+ + "PATTERN "
+ + "(\"STRT\" \"DOWN\" + \"UP\" +)\n"
+ + "DEFINE "
+ + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < "
+ + "PREV(\"DOWN\".\"net_weight\", 1), "
+ + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > "
+ + "PREV(\"UP\".\"net_weight\", 1))\n"
+ + "ORDER BY \"START_NW\", \"UP_CNT\"";
sql(sql).ok(expected);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 4426bcb..564ce5f 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
@@ -307,6 +307,7 @@ public class SqlParserTest {
"MATCH_RECOGNIZE", "c",
"MAX", "92", "2011", "c",
"MAX_CARDINALITY", "2011",
+ "MEASURES", "c",
"MEMBER", "2003", "2011", "c",
"MERGE", "2003", "2011", "c",
"METHOD", "99", "2003", "2011", "c",
@@ -7183,8 +7184,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7202,8 +7203,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7221,8 +7222,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7240,8 +7241,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7259,8 +7260,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7278,8 +7279,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7297,8 +7298,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7316,8 +7317,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7335,8 +7336,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7356,9 +7357,9 @@ public class SqlParserTest {
+ "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)))"
+ + "`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);
}
@@ -7374,7 +7375,7 @@ public class SqlParserTest {
final String expected = "SELECT *\n"
+ "FROM `T` MATCH_RECOGNIZE(\n"
+ "PATTERN ((`a` `b c`))\n"
- + "DEFINE `A` AS (`A`.`PRICE` > (PREV(`A`.`PRICE`, 1))),"
+ + "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);
@@ -7393,8 +7394,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > NEXT(`UP`.`PRICE`, 1))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7411,9 +7412,8 @@ public class SqlParserTest {
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)))"
+ + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < FIRST(`DOWN`.`PRICE`, 0)), "
+ + "`UP` AS (`UP`.`PRICE` > LAST(`UP`.`PRICE`, 0))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7431,8 +7431,8 @@ public class SqlParserTest {
+ "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)))"
+ + "`DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), "
+ + "`UP` AS (`UP`.`PRICE` > LAST((`UP`.`PRICE` + `UP`.`TAX`), 0))"
+ ") AS `MR`";
sql(sql).ok(expected);
}
@@ -7449,9 +7449,156 @@ public class SqlParserTest {
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)))"
+ + "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);
+ }
+
+ @Test public void testMatchRecognizeMeasures1() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures STRT.ts as start_ts,"
+ + " LAST(DOWN.ts) as bottom_ts,"
+ + " LAST(up.ts) as end_ts"
+ + " 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"
+ + "MEASURES `STRT`.`TS` AS `START_TS`, "
+ + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, "
+ + "LAST(`UP`.`TS`, 0) AS `END_TS`\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 testMatchRecognizeMeasures2() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures STRT.ts as start_ts,"
+ + " FINAL LAST(DOWN.ts) as bottom_ts,"
+ + " LAST(up.ts) as end_ts"
+ + " 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"
+ + "MEASURES `STRT`.`TS` AS `START_TS`, "
+ + "FINAL LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, "
+ + "LAST(`UP`.`TS`, 0) AS `END_TS`\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 testMatchRecognizeMeasures3() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures STRT.ts as start_ts,"
+ + " RUNNING LAST(DOWN.ts) as bottom_ts,"
+ + " LAST(up.ts) as end_ts"
+ + " 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"
+ + "MEASURES `STRT`.`TS` AS `START_TS`, "
+ + "RUNNING LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, "
+ + "LAST(`UP`.`TS`, 0) AS `END_TS`\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 testMatchRecognizeMeasures4() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FINAL count(up.ts) as up_ts,"
+ + " FINAL count(ts) as total_ts,"
+ + " RUNNING count(ts) as cnt_ts,"
+ + " price - strt.price as price_dif"
+ + " 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"
+ + "MEASURES FINAL COUNT(`UP`.`TS`) AS `UP_TS`, "
+ + "FINAL COUNT(`TS`) AS `TOTAL_TS`, "
+ + "RUNNING COUNT(`TS`) AS `CNT_TS`, "
+ + "(`PRICE` - `STRT`.`PRICE`) AS `PRICE_DIF`\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 testMatchRecognizeMeasures5() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FIRST(STRT.ts) as strt_ts,"
+ + " LAST(DOWN.ts) as down_ts,"
+ + " AVG(DOWN.ts) as avg_down_ts"
+ + " 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"
+ + "MEASURES FIRST(`STRT`.`TS`, 0) AS `STRT_TS`, "
+ + "LAST(`DOWN`.`TS`, 0) AS `DOWN_TS`, "
+ + "AVG(`DOWN`.`TS`) AS `AVG_DOWN_TS`\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 testMatchRecognizeMeasures6() {
+ final String sql = "select *\n"
+ + " from t match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " FIRST(STRT.ts) as strt_ts,"
+ + " LAST(DOWN.ts) as down_ts,"
+ + " FINAL SUM(DOWN.ts) as sum_down_ts"
+ + " 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"
+ + "MEASURES FIRST(`STRT`.`TS`, 0) AS `STRT_TS`, "
+ + "LAST(`DOWN`.`TS`, 0) AS `DOWN_TS`, "
+ + "FINAL SUM(`DOWN`.`TS`) AS `SUM_DOWN_TS`\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);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/core/src/test/java/org/apache/calcite/sql/parser/SqlUnParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlUnParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlUnParserTest.java
index 4713f5f..a067e49 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlUnParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlUnParserTest.java
@@ -28,7 +28,7 @@ public class SqlUnParserTest extends SqlParserTest {
//~ Methods ----------------------------------------------------------------
- protected Tester getTester() {
+ @Override protected Tester getTester() {
return new UnparsingTesterImpl();
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/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 ea2139a..0666dec 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7960,6 +7960,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "+ pre\n"
+ "- pre\n"
+ ". left\n"
+ + "FINAL pre\n"
+ + "RUNNING pre\n"
+ "\n"
+ "| left\n"
+ "\n"
@@ -9178,9 +9180,9 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
// FINAL and other functions should not be visible outside of
// MATCH_RECOGNIZE
sql("values ^\"FINAL\"(1, 2)^")
- .fails("Function 'FINAL\\(1, 2\\)' can only be used in MATCH_RECOGNIZE");
+ .fails("No match found for function signature FINAL\\(<NUMERIC>, <NUMERIC>\\)");
sql("values ^\"RUNNING\"(1, 2)^")
- .fails("Function 'RUNNING\\(1, 2\\)' can only be used in MATCH_RECOGNIZE");
+ .fails("No match found for function signature RUNNING\\(<NUMERIC>, <NUMERIC>\\)");
sql("values ^\"FIRST\"(1, 2)^")
.fails("Function 'FIRST\\(1, 2\\)' can only be used in MATCH_RECOGNIZE");
sql("values ^\"LAST\"(1, 2)^")
@@ -9649,6 +9651,23 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
"Duplicate name 'EXTRA' in column list");
}
+ @Test public void testMatchRecognizeMeasures1() throws Exception {
+ final String sql = "select *\n"
+ + " from emp match_recognize\n"
+ + " (\n"
+ + " measures "
+ + " STRT.sal as start_sal,"
+ + " ^LAST(null)^ as bottom_sal,"
+ + " LAST(up.ts) as end_sal"
+ + " 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)
+ .fails("Null parameters in 'LAST\\(NULL, 0\\)'");
+ }
+
}
// End SqlValidatorTest.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/4d20d62d/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index f5f4f6e..728f5e1 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -500,6 +500,7 @@ MATCHED,
**MATCH_RECOGNIZE**,
**MAX**,
MAXVALUE,
+**MEASURES**,
**MEMBER**,
**MERGE**,
MESSAGE_LENGTH,