Why do you want to enable AggregateUnionAggregateRule[1]? It is doing the opposite of AggregateUnionTransposeRule.
Julian [1] https://calcite.apache.org/apidocs/org/apache/calcite/rel/rules/AggregateUnionAggregateRule.html On Thu, Jun 2, 2016 at 2:03 PM, Ravikumar CS <[email protected]> wrote: > Thanks Julian. FilterSetOpTransposeRule worked for pushing the filter into > the union. > > However the partial aggregate logic doesn't seem to work even after adding > the rules AggregateUnionTransposeRule & AggregateUnionAggregateRule. > > *Query:* SELECT id, SUM(units) FROM (SELECT id, units FROM Orders1 UNION > ALL SELECT id, units FROM Orders2) where id=1 group by id > > *Plan:* > > LogicalAggregate(group=[{0}], EXPR$1=[$SUM0($1)]) > > LogicalUnion(all=[true]) > > LogicalFilter(condition=[=($0, 1)]) > > LogicalProject(id=[$0], units=[$2]) > > LogicalTableScan(table=[[SALES, Orders1]]) > > LogicalFilter(condition=[=($0, 1)]) > > LogicalProject(id=[$0], units=[$2]) > > LogicalTableScan(table=[[SALES, Orders2]]) > > ~Ravi > > On Thu, Jun 2, 2016 at 12:11 PM, Julian Hyde <[email protected]> wrote: > >> I logged https://issues.apache.org/jira/browse/CALCITE-1271. >> >> On Thu, Jun 2, 2016 at 12:11 PM, Julian Hyde <[email protected]> wrote: >> > By the way, I noticed that FilterSetOpTransposeRule and >> > AggregateUnionTransposeRule are not part of the default rule set. We >> > should fix that. >> > >> > On Thu, Jun 2, 2016 at 10:20 AM, Julian Hyde <[email protected]> wrote: >> >> You need to push the Filter into the Union. Otherwise the Aggregate is >> on top of a Filter, not a Union. Use FilterSetOpTransposeRule. >> >> >> >> Julian >> >> >> >>> On Jun 2, 2016, at 9:54 AM, Ravikumar CS <[email protected]> >> wrote: >> >>> >> >>> Hi, >> >>> >> >>> I am trying to come up with an optimized relational expression which >> does >> >>> predicate push downs >> >>> >> >>> & partial aggregates in preparation for a distributed execution of the >> >>> query. >> >>> >> >>> SQL of interest: >> >>> >> >>> SELECT col1, SUM(col2) FROM ( SELECT col2, col2 FROM Orders1 >> >>> >> >>> UNION ALL >> >>> >> >>> SELECT col1, >> col2 >> >>> FROM Orders2 >> >>> >> >>> ) WHERE col1=1 >> GROUP >> >>> BY col1; >> >>> >> >>> >> >>> Relational Expression - Initial: >> >>> >> >>> LogicalAggregate(group=[{0}], EXPR$1=[AVG($1)]) >> >>> >> >>> LogicalFilter(condition=[=($0, 1)]) >> >>> >> >>> LogicalUnion(all=[true]) >> >>> >> >>> LogicalProject(id=[$0], units=[$2]) >> >>> >> >>> LogicalTableScan(table=[[SALES, Orders1]]) >> >>> >> >>> LogicalProject(id=[$0], units=[$2]) >> >>> >> >>> LogicalTableScan(table=[[SALES, Orders2]]) >> >>> >> >>> >> >>> Query Optimization rules applied: >> >>> >> >>> HepProgram program = new HepProgramBuilder() >> >>> >> >>> .addRuleInstance(AggregateUnionAggregateRule.INSTANCE) >> >>> >> >>> .addRuleInstance(AggregateUnionTransposeRule.INSTANCE) >> >>> >> >>> .addRuleInstance(AggregateReduceFunctionsRule.INSTANCE) >> >>> >> >>> .build(); >> >>> >> >>> HepPlanner planner = new HepPlanner(program); >> >>> >> >>> planner.setRoot(oldLogicalPlan); >> >>> >> >>> >> >>> Relational Expression after Query Optimization: RelNode newLogicalPlan >> = >> >>> planner.findBestExp(); >> >>> >> >>> LogicalProject(id=[$0], EXPR$1=[CAST(/($1, $2)):INTEGER NOT NULL]) >> >>> >> >>> LogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT()]) >> >>> >> >>> LogicalFilter(condition=[=($0, 1)]) >> >>> >> >>> LogicalUnion(all=[true]) >> >>> >> >>> LogicalProject(id=[$0], units=[$2]) >> >>> >> >>> LogicalTableScan(table=[[SALES, Orders1]]) >> >>> >> >>> LogicalProject(id=[$0], units=[$2]) >> >>> >> >>> LogicalTableScan(table=[[SALES, Orders2]]) >> >>> >> >>> >> >>> Questions: >> >>> >> >>> 1. Are there any rules to push the predicates(col1=1) down to the sub >> >>> queries ? >> >>> >> >>> 2. How can I rewrite the query such that the partial aggregates are >> >>> computed within each union(as below)? >> >>> >> >>> Tried AggregateUnionTransposeRule and AggregateJoinTransposeRule. >> May be >> >>> I am missed something. >> >>> >> >>> SELECT col1, SUM(partialCol2) AS c >> >>> >> >>> FROM ( SELECT col1, SUM(col2) AS partialCol2 FROM Orders1 where >> >>> col1=1 GROUP BY deptno >> >>> >> >>> UNION ALL >> >>> >> >>> SELECT col1, SUM(col2) AS partialCol2 FROM Orders2 >> >>> where col1=1 GROUP BY deptno >> >>> >> >>> ) GROUP BY col1; >> >> >>
