Repository: calcite Updated Branches: refs/heads/master 495fd1dc7 -> aed5bca7c
[CALCITE-476] DISTINCT flag in windowed aggregates (Radu Tudoran) Adds support in parser, SqlToRelConverter, and RexOver; but the runtime (EnumerableWindow) still ignores the flag. Close apache/calcite#428 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/aed5bca7 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/aed5bca7 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/aed5bca7 Branch: refs/heads/master Commit: aed5bca7c5f49d117d7b794395ff4b6ad2d76bde Parents: 495fd1d Author: rtudoran <[email protected]> Authored: Thu Apr 13 20:58:24 2017 +0200 Committer: Julian Hyde <[email protected]> Committed: Mon Apr 24 13:33:54 2017 -0700 ---------------------------------------------------------------------- .../org/apache/calcite/rel/core/Window.java | 10 ++++-- .../calcite/rel/logical/LogicalWindow.java | 3 +- .../rel/rules/ProjectWindowTransposeRule.java | 3 +- .../java/org/apache/calcite/rex/RexBuilder.java | 13 ++++--- .../java/org/apache/calcite/rex/RexOver.java | 37 +++++++++++++++++--- .../java/org/apache/calcite/rex/RexShuttle.java | 3 +- .../calcite/sql2rel/SqlToRelConverter.java | 25 ++++++++++--- .../apache/calcite/test/RelOptRulesTest.java | 2 ++ .../calcite/test/SqlToRelConverterTest.java | 25 +++++++++++++ .../calcite/test/SqlToRelConverterTest.xml | 33 +++++++++++++++++ 10 files changed, 135 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rel/core/Window.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/Window.java b/core/src/main/java/org/apache/calcite/rel/core/Window.java index 462f5cb..b9d92ac 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Window.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Window.java @@ -314,7 +314,7 @@ public abstract class Window extends SingleRel { public AggregateCall get(int index) { final RexWinAggCall aggCall = aggCalls.get(index); return AggregateCall.create((SqlAggFunction) aggCall.getOperator(), - false, getProjectOrdinals(aggCall.getOperands()), -1, + aggCall.distinct, getProjectOrdinals(aggCall.getOperands()), -1, aggCall.getType(), fieldNames.get(aggCall.ordinal)); } }; @@ -336,6 +336,9 @@ public abstract class Window extends SingleRel { */ public final int ordinal; + /** Whether to eliminate duplicates before applying aggregate function. */ + public final boolean distinct; + /** * Creates a RexWinAggCall. * @@ -343,14 +346,17 @@ public abstract class Window extends SingleRel { * @param type Result type * @param operands Operands to call * @param ordinal Ordinal within its partition + * @param distinct Eliminate duplicates before applying aggregate function */ public RexWinAggCall( SqlAggFunction aggFun, RelDataType type, List<RexNode> operands, - int ordinal) { + int ordinal, + boolean distinct) { super(type, aggFun, operands); this.ordinal = ordinal; + this.distinct = distinct; } @Override public RexCall clone(RelDataType type, List<RexNode> operands) { http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java index abd703a..1b6445e 100644 --- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java +++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java @@ -156,7 +156,8 @@ public final class LogicalWindow extends Window { over.getAggOperator(), over.getType(), toInputRefs(over.operands), - aggMap.size()); + aggMap.size(), + over.isDistinct()); aggCalls.add(aggCall); aggMap.put(over, aggCall); } http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java index 98f22ea..0cf2949 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java @@ -111,7 +111,8 @@ public class ProjectWindowTransposeRule extends RelOptRule { (SqlAggFunction) call.getOperator(), call.getType(), clonedOperands, - ((Window.RexWinAggCall) call).ordinal); + ((Window.RexWinAggCall) call).ordinal, + ((Window.RexWinAggCall) call).distinct); } else { return call; } http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rex/RexBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java index eefb2d8..1906227 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java +++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java @@ -335,7 +335,8 @@ public class RexBuilder { RexWindowBound upperBound, boolean physical, boolean allowPartial, - boolean nullWhenCountZero) { + boolean nullWhenCountZero, + boolean distinct) { assert operator != null; assert exprs != null; assert partitionKeys != null; @@ -347,7 +348,7 @@ public class RexBuilder { lowerBound, upperBound, physical); - final RexOver over = new RexOver(type, operator, exprs, window); + final RexOver over = new RexOver(type, operator, exprs, window, distinct); RexNode result = over; // This should be correct but need time to go over test results. @@ -363,14 +364,15 @@ public class RexBuilder { bigintType, SqlStdOperatorTable.COUNT, exprs, - window), + window, + distinct), makeLiteral( BigDecimal.ZERO, bigintType, SqlTypeName.DECIMAL)), ensureType(type, // SUM0 is non-nullable, thus need a cast new RexOver(typeFactory.createTypeWithNullability(type, false), - operator, exprs, window), + operator, exprs, window, distinct), false), makeCast(type, constantNull())); } @@ -388,7 +390,8 @@ public class RexBuilder { bigintType, SqlStdOperatorTable.COUNT, ImmutableList.<RexNode>of(), - window), + window, + distinct), makeLiteral( BigDecimal.valueOf(2), bigintType, http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rex/RexOver.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexOver.java b/core/src/main/java/org/apache/calcite/rex/RexOver.java index d2c4684..38d1f9c 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexOver.java +++ b/core/src/main/java/org/apache/calcite/rex/RexOver.java @@ -35,13 +35,15 @@ public class RexOver extends RexCall { //~ Instance fields -------------------------------------------------------- private final RexWindow window; + private final boolean distinct; //~ Constructors ----------------------------------------------------------- /** * Creates a RexOver. * - * <p>For example, "SUM(x) OVER (ROWS 3 PRECEDING)" is represented as: + * <p>For example, "SUM(DISTINCT x) OVER (ROWS 3 PRECEDING)" is represented + * as: * * <ul> * <li>type = Integer, @@ -54,15 +56,18 @@ public class RexOver extends RexCall { * @param op Aggregate operator * @param operands Operands list * @param window Window specification + * @param distinct Aggregate operator is applied on distinct elements */ RexOver( RelDataType type, SqlAggFunction op, List<RexNode> operands, - RexWindow window) { + RexWindow window, + boolean distinct) { super(type, op, operands); Preconditions.checkArgument(op.isAggregator()); this.window = Preconditions.checkNotNull(window); + this.distinct = distinct; } //~ Methods ---------------------------------------------------------------- @@ -78,8 +83,32 @@ public class RexOver extends RexCall { return window; } - protected String computeDigest(boolean withType) { - return super.computeDigest(withType) + " OVER (" + window + ")"; + public boolean isDistinct() { + return distinct; + } + + @Override protected String computeDigest(boolean withType) { + final StringBuilder sb = new StringBuilder(op.getName()); + sb.append("("); + if (distinct) { + sb.append("DISTINCT "); + } + for (int i = 0; i < operands.size(); i++) { + if (i > 0) { + sb.append(", "); + } + RexNode operand = operands.get(i); + sb.append(operand.toString()); + } + sb.append(")"); + if (withType) { + sb.append(":"); + sb.append(type.getFullTypeString()); + } + sb.append(" OVER (") + .append(window) + .append(")"); + return sb.toString(); } public <R> R accept(RexVisitor<R> visitor) { http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/main/java/org/apache/calcite/rex/RexShuttle.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java index 36b9e40..b642503 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java +++ b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java @@ -50,7 +50,8 @@ public class RexShuttle implements RexVisitor<RexNode> { over.getType(), over.getAggOperator(), clonedOperands, - window); + window, + over.isDistinct()); } else { return over; } http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/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 5aa5ec8..573214e 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -1868,13 +1868,23 @@ public class SqlToRelConverter { // Walk over the tree and apply 'over' to all agg functions. This is // necessary because the returned expression is not necessarily a call // to an agg function. For example, AVG(x) becomes SUM(x) / COUNT(x). + + boolean isDistinct = false; + if (aggCall.getFunctionQuantifier() != null + && aggCall.getFunctionQuantifier().getValue().equals(SqlSelectKeyword.DISTINCT)) { + isDistinct = true; + } + final RexShuttle visitor = new HistogramShuttle( partitionKeys.build(), orderKeys.build(), RexWindowBound.create(window.getLowerBound(), lowerBound), RexWindowBound.create(window.getUpperBound(), upperBound), - window); - return rexAgg.accept(visitor); + window, + isDistinct); + RexNode overNode = rexAgg.accept(visitor); + + return overNode; } finally { bb.window = null; } @@ -4957,17 +4967,20 @@ public class SqlToRelConverter { private final RexWindowBound lowerBound; private final RexWindowBound upperBound; private final SqlWindow window; + private final boolean distinct; HistogramShuttle( List<RexNode> partitionKeys, ImmutableList<RexFieldCollation> orderKeys, RexWindowBound lowerBound, RexWindowBound upperBound, - SqlWindow window) { + SqlWindow window, + boolean distinct) { this.partitionKeys = partitionKeys; this.orderKeys = orderKeys; this.lowerBound = lowerBound; this.upperBound = upperBound; this.window = window; + this.distinct = distinct; } public RexNode visitCall(RexCall call) { @@ -5023,7 +5036,8 @@ public class SqlToRelConverter { upperBound, window.isRows(), window.isAllowPartial(), - false); + false, + distinct); RexNode histogramCall = rexBuilder.makeCall( @@ -5063,7 +5077,8 @@ public class SqlToRelConverter { upperBound, window.isRows(), window.isAllowPartial(), - needSum0); + needSum0, + distinct); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index efb77e7..8f553bb 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -112,6 +112,8 @@ import java.util.List; import static org.junit.Assert.assertTrue; + + /** * Unit test for rules in {@code org.apache.calcite.rel} and subpackages. * http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/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 91889b2..494ec44 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -443,6 +443,31 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql("select distinct sal + 5 from emp").ok(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-476";>[CALCITE-476] + * DISTINCT flag in windowed aggregates</a>. */ + @Test public void testSelectOverDistinct() { + // Checks to see if <aggregate>(DISTINCT x) is set and preserved + // as a flag for the aggregate call. + final String sql = "select SUM(DISTINCT deptno)\n" + + "over (ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)\n" + + "from emp\n"; + sql(sql).ok(); + } + + /** As {@link #testSelectOverDistinct()} but for streaming queries. */ + @Test public void testSelectStreamPartitionDistinct() { + final String sql = "select stream\n" + + " count(distinct orderId) over (partition by productId\n" + + " order by rowtime\n" + + " range interval '1' second preceding) as c,\n" + + " count(distinct orderId) over w as c2,\n" + + " count(orderId) over w as c3\n" + + "from orders\n" + + "window w as (partition by productId)"; + sql(sql).ok(); + } + @Test public void testSelectDistinctGroup() { sql("select distinct sum(sal) from emp group by deptno").ok(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/aed5bca7/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 f8374f3..f949786 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -235,6 +235,39 @@ LogicalProject(DEPTNO=[$0], NAME=[$1]) <![CDATA[select*from unnest(multiset(select*from dept))]]> </Resource> </TestCase> + <TestCase name="testSelectOverDistinct"> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EXPR$0=[CASE(>(COUNT(DISTINCT $7) OVER (ROWS BETWEEN 10 PRECEDING AND CURRENT ROW), 0), CAST($SUM0(DISTINCT $7) OVER (ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="sql"> + <![CDATA[select SUM(DISTINCT deptno) +over (ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) +from emp +]]> + </Resource> + </TestCase> + <TestCase name="testSelectStreamPartitionDistinct"> + <Resource name="plan"> + <![CDATA[ +LogicalDelta + LogicalProject(C=[COUNT(DISTINCT $2) OVER (PARTITION BY $1 ORDER BY $0 RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW)], C2=[COUNT(DISTINCT $2) OVER (PARTITION BY $1 ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], C3=[COUNT($2) OVER (PARTITION BY $1 ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)]) + LogicalTableScan(table=[[CATALOG, SALES, ORDERS]]) +]]> + </Resource> + <Resource name="sql"> + <![CDATA[select stream + count(distinct orderId) over (partition by productId + order by rowtime + range interval '1' second preceding) as c, + count(distinct orderId) over w as c2, + count(orderId) over w as c3 +from orders +window w as (partition by productId)]]> + </Resource> + </TestCase> <TestCase name="testUnnestArray"> <Resource name="sql"> <![CDATA[select*from unnest(array(select*from dept))]]>
