[
https://issues.apache.org/jira/browse/CALCITE-7302?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18040035#comment-18040035
]
Alessandro Solimando commented on CALCITE-7302:
-----------------------------------------------
Your analysis is spot on, unfortunately this is a known issue, as you correctly
noticed, the rule is based on a fix point depending on simplification, as soon
as we have redundant predicates with a different syntax that our simplyfier
can't handle, it goes into an infinite loop (see
https://calcite.apache.org/community/#debugging-planning-issues-using-calcites-built-in-loggers,
there a few examples).
SARG is a relatively recent (and great IMO) introduction so we still find
places that need improvement, there are recent Jira tickets in this area that
you can check.
The only long term solution I see is to equip the rule with a safeguard
mechanism like the "bloat" parameter we use in some transposition rules, and
here it would be on the predicates (not sure exactly what would make most
sense, we would need to think a bit on it), so that the fix point is either
"nothing can be added" or "we have a too complex already expression, stop
adding", whichever comes first
The second part will play well with simplification rules as they are free to
reduce the "complexity", produce a different rel, the planner will try to apply
this rule to it and might pass on the simplified expression, so it won't affect
cases where we can keep adding and simplify later.
Would you be interested in contributing such a 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: 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)
