Hi Xiaoying, Sorry for the misleading. (I was debugging this problem in Flink, and I did miss one rule[1] which did the critical work. It converts the join condition from `R.key = T.key and S.key = T.key` to `R.key = S.key and R.key = T.key`. After that, the condition can be pushed below.)
Then I think `FilterJoinRule` in Calcite should be improved about this. I will open a jira issue and try to fix this. [1] https://github.com/apache/flink/blob/master/flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/plan/rules/logical/JoinConditionEqualityTransferRule.scala Xiaoying Wang <[email protected]> 于2022年3月29日周二 03:30写道： > Hi Julian, > > Oh, I see. Thank you for the hint! I just subscribed to the list. > > Best, > Xiaoying > > On Mon, Mar 28, 2022 at 11:53 AM Julian Hyde <[email protected]> > wrote: > > > Xiaoying, I know why you didn’t receive the email. Your reply went > through > > moderation, which indicates that you are not subscribed to dev@calcite. > > Please subscribe. > > > > > On Mar 28, 2022, at 11:10 AM, Xiaoying Wang < > [email protected]> > > wrote: > > > > > > 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 > > >> > > > > > -- Best, Benchao Li
