[
https://issues.apache.org/jira/browse/CALCITE-7302?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18040051#comment-18040051
]
Zhen Chen commented on CALCITE-7302:
------------------------------------
I'm trying to look into this issue. Can we attempt to find a simple way to fix
this type of comparison in different forms (perhaps focusing on SEARCH for now)
to temporarily resolve this problem?
> Infinite loop with JoinPushTransitivePredicatesRule
> ---------------------------------------------------
>
> Key: CALCITE-7302
> URL: https://issues.apache.org/jira/browse/CALCITE-7302
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.41.0
> Reporter: Nirmal Govindaraj
> Priority: Minor
>
> The following testcase in RelOptRulesTest leads to an infinite loop:
> {code:java}
> final String sql = "With dept_temp as (SELECT deptno, name FROM dept where
> deptno between 30 and 50),"
> + "emp_temp as (Select ename, deptno from emp)"
> + "select * from dept_temp inner join emp_temp on dept_temp.deptno =
> emp_temp.deptno ";
> sql(sql).withRule(CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES)
> .check(); {code}
> The root cause is essentially due to a kind of mismatch caused due to
> simplify. The beforePlan for this query is the following
> {code:java}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], DEPTNO0=[$3])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalFilter(condition=[AND(>=($0, 30), <=($0, 50))])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(ENAME=[$1], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> The leftnode of this plan has a condition AND(>=($0, 30), <=($0, 50)) and
> therefore we should derive AND(>=($1, 30), <=($1, 50)) for the rightnode. But
> what happens is that on the first time JoinPushTransitivePredicatesRule is
> applied on the join node it successfully derives the AND(>=($1, 30), <=($1,
> 50)) condition and it shows up in rightInferredPredicates. However when we do
> {code:java}
> right =
> relBuilder.push(right).filter(preds.rightInferredPredicates).build(){code}
> To push the newly derived AND condition into the rightnode the simplifier
> converts the AND condition to SEARCH($1, Sarg[[30..50]]). Now when
> JoinPushTransitivePredicatesRule is applied on the join node for the second
> time it again infers AND(>=($1, 30), <=($1, 50)) for the rightnode. The
> deduplication was bypassed because the inference is done individually on the
> rexnodes formed after decomposing the conjunction
> (RelOptUtil.conjunctions(predicates)). So we infer >=($1, 30) and <=($1, 50)
> individually but neither of them are equal to SEARCH($1, Sarg[[30..50]])
> pulled up from the rightnode so the deduplication fails and it ends up
> inferring the same predicate again. This causes a loop that keeps repeating.
> There is code to guard against these cases by applying simplify on individual
> rexnodes formed and checking with that but this issue is caused due to
> applying simplify on the full predicate as a whole for which there is no guard
> One solution is to use a version of this rule with simplify disabled:
> {code:java}
> JoinPushTransitivePredicatesRule.Config.DEFAULT.withRelBuilderFactory(RelBuilder.proto(
> Contexts.of(RelBuilder.Config.DEFAULT.withSimplify(false))
> )).toRule() {code}
> But I am not sure if this is a long term fix
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
