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)
