This is an automated email from the ASF dual-hosted git repository.
silun pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new c46978659b [CALCITE-7442] Correlated variable has wrong index inside
subquery
c46978659b is described below
commit c46978659bb997241c4aef0e193b34746770d731
Author: Yash Limbad <[email protected]>
AuthorDate: Wed Mar 18 16:26:45 2026 +0530
[CALCITE-7442] Correlated variable has wrong index inside subquery
---
.../java/org/apache/calcite/plan/RelOptUtil.java | 87 +++++-
.../org/apache/calcite/rel/rules/CoreRules.java | 2 +-
.../main/java/org/apache/calcite/rex/RexUtil.java | 49 +++-
.../calcite/sql2rel/RelDecorrelatorTest.java | 295 +++++++++++++++++++++
4 files changed, 423 insertions(+), 10 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index 274e91f14d..9b24cf2ad2 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -2934,15 +2934,39 @@ public static boolean classifyFilters(
ImmutableBitSet rightBitmap =
ImmutableBitSet.range(nSysFields + nFieldsLeft, nTotalFields);
+ // Correlation variables introduced by this join itself: i.e. ids whose
+ // binding is established by *this* join. A predicate that references any
+ // of these cannot be pushed to either input -- the binder lives on the
+ // join, so pushing the reference below it would strand the variable.
+ // Such predicates must stay on the join itself.
+ final Set<CorrelationId> joinCorrelationIds = joinRel instanceof Join
+ ? joinRel.getVariablesSet()
+ : ImmutableSet.of();
+
final List<RexNode> filtersToRemove = new ArrayList<>();
for (RexNode filter : filters) {
- final InputFinder inputFinder = InputFinder.analyze(filter);
+
+ // Only consider correlation ids bound by *this* join when computing
+ // the input bitmap of a sub-query inside the predicate. Foreign
+ // correlation ids are bound by an outer scope and their
+ // correlationColumns indices would otherwise alias onto unrelated
+ // columns of this join's row type, mis-classifying the predicate.
+ final InputFinder inputFinder = InputFinder.analyze(filter,
joinCorrelationIds);
final ImmutableBitSet inputBits = inputFinder.build();
+ // Block pushing to either input for filters that reference a
+ // CorrelationId bound by this join; they must remain on the join.
+ // pushing down correlated subqueries carries risks and involves
extremely complex logic,
+ // and therefore pushing down is prohibited.
+ final boolean blockPush =
+ RexUtil.containsCorrelation(filter, joinCorrelationIds);
+ final boolean effectivePushLeft = pushLeft && !blockPush &&
leftBitmap.contains(inputBits);
+ final boolean effectivePushRight = pushRight && !blockPush &&
rightBitmap.contains(inputBits);
+
// REVIEW - are there any expressions that need special handling
// and therefore cannot be pushed?
- if (pushLeft && leftBitmap.contains(inputBits)) {
+ if (effectivePushLeft) {
// ignore filters that always evaluate to true
if (!filter.isAlwaysTrue()) {
// adjust the field references in the filter to reflect
@@ -2962,7 +2986,7 @@ public static boolean classifyFilters(
leftFilters.add(shiftedFilter);
}
filtersToRemove.add(filter);
- } else if (pushRight && rightBitmap.contains(inputBits)) {
+ } else if (effectivePushRight) {
if (!filter.isAlwaysTrue()) {
// adjust the field references in the filter to reflect
// that fields in the right now shift over to the left
@@ -4678,12 +4702,29 @@ public RexCorrelVariableMapShuttle(final CorrelationId
correlationId,
public static class InputFinder extends RexVisitorImpl<Void> {
private final ImmutableBitSet.Builder bitBuilder;
private final @Nullable Set<RelDataTypeField> extraFields;
+ /** Correlation ids whose binder is the current scope. When non-null,
+ * {@link #visitSubQuery} projects bits for each id in this set by looking
+ * up its {@code correlationColumns} against the sub-query's inner plan
+ * and adding those column indices to the bitmap. Correlation ids bound
+ * by an outer scope are skipped, since their column indices are relative
+ * to a foreign row type and would otherwise alias onto unrelated columns
+ * of the current scope. When null, {@link #visitSubQuery} contributes no
+ * correlation-related bits and simply descends into the sub-query's
+ * operands (legacy behaviour). */
+ private final @Nullable Set<CorrelationId> localCorrelationIds;
private InputFinder(@Nullable Set<RelDataTypeField> extraFields,
- ImmutableBitSet.Builder bitBuilder) {
+ ImmutableBitSet.Builder bitBuilder,
+ @Nullable Set<CorrelationId> localCorrelationIds) {
super(true);
this.bitBuilder = bitBuilder;
this.extraFields = extraFields;
+ this.localCorrelationIds = localCorrelationIds;
+ }
+
+ private InputFinder(@Nullable Set<RelDataTypeField> extraFields,
+ ImmutableBitSet.Builder bitBuilder) {
+ this(extraFields, bitBuilder, null);
}
public InputFinder() {
@@ -4706,6 +4747,22 @@ public static InputFinder analyze(RexNode node) {
return inputFinder;
}
+ /** Returns an input finder that has analyzed a given expression,
+ * treating {@code localCorrelationIds} as the set of correlation ids
+ * bound by the current scope. For each nested {@link RexSubQuery},
+ * any correlation id used inside it that belongs to this set
+ * contributes its {@code correlationColumns} indices to the bitmap;
+ * correlation ids bound by an outer scope are ignored, because their
+ * indices are relative to a foreign row type and would otherwise
+ * alias onto unrelated columns of the current scope. */
+ public static InputFinder analyze(RexNode node,
+ Set<CorrelationId> localCorrelationIds) {
+ final InputFinder inputFinder =
+ new InputFinder(null, ImmutableBitSet.builder(),
localCorrelationIds);
+ node.accept(inputFinder);
+ return inputFinder;
+ }
+
/**
* Returns a bit set describing the inputs used by an expression.
*/
@@ -4752,6 +4809,28 @@ public ImmutableBitSet build() {
}
return super.visitCall(call);
}
+
+ @Override public Void visitSubQuery(RexSubQuery subQuery) {
+ if (localCorrelationIds == null) {
+ return super.visitSubQuery(subQuery);
+ }
+
+ final Set<CorrelationId> variablesSet =
RelOptUtil.getVariablesUsed(subQuery.rel);
+ for (CorrelationId id : variablesSet) {
+ // Skip correlation ids that are not bound by the *current* scope.
+ // Their requiredColumns indices are relative to whichever outer
+ // RelNode produces them and would otherwise alias onto unrelated
+ // columns of the current row type.
+ if (!localCorrelationIds.contains(id)) {
+ continue;
+ }
+ ImmutableBitSet requiredColumns = RelOptUtil.correlationColumns(id,
subQuery.rel);
+ for (int index : requiredColumns) {
+ bitBuilder.set(index);
+ }
+ }
+ return super.visitSubQuery(subQuery);
+ }
}
/**
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 444c89ccdd..0337b1d5ab 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -284,7 +284,7 @@ private CoreRules() {}
* {@link org.apache.calcite.rel.rules.FilterProjectTransposeRule}.
*
* <p>It does not allow a Filter to be pushed past the Project if
- * {@link RexUtil#containsCorrelation there is a correlation condition}
+ * {@link RexUtil#containsCorrelation(org.apache.calcite.rex.RexNode) there
is a correlation condition}
* anywhere in the Filter, since in some cases it can prevent a
* {@link Correlate} from being de-correlated.
*/
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index 8c45ffc97a..3604e98dfd 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -2412,6 +2412,23 @@ public static boolean containsCorrelation(RexNode
condition) {
}
}
+ /** Returns whether an expression references a {@link RexCorrelVariable}
+ * whose id is in {@code ids}, either directly (typically through a
+ * {@link RexFieldAccess}) or transitively via the inner plan of a
+ * {@link RexSubQuery}. */
+ public static boolean containsCorrelation(RexNode condition,
+ Set<CorrelationId> ids) {
+ if (ids.isEmpty()) {
+ return false;
+ }
+ try {
+ condition.accept(new CorrelationFinder(ids));
+ return false;
+ } catch (Util.FoundOne e) {
+ return true;
+ }
+ }
+
/**
* Given an expression, it will swap the table references contained in its
* {@link RexTableInputRef} using the contents in the map.
@@ -3116,14 +3133,28 @@ private static class RexShiftShuttle extends RexShuttle
{
/** Visitor that throws {@link org.apache.calcite.util.Util.FoundOne} if
* applied to an expression that contains a {@link RexCorrelVariable}. */
private static class CorrelationFinder extends RexVisitorImpl<Void> {
- static final CorrelationFinder INSTANCE = new CorrelationFinder();
+ static final CorrelationFinder INSTANCE = new CorrelationFinder(null);
- private CorrelationFinder() {
+ /** Optional filter: when non-null, only correlation ids in this set
+ * trigger a match; when null, every correlation id matches. */
+ private final @Nullable Set<CorrelationId> ids;
+
+ /**
+ * Creates a CorrelationFinder.
+ *
+ * @param ids correlation ids to look for; pass {@code null} to match any
+ * {@link RexCorrelVariable} regardless of its id
+ */
+ private CorrelationFinder(@Nullable Set<CorrelationId> ids) {
super(true);
+ this.ids = ids;
}
@Override public Void visitCorrelVariable(RexCorrelVariable var) {
- throw Util.FoundOne.NULL;
+ if (ids == null || ids.contains(var.id)) {
+ throw Util.FoundOne.NULL;
+ }
+ return null;
}
@Override public Void visitSubQuery(RexSubQuery subQuery) {
@@ -3135,8 +3166,16 @@ private CorrelationFinder() {
operand.accept(this);
}
- if (!RelOptUtil.getVariablesUsed(subQuery.rel).isEmpty()) {
- throw Util.FoundOne.NULL;
+ Set<CorrelationId> used = RelOptUtil.getVariablesUsed(subQuery.rel);
+ if (!used.isEmpty()) {
+ if (ids == null) {
+ throw Util.FoundOne.NULL;
+ }
+ for (CorrelationId id : used) {
+ if (ids.contains(id)) {
+ throw Util.FoundOne.NULL;
+ }
+ }
}
return null;
}
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
index 2b406c3c01..e91b138861 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/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"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalFilter(condition=[AND(=($0, $cor0.EMPNO),
=($1, $cor1.ENAME))])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\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])\n"
+ + " LogicalJoin(condition=[=($1, $10)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(DEPTNO=[$11], EMPNO0=[$8], ENAME0=[$9])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], $f2=[true])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($1)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(afterDecorrelation, hasTree(planAfterDecorrelation));
+ }
}
