[CALCITE-1761] Auxiliary group functions (TUMBLE_START, HOP_END) do not resolve time field correctly
Also, add tests missed in [CALCITE-1615]. Test case by Haohui Mai. Close apache/calcite#435 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a11d1405 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a11d1405 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a11d1405 Branch: refs/heads/master Commit: a11d14054e9c1d2ce22f60e11536f1885faaae7c Parents: 9c10c3d Author: Julian Hyde <[email protected]> Authored: Tue Apr 25 14:07:44 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Mon May 1 20:56:44 2017 -0700 ---------------------------------------------------------------------- .../org/apache/calcite/sql/SqlBasicCall.java | 6 +- .../calcite/sql/fun/SqlGroupFunction.java | 20 +++- .../calcite/sql/fun/SqlStdOperatorTable.java | 82 ++++++++++++-- .../apache/calcite/sql/validate/AggChecker.java | 14 +-- .../calcite/sql2rel/AuxiliaryConverter.java | 73 ++++++++++++ .../calcite/sql2rel/SqlToRelConverter.java | 37 +++++++ .../apache/calcite/test/MockCatalogReader.java | 3 +- .../calcite/test/SqlToRelConverterTest.java | 52 ++++++++- .../calcite/test/SqlToRelConverterTest.xml | 110 ++++++++++++++----- 9 files changed, 336 insertions(+), 61 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/core/src/main/java/org/apache/calcite/sql/SqlBasicCall.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlBasicCall.java b/core/src/main/java/org/apache/calcite/sql/SqlBasicCall.java index 5e303e5..bd311f2 100755 --- a/core/src/main/java/org/apache/calcite/sql/SqlBasicCall.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlBasicCall.java @@ -19,6 +19,8 @@ package org.apache.calcite.sql; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.util.UnmodifiableArrayList; +import com.google.common.base.Preconditions; + import java.util.List; /** @@ -44,7 +46,7 @@ public class SqlBasicCall extends SqlCall { boolean expanded, SqlLiteral functionQualifier) { super(pos); - this.operator = operator; + this.operator = Preconditions.checkNotNull(operator); this.operands = operands; this.expanded = expanded; this.functionQuantifier = functionQualifier; @@ -63,7 +65,7 @@ public class SqlBasicCall extends SqlCall { } public void setOperator(SqlOperator operator) { - this.operator = operator; + this.operator = Preconditions.checkNotNull(operator); } public SqlOperator getOperator() { http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupFunction.java index 24f2b9c..d44267a 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupFunction.java @@ -24,13 +24,17 @@ import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandTypeChecker; import org.apache.calcite.sql.validate.SqlMonotonicity; +import com.google.common.collect.ImmutableList; + +import java.util.List; + /** * SQL function that computes keys by which rows can be partitioned and * aggregated. * - * <p>Group functions always occur in the GROUP BY clause. They often have - * auxiliary functions that access information about the group. For example, - * {@code HOP} is a group function, and its auxiliary functions are + * <p>Grouped window functions always occur in the GROUP BY clause. They often + * have auxiliary functions that access information about the group. For + * example, {@code HOP} is a group function, and its auxiliary functions are * {@code HOP_START} and {@code HOP_END}. Here they are used in a streaming * query: * @@ -43,7 +47,8 @@ import org.apache.calcite.sql.validate.SqlMonotonicity; * </pre></blockquote> */ class SqlGroupFunction extends SqlFunction { - private final SqlGroupFunction groupFunction; + /** The grouped function, if this an auxiliary function; null otherwise. */ + final SqlGroupFunction groupFunction; /** Creates a SqlGroupFunction. * @@ -62,11 +67,16 @@ class SqlGroupFunction extends SqlFunction { } } - /** Creates an auxiliary function from this group function. */ + /** Creates an auxiliary function from this grouped window function. */ SqlGroupFunction auxiliary(SqlKind kind) { return new SqlGroupFunction(kind, this, getOperandTypeChecker()); } + /** Returns a list of this grouped window function's auxiliary functions. */ + List<SqlGroupFunction> getAuxiliaryFunctions() { + return ImmutableList.of(); + } + @Override public boolean isGroup() { // Auxiliary functions are not group functions return groupFunction == null; http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/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 bd63b91..03be569 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 @@ -21,6 +21,7 @@ import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlAsOperator; +import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFilterOperator; @@ -56,7 +57,13 @@ import org.apache.calcite.sql.type.SqlOperandCountRanges; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable; import org.apache.calcite.sql.validate.SqlModality; +import org.apache.calcite.sql2rel.AuxiliaryConverter; import org.apache.calcite.util.Litmus; +import org.apache.calcite.util.Pair; + +import com.google.common.collect.ImmutableList; + +import java.util.List; /** * Implementation of {@link org.apache.calcite.sql.SqlOperatorTable} containing @@ -1916,48 +1923,60 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { public static final SqlGroupFunction TUMBLE = new SqlGroupFunction(SqlKind.TUMBLE, null, OperandTypes.or(OperandTypes.DATETIME_INTERVAL, - OperandTypes.DATETIME_INTERVAL_TIME)); + OperandTypes.DATETIME_INTERVAL_TIME)) { + @Override List<SqlGroupFunction> getAuxiliaryFunctions() { + return ImmutableList.of(TUMBLE_START, TUMBLE_END); + } + }; /** The {@code TUMBLE_START} auxiliary function of * the {@code TUMBLE} group function. */ - public static final SqlFunction TUMBLE_START = + public static final SqlGroupFunction TUMBLE_START = TUMBLE.auxiliary(SqlKind.TUMBLE_START); /** The {@code TUMBLE_END} auxiliary function of * the {@code TUMBLE} group function. */ - public static final SqlFunction TUMBLE_END = + public static final SqlGroupFunction TUMBLE_END = TUMBLE.auxiliary(SqlKind.TUMBLE_END); /** The {@code HOP} group function. */ public static final SqlGroupFunction HOP = new SqlGroupFunction(SqlKind.HOP, null, OperandTypes.or(OperandTypes.DATETIME_INTERVAL_INTERVAL, - OperandTypes.DATETIME_INTERVAL_INTERVAL_TIME)); + OperandTypes.DATETIME_INTERVAL_INTERVAL_TIME)) { + @Override List<SqlGroupFunction> getAuxiliaryFunctions() { + return ImmutableList.of(HOP_START, HOP_END); + } + }; /** The {@code HOP_START} auxiliary function of * the {@code HOP} group function. */ - public static final SqlFunction HOP_START = + public static final SqlGroupFunction HOP_START = HOP.auxiliary(SqlKind.HOP_START); /** The {@code HOP_END} auxiliary function of * the {@code HOP} group function. */ - public static final SqlFunction HOP_END = + public static final SqlGroupFunction HOP_END = HOP.auxiliary(SqlKind.HOP_END); /** The {@code SESSION} group function. */ public static final SqlGroupFunction SESSION = new SqlGroupFunction(SqlKind.SESSION, null, OperandTypes.or(OperandTypes.DATETIME_INTERVAL, - OperandTypes.DATETIME_INTERVAL_TIME)); + OperandTypes.DATETIME_INTERVAL_TIME)) { + @Override List<SqlGroupFunction> getAuxiliaryFunctions() { + return ImmutableList.of(SESSION_START, SESSION_END); + } + }; /** The {@code SESSION_START} auxiliary function of * the {@code SESSION} group function. */ - public static final SqlFunction SESSION_START = + public static final SqlGroupFunction SESSION_START = SESSION.auxiliary(SqlKind.SESSION_START); /** The {@code SESSION_END} auxiliary function of * the {@code SESSION} group function. */ - public static final SqlFunction SESSION_END = + public static final SqlGroupFunction SESSION_END = SESSION.auxiliary(SqlKind.SESSION_END); /** {@code |} operator to create alternate patterns @@ -2088,6 +2107,51 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { return null; } } + + /** Converts a call to a grouped auxiliary function + * to a call to the grouped window function. For other calls returns null. + * + * <p>For example, converts {@code TUMBLE_START(rowtime, INTERVAL '1' HOUR))} + * to {@code TUMBLE(rowtime, INTERVAL '1' HOUR))}. */ + public static SqlCall convertAuxiliaryToGroupCall(SqlCall call) { + final SqlOperator op = call.getOperator(); + if (op instanceof SqlGroupFunction + && op.isGroupAuxiliary()) { + return copy(call, ((SqlGroupFunction) op).groupFunction); + } + return null; + } + + /** Converts a call to a grouped window function to a call to its auxiliary + * window function(s). For other calls returns null. + * + * <p>For example, converts {@code TUMBLE_START(rowtime, INTERVAL '1' HOUR))} + * to {@code TUMBLE(rowtime, INTERVAL '1' HOUR))}. */ + public static List<Pair<SqlNode, AuxiliaryConverter>> + convertGroupToAuxiliaryCalls(SqlCall call) { + final SqlOperator op = call.getOperator(); + if (op instanceof SqlGroupFunction + && op.isGroup()) { + ImmutableList.Builder<Pair<SqlNode, AuxiliaryConverter>> builder = + ImmutableList.builder(); + for (final SqlGroupFunction f + : ((SqlGroupFunction) op).getAuxiliaryFunctions()) { + builder.add( + Pair.<SqlNode, AuxiliaryConverter>of(copy(call, f), + new AuxiliaryConverter.Impl(f))); + } + return builder.build(); + } + return ImmutableList.of(); + } + + /** Creates a copy of a call with a new operator. */ + private static SqlCall copy(SqlCall call, SqlOperator operator) { + final List<SqlNode> list = call.getOperandList(); + return new SqlBasicCall(operator, list.toArray(new SqlNode[list.size()]), + call.getParserPosition()); + } + } // End SqlStdOperatorTable.java http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java index bb5622f..cf45da2 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java @@ -16,13 +16,11 @@ */ package org.apache.calcite.sql.validate; -import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlNodeList; -import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlSelect; import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.sql.SqlWindow; @@ -180,7 +178,8 @@ class AggChecker extends SqlBasicVisitor<Void> { return null; } - final SqlCall groupCall = convertAuxiliaryToGroupCall(call); + final SqlCall groupCall = + SqlStdOperatorTable.convertAuxiliaryToGroupCall(call); if (groupCall != null) { if (isGroupExpr(groupCall)) { // This call is an auxiliary function that matches a group call in the @@ -217,15 +216,6 @@ class AggChecker extends SqlBasicVisitor<Void> { return null; } - private SqlCall convertAuxiliaryToGroupCall(SqlCall call) { - SqlOperator op = SqlStdOperatorTable.auxiliaryToGroup(call.getKind()); - if (op == null) { - return null; - } - final List<SqlNode> list = call.getOperandList(); - return new SqlBasicCall(op, list.toArray(new SqlNode[list.size()]), - call.getParserPosition()); - } } // End AggChecker.java http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/core/src/main/java/org/apache/calcite/sql2rel/AuxiliaryConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/AuxiliaryConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/AuxiliaryConverter.java new file mode 100644 index 0000000..c9a2fb5 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql2rel/AuxiliaryConverter.java @@ -0,0 +1,73 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.sql2rel; + +import org.apache.calcite.rex.RexBuilder; +import org.apache.calcite.rex.RexCall; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.sql.SqlFunction; +import org.apache.calcite.sql.fun.SqlStdOperatorTable; + +/** Converts an expression for a group window function (e.g. TUMBLE) + * into an expression for an auxiliary group function (e.g. TUMBLE_START). + * + * @see SqlStdOperatorTable#TUMBLE + */ +public interface AuxiliaryConverter { + /** Converts an expression. + * + * @param rexBuilder Rex builder + * @param groupCall Call to the group function, e.g. "TUMBLE($2, 36000)" + * @param e Expression holding result of the group function, e.g. "$0" + * + * @return Expression for auxiliary function, e.g. "$0 + 36000" converts + * the result of TUMBLE to the result of TUMBLE_END + */ + RexNode convert(RexBuilder rexBuilder, RexNode groupCall, RexNode e); + + /** Simple implementation of {@link AuxiliaryConverter}. */ + class Impl implements AuxiliaryConverter { + private final SqlFunction f; + + public Impl(SqlFunction f) { + this.f = f; + } + + public RexNode convert(RexBuilder rexBuilder, RexNode groupCall, + RexNode e) { + switch (f.getKind()) { + case TUMBLE_START: + case HOP_START: + case SESSION_START: + case SESSION_END: // TODO: ? + return e; + case TUMBLE_END: + return rexBuilder.makeCall( + SqlStdOperatorTable.PLUS, e, + ((RexCall) groupCall).operands.get(1)); + case HOP_END: + return rexBuilder.makeCall( + SqlStdOperatorTable.PLUS, e, + ((RexCall) groupCall).operands.get(2)); + default: + throw new AssertionError("unknown: " + f); + } + } + } +} + +// End AuxiliaryConverter.java http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/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 abf3fca..cc949e2 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -4577,6 +4577,12 @@ public class SqlToRelConverter { new SqlNodeList(SqlParserPos.ZERO); /** + * The auxiliary group-by expressions. + */ + private final Map<SqlNode, Ord<AuxiliaryConverter>> auxiliaryGroupExprs = + new HashMap<>(); + + /** * Input expressions for the group columns and aggregates, in * {@link RexNode} format. The first elements of the list correspond to the * elements in {@link #groupExprs}; the remaining elements are for @@ -4643,9 +4649,28 @@ public class SqlToRelConverter { String name = nameMap.get(expr.toString()); RexNode convExpr = bb.convertExpression(expr); addExpr(convExpr, name); + + if (expr instanceof SqlCall) { + SqlCall call = (SqlCall) expr; + for (Pair<SqlNode, AuxiliaryConverter> p + : SqlStdOperatorTable.convertGroupToAuxiliaryCalls(call)) { + addAuxiliaryGroupExpr(p.left, index, p.right); + } + } + return index; } + void addAuxiliaryGroupExpr(SqlNode node, int index, + AuxiliaryConverter converter) { + for (SqlNode node2 : auxiliaryGroupExprs.keySet()) { + if (node2.equalsDeep(node, Litmus.IGNORE)) { + return; + } + } + auxiliaryGroupExprs.put(node, Ord.of(index, converter)); + } + /** * Adds an expression, deducing an appropriate name if possible. * @@ -4869,6 +4894,18 @@ public class SqlToRelConverter { return node; } } + + for (Map.Entry<SqlNode, Ord<AuxiliaryConverter>> e + : auxiliaryGroupExprs.entrySet()) { + if (call.equalsDeep(e.getKey(), Litmus.IGNORE)) { + AuxiliaryConverter converter = e.getValue().e; + final int groupOrdinal = e.getValue().i; + return converter.convert(rexBuilder, + convertedInputExprs.get(groupOrdinal).left, + rexBuilder.makeInputRef(bb.root, groupOrdinal)); + } + } + return aggMapping.get(call); } http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java index c3e112c..dae8535 100644 --- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java +++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java @@ -343,11 +343,12 @@ public class MockCatalogReader extends CalciteCatalogReader { registerTable(ordersStream); // Register "SHIPMENTS" stream. + // "ROWTIME" is not column 0, just to mix things up. MockTable shipmentsStream = MockTable.create(this, salesSchema, "SHIPMENTS", true, Double.POSITIVE_INFINITY); + shipmentsStream.addColumn("ORDERID", f.intType); shipmentsStream.addColumn("ROWTIME", f.timestampType); shipmentsStream.addMonotonic("ROWTIME"); - shipmentsStream.addColumn("ORDERID", f.intType); registerTable(shipmentsStream); // Register "PRODUCTS" table. http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/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 64ff7a5..01b1897 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -1394,6 +1394,15 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test public void testTumble() { + final String sql = "select STREAM\n" + + " TUMBLE_START(rowtime, INTERVAL '1' MINUTE) AS s,\n" + + " TUMBLE_END(rowtime, INTERVAL '1' MINUTE) AS e\n" + + "from Shipments\n" + + "GROUP BY TUMBLE(rowtime, INTERVAL '1' MINUTE)"; + sql(sql).ok(); + } + @Test public void testNotNotIn() { final String sql = "select * from EMP where not (ename not in ('Fred') )"; sql(sql).ok(); @@ -1505,6 +1514,43 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql2).ok(); } + @Test public void testTumbleTable() { + final String sql = "select stream" + + " tumble_end(rowtime, interval '2' hour) as rowtime, productId\n" + + "from orders\n" + + "group by tumble(rowtime, interval '2' hour), productId"; + sql(sql).ok(); + } + + /** As {@link #testTumbleTable()} but on a table where "rowtime" is at + * position 1 not 0. */ + @Test public void testTumbleTableRowtimeNotFirstColumn() { + final String sql = "select stream\n" + + " tumble_end(rowtime, interval '2' hour) as rowtime, orderId\n" + + "from shipments\n" + + "group by tumble(rowtime, interval '2' hour), orderId"; + sql(sql).ok(); + } + + @Test public void testHopTable() { + final String sql = "select stream hop_start(rowtime, interval '1' hour," + + " interval '3' hour) as rowtime,\n" + + " count(*) as c\n" + + "from orders\n" + + "group by hop(rowtime, interval '1' hour, interval '3' hour)"; + sql(sql).ok(); + } + + @Test public void testSessionTable() { + final String sql = "select stream session_start(rowtime, interval '1' hour)" + + " as rowtime,\n" + + " session_end(rowtime, interval '1' hour),\n" + + " count(*) as c\n" + + "from orders\n" + + "group by session(rowtime, interval '1' hour)"; + sql(sql).ok(); + } + @Test public void testInterval() { // temporarily disabled per DTbug 1212 if (!Bug.DT785_FIXED) { @@ -1656,7 +1702,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testInsertSubset() { - final String sql = "insert into empnullables \n" + final String sql = "insert into empnullables\n" + "values (50, 'Fred')"; sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok(); } @@ -1678,7 +1724,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testInsertBindSubset() { - final String sql = "insert into empnullables \n" + final String sql = "insert into empnullables\n" + "values (?, ?)"; sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok(); } @@ -1694,7 +1740,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testInsertSubsetView() { - final String sql = "insert into empnullables_20 \n" + final String sql = "insert into empnullables_20\n" + "values (10, 'Fred')"; sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/a11d1405/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 85bdd75..13257e2 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -1239,7 +1239,7 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()], EXPR$1=[SUM($0)]) <TestCase name="testAliasList"> <Resource name="sql"> <![CDATA[select a + b from ( - select deptno, 1 as one, name from dept + select deptno, 1 as uno, name from dept ) as d(a, b, c) where c like 'X%']]> </Resource> @@ -2534,45 +2534,44 @@ group by tumble(rowtime, interval '2' hour), productId]]> <Resource name="plan"> <![CDATA[ LogicalDelta - LogicalProject(ROWTIME=[TUMBLE_END($0, 7200000)], PRODUCTID=[$1]) + LogicalProject(ROWTIME=[+($0, 7200000)], PRODUCTID=[$1]) LogicalAggregate(group=[{0, 1}]) - LogicalProject($f0=[TUMBLE($0, 7200000)], PRODUCTID=[$2]) - LogicalTableScan(table=[[STREAMS, ORDERS]]) + LogicalProject($f0=[TUMBLE($0, 7200000)], PRODUCTID=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) ]]> </Resource> </TestCase> <TestCase name="testHopTable"> <Resource name="sql"> <![CDATA[select stream hop_start(rowtime, interval '1' hour, interval '3' hour) as rowtime, -count(*) as c + count(*) as c from orders group by hop(rowtime, interval '1' hour, interval '3' hour)]]> </Resource> <Resource name="plan"> <![CDATA[ LogicalDelta - LogicalProject(ROWTIME=[HOP_START($0, 3600000, 10800000)], C=[$1]) - LogicalAggregate(group=[{0}], C=[COUNT()]) - LogicalProject($f0=[HOP($0, 3600000, 10800000)]) - LogicalTableScan(table=[[STREAM_JOINS, ORDERS]]) + LogicalAggregate(group=[{0}], C=[COUNT()]) + LogicalProject($f0=[HOP($0, 3600000, 10800000)]) + LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) ]]> </Resource> </TestCase> <TestCase name="testSessionTable"> <Resource name="sql"> <![CDATA[select stream session_start(rowtime, interval '1' hour) as rowtime, -session_end(rowtime, interval '1' hour), -count(*) as c + session_end(rowtime, interval '1' hour), + count(*) as c from orders group by session(rowtime, interval '1' hour)]]> </Resource> <Resource name="plan"> <![CDATA[ LogicalDelta - LogicalProject(ROWTIME=[SESSION_START($0, 3600000)], EXPR$1=[SESSION_END($0, 3600000)], C=[$1]) + LogicalProject(ROWTIME=[$0], EXPR$1=[$0], C=[$1]) LogicalAggregate(group=[{0}], C=[COUNT()]) LogicalProject($f0=[SESSION($0, 3600000)]) - LogicalTableScan(table=[[STREAMS, ORDERS]]) + LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) ]]> </Resource> </TestCase> @@ -2764,7 +2763,8 @@ LogicalProject(DEPTNO=[$0], B=[>($1, $2)]) </TestCase> <TestCase name="testInsert"> <Resource name="sql"> - <![CDATA[insert into empnullables (deptno, empno, ename) values (10, 150, 'Fred')]]> + <![CDATA[insert into empnullables (deptno, empno, ename) +values (10, 150, 'Fred')]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2776,7 +2776,8 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], f </TestCase> <TestCase name="testInsertSubset"> <Resource name="sql"> - <![CDATA[insert into empnullables values (10, 150, 'Fred')]]> + <![CDATA[insert into empnullables +values (50, 'Fred')]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2788,7 +2789,8 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], f </TestCase> <TestCase name="testInsertBind"> <Resource name="sql"> - <![CDATA[insert into empnullables (deptno, empno, ename) values (?, ?, ?)]]> + <![CDATA[insert into empnullables (deptno, empno, ename) +values (?, ?, ?)]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2801,7 +2803,8 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], f </TestCase> <TestCase name="testInsertBindSubset"> <Resource name="sql"> - <![CDATA[insert into empnullables values (?, ?, ?)]]> + <![CDATA[insert into empnullables +values (?, ?)]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2870,7 +2873,9 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ </TestCase> <TestCase name="testSelectViewExtendedColumnCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR from EMP_MODIFIABLEVIEW3 extend (SAL int) where SAL = 20]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR + from EMP_MODIFIABLEVIEW3 extend (SAL int) + where SAL = 20]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2882,7 +2887,9 @@ LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDAT </TestCase> <TestCase name="testSelectViewExtendedColumnCaseSensitiveCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, "sal", HIREDATE, MGR from EMP_MODIFIABLEVIEW3 extend ("sal" boolean) where "sal" = true]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, "sal", HIREDATE, MGR + from EMP_MODIFIABLEVIEW3 extend ("sal" boolean) + where "sal" = true]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2894,7 +2901,9 @@ LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], sal=[$7], HIREDAT </TestCase> <TestCase name="testSelectViewExtendedColumnExtendedCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, EXTRA from EMP_MODIFIABLEVIEW2 extend (EXTRA boolean) where SAL = 20]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, EXTRA + from EMP_MODIFIABLEVIEW2 extend (EXTRA boolean) + where SAL = 20]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2906,7 +2915,9 @@ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], SLACKER=[$4], SAL=[$5], HIREDAT </TestCase> <TestCase name="testSelectViewExtendedColumnCaseSensitiveExtendedCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, extra from EMP_MODIFIABLEVIEW2 extend (extra boolean) where extra = false]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, "extra" + from EMP_MODIFIABLEVIEW2 extend ("extra" boolean) + where "extra" = false]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2918,7 +2929,9 @@ LogicalProject(ENAME=[$0], EMPNO=[$1], JOB=[$2], SLACKER=[$4], SAL=[$5], HIREDAT </TestCase> <TestCase name="testSelectViewExtendedColumnUnderlyingCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM from EMP_MODIFIABLEVIEW3 extend (COMM int) where SAL = 20]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM + from EMP_MODIFIABLEVIEW3 extend (COMM int) + where SAL = 20]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2930,7 +2943,9 @@ LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDAT </TestCase> <TestCase name="testSelectViewExtendedColumnCaseSensitiveUnderlyingCollision"> <Resource name="sql"> - <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, "comm" from EMP_MODIFIABLEVIEW3 extend ("comm" int) where "comm" = 20]]> + <![CDATA[select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, "comm" + from EMP_MODIFIABLEVIEW3 extend ("comm" int) + where "comm" = 20]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2942,7 +2957,8 @@ LogicalProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], SLACKER=[$6], SAL=[$5], HIREDAT </TestCase> <TestCase name="testInsertView"> <Resource name="sql"> - <![CDATA[insert into empnullables_20 (empno, ename) values (150, 'Fred')]]> + <![CDATA[insert into empnullables_20 (empno, ename) +values (150, 'Fred')]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2956,7 +2972,8 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], f </TestCase> <TestCase name="testInsertSubsetView"> <Resource name="sql"> - <![CDATA[insert into empnullables_20 (empno, ename) values (150, 'Fred')]]> + <![CDATA[insert into empnullables_20 +values (10, 'Fred')]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -3048,7 +3065,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[INSERT], fl </TestCase> <TestCase name="testInsertBindWithCustomInitializerExpressionFactory"> <Resource name="sql"> - <![CDATA[nsert into empdefaults (deptno) values (?)]]> + <![CDATA[insert into empdefaults (deptno) values (?)]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -3590,7 +3607,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW3]], operation=[UPD </TestCase> <TestCase name="testUpdateExtendedColumnModifiableViewCaseSensitiveCollision"> <Resource name="sql"> - <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, deptno INTEGER) set deptno = 20, \"slacker\" = 100 where ename = 'Bob']]> + <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, deptno INTEGER) set deptno = 20, "slacker" = 100 where ename = 'Bob']]> </Resource> <Resource name="plan"> <![CDATA[ @@ -3603,7 +3620,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPD </TestCase> <TestCase name="testUpdateExtendedColumnModifiableViewExtendedCollision"> <Resource name="sql"> - <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, extra BOOLEAN) set deptno = 20, \"slacker\" = 100, extra = true where ename = 'Bob']]> + <![CDATA[update EMP_MODIFIABLEVIEW2("slacker" INTEGER, extra BOOLEAN) set deptno = 20, "slacker" = 100, extra = true where ename = 'Bob']]> </Resource> <Resource name="plan"> <![CDATA[ @@ -3616,7 +3633,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP_MODIFIABLEVIEW2]], operation=[UPD </TestCase> <TestCase name="testUpdateExtendedColumnModifiableViewExtendedCaseSensitiveCollision"> <Resource name="sql"> - <![CDATA[update EMP_MODIFIABLEVIEW2("extra" INTEGER, extra BOOLEAN) set deptno = 20, \"extra\" = 100, extra = true where ename = 'Bob']]> + <![CDATA[update EMP_MODIFIABLEVIEW2("extra" INTEGER, extra BOOLEAN) set deptno = 20, "extra" = 100, extra = true where ename = 'Bob']]> </Resource> <Resource name="plan"> <![CDATA[ @@ -3652,6 +3669,41 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]], operation=[INSERT], fl ]]> </Resource> </TestCase> + <TestCase name="testTumble"> + <Resource name="sql"> + <![CDATA[select STREAM + TUMBLE_START(rowtime, INTERVAL '1' MINUTE) AS s, + TUMBLE_END(rowtime, INTERVAL '1' MINUTE) AS e +from Shipments +GROUP BY TUMBLE(rowtime, INTERVAL '1' MINUTE)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalDelta + LogicalProject(S=[$0], E=[+($0, 60000)]) + LogicalAggregate(group=[{0}]) + LogicalProject($f0=[TUMBLE($1, 60000)]) + LogicalTableScan(table=[[CATALOG, SALES, SHIPMENTS]]) +]]> + </Resource> + </TestCase> + <TestCase name="testTumbleTableRowtimeNotFirstColumn"> + <Resource name="sql"> + <![CDATA[select stream + tumble_end(rowtime, interval '2' hour) as rowtime, orderId +from shipments +group by tumble(rowtime, interval '2' hour), orderId]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalDelta + LogicalProject(ROWTIME=[+($0, 7200000)], ORDERID=[$1]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject($f0=[TUMBLE($1, 7200000)], ORDERID=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, SHIPMENTS]]) +]]> + </Resource> + </TestCase> <TestCase name="testUpdateExtendedColumn"> <Resource name="sql"> <![CDATA[update empdefaults(updated TIMESTAMP) set deptno = 1, updated = timestamp '2017-03-12 13:03:05', empno = 20, ename = 'Bob' where deptno = 10]]>
