Hi Benchao, Thank you so much for the help!
This rule looks very promising. I will also try to add this on my side and see whether it works. Best, Xiaoying On Tue, Mar 29, 2022 at 2:24 AM Benchao Li <[email protected]> wrote: > 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 >
