[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 <[email protected]> Authored: Tue Aug 15 18:54:19 2017 +0000 Committer: Julian Hyde <[email protected]> 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>
