Enrico, I cannot understand how you can arrive at scenario C. Theoretically, if there are not enough rules to implement a certain plan, you should get a CannotPlanException from RelOptPlanner#findBest. I would expect this outcome if e.g. you do not have "rules that handle the LIMIT". Moreover, having "wrong plans computed for joins" seems like a bug. But it is not easy to assess the situation and evaluate what is going on without the actual query and rules that are applied.
Best regards, Ruben Le mer. 16 sept. 2020 à 17:24, Enrico Olivelli <[email protected]> a écrit : > Ruben, > thank you very much, using your snippet I got the planner working ! > > In HerdDB we are currently using > Programs.ofRules(Programs.RULE_SET) +RelOptUtils#registerDefaultRules (that > add lots of rules) and we are able to handle several cases of very complex > generic SQL. > > But If I try to reduce the number of rules I see this behaviour: > a) if I am lucky I get some error from the planner that tells that it can't > find a way to compute the plan -> I can switch to the full planner ruleset > b) If I am less lucky I have a plan that works but it is less efficient -> > (this is expected, and this is what I want, that is to trade planner speed > vs efficiency of plans > c) If I am unlucky the planner returns a plan that leads to wrong results ! > > For instance you can see c) if you do not add rules that handle "LIMIT" and > simply the plan sometimes won't pick the LIMIT and the database will return > unexpected results > but I saw worse cases, like wrong plans computed for joins. > > So my next question is: > is there a way to create a minimal set of rules that covers simple SQL > (think about basic JPA stuff....) and eventually fails the plan in case of > unhandled cases ? > > Best regards > Enrico > > Il giorno mer 16 set 2020 alle ore 09:57 Ruben Q L <[email protected]> ha > scritto: > > > Hi Enrico, > > > > not sure if this can be applied to your case, but I also have a use case > > with HepPlanner and VolcanoPlanner. > > In my case I have an initial phase of rules applied with HepPlanner. > These > > are rules that optimize the logical plan and are always a "quickwin" > (they > > always generate a better version of the plan, so it makes sense to apply > > them with a Hep). > > Then, I have a "main" phase, applied with VolcanoPlanner, that takes care > > of converting the plan into the most optimized Enumerable result. > > A simplified code snippet would be: > > > > private static final Program PHASE_1 = > > Programs.of(HepProgram.builder().addRuleCollection( > > Arrays.asList( > > CoreRules.FILTER_INTO_JOIN, > > CoreRules.SORT_PROJECT_TRANSPOSE, > > CoreRules.SORT_JOIN_TRANSPOSE, > > CoreRules.FILTER_REDUCE_EXPRESSIONS, > > ... > > )).build(), false, MyRelMetadataProvider.INSTANCE); > > > > private static final List<RelOptRule> PHASE_2_RULES = Arrays.asList( > > EnumerableRules.ENUMERABLE_VALUES_RULE, > > EnumerableRules.ENUMERABLE_UNION_RULE, > > EnumerableRules.ENUMERABLE_FILTER_TO_CALC_RULE, > > EnumerableRules.ENUMERABLE_PROJECT_TO_CALC_RULE, > > EnumerableRules.ENUMERABLE_COLLECT_RULE, > > EnumerableRules.ENUMERABLE_UNCOLLECT_RULE, > > ...); > > private static final Program PHASE_2 = > > Programs.of(RuleSets.ofList(PHASE_2_RULES)); > > > > > > EnumerableRel optimizeLogicalPlan(RelNode query) > > { > > Program optPhases = Programs.sequence(PHASE_1, PHASE_2); > > RelTraitSet desiredTraitSet = query.getTraitSet() > > .replace(EnumerableConvention.INSTANCE) > > .simplify(); > > EnumerableRel result = (EnumerableRel) optPhases.run( > > query.getCluster().getPlanner(), // this is a VolcanoPlanner > > query, > > desiredTraitSet, > > Collections.emptyList(), > > Collections.emptyList()); > > return result; > > } > > > > > > I hope it helps. > > > > Best regards, > > Ruben > > > > > > Le mar. 15 sept. 2020 à 17:43, Enrico Olivelli <[email protected]> a > > écrit : > > > > > Hi, > > > > > > I am trying to create a two stage planner using HepPlanner and then > > > VolcanoPlanner > > > > > > Which is the correct sequence of steps to pass from SQL to Enumerable ? > > > My goal is to use Hep for very simple queries like simple INSERTs, > > SELECT * > > > FROM TABLE, SELECT * FROM TABLE WHERE pk=?.... > > > Volcano is overkill for such stuff. > > > > > > This is my idea: > > > 1) get a Planner (that's Volcano) > > > Planner planner = Frameworks.getPlanner(config); > > > > > > 2) Get the logical plan > > > SqlNode n = planner.parse(query); > > > n = planner.validate(n); > > > RelNode logicalPlan = planner.rel(n).project(); > > > 3) Create HepPlanner > > > HepProgram hepProgram = > > > HepProgram. > > > builder() > > > . ?? which Rules ? > > > .build(); > > > > > > HepPlanner hepPlanner = new HepPlanner(hepProgram); > > > hepPlanner.addRelTraitDef(ConventionTraitDef.INSTANCE); > > > > > > hepPlanner.setRoot(logicalPlan); > > > > > > 4) Run HepPlanner > > > RelNode bestForHep = hepPlanner.findBestExp(); > > > > > > 5) Pick Volcano > > > > > > final RelOptPlanner optPlanner = cluster.getPlanner(); > > > > > > 6) Convert to Enumerable > > > > > > final RelOptPlanner optPlanner = cluster.getPlanner(); > > > > > > optPlanner.addRule(CoreRules.FILTER_REDUCE_EXPRESSIONS); > > > RelTraitSet desiredTraits = > > > cluster.traitSet() > > > .replace(EnumerableConvention.INSTANCE); > > > final RelCollation collation = > > > logicalPlan instanceof Sort > > > ? ((Sort) logicalPlan).collation > > > : null; > > > if (collation != null) { > > > desiredTraits = desiredTraits.replace(collation); > > > } > > > final RelNode newRoot = > optPlanner.changeTraits(logicalPlan, > > > desiredTraits); > > > optPlanner.setRoot(newRoot); > > > RelNode bestExp = optPlanner.findBestExp(); > > > > > > Any hint/pointer is very appreciated > > > > > > The alternative is to detect such simple queries and use a little set > of > > > Rules and not Programs.ofRules(Programs.RULE_SET) > > > > > > Best regards > > > Enrico > > > > > >
