Hi, I am trying to get the VolcanoPlanner working. I took the simple planner from Milinda[1] and built a modified planner[2] which uses Volcano planner for optimization.
The table that I am using is CSVFilterableTable[3]. However, the volcano planner fails to optimize with the following error[4] Questions: 1. It works when I explicitly set the EnumerableConvention(Line 95-98). In that case the rules seem to fire. I get back a plan in enumerable convention. Is that expected ? 2. If I want to take the initial LogicalPlan & generate the optimized logical plan. How can I achieve this using VolcanoPlanner? ( Just the way it worked using HepPlanner) 3. Am I missing any crucial planner rules ? 4. I want to understand more about the Convention concept and how it relates to Planner. Is there a documentation that I can go through? ~Ravi [1] https://github.com/milinda/calcite-tutorial/blob/master/src/main/java/org/pathirage/calcite/tutorial/planner/SimpleQueryPlanner.java [2] BasicQueryPlanner with Volcano: Script: https://gist.github.com/ravikumarcs/724b7cbb1053a1650664aabc6eeb7271 Output: https://gist.github.com/ravikumarcs/d0d50c414cae47be18f45e57a58749dd [3] Model: https://github.com/apache/calcite/blob/master/example/csv/src/test/resources/filterable-model.json [4] VolcanoPlanner failure: https://gist.github.com/ravikumarcs/10b53d47ad0bd1037436eab7c342c048 On Thu, Jun 2, 2016 at 2:33 PM, Ravikumar CS <[email protected]> wrote: > You are right. I changed the order of the rules & it worked. Thanks Julian. > > Rule Order: FilterSetOpTransposeRule -> AggregateReduceFunctionsRule -> > AggregateUnionTransposeRule > > New Plan: > > LogicalProject(id=[$0], EXPR$1=[CAST(/($1, $2)):INTEGER NOT NULL]) > > LogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[$SUM0($2)]) > > LogicalUnion(all=[true]) > > LogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT()]) > > LogicalFilter(condition=[=($0, 1)]) > > LogicalProject(id=[$0], units=[$2]) > > LogicalTableScan(table=[[SALES, Orders1]]) > > LogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT()]) > > LogicalFilter(condition=[=($0, 1)]) > > LogicalProject(id=[$0], units=[$2]) > > LogicalTableScan(table=[[SALES, Orders2]]) > > On Thu, Jun 2, 2016 at 2:07 PM, Julian Hyde <[email protected]> wrote: > >> 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; >> >> >> >> >> >> > >
