You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Xiaoying Wang <wa...@gmail.com> on 2022/03/25 07:07:48 UTC
JDBC join order question
Hi all,
I have a query joins three tables (star schema, T is the fact
table): "select * from R, S, T where R.key = T.key and S.key = T.key" and
want to use calcite to parse and generate the physical plan.
When I pass the query to a volcano planner, it throws the
CannotPlanException:
Exception in thread "main"
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.DB1] (2 cases)
There are 2 empty subsets:
Empty subset 0: rel#34:RelSubset#2.JDBC.DB1, the relevant part of the
original plan is as follows
14:LogicalJoin(condition=[true], joinType=[inner])
1:JdbcTableScan(subset=[rel#12:RelSubset#0.JDBC.DB1], table=[[DB1, R]])
3:JdbcTableScan(subset=[rel#13:RelSubset#1.JDBC.DB1], table=[[DB1, S]])
Empty subset 1: rel#36:RelSubset#4.JDBC.DB1, the relevant part of the
original plan is as follows
17:LogicalJoin(condition=[true], joinType=[inner])
14:LogicalJoin(subset=[rel#15:RelSubset#2.NONE], condition=[true],
joinType=[inner])
1:JdbcTableScan(subset=[rel#12:RelSubset#0.JDBC.DB1], table=[[DB1, R]])
3:JdbcTableScan(subset=[rel#13:RelSubset#1.JDBC.DB1], table=[[DB1, S]])
7:JdbcTableScan(subset=[rel#16:RelSubset#3.JDBC.DB1], table=[[DB1, T]])
I tried to rewrite the query, and found that as long as T is not the last
relation (e.g. "select * from T, R, S where R.key = T.key and S.key =
T.key"), there is no error. I think it means that the join order in the
initial logical plan makes the difference, so I tried to add the
JoinAssociateRule. However, I found this rule was not applied by the
optimizer because of the convocation not match. Using the above example,
for 17:LogicalJoin the convocation of 14:LogicalJoin and 7:JdbcTableScan
are not the same ( at here:
https://github.com/apache/calcite/blob/e42b85a45bd16dd58db1546736e653deda5463fe/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoRuleCall.java#L358
).
These are the rules in the planner:
[FilterIntoJoinRule, JoinAssociateRule, JoinCommuteRule,
JdbcToEnumerableConverterRule(in:JDBC.DB1,out:ENUMERABLE),
JdbcJoinRule(in:NONE,out:JDBC.DB1), JdbcProjectRule(in:NONE,out:JDBC.DB1),
JdbcFilterRule(in:NONE,out:JDBC.DB1),
JdbcAggregateRule(in:NONE,out:JDBC.DB1),
JdbcSortRule(in:NONE,out:JDBC.DB1), JdbcUnionRule(in:NONE,out:JDBC.DB1),
JdbcIntersectRule(in:NONE,out:JDBC.DB1),
JdbcMinusRule(in:NONE,out:JDBC.DB1),
JdbcTableModificationRule(in:NONE,out:JDBC.DB1),
JdbcValuesRule(in:NONE,out:JDBC.DB1), FilterSetOpTransposeRule,
ProjectRemoveRule]
I'm very new to Calcite. I'm not sure whether I missed some rules or I have
to implement something to make it work. Can anyone help me on this?
Best,
Xiaoying
Re: JDBC join order question
Posted by Benchao Li <li...@apache.org>.
Hi Xiaoying,
You can try to add `JoinConditionPushRule`[1] to solve the problem.
According the rules you provide, you only have `FilterIntoJoinRule`,
hence the filters will only be push into the second Join.
The exception you see indicates that the first Join has no condition,
and it cannot be handled by ENUMERABLE convention as you can see here[2]
[1]
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java#L281
[2]
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java#L304
Xiaoying Wang <wa...@gmail.com> 于2022年3月26日周六 04:46写道：
> Hi all,
>
>
> I have a query joins three tables (star schema, T is the fact
> table): "select * from R, S, T where R.key = T.key and S.key = T.key" and
> want to use calcite to parse and generate the physical plan.
>
>
> When I pass the query to a volcano planner, it throws the
> CannotPlanException:
>
>
> Exception in thread "main"
> 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.DB1] (2 cases)
>
> There are 2 empty subsets:
> Empty subset 0: rel#34:RelSubset#2.JDBC.DB1, the relevant part of the
> original plan is as follows
> 14:LogicalJoin(condition=[true], joinType=[inner])
> 1:JdbcTableScan(subset=[rel#12:RelSubset#0.JDBC.DB1], table=[[DB1, R]])
> 3:JdbcTableScan(subset=[rel#13:RelSubset#1.JDBC.DB1], table=[[DB1, S]])
>
> Empty subset 1: rel#36:RelSubset#4.JDBC.DB1, the relevant part of the
> original plan is as follows
> 17:LogicalJoin(condition=[true], joinType=[inner])
> 14:LogicalJoin(subset=[rel#15:RelSubset#2.NONE], condition=[true],
> joinType=[inner])
> 1:JdbcTableScan(subset=[rel#12:RelSubset#0.JDBC.DB1], table=[[DB1, R]])
> 3:JdbcTableScan(subset=[rel#13:RelSubset#1.JDBC.DB1], table=[[DB1, S]])
> 7:JdbcTableScan(subset=[rel#16:RelSubset#3.JDBC.DB1], table=[[DB1, T]])
>
>
>
> I tried to rewrite the query, and found that as long as T is not the last
> relation (e.g. "select * from T, R, S where R.key = T.key and S.key =
> T.key"), there is no error. I think it means that the join order in the
> initial logical plan makes the difference, so I tried to add the
> JoinAssociateRule. However, I found this rule was not applied by the
> optimizer because of the convocation not match. Using the above example,
> for 17:LogicalJoin the convocation of 14:LogicalJoin and 7:JdbcTableScan
> are not the same ( at here:
>
> https://github.com/apache/calcite/blob/e42b85a45bd16dd58db1546736e653deda5463fe/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoRuleCall.java#L358
> ).
>
>
> These are the rules in the planner:
>
> [FilterIntoJoinRule, JoinAssociateRule, JoinCommuteRule,
> JdbcToEnumerableConverterRule(in:JDBC.DB1,out:ENUMERABLE),
> JdbcJoinRule(in:NONE,out:JDBC.DB1), JdbcProjectRule(in:NONE,out:JDBC.DB1),
> JdbcFilterRule(in:NONE,out:JDBC.DB1),
> JdbcAggregateRule(in:NONE,out:JDBC.DB1),
> JdbcSortRule(in:NONE,out:JDBC.DB1), JdbcUnionRule(in:NONE,out:JDBC.DB1),
> JdbcIntersectRule(in:NONE,out:JDBC.DB1),
> JdbcMinusRule(in:NONE,out:JDBC.DB1),
> JdbcTableModificationRule(in:NONE,out:JDBC.DB1),
> JdbcValuesRule(in:NONE,out:JDBC.DB1), FilterSetOpTransposeRule,
> ProjectRemoveRule]
>
>
> I'm very new to Calcite. I'm not sure whether I missed some rules or I have
> to implement something to make it work. Can anyone help me on this?
>
> Best,
>
> Xiaoying
>
--
Best,
Benchao Li