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; >>
