calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [17/18] incubator-calcite git commit: [CALCITE-846] Push aggregate with FILTER through UNION ALL
Date Wed, 02 Sep 2015 00:09:53 GMT
[CALCITE-846] Push aggregate with FILTER through UNION ALL


Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/0c1a1358
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/0c1a1358
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/0c1a1358

Branch: refs/heads/master
Commit: 0c1a135833cccdd7f75e18544ed2a627fdad1119
Parents: b967073
Author: Julian Hyde <jhyde@apache.org>
Authored: Fri Aug 14 11:49:23 2015 -0700
Committer: Julian Hyde <jhyde@apache.org>
Committed: Tue Sep 1 16:17:16 2015 -0700

----------------------------------------------------------------------
 .../rel/rules/AggregateUnionTransposeRule.java  |  7 ++-
 .../apache/calcite/test/RelOptRulesTest.java    | 19 ++++----
 .../org/apache/calcite/test/RelOptRulesTest.xml | 50 +++++++++++++++++---
 core/src/test/resources/sql/agg.oq              | 25 ++++++++++
 4 files changed, 82 insertions(+), 19 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/0c1a1358/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
index df55f0d..bcf80a9 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
@@ -61,8 +61,7 @@ public class AggregateUnionTransposeRule extends RelOptRule {
   private final RelFactories.SetOpFactory setOpFactory;
 
   private static final Map<Class<? extends SqlAggFunction>, Boolean>
-  SUPPORTED_AGGREGATES =
-      new IdentityHashMap<Class<? extends SqlAggFunction>, Boolean>();
+  SUPPORTED_AGGREGATES = new IdentityHashMap<>();
 
   static {
     SUPPORTED_AGGREGATES.put(SqlMinMaxAggFunction.class, true);
@@ -118,7 +117,7 @@ public class AggregateUnionTransposeRule extends RelOptRule {
     boolean anyTransformed = false;
 
     // create corresponding aggregates on top of each union child
-    List<RelNode> newUnionInputs = new ArrayList<RelNode>();
+    final List<RelNode> newUnionInputs = new ArrayList<>();
     for (RelNode input : union.getInputs()) {
       boolean alreadyUnique =
           RelMdUtil.areColumnsDefinitelyUnique(
@@ -178,7 +177,7 @@ public class AggregateUnionTransposeRule extends RelOptRule {
       }
       AggregateCall newCall =
           AggregateCall.create(aggFun, origCall.isDistinct(),
-              ImmutableList.of(groupCount + ord.i), groupCount, input,
+              ImmutableList.of(groupCount + ord.i), -1, groupCount, input,
               aggType, origCall.getName());
       newCalls.add(newCall);
     }

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/0c1a1358/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 8c1f678..b98bf73 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -149,14 +149,13 @@ public class RelOptRulesTest extends RelOptTestBase {
     HepPlanner hepPlanner = new HepPlanner(builder.build());
     hepPlanner.addRule(ProjectToWindowRule.PROJECT);
 
-    checkPlanning(tester,
-        preProgram,
-        hepPlanner,
-        "select count(*) over(partition by empno order by sal) as count1,\n"
-            + "count(*) over(partition by deptno order by sal) as count2, \n"
-            + "sum(deptno) over(partition by empno order by sal) as sum1, \n"
-            + "sum(deptno) over(partition by deptno order by sal) as sum2 \n"
-            + "from emp");
+    final String sql = "select\n"
+        + " count(*) over(partition by empno order by sal) as count1,\n"
+        + " count(*) over(partition by deptno order by sal) as count2,\n"
+        + " sum(deptno) over(partition by empno order by sal) as sum1,\n"
+        + " sum(deptno) over(partition by deptno order by sal) as sum2\n"
+        + "from emp";
+    checkPlanning(tester, preProgram, hepPlanner, sql);
   }
 
   @Test public void testUnionToDistinctRule() {
@@ -1338,6 +1337,10 @@ public class RelOptRulesTest extends RelOptTestBase {
     basePushAggThroughUnion();
   }
 
+  @Test public void testPushCountFilterThroughUnion() throws Exception {
+    basePushAggThroughUnion();
+  }
+
   @Test public void testPullFilterThroughAggregate() throws Exception {
     HepProgram preProgram = HepProgram.builder()
         .addRuleInstance(ProjectMergeRule.INSTANCE)

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/0c1a1358/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 542f153..199fb6b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -18,13 +18,12 @@ limitations under the License.
 <Root>
     <TestCase name="testProjectToWindowRuleForMultipleWindows">
         <Resource name="sql">
-            <![CDATA[
-select count(*) over(partition by empno order by sal) as count1,
-    count(*) over(partition by deptno order by sal) as count2,
-    sum(deptno)  over(partition by empno order by sal) as sum1,
-    sum(deptno)  over(partition by deptno order by sal) as sum2
-    from emp
-]]>
+            <![CDATA[select
+ count(*) over(partition by empno order by sal) as count1,
+ count(*) over(partition by deptno order by sal) as count2,
+ sum(deptno) over(partition by empno order by sal) as sum1,
+ sum(deptno) over(partition by deptno order by sal) as sum2
+from emp]]>
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
@@ -1598,6 +1597,43 @@ LogicalProject(DEPTNO=[CASE($2, null, $0)], JOB=[CASE($3, null, $1)],
EXPR$2=[$4
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testPushCountFilterThroughUnion">
+        <Resource name="sql">
+            <![CDATA[select deptno, count(*) filter (where job = 'CLERK')
+from (
+  select * from emp where deptno = 10
+  union all
+  select * from emp where deptno > 20)
+group by deptno]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[COUNT() FILTER $1])
+  LogicalProject(DEPTNO=[$7], $f1=[=($2, 'CLERK')])
+    LogicalUnion(all=[true])
+      LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+        LogicalFilter(condition=[=($7, 10)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+        LogicalFilter(condition=[>($7, 20)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[$SUM0($1)])
+  LogicalUnion(all=[true])
+    LogicalAggregate(group=[{0}], EXPR$1=[COUNT() FILTER $1])
+      LogicalProject(DEPTNO=[$7], $f1=[=($2, 'CLERK')])
+        LogicalFilter(condition=[=($7, 10)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalAggregate(group=[{0}], EXPR$1=[COUNT() FILTER $1])
+      LogicalProject(DEPTNO=[$7], $f1=[=($2, 'CLERK')])
+        LogicalFilter(condition=[>($7, 20)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testPullFilterThroughAggregate">
         <Resource name="sql">
             <![CDATA[select empno, sal, deptno from (  select empno, sal, deptno  from
emp  where sal > 5000)group by empno, sal, deptno]]>

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/0c1a1358/core/src/test/resources/sql/agg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq
index 2fe0cc2..0ef4909 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -771,6 +771,31 @@ select avg(comm) as a, count(comm) as c from "scott".emp where empno
< 7844;
 
 !ok
 
+# [CALCITE-846] Push aggregate with FILTER through UNION ALL
+select deptno, count(*) filter (where job = 'CLERK') as cf, count(*) as c
+from (
+  select * from "scott".emp where deptno < 20
+  union all
+  select * from "scott".emp where deptno > 20)
+group by deptno;
++--------+----+---+
+| DEPTNO | CF | C |
++--------+----+---+
+|     10 |  1 | 3 |
+|     30 |  1 | 6 |
++--------+----+---+
+(2 rows)
+
+!ok
+EnumerableAggregate(group=[{0}], CF=[COUNT() FILTER $1], C=[COUNT()])
+  EnumerableCalc(expr#0..1=[{inputs}], expr#2=['CLERK'], expr#3=[=($t0, $t2)], DEPTNO=[$t1],
$f1=[$t3])
+    EnumerableUnion(all=[true])
+      EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[<($t7, $t8)], JOB=[$t2],
DEPTNO=[$t7], $condition=[$t9])
+        EnumerableTableScan(table=[[scott, EMP]])
+      EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[>($t7, $t8)], JOB=[$t2],
DEPTNO=[$t7], $condition=[$t9])
+        EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
 # [CALCITE-751] Aggregate join transpose
 select count(*)
 from "scott".emp join "scott".dept using (deptno);


Mime
View raw message