Hi Benchao, Thank you so much for the reply! I don't know why I didn't receive the email but I found your answer at the archive website.
I tried to add the `JoinConditionPushRule` as you suggested but the filter cannot be pushed from the second join to the first, and the code throws the same error. It is because the initial state of my query is `(R join1 S) join2 T`, and the conditions pushed to join2 by `FilterIntoJoinRule` are `R.key = T.key and S.key = T.key`. Neither `R.key = T.key` nor `S.key = T.key` can be pushed into join1 because they both require `T.key`. So both `leftBitmap.contains(inputBits)` and `rightBitmap.contains(inputBits)` at [1] are false and the condition cannot be pushed down into the first join. Do you think I need to add other rules to solve the problem? (I tried to add several rules to alter the join order including `JoinAssociateRule`, `JoinCommuteRule`, `JoinPushThroughJoinRule.RIGHT/LEFT` but none of them were fired.) Best, Xiaoying [1] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java#L2838 On 2022/03/27 00:41:57 Benchao Li wrote: > 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 <[email protected]> 于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 >
