This is an automated email from the ASF dual-hosted git repository.
zwh 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 69a7a06025 [CALCITE-5390] RelDecorrelator throws NullPointerException
69a7a06025 is described below
commit 69a7a060259153b2c2a105cea13b9d0fb588772e
Author: iwanttobepowerful <[email protected]>
AuthorDate: Wed Jan 28 22:29:15 2026 +0800
[CALCITE-5390] RelDecorrelator throws NullPointerException
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 46 ++++-
.../sql2rel/TopDownGeneralDecorrelator.java | 3 -
.../calcite/sql2rel/RelDecorrelatorTest.java | 89 +++++++++
core/src/test/resources/sql/sub-query.iq | 75 ++++++++
.../org/apache/calcite/adapter/tpch/TpchTest.java | 212 +++++++++++++++++++++
5 files changed, 418 insertions(+), 7 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 47c0040fc3..45596c1d7d 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -918,7 +918,7 @@ protected RexNode removeCorrelationExpr(
* CASE WHEN cnt0 IS NOT NULL THEN cnt0 ELSE 0 END AS cnt
* FROM (SELECT deptno FROM dept GROUP BY deptno) d2
* LEFT JOIN (
- * SELECT deptno, COUNT(e.empno) cnt0
+ * SELECT deptno, COUNT(emp.empno) cnt0
* FROM emp
* WHERE deptno IS NOT NULL
* GROUP BY deptno) e
@@ -1421,7 +1421,8 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
for (CorRef corVar : correlations) {
final int oldCorVarOffset = corVar.field;
- final RelNode oldInput = requireNonNull(getCorRel(corVar));
+ final RelNode oldInput = findInputRel(corVar);
+
final Frame frame = requireNonNull(getOrCreateFrame(oldInput));
final RelNode newInput = frame.r;
@@ -1453,7 +1454,7 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
RelNode r = null;
for (CorRef corVar : correlations) {
- final RelNode oldInput = requireNonNull(getCorRel(corVar));
+ final RelNode oldInput = findInputRel(corVar);
final RelNode newInput = requireNonNull(getOrCreateFrame(oldInput).r);
if (!joinedInputs.contains(newInput)) {
@@ -1487,7 +1488,7 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
for (CorRef corRef : correlations) {
// The first input of a Correlate is always the rel defining
// the correlated variables.
- final RelNode oldInput = requireNonNull(getCorRel(corRef));
+ final RelNode oldInput = findInputRel(corRef);
final Frame frame = getOrCreateFrame(oldInput);
final RelNode newInput = requireNonNull(frame.r);
@@ -1533,6 +1534,39 @@ private RelNode getCorRel(CorRef corVar) {
() -> "r.getInput(0) is null for " + r);
}
+ /**
+ * Finds the RelNode that produces the given correlation variable.
+ *
+ * <p>This method resolves correlation variables by inspecting the {@link
#frameStack},
+ * which maintains the active correlation contexts during the top-down
traversal.
+ *
+ * <p>The lookup logic implements <b>Lexical Scoping</b> (with Shadowing):
+ * <ul>
+ * <li>The {@code frameStack} is traversed from top to bottom (most
recently pushed to
+ * least recently pushed). This ensures that if multiple nested
queries use the same
+ * {@link CorrelationId}, the innermost definition takes precedence,
shadowing outer ones.
+ * </li>
+ * </ul>
+ *
+ * <p>If the variable is not found in the {@code frameStack} (e.g., it might
be defined outside
+ * the current traversal path or in a global context), the method falls back
to looking it up
+ * in the global {@link #cm} (CorelMap).
+ *
+ * @param corVar The correlation variable reference to resolve.
+ * @return The {@link RelNode} that produces the correlation variable.
+ */
+ private RelNode findInputRel(CorRef corVar) {
+ final int oldCorVarOffset = corVar.field;
+ for (Pair<CorrelationId, Frame> pair : frameStack) {
+ if (pair.left.equals(corVar.corr)) {
+ if (oldCorVarOffset < pair.right.oldRel.getRowType().getFieldCount()) {
+ return pair.right.oldRel;
+ }
+ }
+ }
+ return getCorRel(corVar);
+ }
+
/** Adds a value generator to satisfy the correlating variables used by
* a relational expression, if those variables are not already provided by
* its input. */
@@ -3766,12 +3800,16 @@ private RexVisitorImpl<Void> rexVisitor(final RelNode
rel) {
* and where to find the output fields and correlation variables
* among its output fields. */
static class Frame {
+ // The original relational expression before decorrelation
+ final RelNode oldRel;
+ // The decorrelated relational expression
final RelNode r;
final ImmutableSortedMap<CorDef, Integer> corDefOutputs;
final ImmutableSortedMap<Integer, Integer> oldToNewOutputs;
Frame(RelNode oldRel, RelNode r, NavigableMap<CorDef, Integer>
corDefOutputs,
Map<Integer, Integer> oldToNewOutputs) {
+ this.oldRel = requireNonNull(oldRel, "oldRel");
this.r = requireNonNull(r, "r");
this.corDefOutputs = ImmutableSortedMap.copyOf(corDefOutputs);
this.oldToNewOutputs = ImmutableSortedMap.copyOf(oldToNewOutputs);
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index 8246579946..c1f8494ebd 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -973,8 +973,6 @@ public TopDownGeneralDecorrelator getVisitor() {
* Unnesting information.
*/
static class UnnestedQuery extends Frame {
- final RelNode oldRel;
-
/**
* Creates a UnnestedQuery.
*
@@ -986,7 +984,6 @@ static class UnnestedQuery extends Frame {
UnnestedQuery(RelNode oldRel, RelNode r, NavigableMap<CorDef, Integer>
corDefOutputs,
Map<Integer, Integer> oldToNewOutputs) {
super(oldRel, r, corDefOutputs, oldToNewOutputs);
- this.oldRel = oldRel;
}
/**
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 1fabc39d8c..2b406c3c01 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -1669,6 +1669,95 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5390";>[CALCITE-5390]
+ * RelDecorrelator throws NullPointerException</a>. */
+ @Test void testCorrelationLexicalScoping() {
+ 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 deptno,\n"
+ + " (select min(1) from emp where empno > d.deptno) as i0,\n"
+ + " (select min(0) from emp where deptno = d.deptno and "
+ + "ename = 'SMITH' and d.deptno > 0) as i1\n"
+ + "from dept as d";
+ 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(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DEPTNO=[$0], I0=[$3], I1=[$4])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])\n"
+ + " LogicalProject($f0=[1])\n"
+ + " LogicalFilter(condition=[>($0,
CAST($cor0.DEPTNO):SMALLINT NOT NULL)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])\n"
+ + " LogicalProject($f0=[0])\n"
+ + " LogicalFilter(condition=[AND(=($7, $cor0.DEPTNO), =($1,
'SMITH'), >(CAST($cor0.DEPTNO):INTEGER NOT NULL, 0))])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ final String planAfter = ""
+ + "LogicalProject(DEPTNO=[$0], I0=[$3], I1=[$8])\n"
+ + " LogicalJoin(condition=[AND(=($0, $6), =($5, $7))],
joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EXPR$0=[$5],
DEPTNO0=[$0], $f5=[>(CAST($0):INTEGER NOT NULL, 0)])\n"
+ + " LogicalJoin(condition=[=($3, $4)], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
DEPTNO0=[CAST($0):SMALLINT NOT NULL])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)])\n"
+ + " LogicalProject(DEPTNO0=[$8], $f0=[1])\n"
+ + " LogicalJoin(condition=[>($0, $8)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO0=[CAST($0):SMALLINT NOT
NULL])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($2)])\n"
+ + " LogicalProject(DEPTNO0=[$8], $f5=[$9], $f0=[0])\n"
+ + " LogicalJoin(condition=[=($7, $8)], joinType=[inner])\n"
+ + " LogicalFilter(condition=[=($1, 'SMITH')])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[$1])\n"
+ + " LogicalProject(DEPTNO=[$0], $f5=[>(CAST($0):INTEGER NOT
NULL, 0)])\n"
+ + " LogicalJoin(condition=[=($3, $4)], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
DEPTNO0=[CAST($0):SMALLINT NOT NULL])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)])\n"
+ + " LogicalProject(DEPTNO0=[$8], $f0=[1])\n"
+ + " LogicalJoin(condition=[>($0, $8)],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO0=[CAST($0):SMALLINT
NOT NULL])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7320";>[CALCITE-7320]
* AggregateProjectMergeRule throws AssertionError when Project maps
multiple grouping keys
* to the same field</a>. */
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 5fb0655372..2b6e459cda 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -8967,4 +8967,79 @@ where e.deptno = 10;
!ok
+# [CALCITE-5390] RelDecorrelator throws NullPointerException
+# Verified against PostgreSQL.
+select deptno,
+ (select min(1) from emp where empno > d.deptno) as i0,
+ (select min(0) from emp where deptno = d.deptno and ename = 'SMITH' and
d.deptno > 0) as i1
+from dept as d;
++--------+----+----+
+| DEPTNO | I0 | I1 |
++--------+----+----+
+| 10 | 1 | |
+| 20 | 1 | 0 |
+| 30 | 1 | |
+| 40 | 1 | |
++--------+----+----+
+(4 rows)
+
+!ok
+
+# [CALCITE-5390] RelDecorrelator throws NullPointerException
+# Verified against PostgreSQL.
+SELECT
+ (SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1,
+ (SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2
+FROM emp a;
++----+-------+
+| T1 | T2 |
++----+-------+
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | false |
+| 1 | true |
++----+-------+
+(14 rows)
+
+!ok
+
+# [CALCITE-5390] RelDecorrelator throws NullPointerException
+# Verified against PostgreSQL.
+SELECT *
+FROM emp e
+WHERE e.ename NOT IN (
+ SELECT d.dname
+ FROM dept d
+ WHERE e.deptno = d.deptno OR e.sal > 2000.0);
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
# End sub-query.iq
diff --git a/plus/src/test/java/org/apache/calcite/adapter/tpch/TpchTest.java
b/plus/src/test/java/org/apache/calcite/adapter/tpch/TpchTest.java
index 5f4e93d9d7..39aa2f3f16 100644
--- a/plus/src/test/java/org/apache/calcite/adapter/tpch/TpchTest.java
+++ b/plus/src/test/java/org/apache/calcite/adapter/tpch/TpchTest.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.adapter.tpch;
+import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.hep.HepPlanner;
@@ -27,11 +28,16 @@
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParseException;
+import org.apache.calcite.sql2rel.RelDecorrelator;
import org.apache.calcite.test.CalciteAssert;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Planner;
+import org.apache.calcite.tools.Program;
+import org.apache.calcite.tools.Programs;
+import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelConversionException;
+import org.apache.calcite.tools.RuleSets;
import org.apache.calcite.tools.ValidationException;
import org.apache.calcite.util.TestUtil;
@@ -41,15 +47,19 @@
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.Timeout;
+import java.util.Collections;
import java.util.List;
import java.util.concurrent.TimeUnit;
import static org.apache.calcite.test.Matchers.containsStringLinux;
+import static org.apache.calcite.test.Matchers.hasTree;
import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.CoreMatchers.not;
import static org.hamcrest.MatcherAssert.assertThat;
+import static java.util.Objects.requireNonNull;
+
/** Unit test for {@link org.apache.calcite.adapter.tpch.TpchSchema}.
*
* <p>Because the TPC-H data generator takes time and memory to instantiate,
@@ -1001,6 +1011,208 @@ private CalciteAssert.AssertThat with() {
checkQuery(22);
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5390";>[CALCITE-5390]
+ * RelDecorrelator throws NullPointerException</a>. */
+ @Test public void test5390()
+ throws SqlParseException, ValidationException, RelConversionException {
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ TpchSchema tpchSchema = new TpchSchema(1.0, 0, 1, false);
+ rootSchema.add("TPCH", tpchSchema);
+ FrameworkConfig config = Frameworks.newConfigBuilder()
+ .defaultSchema(rootSchema)
+ .build();
+ final RelBuilder builder = RelBuilder.create(config);
+ final RelOptCluster cluster = builder.getCluster();
+
+ Planner planner = Frameworks.getPlanner(config);
+
+ String sql = "select\n"
+ + " (select count(*) from tpch.part where p_partkey =
tpch.partsupp.ps_partkey),\n"
+ + " (select count(*) from tpch.supplier\n"
+ + " where s_suppkey = case when s_acctbal > 0\n"
+ + " then tpch.partsupp.ps_partkey + 1\n"
+ + " else 1234 end)\n"
+ + "from tpch.partsupp";
+
+ SqlNode parsed = planner.parse(sql);
+ SqlNode validated = planner.validate(parsed);
+ RelRoot root = planner.rel(validated);
+ final RelNode originalRel = root.rel;
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(EXPR$0=[$5], EXPR$1=[$6])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.PS_PARTKEY)])\n"
+ + " LogicalTableScan(table=[[TPCH, PART]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+ + " LogicalFilter(condition=[=(CAST($0):BIGINT,
CASE(>(CAST($5):DOUBLE, CAST(0):DOUBLE NOT NULL), +($cor0.PS_PARTKEY, 1),
1234:BIGINT))])\n"
+ + " LogicalTableScan(table=[[TPCH, SUPPLIER]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
+ RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ final String planAfter = ""
+ + "LogicalProject(EXPR$0=[$5], EXPR$1=[$8])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($6, $7)],
joinType=[left])\n"
+ + " LogicalProject(PS_PARTKEY=[$0], PS_SUPPKEY=[$1],
PS_AVAILQTY=[$2], PS_SUPPLYCOST=[$3], PS_COMMENT=[$4], EXPR$0=[$6], $f6=[+($0,
1)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)],
joinType=[left])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalProject(P_PARTKEY=[$0], EXPR$0=[CASE(IS NOT
NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(PS_PARTKEY=[$0])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject(P_PARTKEY=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($0)])\n"
+ + " LogicalTableScan(table=[[TPCH, PART]])\n"
+ + " LogicalProject($f6=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2,
0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject($f6=[+($0, 1)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)],
joinType=[left])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalProject(P_PARTKEY=[$0], EXPR$0=[CASE(IS NOT
NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0,
$1)], joinType=[left])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(PS_PARTKEY=[$0])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject(P_PARTKEY=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($0)])\n"
+ + " LogicalTableScan(table=[[TPCH, PART]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject($f6=[$7])\n"
+ + " LogicalJoin(condition=[=(CAST($0):BIGINT,
CASE(>(CAST($5):DOUBLE, 0.0E0), $7, 1234:BIGINT))], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[TPCH, SUPPLIER]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject($f6=[+($0, 1)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0,
$5)], joinType=[left])\n"
+ + " LogicalTableScan(table=[[TPCH, PARTSUPP]])\n"
+ + " LogicalProject(P_PARTKEY=[$0], EXPR$0=[CASE(IS
NOT NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $1)], joinType=[left])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(PS_PARTKEY=[$0])\n"
+ + " LogicalTableScan(table=[[TPCH,
PARTSUPP]])\n"
+ + " LogicalAggregate(group=[{0}],
EXPR$0=[COUNT()])\n"
+ + " LogicalProject(P_PARTKEY=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($0)])\n"
+ + " LogicalTableScan(table=[[TPCH,
PART]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ @Test public void test53902()
+ throws SqlParseException, ValidationException, RelConversionException {
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ TpchSchema tpchSchema = new TpchSchema(1.0, 0, 1, false);
+ rootSchema.add("TPCH", tpchSchema);
+ FrameworkConfig config = Frameworks.newConfigBuilder()
+ .defaultSchema(rootSchema)
+ .build();
+ final RelBuilder builder = RelBuilder.create(config);
+ final RelOptCluster cluster = builder.getCluster();
+
+ Planner planner = Frameworks.getPlanner(config);
+
+ String sql = ""
+ + "SELECT *\n"
+ + "FROM tpch.customer\n"
+ + "WHERE c_mktsegment = 'AUTOMOBILE'\n"
+ + " AND (SELECT COUNT(*)\n"
+ + " FROM tpch.orders\n"
+ + " WHERE o_custkey = c_custkey\n"
+ + " AND (SELECT SUM(l_extendedprice)\n"
+ + " FROM tpch.lineitem\n"
+ + " WHERE l_orderkey = o_orderkey\n"
+ + " ) > 300000\n"
+ + " ) > 5";
+
+ SqlNode parsed = planner.parse(sql);
+ SqlNode validated = planner.validate(parsed);
+ RelRoot root = planner.rel(validated);
+ final RelNode originalRel = root.rel;
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(C_CUSTKEY=[$0], C_NAME=[$1], C_ADDRESS=[$2],
C_NATIONKEY=[$3], C_PHONE=[$4], C_ACCTBAL=[$5], C_MKTSEGMENT=[$6],
C_COMMENT=[$7])\n"
+ + " LogicalProject(C_CUSTKEY=[$0], C_NAME=[$1], C_ADDRESS=[$2],
C_NATIONKEY=[$3], C_PHONE=[$4], C_ACCTBAL=[$5], C_MKTSEGMENT=[$6],
C_COMMENT=[$7])\n"
+ + " LogicalFilter(condition=[AND(=(CAST($6):VARCHAR, 'AUTOMOBILE'),
>($8, 5))])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[TPCH, CUSTOMER]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject(O_ORDERKEY=[$0], O_CUSTKEY=[$1],
O_ORDERSTATUS=[$2], O_TOTALPRICE=[$3], O_ORDERDATE=[$4], O_ORDERPRIORITY=[$5],
O_CLERK=[$6], O_SHIPPRIORITY=[$7], O_COMMENT=[$8])\n"
+ + " LogicalFilter(condition=[AND(=($1, $cor0.C_CUSTKEY),
>(CAST($9):DOUBLE, 300000.0E0))])\n"
+ + " LogicalCorrelate(correlation=[$cor1],
joinType=[left], requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[TPCH, ORDERS]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])\n"
+ + " LogicalProject(L_EXTENDEDPRICE=[$5])\n"
+ + " LogicalFilter(condition=[=($0,
$cor1.O_ORDERKEY)])\n"
+ + " LogicalTableScan(table=[[TPCH, LINEITEM]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
+ RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ final String planAfter = ""
+ + "LogicalProject(C_CUSTKEY=[$0], C_NAME=[$1], C_ADDRESS=[$2],
C_NATIONKEY=[$3], C_PHONE=[$4], C_ACCTBAL=[$5], C_MKTSEGMENT=[$6],
C_COMMENT=[$7])\n"
+ + " LogicalProject(C_CUSTKEY=[$0], C_NAME=[$1], C_ADDRESS=[$2],
C_NATIONKEY=[$3], C_PHONE=[$4], C_ACCTBAL=[$5], C_MKTSEGMENT=[$6],
C_COMMENT=[$7], O_CUSTKEY9=[$8], EXPR$0=[CAST($9):BIGINT])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $8)],
joinType=[inner])\n"
+ + " LogicalFilter(condition=[=(CAST($6):VARCHAR,
'AUTOMOBILE')])\n"
+ + " LogicalTableScan(table=[[TPCH, CUSTOMER]])\n"
+ + " LogicalFilter(condition=[>($1, 5)])\n"
+ + " LogicalProject(O_CUSTKEY9=[$0], EXPR$0=[CASE(IS NOT
NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(C_CUSTKEY=[$0])\n"
+ + " LogicalTableScan(table=[[TPCH, CUSTOMER]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject(O_CUSTKEY9=[$1])\n"
+ + " LogicalJoin(condition=[=($0, $9)],
joinType=[inner])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($1)])\n"
+ + " LogicalTableScan(table=[[TPCH, ORDERS]])\n"
+ + " LogicalFilter(condition=[>(CAST($1):DOUBLE,
300000.0E0)])\n"
+ + " LogicalAggregate(group=[{0}],
EXPR$0=[SUM($1)])\n"
+ + " LogicalProject(L_ORDERKEY=[$0],
L_EXTENDEDPRICE=[$5])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($0)])\n"
+ + " LogicalTableScan(table=[[TPCH,
LINEITEM]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
private void checkQuery(int i) {
query(i).runs();
}
