yashlimbad commented on code in PR #4840:
URL: https://github.com/apache/calcite/pull/4840#discussion_r3091944233
##########
core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java:
##########
@@ -1830,4 +1830,299 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalTableScan(table=[[scott, EMP]])\n";
assertThat(after, hasTree(planAfter));
}
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7442";>[CALCITE-7442]
+ * Getting Wrong index of Correlated variable inside Subquery after
FilterJoinRule</a>. */
+ @Test void testCorrelatedVariableIndexForInClause() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "select e.empno, d.dname, b.ename\n"
+ + "from emp e\n"
+ + "inner join dept d\n"
+ + " on d.deptno = e.deptno\n"
+ + "inner join bonus b\n"
+ + " on e.ename = b.ename\n"
+ + " and b.job in (\n"
+ + " select b2.job\n"
+ + " from bonus b2\n"
+ + " where b2.ename = b.ename)\n"
+ + "where e.sal > 1000 and d.dname = 'SALES'";
+
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(ImmutableList.of(CoreRules.FILTER_INTO_JOIN))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterFilterIntoJoin =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterFilterIntoJoin = "LogicalProject(EMPNO=[$0],
DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalJoin(condition=[AND(=($1, $11), IN($12, {\n"
+ + "LogicalProject(JOB=[$1])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.ENAME0)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + "}))], joinType=[inner], variablesSet=[[$cor0]])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterFilterIntoJoin, hasTree(planAfterFilterIntoJoin));
+
+ final HepProgram hepProgram1 = HepProgram.builder()
+
.addRuleCollection(ImmutableList.of(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program1 =
+ Programs.of(hepProgram1, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterJoinSubqueryCorrelate =
+ program1.run(cluster.getPlanner(), afterFilterIntoJoin,
cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterJoinSubqueryCorrelate =
+ "LogicalProject(EMPNO=[$0], DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4],"
+ + " SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8], DNAME=[$9],
LOC=[$10],"
+ + " ENAME0=[$11], JOB0=[$12], SAL0=[$13], COMM0=[$14])\n"
+ + " LogicalJoin(condition=[=($1, $11)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalFilter(condition=[=($1, $4)])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner],"
+ + " requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalProject(JOB=[$1])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.ENAME)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterJoinSubqueryCorrelate,
hasTree(planAfterJoinSubqueryCorrelate));
+
+ final RelNode afterDecorrelation =
+ RelDecorrelator.decorrelateQuery(afterJoinSubqueryCorrelate, builder,
+ RuleSets.ofList(Collections.emptyList()),
RuleSets.ofList(Collections.emptyList()));
+ final String planAfterDecorrelation = "LogicalProject(EMPNO=[$0],
DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalJoin(condition=[=($1, $11)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalJoin(condition=[AND(=($0, $5), =($1, $4))],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalProject(JOB=[$1], ENAME=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($0)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterDecorrelation, hasTree(planAfterDecorrelation));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7442";>[CALCITE-7442]
+ * Getting Wrong index of Correlated variable inside Subquery after
FilterJoinRule</a>.
+ * Same as {@link #testCorrelatedVariableIndexForInClause()} but uses EXISTS
+ * instead of IN. */
+ @Test void testCorrelatedVariableIndexForExistsClause() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "select e.empno, d.dname, b.ename\n"
+ + "from emp e\n"
+ + "inner join dept d\n"
+ + " on d.deptno = e.deptno\n"
+ + "inner join bonus b\n"
+ + " on e.ename = b.ename\n"
+ + " and exists (\n"
+ + " select b2.job\n"
+ + " from bonus b2\n"
+ + " where b2.ename = b.ename\n"
+ + " and b2.job = b.job)\n"
+ + "where e.sal > 1000 and d.dname = 'SALES'";
+
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(ImmutableList.of(CoreRules.FILTER_INTO_JOIN))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterFilterIntoJoin =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterFilterIntoJoin = "LogicalProject(EMPNO=[$0],
DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalJoin(condition=[AND(=($1, $11), EXISTS({\n"
+ + "LogicalFilter(condition=[AND(=($0, $cor0.ENAME0), =($1,
$cor0.JOB0))])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + "}))], joinType=[inner], variablesSet=[[$cor0]])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterFilterIntoJoin, hasTree(planAfterFilterIntoJoin));
+
+ final HepProgram hepProgram1 = HepProgram.builder()
+
.addRuleCollection(ImmutableList.of(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program1 =
+ Programs.of(hepProgram1, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterJoinSubqueryCorrelate =
+ program1.run(cluster.getPlanner(), afterFilterIntoJoin,
cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterJoinSubqueryCorrelate =
+ "LogicalProject(EMPNO=[$0], DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4],"
+ + " SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8], DNAME=[$9],
LOC=[$10],"
+ + " ENAME0=[$11], JOB0=[$12], SAL0=[$13], COMM0=[$14])\n"
+ + " LogicalJoin(condition=[=($1, $11)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner], "
+ + "requiredColumns=[{0, 1}])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalFilter(condition=[AND(=($0, $cor0.ENAME),
=($1, $cor0.JOB))])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterJoinSubqueryCorrelate,
hasTree(planAfterJoinSubqueryCorrelate));
+
+ final RelNode afterDecorrelation =
+ RelDecorrelator.decorrelateQuery(afterJoinSubqueryCorrelate, builder,
+ RuleSets.ofList(Collections.emptyList()),
RuleSets.ofList(Collections.emptyList()));
+ final String planAfterDecorrelation = "LogicalProject(EMPNO=[$0],
DNAME=[$9], ENAME=[$11])\n"
+ + " LogicalJoin(condition=[=($1, $11)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[=($8, $7)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2),
1000.00)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($1, 'SALES')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalJoin(condition=[AND(=($0, $4), =($1, $5))],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalProject(ENAME=[$0], JOB=[$1], $f2=[true])\n"
+ + " LogicalFilter(condition=[AND(IS NOT NULL($0), IS NOT
NULL($1))])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(afterDecorrelation, hasTree(planAfterDecorrelation));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7442";>[CALCITE-7442]
+ * Getting Wrong index of Correlated variable inside Subquery after
FilterJoinRule</a>.
+ * Same as {@link #testCorrelatedVariableIndexForInClause()} EXISTS edge
case */
+ @Test void testCorrelatedVariableIndexForExistsClause2() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "select e1.empno \n"
+ + "from emp e1, \n"
+ + "lateral(\n"
+ + " select d.deptno \n"
+ + " from emp e2 inner join dept d \n"
+ + " on exists(\n"
+ + " select e3.empno from emp e3 where e3.empno = e2.empno and
e3.ename = e1.ename\n"
+ + " )\n"
+ + ")";
+
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(ImmutableList.of(CoreRules.JOIN_CONDITION_PUSH))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterJoinConditionPush =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterJoinConditionPush = "LogicalProject(EMPNO=[$0])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{1}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(DEPTNO=[$8])\n"
+ + " LogicalJoin(condition=[EXISTS({\n"
+ + "LogicalFilter(condition=[AND(=($0, $cor0.EMPNO), =($1,
$cor1.ENAME))])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + "})], joinType=[inner], variablesSet=[[$cor0]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(afterJoinConditionPush, hasTree(planAfterJoinConditionPush));
+
+ final HepProgram hepProgram1 = HepProgram.builder()
+
.addRuleCollection(ImmutableList.of(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program1 =
+ Programs.of(hepProgram1, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode afterJoinSubqueryCorrelate =
+ program1.run(cluster.getPlanner(), afterJoinConditionPush,
cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+
+ final String planAfterJoinSubqueryCorrelate =
"LogicalProject(EMPNO=[$0])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{1}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(DEPTNO=[$8])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4],"
+ + " SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$9], DNAME=[$10],
LOC=[$11])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner],"
+ + " variablesSet=[[$cor0, $cor1]])\n"
Review Comment:
got it, will squash my commits
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
