You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Madhav Suresh <ma...@u.northwestern.edu> on 2019/10/02 15:50:13 UTC
Volcano Optimizer JDBC Table Scan -> Join
Hi All,
I'm trying to run the volcano optimizer on a simple join query with the
TPC-H schema. I'm a little unclear on how to solve this, I thought I needed
to add a converter rules from JDBC->Enumerable, but it seems like those
rules are included at runtime. I then tried adding a None->Bindable rule,
but that also didn't fix the issue. I get the error below, code included
below error:
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not
> enough rules to produce a node with desired properties:
> convention=ENUMERABLE.
> Missing conversions are LogicalJoin[convention: NONE -> JDBC.name],
> LogicalJoin[convention: NONE -> ENUMERABLE]
> There are 2 empty subsets:
> Empty subset 0: rel#51:Subset#4.ENUMERABLE, the relevant part of the
> original plan is as follows
> 14:LogicalJoin(condition=[true], joinType=[inner])
> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
> joinType=[inner])
> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name], table=[[customer]])
> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>
> Empty subset 1: rel#49:Subset#4.JDBC.name, the relevant part of the
> original plan is as follows
> 14:LogicalJoin(condition=[true], joinType=[inner])
> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
> joinType=[inner])
> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name], table=[[customer]])
> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>
The test case I'm running is here (
https://gist.github.com/madhavsuresh/e95631776eceb47ab8eeb608268531ce):
> @Test
> public void testSimpleJoin() throws SqlParseException,
> ValidationException {
> String sql =
> "select\n"
> + " l.l_orderkey\n"
> + "from\n"
> + " customer c,\n"
> + " orders o,\n"
> + " lineitem l\n"
> + "\n"
> + "where\n"
> + " c.c_mktsegment = 'HOUSEHOLD'\n"
> + " and c.c_custkey = o.o_custkey\n";
> optimizer = new VolcanoPlanner();
> optimizer.addRelTraitDef(ConventionTraitDef.INSTANCE);
> optimizer.addRule(new OptToyRules.OptToyTestFilter());
> // add rules
> optimizer.addRule(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN);
> optimizer.addRule(ReduceExpressionsRule.PROJECT_INSTANCE);
> optimizer.addRule(PruneEmptyRules.PROJECT_INSTANCE);
>
> // add ConverterRule
> optimizer.addRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
> optimizer.addRule(EnumerableRules.ENUMERABLE_SORT_RULE);
> optimizer.addRule(EnumerableRules.ENUMERABLE_VALUES_RULE);
> optimizer.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE);
> optimizer.addRule(EnumerableRules.ENUMERABLE_FILTER_RULE);
> optimizer.addRule(NoneToBindableConverterRule.INSTANCE);
> SqlNode node = planner.parse(sql);
> node = planner.validate(node);
> SqlToRelConverter converter = createSqlToRelConverter();
> RelRoot n = converter.convertQuery(node, true, true);
> RelNode relNode = n.rel;
>
> // TODO(madhavsuresh): only works with needsValidation set to true.
> RelTraitSet desiredTraits =
>
> relNode.getCluster().traitSet().replace(EnumerableConvention.INSTANCE);
> relNode = optimizer.changeTraits(relNode, desiredTraits);
> optimizer.setRoot(relNode);
> optimizer.findBestExp();
>
>
Thanks for the help!
Madhav
Re: Volcano Optimizer JDBC Table Scan -> Join
Posted by Julian Hyde <jh...@apache.org>.
No need to apologize! We really appreciate you following up and saying you found a solution. Your solution may help someone else.
Julian
> On Oct 2, 2019, at 9:34 AM, Madhav Suresh <ma...@u.northwestern.edu> wrote:
>
> I apologize for the immediate follow up, however, I found out my issue was
> not adding the EnumerableRules.ENUMERABLE_JOIN_RULE.
>
> On Wed, Oct 2, 2019 at 10:50 AM Madhav Suresh <ma...@u.northwestern.edu>
> wrote:
>
>> Hi All,
>>
>> I'm trying to run the volcano optimizer on a simple join query with the
>> TPC-H schema. I'm a little unclear on how to solve this, I thought I needed
>> to add a converter rules from JDBC->Enumerable, but it seems like those
>> rules are included at runtime. I then tried adding a None->Bindable rule,
>> but that also didn't fix the issue. I get the error below, code included
>> below error:
>>
>> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not
>>> enough rules to produce a node with desired properties:
>>> convention=ENUMERABLE.
>>> Missing conversions are LogicalJoin[convention: NONE -> JDBC.name],
>>> LogicalJoin[convention: NONE -> ENUMERABLE]
>>> There are 2 empty subsets:
>>> Empty subset 0: rel#51:Subset#4.ENUMERABLE, the relevant part of the
>>> original plan is as follows
>>> 14:LogicalJoin(condition=[true], joinType=[inner])
>>> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
>>> joinType=[inner])
>>> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name],
>>> table=[[customer]])
>>> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
>>> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>>>
>>> Empty subset 1: rel#49:Subset#4.JDBC.name, the relevant part of the
>>> original plan is as follows
>>> 14:LogicalJoin(condition=[true], joinType=[inner])
>>> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
>>> joinType=[inner])
>>> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name],
>>> table=[[customer]])
>>> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
>>> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>>>
>>
>>
>> The test case I'm running is here (
>> https://gist.github.com/madhavsuresh/e95631776eceb47ab8eeb608268531ce):
>>
>>> @Test
>>> public void testSimpleJoin() throws SqlParseException,
>>> ValidationException {
>>> String sql =
>>> "select\n"
>>> + " l.l_orderkey\n"
>>> + "from\n"
>>> + " customer c,\n"
>>> + " orders o,\n"
>>> + " lineitem l\n"
>>> + "\n"
>>> + "where\n"
>>> + " c.c_mktsegment = 'HOUSEHOLD'\n"
>>> + " and c.c_custkey = o.o_custkey\n";
>>> optimizer = new VolcanoPlanner();
>>> optimizer.addRelTraitDef(ConventionTraitDef.INSTANCE);
>>> optimizer.addRule(new OptToyRules.OptToyTestFilter());
>>> // add rules
>>> optimizer.addRule(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN);
>>> optimizer.addRule(ReduceExpressionsRule.PROJECT_INSTANCE);
>>> optimizer.addRule(PruneEmptyRules.PROJECT_INSTANCE);
>>>
>>> // add ConverterRule
>>> optimizer.addRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
>>> optimizer.addRule(EnumerableRules.ENUMERABLE_SORT_RULE);
>>> optimizer.addRule(EnumerableRules.ENUMERABLE_VALUES_RULE);
>>> optimizer.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE);
>>> optimizer.addRule(EnumerableRules.ENUMERABLE_FILTER_RULE);
>>> optimizer.addRule(NoneToBindableConverterRule.INSTANCE);
>>> SqlNode node = planner.parse(sql);
>>> node = planner.validate(node);
>>> SqlToRelConverter converter = createSqlToRelConverter();
>>> RelRoot n = converter.convertQuery(node, true, true);
>>> RelNode relNode = n.rel;
>>>
>>> // TODO(madhavsuresh): only works with needsValidation set to true.
>>> RelTraitSet desiredTraits =
>>>
>>> relNode.getCluster().traitSet().replace(EnumerableConvention.INSTANCE);
>>> relNode = optimizer.changeTraits(relNode, desiredTraits);
>>> optimizer.setRoot(relNode);
>>> optimizer.findBestExp();
>>>
>>>
>>
>> Thanks for the help!
>>
>> Madhav
>>
Re: Volcano Optimizer JDBC Table Scan -> Join
Posted by Madhav Suresh <ma...@u.northwestern.edu>.
I apologize for the immediate follow up, however, I found out my issue was
not adding the EnumerableRules.ENUMERABLE_JOIN_RULE.
On Wed, Oct 2, 2019 at 10:50 AM Madhav Suresh <ma...@u.northwestern.edu>
wrote:
> Hi All,
>
> I'm trying to run the volcano optimizer on a simple join query with the
> TPC-H schema. I'm a little unclear on how to solve this, I thought I needed
> to add a converter rules from JDBC->Enumerable, but it seems like those
> rules are included at runtime. I then tried adding a None->Bindable rule,
> but that also didn't fix the issue. I get the error below, code included
> below error:
>
> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not
>> enough rules to produce a node with desired properties:
>> convention=ENUMERABLE.
>> Missing conversions are LogicalJoin[convention: NONE -> JDBC.name],
>> LogicalJoin[convention: NONE -> ENUMERABLE]
>> There are 2 empty subsets:
>> Empty subset 0: rel#51:Subset#4.ENUMERABLE, the relevant part of the
>> original plan is as follows
>> 14:LogicalJoin(condition=[true], joinType=[inner])
>> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
>> joinType=[inner])
>> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name],
>> table=[[customer]])
>> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
>> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>>
>> Empty subset 1: rel#49:Subset#4.JDBC.name, the relevant part of the
>> original plan is as follows
>> 14:LogicalJoin(condition=[true], joinType=[inner])
>> 11:LogicalJoin(subset=[rel#12:Subset#2.NONE], condition=[true],
>> joinType=[inner])
>> 0:JdbcTableScan(subset=[rel#9:Subset#0.JDBC.name],
>> table=[[customer]])
>> 1:JdbcTableScan(subset=[rel#10:Subset#1.JDBC.name], table=[[orders]])
>> 4:JdbcTableScan(subset=[rel#13:Subset#3.JDBC.name], table=[[lineitem]])
>>
>
>
> The test case I'm running is here (
> https://gist.github.com/madhavsuresh/e95631776eceb47ab8eeb608268531ce):
>
>> @Test
>> public void testSimpleJoin() throws SqlParseException,
>> ValidationException {
>> String sql =
>> "select\n"
>> + " l.l_orderkey\n"
>> + "from\n"
>> + " customer c,\n"
>> + " orders o,\n"
>> + " lineitem l\n"
>> + "\n"
>> + "where\n"
>> + " c.c_mktsegment = 'HOUSEHOLD'\n"
>> + " and c.c_custkey = o.o_custkey\n";
>> optimizer = new VolcanoPlanner();
>> optimizer.addRelTraitDef(ConventionTraitDef.INSTANCE);
>> optimizer.addRule(new OptToyRules.OptToyTestFilter());
>> // add rules
>> optimizer.addRule(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN);
>> optimizer.addRule(ReduceExpressionsRule.PROJECT_INSTANCE);
>> optimizer.addRule(PruneEmptyRules.PROJECT_INSTANCE);
>>
>> // add ConverterRule
>> optimizer.addRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
>> optimizer.addRule(EnumerableRules.ENUMERABLE_SORT_RULE);
>> optimizer.addRule(EnumerableRules.ENUMERABLE_VALUES_RULE);
>> optimizer.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE);
>> optimizer.addRule(EnumerableRules.ENUMERABLE_FILTER_RULE);
>> optimizer.addRule(NoneToBindableConverterRule.INSTANCE);
>> SqlNode node = planner.parse(sql);
>> node = planner.validate(node);
>> SqlToRelConverter converter = createSqlToRelConverter();
>> RelRoot n = converter.convertQuery(node, true, true);
>> RelNode relNode = n.rel;
>>
>> // TODO(madhavsuresh): only works with needsValidation set to true.
>> RelTraitSet desiredTraits =
>>
>> relNode.getCluster().traitSet().replace(EnumerableConvention.INSTANCE);
>> relNode = optimizer.changeTraits(relNode, desiredTraits);
>> optimizer.setRoot(relNode);
>> optimizer.findBestExp();
>>
>>
>
> Thanks for the help!
>
> Madhav
>