[
https://issues.apache.org/jira/browse/CALCITE-7302?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nirmal Govindaraj updated CALCITE-7302:
---------------------------------------
Description:
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
was:
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
> 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: Major
>
> 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)
