asdfgh19 created CALCITE-6432: --------------------------------- Summary: 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 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)