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)
