[
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
deduplication was bypassed because the inference is down individually on the
rexnodes formed after decomposing the conjunction
(RelOptUtil.conjunctions(predicates)). There is code to guard against these
cases by applying simplify on individual rexnodes formed and checking with that
but this is caused due to applying simplify on the full predicate as a whole.
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
deduplication was bypassed because the inference is down individually on the
rexnodes formed after decomposing the conjunction
(RelOptUtil.conjunctions(predicates)). There is code to guard against these
cases by applying simplify on individual rexnodes formed and checking witht
that but this is caused due to applying simplify on the full predicate as a
whole. 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
> deduplication was bypassed because the inference is down individually on the
> rexnodes formed after decomposing the conjunction
> (RelOptUtil.conjunctions(predicates)). There is code to guard against these
> cases by applying simplify on individual rexnodes formed and checking with
> that but this is caused due to applying simplify on the full predicate as a
> whole. 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)
