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 > >> > >
