[jira] [Created] (CALCITE-7302) Infinite loop with JoinPushTransitivePredicatesRule

["Nirmal Govindaraj (Jira)" <ji...@apache.org>]

Nirmal Govindaraj created CALCITE-7302:
------------------------------------------

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


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 duplication was bypassed because the inference is down 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

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)