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/08/24 19:05:18 UTC
[04/11] calcite git commit: [CALCITE-1944] Window function applied to
sub-query that returns dynamic star gets wrong plan (Volodymyr Vysotskyi)
[CALCITE-1944] Window function applied to sub-query that returns dynamic star gets wrong plan (Volodymyr Vysotskyi)
Also some minor/cosmetic changes:
* Ensure that SqlSelect.windowDecls and .keywordList are not null.
* Fix javadoc in AbstractTable.
* Rename/move a few resources in SqlToRelConverterTest.
Close apache/calcite#517
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/18300409
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/18300409
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/18300409
Branch: refs/heads/master
Commit: 183004093139d697b479a32b1f82db511bf74127
Parents: 22e5cfc
Author: Volodymyr Vysotskyi <vv...@gmail.com>
Authored: Tue Aug 15 18:54:19 2017 +0000
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 23 11:20:35 2017 -0700
----------------------------------------------------------------------
.../calcite/schema/impl/AbstractTable.java | 8 +-
.../java/org/apache/calcite/sql/SqlSelect.java | 16 +-
.../java/org/apache/calcite/sql/SqlWindow.java | 8 +
.../calcite/sql/validate/SqlValidatorImpl.java | 29 +-
.../calcite/test/SqlToRelConverterTest.java | 42 ++-
.../apache/calcite/test/SqlValidatorTest.java | 2 +-
.../calcite/test/SqlToRelConverterTest.xml | 366 ++++++++++---------
7 files changed, 260 insertions(+), 211 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/core/src/main/java/org/apache/calcite/schema/impl/AbstractTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/schema/impl/AbstractTable.java b/core/src/main/java/org/apache/calcite/schema/impl/AbstractTable.java
index 14cce99..2f2b6bf 100644
--- a/core/src/main/java/org/apache/calcite/schema/impl/AbstractTable.java
+++ b/core/src/main/java/org/apache/calcite/schema/impl/AbstractTable.java
@@ -27,10 +27,10 @@ import org.apache.calcite.sql.SqlNode;
/**
* Abstract base class for implementing {@link Table}.
*
- * Subclasses should override {@link #isRolledUp}
- * and {@link Table#rolledUpColumnValidInsideAgg(String, SqlCall, SqlNode, CalciteConnectionConfig)}
- * if their table can potentially contain rolled up values.
- * This information is used by the validator to check for illegal uses of these columns.
+ * <p>Sub-classes should override {@link #isRolledUp} and
+ * {@link Table#rolledUpColumnValidInsideAgg(String, SqlCall, SqlNode, CalciteConnectionConfig)}
+ * if their table can potentially contain rolled up values. This information is
+ * used by the validator to check for illegal uses of these columns.
*/
public abstract class AbstractTable implements Table {
protected AbstractTable() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
index 4854696..61b0157 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
@@ -21,7 +21,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;
/**
* A <code>SqlSelect</code> is a node of a parse tree which represents a select
@@ -47,6 +50,7 @@ public class SqlSelect extends SqlCall {
SqlNode offset;
SqlNode fetch;
SqlMatchRecognize matchRecognize;
+
//~ Constructors -----------------------------------------------------------
public SqlSelect(SqlParserPos pos,
@@ -61,13 +65,15 @@ public class SqlSelect extends SqlCall {
SqlNode offset,
SqlNode fetch) {
super(pos);
- this.keywordList = keywordList != null ? keywordList : new SqlNodeList(pos);
+ this.keywordList = Preconditions.checkNotNull(keywordList != null
+ ? keywordList : new SqlNodeList(pos));
this.selectList = selectList;
this.from = from;
this.where = where;
this.groupBy = groupBy;
this.having = having;
- this.windowDecls = windowDecls != null ? windowDecls : new SqlNodeList(pos);
+ this.windowDecls = Preconditions.checkNotNull(windowDecls != null
+ ? windowDecls : new SqlNodeList(pos));
this.orderBy = orderBy;
this.offset = offset;
this.fetch = fetch;
@@ -91,7 +97,7 @@ public class SqlSelect extends SqlCall {
@Override public void setOperand(int i, SqlNode operand) {
switch (i) {
case 0:
- keywordList = (SqlNodeList) operand;
+ keywordList = Preconditions.checkNotNull((SqlNodeList) operand);
break;
case 1:
selectList = (SqlNodeList) operand;
@@ -109,7 +115,7 @@ public class SqlSelect extends SqlCall {
having = operand;
break;
case 6:
- windowDecls = (SqlNodeList) operand;
+ windowDecls = Preconditions.checkNotNull((SqlNodeList) operand);
break;
case 7:
orderBy = (SqlNodeList) operand;
@@ -180,7 +186,7 @@ public class SqlSelect extends SqlCall {
this.where = whereClause;
}
- public final SqlNodeList getWindowList() {
+ @Nonnull public final SqlNodeList getWindowList() {
return windowDecls;
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
index dc6b34c..4887697 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
@@ -260,10 +260,18 @@ public class SqlWindow extends SqlCall {
return orderList;
}
+ public void setOrderList(SqlNodeList orderList) {
+ this.orderList = orderList;
+ }
+
public SqlNodeList getPartitionList() {
return partitionList;
}
+ public void setPartitionList(SqlNodeList partitionList) {
+ this.partitionList = partitionList;
+ }
+
public SqlIdentifier getRefName() {
return refName;
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/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 3d76bb4..0440129 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
@@ -3596,7 +3596,9 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
protected void validateWindowClause(SqlSelect select) {
final SqlNodeList windowList = select.getWindowList();
- if ((windowList == null) || (windowList.size() == 0)) {
+ @SuppressWarnings("unchecked") final List<SqlWindow> windows =
+ (List) windowList.getList();
+ if (windows.isEmpty()) {
return;
}
@@ -3605,9 +3607,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
// 1. ensure window names are simple
// 2. ensure they are unique within this scope
- for (SqlNode node : windowList) {
- final SqlWindow child = (SqlWindow) node;
- SqlIdentifier declName = child.getDeclName();
+ for (SqlWindow window : windows) {
+ SqlIdentifier declName = window.getDeclName();
if (!declName.isSimple()) {
throw newValidationError(declName, RESOURCE.windowNameMustBeSimple());
}
@@ -3621,16 +3622,28 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
// 7.10 rule 2
// Check for pairs of windows which are equivalent.
- for (int i = 0; i < windowList.size(); i++) {
- SqlNode window1 = windowList.get(i);
- for (int j = i + 1; j < windowList.size(); j++) {
- SqlNode window2 = windowList.get(j);
+ for (int i = 0; i < windows.size(); i++) {
+ SqlNode window1 = windows.get(i);
+ for (int j = i + 1; j < windows.size(); j++) {
+ SqlNode window2 = windows.get(j);
if (window1.equalsDeep(window2, Litmus.IGNORE)) {
throw newValidationError(window2, RESOURCE.dupWindowSpec());
}
}
}
+ for (SqlWindow window : windows) {
+ final SqlNodeList expandedOrderList =
+ (SqlNodeList) expand(window.getOrderList(), windowScope);
+ window.setOrderList(expandedOrderList);
+ expandedOrderList.validate(this, windowScope);
+
+ final SqlNodeList expandedPartitionList =
+ (SqlNodeList) expand(window.getPartitionList(), windowScope);
+ window.setPartitionList(expandedPartitionList);
+ expandedPartitionList.validate(this, windowScope);
+ }
+
// Hand off to validate window spec components
windowList.validate(this, windowScope);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 9275cfa..581f571 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2410,12 +2410,11 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).with(getTesterWithDynamicTable()).ok();
}
- /**
- * Test case for Dynamic Table / Dynamic Star support
- * <a href="https://issues.apache.org/jira/browse/CALCITE-1150">[CALCITE-1150]</a>
- */
- @Test
- public void testSelStarOrderBy() throws Exception {
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1150">[CALCITE-1150]
+ * Create the a new DynamicRecordType, avoiding star expansion when working
+ * with this type</a>. */
+ @Test public void testSelectDynamicStarOrderBy() throws Exception {
final String sql = "SELECT * from SALES.NATION order by n_nationkey";
sql(sql).with(getTesterWithDynamicTable()).ok();
}
@@ -2424,21 +2423,30 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
* <a href="https://issues.apache.org/jira/browse/CALCITE-1321">[CALCITE-1321]
* Configurable IN list size when converting IN clause to join</a>. */
@Test public void testInToSemiJoin() {
- final String sql = "SELECT empno"
- + " FROM emp AS e"
- + " WHERE cast(e.empno as bigint) in (130, 131, 132, 133, 134)";
+ final String sql = "SELECT empno\n"
+ + "FROM emp AS e\n"
+ + "WHERE cast(e.empno as bigint) in (130, 131, 132, 133, 134)";
// No conversion to join since less than IN-list size threshold 10
- SqlToRelConverter.Config noConvertConfig = SqlToRelConverter.configBuilder().
-
-
- withInSubQueryThreshold(10).build();
+ SqlToRelConverter.Config noConvertConfig =
+ SqlToRelConverter.configBuilder().withInSubQueryThreshold(10).build();
sql(sql).withConfig(noConvertConfig).convertsTo("${planNotConverted}");
// Conversion to join since greater than IN-list size threshold 2
- SqlToRelConverter.Config convertConfig = SqlToRelConverter.configBuilder().
- withInSubQueryThreshold(2).build();
+ SqlToRelConverter.Config convertConfig =
+ SqlToRelConverter.configBuilder().withInSubQueryThreshold(2).build();
sql(sql).withConfig(convertConfig).convertsTo("${planConverted}");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1944">[CALCITE-1944]
+ * Window function applied to sub-query with dynamic star gets wrong
+ * plan</a>. */
+ @Test public void testWindowOnDynamicStar() throws Exception {
+ final String sql = "SELECT SUM(n_nationkey) OVER w\n"
+ + "FROM (SELECT * FROM SALES.NATION) subQry\n"
+ + "WINDOW w AS (PARTITION BY REGION ORDER BY n_nationkey)";
+ sql(sql).with(getTesterWithDynamicTable()).ok();
+ }
+
private Tester getExtendedTester() {
return tester.withCatalogReaderFactory(
new Function<RelDataTypeFactory, Prepare.CatalogReader>() {
@@ -2612,7 +2620,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
- @Test public void testMRPrevLast() {
+ @Test public void testMatchRecognizePrevLast() {
final String sql = "SELECT *\n"
+ "FROM emp\n"
+ "MATCH_RECOGNIZE (\n"
@@ -2629,7 +2637,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
- @Test public void testMRPrevDown() {
+ @Test public void testMatchRecognizePrevDown() {
final String sql = "SELECT *\n"
+ "FROM emp\n"
+ "MATCH_RECOGNIZE (\n"
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/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 8d0ed48..ab54857 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6763,7 +6763,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ " from emp group by deptno\n"
+ " window w1 as (partition by ^empno^ ROWS 2 PRECEDING),\n"
+ " w2 as (order by deptno ROWS 2 PRECEDING)")
- .fails("Expression 'EMP.EMPNO' is not being grouped");
+ .fails("Expression 'EMPNO' is not being grouped");
sql("select avg(count(empno)) over w\n"
+ "from emp group by deptno\n"
+ "window w as (partition by deptno order by ^empno^)")
http://git-wip-us.apache.org/repos/asf/calcite/blob/18300409/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 94824a9..c5ba797 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2374,6 +2374,178 @@ LogicalProject(DEPTNO=[$1], EXPR$1=[CASE($3, 1, 0)], EXPR$2=[$4], EXPR$3=[CASE($
]]>
</Resource>
</TestCase>
+ <TestCase name="testMatchRecognize1">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ partition by job, sal
+ order by job asc, sal desc, empno
+ pattern (strt down+ up+)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > prev(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC, 0]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizeMeasures1">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ partition by job, sal
+ order by job asc, sal desc
+ measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > prev(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4], BOTTOM_NW=[$5], END_NW=[$6])
+ LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[JOB, SAL, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizeMeasures2">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ partition by job
+ order by sal
+ measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > prev(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_NW=[$4], END_NW=[$5])
+ LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizeMeasures3">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ partition by job
+ order by sal
+ measures MATCH_NUMBER() as match_num, CLASSIFIER() as var_match, STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw ALL ROWS PER MATCH pattern (strt down+ up+)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > prev(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(JOB=[$0], SAL=[$1], EMPNO=[$2], ENAME=[$3], MGR=[$4], HIREDATE=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], MATCH_NUM=[$9], VAR_MATCH=[$10], START_NW=[$11], BOTTOM_NW=[$12], END_NW=[$13])
+ LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, SAL, EMPNO, ENAME, MGR, HIREDATE, COMM, DEPTNO, SLACKER, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[true], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizePatternSkip1">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ after match skip to next row
+ pattern (strt down+ up+)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > NEXT(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizePrevDown">
+ <Resource name="sql">
+ <![CDATA[SELECT *
+FROM emp
+MATCH_RECOGNIZE (
+ MEASURES
+ STRT.mgr AS start_mgr,
+ LAST(DOWN.mgr) AS up_days,
+ LAST(UP.mgr) AS total_days
+ PATTERN (STRT DOWN+ UP+)
+ DEFINE
+ DOWN AS DOWN.mgr < PREV(DOWN.mgr),
+ UP AS UP.mgr > PREV(DOWN.mgr)
+) AS T]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(START_MGR=[$0], UP_DAYS=[$1], TOTAL_DAYS=[$2])
+ LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, UP_DAYS, TOTAL_DAYS]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(DOWN.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizePrevLast">
+ <Resource name="sql">
+ <![CDATA[SELECT *
+FROM emp
+MATCH_RECOGNIZE (
+ MEASURES
+ STRT.mgr AS start_mgr,
+ LAST(DOWN.mgr) AS bottom_mgr,
+ LAST(UP.mgr) AS end_mgr
+ ONE ROW PER MATCH
+ PATTERN (STRT DOWN+ UP+)
+ DEFINE
+ DOWN AS DOWN.mgr < PREV(DOWN.mgr),
+ UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)
+) AS T]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(START_MGR=[$0], BOTTOM_MGR=[$1], END_MGR=[$2])
+ LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, BOTTOM_MGR, END_MGR]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(LAST(DOWN.$3, 1), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMatchRecognizeSubset1">
+ <Resource name="sql">
+ <![CDATA[select *
+ from emp match_recognize
+ (
+ after match skip to down
+ pattern (strt down+ up+)
+ subset stdn = (strt, down)
+ define
+ down as down.mgr < PREV(down.mgr),
+ up as up.mgr > NEXT(up.mgr)
+ ) mr]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[SKIP TO LAST('DOWN')], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[[DOWN, STRT]]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testNotNotIn">
<Resource name="sql">
<![CDATA[select * from EMP where not (ename not in ('Fred') )]]>
@@ -4672,7 +4844,7 @@ LogicalProject(R_REGIONKEY=[$0], R_NAME=[$1], R_COMMENT=[$2])
]]>
</Resource>
</TestCase>
- <TestCase name="testSelStarOrderBy">
+ <TestCase name="testSelectDynamicStarOrderBy">
<Resource name="sql">
<![CDATA[SELECT * from SALES.NATION order by n_nationkey]]>
</Resource>
@@ -4687,7 +4859,9 @@ LogicalProject(**=[$0])
</TestCase>
<TestCase name="testInToSemiJoin">
<Resource name="sql">
- <![CDATA[SELECT empno FROM emp AS e WHERE cast(e.empno as bigint) in (130, 131, 132, 133, 134)]]>
+ <![CDATA[SELECT empno
+FROM emp AS e
+WHERE cast(e.empno as bigint) in (130, 131, 132, 133, 134)]]>
</Resource>
<Resource name="planNotConverted">
<![CDATA[
@@ -4707,6 +4881,20 @@ LogicalProject(EMPNO=[$0])
]]>
</Resource>
</TestCase>
+ <TestCase name="testWindowOnDynamicStar">
+ <Resource name="sql">
+ <![CDATA[SELECT SUM(n_nationkey) OVER w
+FROM (SELECT * FROM SALES.NATION) subQry
+WINDOW w AS (PARTITION BY REGION ORDER BY n_nationkey)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[CASE(>(COUNT(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY') RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0), $SUM0(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY') RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), null)])
+ LogicalProject(**=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, NATION]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testWithExists">
<Resource name="sql">
<![CDATA[with t (a, b) as (select * from (values (1, 2)))
@@ -4729,178 +4917,4 @@ LogicalProject(A=[$0], B=[$1])
]]>
</Resource>
</TestCase>
- <TestCase name="testMatchRecognize1">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- partition by job, sal
- order by job asc, sal desc, empno
- pattern (strt down+ up+)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > prev(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC, 0]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMatchRecognizeMeasures1">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- partition by job, sal
- order by job asc, sal desc
- measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > prev(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4], BOTTOM_NW=[$5], END_NW=[$6])
- LogicalMatch(partition=[[$2, $5]], order=[[2, 5 DESC]], outputFields=[[JOB, SAL, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMatchRecognizeMeasures2">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- partition by job
- order by sal
- measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > prev(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_NW=[$4], END_NW=[$5])
- LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMatchRecognizeMeasures3">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- partition by job
- order by sal
- measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw
- ALL ROWS PER MATCH
- pattern (strt down+ up+)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > prev(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(JOB=[$0], SAL=[$1], EMPNO=[$2], ENAME=[$3], MGR=[$4], HIREDATE=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], MATCH_NUM=[$9], VAR_MATCH=[$10], START_NW=[$11], BOTTOM_NW=[$12], END_NW=[$13])
- LogicalMatch(partition=[[$2]], order=[[5]], outputFields=[[JOB, SAL, EMPNO, ENAME, MGR, HIREDATE, COMM, DEPTNO, SLACKER, MATCH_NUM, VAR_MATCH, START_NW, BOTTOM_NW, END_NW]], allRows=[true], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMatchRecognizePatternSkip1">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- after match skip to next row
- pattern (strt down+ up+)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > NEXT(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMatchRecognizeSubset1">
- <Resource name="sql">
- <![CDATA[select *
- from emp match_recognize
- (
- after match skip to down
- pattern (strt down+ up+)
- subset stdn = (strt, down)
- define
- down as down.mgr < PREV(down.mgr),
- up as up.mgr > NEXT(up.mgr)
- ) mr]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalMatch(partition=[[]], order=[[]], outputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]], allRows=[false], after=[SKIP TO LAST('DOWN')], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[[DOWN, STRT]]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, NEXT(UP.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMRPrevLast">
- <Resource name="sql">
- <![CDATA[SELECT *
-FROM emp
-MATCH_RECOGNIZE (
- MEASURES
- STRT.mgr AS start_mgr,
- LAST(DOWN.mgr) AS bottom_mgr,
- LAST(UP.mgr) AS end_mgr
- ONE ROW PER MATCH\n
- PATTERN (STRT DOWN+ UP+)
- DEFINE
- DOWN AS DOWN.mgr < PREV(DOWN.mgr),
- UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)
-) AS T;]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(START_MGR=[$0], BOTTOM_MGR=[$1], END_MGR=[$2])
- LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, BOTTOM_MGR, END_MGR]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(LAST(DOWN.$3, 1), 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testMRPrevDown">
- <Resource name="sql">
- <![CDATA[SELECT *
-FROM tmp
-MATCH_RECOGNIZE (
- MEASURES
- STRT.mgr AS start_mgr,
- LAST(DOWN.mgr) AS up_days,
- LAST(UP.mgr) AS total_days
- PATTERN (STRT DOWN+ UP+)
- DEFINE
- DOWN AS DOWN.mgr < PREV(DOWN.mgr),
- UP AS UP.mgr > PREV(DOWN.mgr)
-) AS T]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(START_MGR=[$0], UP_DAYS=[$1], TOTAL_DAYS=[$2])
- LogicalMatch(partition=[[]], order=[[]], outputFields=[[START_MGR, UP_DAYS, TOTAL_DAYS]], allRows=[false], after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', PATTERN_QUANTIFIER('DOWN', 1, -1, false)), PATTERN_QUANTIFIER('UP', 1, -1, false))], isStrictStarts=[false], isStrictEnds=[false], subsets=[[]], patternDefinitions=[[<(DOWN.$3, PREV(DOWN.$3, 1)), >(UP.$3, PREV(DOWN.$3, 1))]], inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SLACKER]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
</Root>