[
https://issues.apache.org/jira/browse/CALCITE-6432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6432:
------------------------------------
Labels: pull-request-available (was: )
> Infinite loop for JoinPushTransitivePredicatesRule when there are multiple
> project expressions reference the same input field
> -----------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6432
> URL: https://issues.apache.org/jira/browse/CALCITE-6432
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: asdfgh19
> Assignee: asdfgh19
> Priority: Minor
> Labels: pull-request-available
> Attachments: 1.png, 2.png
>
>
> Below is a newly created test case that is stuck in an infinite loop.
> {code:java}
> @Test void testProjectPredicatePull() {
> final String sql = "select e.ename, d.dname\n"
> + "from (select ename, deptno from emp where deptno = 10) e\n"
> + "join (select name dname, deptno, * from dept) d\n"
> + "on e.deptno = d.deptno";
> final HepProgram program = new HepProgramBuilder()
> .addRuleCollection(
> ImmutableList.of(CoreRules.FILTER_PROJECT_TRANSPOSE,
> CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES))
> .build();
> sql(sql).withProgram(program).check();
> } {code}
> !1.png|width=563,height=192!
> From the figure above, we can see the process of this infinite loop:
> # JOIN_PUSH_TRANSITIVE_PREDICATES infers DEPTNO = 10 from the left side of
> the Join
> # JOIN_PUSH_TRANSITIVE_PREDICATES creates a new Filter DEPTNO = 10 on the
> right side of the Join
> # FILTER_PROJECT_TRANSPOSE pushes this newly created Filter past the Project
> # JOIN_PUSH_TRANSITIVE_PREDICATES infers DEPTNO = 10 from the left side of
> the Join and infers {color:#FF0000}DEPTNO0 = 10{color} from the right side of
> the Join，Note that it is DEPTNO0, not DEPTNO
> # So, JOIN_PUSH_TRANSITIVE_PREDICATES creates a new Filter DEPTNO = 10 and
> put it to the right side of the Join again. And then the process keeps
> repeating.
> The reason is that when RelMdPredicates infers predicates for a project,
> and the project has multiple expressions referencing the same input field,
> for example, DEPT=$0 DEPT0=$0, and the input predicate is $0=10, the inferred
> result is DEPT0=10, which means that the predicates related to other
> expressions with the same input field (DEPT=10) are lost, leaving only the
> last one.
>
> Below is the test case from RelOptRulesTest and its planAfter.
> {code:java}
> @Test void testJoinPushTransitivePredicatesRule2() {
> final String sql = "select n1.SAL\n"
> + "from EMPNULLABLES_20 n1\n"
> + "where n1.SAL IN (\n"
> + " select n2.SAL\n"
> + " from EMPNULLABLES_20 n2\n"
> + " where n1.SAL = n2.SAL or n1.SAL = 4)";
> sql(sql).withDecorrelate(true)
> .withExpand(true)
> .withRule(CoreRules.FILTER_INTO_JOIN,
> CoreRules.JOIN_CONDITION_PUSH,
> CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES)
> .check();
> }{code}
> !2.png|width=563,height=175!
> In the case where the left side of the Join already has an identical
> predicate (>($5, 1000)), JOIN_PUSH_TRANSITIVE_PREDICATES infers a new
> predicate (LogicalFilter(condition=[>($5, 1000)])) from the right side of the
> Join and puts it on the left side of the Join. This is because the Project on
> the left side of the Join has SAL=[$5], SAL0=[$5], and
> JOIN_PUSH_TRANSITIVE_PREDICATES fails to find this predicate, so it creates a
> new one.
> If we add a new rule CoreRules.FILTER_PROJECT_TRANSPOSE to this test case
> and modify it slightly, it will also fall into an infinite loop.
> {code:java}
> @Test void testJoinPushTransitivePredicatesRule2() {
> final String sql = "select n1.SAL\n"
> + "from EMPNULLABLES_20 n1\n"
> + "where n1.SAL IN (\n"
> + " select n2.SAL\n"
> + " from EMPNULLABLES_20 n2\n"
> + " where n1.SAL = n2.SAL or n1.SAL = 4)";
> final HepProgram program = new HepProgramBuilder()
> .addRuleCollection(
> ImmutableList.of(CoreRules.FILTER_INTO_JOIN,
> CoreRules.FILTER_PROJECT_TRANSPOSE,
> CoreRules.JOIN_CONDITION_PUSH,
> CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES))
> .build();
> sql(sql).withDecorrelate(true)
> .withExpand(true)
> .withProgram(program)
> .check();
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
