This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 e6265a318e [CALCITE-7646] CorrelateProjectExtractor does not handle
nested field accesses cor0.field0.field1
e6265a318e is described below
commit e6265a318ee943e5b4a79cb617db7b47422d6123
Author: Mihai Budiu <[email protected]>
AuthorDate: Mon Jul 6 14:45:57 2026 -0700
[CALCITE-7646] CorrelateProjectExtractor does not handle nested field
accesses cor0.field0.field1
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql2rel/CorrelateProjectExtractor.java | 77 ++++++++++++++---
.../sql2rel/CorrelateProjectExtractorTest.java | 72 ++++++++++++++++
.../calcite/sql2rel/RelDecorrelatorTest.java | 99 ++++++++++++++++++----
.../org/apache/calcite/test/RelOptRulesTest.xml | 20 ++---
.../apache/calcite/test/SqlToRelConverterTest.xml | 40 ++++-----
core/src/test/resources/sql/lateral.iq | 94 ++++++++++++++++++++
6 files changed, 341 insertions(+), 61 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
b/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
index 50d255c331..127f4e4879 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
@@ -88,6 +88,20 @@ public CorrelateProjectExtractor(RelBuilderFactory factory) {
this.builderFactory = factory;
}
+ /** Returns whether {@code node} is a direct field access on the correlation
+ * variable with the specified id, such as {@code $cor0.DEPTNO}. A nested
+ * access such as {@code $cor0.REC.DEPTNO} is not direct. */
+ private static boolean isDirectFieldAccess(RexNode node, CorrelationId id) {
+ if (node instanceof RexFieldAccess) {
+ RexFieldAccess access = (RexFieldAccess) node;
+ if (access.getReferenceExpr() instanceof RexCorrelVariable) {
+ RexCorrelVariable correlVar = (RexCorrelVariable)
access.getReferenceExpr();
+ return correlVar.id.equals(id);
+ }
+ }
+ return false;
+ }
+
@Override public RelNode visit(LogicalCorrelate correlate) {
RelNode left = correlate.getLeft().accept(this);
RelNode right = correlate.getRight().accept(this);
@@ -95,8 +109,12 @@ public CorrelateProjectExtractor(RelBuilderFactory factory)
{
// Find the correlated expressions from the right side that can be moved
to the left
Set<RexNode> callsWithCorrelationInRight =
findCorrelationDependentCalls(correlate.getCorrelationId(), right);
+ // Only direct field accesses on the correlation variable, such as
+ // $cor0.DEPTNO, are left in place. A nested field access, such as
+ // $cor0.REC.DEPTNO, is extracted
boolean isTrivialCorrelation =
- callsWithCorrelationInRight.stream().allMatch(exp -> exp instanceof
RexFieldAccess);
+ callsWithCorrelationInRight.stream()
+ .allMatch(exp -> isDirectFieldAccess(exp,
correlate.getCorrelationId()));
// Early exit condition
if (isTrivialCorrelation) {
if (correlate.getLeft().equals(left) &&
correlate.getRight().equals(right)) {
@@ -116,27 +134,42 @@ public CorrelateProjectExtractor(RelBuilderFactory
factory) {
// Transform the correlated expression from the right side to an
expression over the left side
builder.push(left);
+ ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
List<RexNode> callsWithCorrelationOverLeft = new ArrayList<>();
for (RexNode callInRight : callsWithCorrelationInRight) {
-
callsWithCorrelationOverLeft.add(replaceCorrelationsWithInputRef(callInRight,
builder));
+ if (isDirectFieldAccess(callInRight, correlate.getCorrelationId())) {
+ // Direct field accesses stay in the right side and keep reading their
+ // original left column; that column must remain a required column.
+ requiredColumns.set(((RexFieldAccess)
callInRight).getField().getIndex());
+ } else {
+
callsWithCorrelationOverLeft.add(replaceCorrelationsWithInputRef(callInRight,
builder));
+ }
}
builder.projectPlus(callsWithCorrelationOverLeft);
// Construct the mapping to transform the expressions in the right side
based on the new
// projection in the left side.
Map<RexNode, RexNode> transformMapping = new HashMap<>();
+ int newFieldIndex = oldLeft;
for (RexNode callInRight : callsWithCorrelationInRight) {
- RexBuilder xb = builder.getRexBuilder();
- RexNode v = xb.makeCorrel(builder.peek().getRowType(),
correlate.getCorrelationId());
- RexNode flatCorrelationInRight = xb.makeFieldAccess(v, oldLeft +
transformMapping.size());
- transformMapping.put(callInRight, flatCorrelationInRight);
+ if (!isDirectFieldAccess(callInRight, correlate.getCorrelationId())) {
+ RexBuilder xb = builder.getRexBuilder();
+ RexNode v = xb.makeCorrel(builder.peek().getRowType(),
correlate.getCorrelationId());
+ RexNode flatCorrelationInRight = xb.makeFieldAccess(v, newFieldIndex);
+ transformMapping.put(callInRight, flatCorrelationInRight);
+ newFieldIndex++;
+ }
}
- // Select the required fields/columns from the left side of the
correlation. Based on the code
- // above all these fields should be at the end of the left relational
expression.
+ // Select the required fields/columns from the left side of the
correlation: the columns
+ // read by the direct field accesses plus the newly projected columns,
which are at the
+ // end of the left relational expression.
List<RexNode> requiredFields =
builder.fields(
- ImmutableBitSet.range(oldLeft, oldLeft +
callsWithCorrelationOverLeft.size()).asList());
+ requiredColumns
+ .set(oldLeft, oldLeft + callsWithCorrelationOverLeft.size())
+ .build()
+ .asList());
final int newLeft = builder.fields().size();
// Transform the expressions in the right side using the mapping
constructed earlier.
@@ -264,8 +297,13 @@ private static boolean
isSimpleCorrelatedExpression(RexNode node, CorrelationId
* +(10, $cor0.DEPTNO) -> TRUE
* /(100,+(10, $cor0.DEPTNO)) -> TRUE
* CAST(+(10, $cor0.DEPTNO)):INTEGER NOT NULL -> TRUE
+ * CASE(IS NOT NULL($cor0.PATH), $cor0.PATH, ARRAY(null:INTEGER)) -> TRUE
* +($0, $cor0.DEPTNO) -> FALSE
* }</pre>
+ *
+ * <p>A subexpression built only from literals and dynamic parameters, such
+ * as {@code ARRAY(null:INTEGER)} above, is neutral: it neither qualifies nor
+ * disqualifies the enclosing call.
*/
private static class SimpleCorrelationDetector
extends RexVisitorImpl<@Nullable Boolean> {
@@ -284,7 +322,8 @@ private SimpleCorrelationDetector(CorrelationId corrId) {
return Boolean.FALSE;
}
- @Override public Boolean visitCall(RexCall call) {
+ @Override public @Nullable Boolean visitCall(RexCall call) {
+ // Constant operands must not disqualify the call
Boolean hasSimpleCorrelation = null;
for (RexNode op : call.operands) {
Boolean b = op.accept(this);
@@ -292,7 +331,8 @@ private SimpleCorrelationDetector(CorrelationId corrId) {
hasSimpleCorrelation = hasSimpleCorrelation == null ? b :
hasSimpleCorrelation && b;
}
}
- return hasSimpleCorrelation == null ? Boolean.FALSE :
hasSimpleCorrelation;
+ // If unsure return null; caller will decide
+ return hasSimpleCorrelation;
}
@Override public @Nullable Boolean visitFieldAccess(RexFieldAccess
fieldAccess) {
@@ -332,8 +372,10 @@ private static RexNode
replaceCorrelationsWithInputRef(RexNode exp, RelBuilder b
}
/**
- * A visitor traversing row expressions and replacing calls with other
expressions according
- * to the specified mapping.
+ * A visitor traversing row expressions and replacing calls and field
+ * accesses with other expressions according to the specified mapping.
+ * The mapping is consulted before recursing so that the outermost
+ * matching expression wins.
*/
private static final class CallReplacer extends RexShuttle {
private final Map<RexNode, RexNode> mapping;
@@ -350,5 +392,14 @@ private static final class CallReplacer extends RexShuttle
{
return super.visitCall(oldCall);
}
}
+
+ @Override public RexNode visitFieldAccess(RexFieldAccess fieldAccess) {
+ RexNode replacement = mapping.get(fieldAccess);
+ if (replacement != null) {
+ return replacement;
+ } else {
+ return super.visitFieldAccess(fieldAccess);
+ }
+ }
}
}
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
index 72276d42c3..da0a17296b 100644
---
a/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
+++
b/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
@@ -84,6 +84,78 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7646";>[CALCITE-7646]
+ * CorrelateProjectExtractor does not handle nested field accesses
cor0.field0.field1</a>. */
+ @Test void testNestedCorrelationFieldAccessInFilter() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
+ RelNode before = builder.scan("EMP")
+ .project(
+ builder.alias(
+ builder.call(SqlStdOperatorTable.ROW,
+ builder.field("EMPNO"), builder.field("DEPTNO")), "R"))
+ .variable(v::set)
+ .scan("DEPT")
+ .filter(
+ builder.equals(builder.field(0),
+ builder.getRexBuilder().makeFieldAccess(builder.field(v.get(),
"R"), 1)))
+ .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "R"))
+ .build();
+
+ final String planBefore = ""
+ + "LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalProject(R=[ROW($0, $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.R.EXPR$1)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ RelNode after = before.accept(new
CorrelateProjectExtractor(RelFactories.LOGICAL_BUILDER));
+ final String planAfter = ""
+ + "LogicalProject(R=[$0], DEPTNO=[$2], DNAME=[$3], LOC=[$4])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])\n"
+ + " LogicalProject(R=[ROW($0, $7)], $f1=[ROW($0, $7).EXPR$1])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.$f1)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Tests that a constant call operand, such as {@code POWER(2, 3)}, does
+ * not prevent extracting the enclosing correlated call. */
+ @Test void testCorrelationCallWithConstantCallOperandInFilter() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
+ RelNode before = builder.scan("EMP")
+ .variable(v::set)
+ .scan("DEPT")
+ .filter(
+ builder.equals(builder.field(0),
+ builder.call(SqlStdOperatorTable.PLUS,
+ builder.call(SqlStdOperatorTable.POWER,
+ builder.literal(2), builder.literal(3)),
+ builder.field(v.get(), "DEPTNO"))))
+ .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "DEPTNO"))
+ .build();
+
+ final String planBefore = ""
+ + "LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, +(POWER(2, 3), $cor0.DEPTNO))])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ RelNode after = before.accept(new
CorrelateProjectExtractor(RelFactories.LOGICAL_BUILDER));
+ final String planAfter = ""
+ + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$9], DNAME=[$10],
LOC=[$11])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{8}])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+(POWER(2, 3), $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.$f8)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
@Test void testDoubleCorrelationCallOverVariableInFilters() {
final RelBuilder builder = RelBuilder.create(config().build());
final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
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 240f9a36ae..ac218b220a 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -290,9 +290,9 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ " LogicalValues(tuples=[[{ 7369 }, { 7499 }]])\n"
+ " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
- + " LogicalProject(EMPNO1=[$12], EXPR$0=[||(||($1, ' from dept
'), $13)])\n"
- + " LogicalJoin(condition=[AND(=($7, $10), =($9, $11))],
joinType=[left])\n"
- + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$7],
EMPNO0=[CAST($0):INTEGER NOT NULL])\n"
+ + " LogicalProject(EMPNO1=[$11], EXPR$0=[||(||($1, ' from dept
'), $12)])\n"
+ + " LogicalJoin(condition=[AND(=($7, $9), =($8, $10))],
joinType=[left])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7],
EMPNO0=[CAST($0):INTEGER NOT NULL])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
+ " LogicalAggregate(group=[{0, 1, 2}],
agg#0=[SINGLE_VALUE($3)])\n"
+ " LogicalProject(DEPTNO0=[$3], EMPNO0=[$4], EMPNO=[$5],
DNAME=[$1])\n"
@@ -556,29 +556,29 @@ public static Frameworks.ConfigBuilder config() {
// LogicalTableScan(table=[[scott, EMP]])
final String planAfter = ""
+ "LogicalSort(sort0=[$0], dir0=[ASC])\n"
- + " LogicalProject(DNAME=[$1], C=[$7])\n"
- + " LogicalJoin(condition=[AND(=($0, $5), =($4, $6))],
joinType=[left])\n"
- + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
DEPTNO0=[$0], $f4=[*($0, 100)])\n"
+ + " LogicalProject(DNAME=[$1], C=[$6])\n"
+ + " LogicalJoin(condition=[AND(=($0, $4), =($3, $5))],
joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f3=[*($0,
100)])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
- + " LogicalProject(DEPTNO8=[$0], $f4=[$1], EXPR$0=[CASE(IS NOT
NULL($4), $4, 0)])\n"
+ + " LogicalProject(DEPTNO8=[$0], $f3=[$1], EXPR$0=[CASE(IS NOT
NULL($4), $4, 0)])\n"
+ " LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($0, $2), IS
NOT DISTINCT FROM($1, $3))], joinType=[left])\n"
- + " LogicalProject(DEPTNO=[$0], $f4=[*($0, 100)])\n"
+ + " LogicalProject(DEPTNO=[$0], $f3=[*($0, 100)])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
+ " LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT()])\n"
- + " LogicalProject(DEPTNO8=[$7], $f4=[$9])\n"
+ + " LogicalProject(DEPTNO8=[$7], $f3=[$9])\n"
+ " LogicalFilter(condition=[IS NOT NULL($7)])\n"
- + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], i=[$11], $f4=[$9])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], i=[$11], $f3=[$9])\n"
+ " LogicalJoin(condition=[=($8, $10)],
joinType=[inner])\n"
+ " LogicalProject(EMPNO=[$0], ENAME=[$1],
JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7],
SAL0=[CAST($5):DECIMAL(12, 2)])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
- + " LogicalProject($f4=[$0], SAL0=[$1],
$f2=[true])\n"
+ + " LogicalProject($f3=[$0], SAL0=[$1],
$f2=[true])\n"
+ " LogicalAggregate(group=[{0, 1}])\n"
- + " LogicalProject($f4=[$1], SAL0=[$2])\n"
+ + " LogicalProject($f3=[$1], SAL0=[$2])\n"
+ " LogicalJoin(condition=[AND(>($2,
CAST($0):DECIMAL(12, 2) NOT NULL), <($1, $0))], joinType=[inner])\n"
+ " LogicalValues(tuples=[[{ 1000 }, { 2000
}, { 3000 }]])\n"
+ " LogicalJoin(condition=[true],
joinType=[inner])\n"
+ " LogicalAggregate(group=[{0}])\n"
- + " LogicalProject($f4=[*($0, 100)])\n"
+ + " LogicalProject($f3=[*($0, 100)])\n"
+ " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ " LogicalAggregate(group=[{0}])\n"
+ "
LogicalProject(SAL0=[CAST($5):DECIMAL(12, 2)])\n"
@@ -1793,9 +1793,9 @@ public static Frameworks.ConfigBuilder config() {
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"
+ + "LogicalProject(DEPTNO=[$0], I0=[$3], I1=[$7])\n"
+ + " LogicalJoin(condition=[AND(=($0, $5), =($4, $6))],
joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EXPR$0=[$5],
$f4=[>(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"
@@ -1807,12 +1807,12 @@ public static Frameworks.ConfigBuilder config() {
+ " 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"
+ + " LogicalProject(DEPTNO0=[$8], $f4=[$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"
+ + " LogicalProject(DEPTNO=[$0], $f4=[>(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"
@@ -2351,4 +2351,67 @@ 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-7646";>[CALCITE-7646]
+ * CorrelateProjectExtractor does not handle nested field accesses
cor0.field0.field1</a>. */
+ @Test void testNestedCorrelatedFieldAccess() throws SqlParseException {
+ final String sql = "select a.\"aid\", t.lat\n"
+ + "from \"bookstore\".\"authors\" a,\n"
+ + "lateral (select b.\"aid\" as c,\n"
+ + " (a.\"birthPlace\").\"coords\".\"latitude\" as lat\n"
+ + " from \"bookstore\".\"authors\" b\n"
+ + " where b.\"aid\" = a.\"aid\") as t";
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.BOOKSTORE);
+ CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new
Properties());
+ // The Frameworks planner cannot be used here because it flattens
+ // structured types, and RelStructuredTypeFlattener does not support
+ // correlations on structured columns.
+ SqlTestFactory factory = SqlTestFactory.INSTANCE
+ .withCatalogReader((typeFactory, caseSensitive) ->
+ new CalciteCatalogReader(
+ CalciteSchema.from(rootSchema),
+ ImmutableList.of("bookstore"),
+ typeFactory,
+ config));
+ SqlParser parser = factory.createParser(sql);
+ SqlNode parsed = parser.parseQuery();
+ final SqlToRelConverter sqlToRelConverter =
factory.createSqlToRelConverter();
+ assert sqlToRelConverter.validator != null;
+ final SqlNode validated = sqlToRelConverter.validator.validate(parsed);
+ final RelNode before = sqlToRelConverter.convertQuery(validated, false,
true).rel;
+
+ final String planBefore = ""
+ + "LogicalProject(aid=[$0], LAT=[$5])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0, 2}])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalProject(C=[$0],
LAT=[$cor1.birthPlace.coords.latitude])\n"
+ + " LogicalFilter(condition=[=($0, $cor1.aid)])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ final RelBuilder relBuilder =
+ RelFactories.LOGICAL_BUILDER.create(before.getCluster(), null);
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, relBuilder,
+ RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+
+ // The nested field access is extracted into the projection $f4 on the
+ // left side and no correlation variables remain.
+ final String planAfter = ""
+ + "LogicalProject(aid=[$0], LAT=[$6])\n"
+ + " LogicalJoin(condition=[AND(=($0, $7), IS NOT DISTINCT FROM($4,
$8))], joinType=[inner])\n"
+ + " LogicalProject(aid=[$0], name=[$1], birthPlace=[$2],
books=[$3], $f4=[$2.coords.latitude])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalProject(C=[$0], LAT=[$4], aid=[$0], $f4=[$4])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject($f4=[$2.coords.latitude])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index d7c03e93f5..1e37b2699b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3097,9 +3097,9 @@ LogicalProject(NAME=[$1], EXPR$1=[$2])
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject(NAME=[$1], EXPR$1=[$4])
- LogicalJoin(condition=[AND(=($0, $5), =($3, $6))], joinType=[left])
- LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$0],
NAME0=[CAST($1):VARCHAR(20) NOT NULL])
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[AND(=($0, $4), =($2, $5))], joinType=[left])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):VARCHAR(20) NOT
NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(SAL=[$0], DEPTNO=[$2], ENAME=[$3])
LogicalFilter(condition=[<=($4, 1)])
@@ -8815,19 +8815,19 @@ where n1.SAL IN (
<Resource name="planBefore">
<![CDATA[
LogicalProject(SAL=[$5])
- LogicalJoin(condition=[AND(=($5, $11), IS NOT DISTINCT FROM($9, $12))],
joinType=[inner])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], SLACKER=[$8], SAL0=[$5], $f9=[=($5, 4)])
+ LogicalJoin(condition=[AND(=($5, $10), IS NOT DISTINCT FROM($8, $11))],
joinType=[inner])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], SLACKER=[$8], $f8=[=($5, 4)])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalFilter(condition=[=($1, $0)])
LogicalAggregate(group=[{0, 1, 2}])
- LogicalProject(SAL=[$5], SAL0=[$8], $f9=[$9])
+ LogicalProject(SAL=[$5], SAL0=[$8], $f8=[$9])
LogicalJoin(condition=[OR(=($8, $5), $9)], joinType=[inner])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalAggregate(group=[{0, 1}])
- LogicalProject(SAL=[$5], $f9=[=($5, 4)])
+ LogicalProject(SAL=[$5], $f8=[=($5, 4)])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
@@ -9212,9 +9212,9 @@ LEFT JOIN LATERAL (
<![CDATA[
LogicalProject(A=[$0], TS=[$1], X=[$2], X0=[$4])
LogicalJoin(condition=[=($3, $5)], joinType=[left])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1], X=[$4],
EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
- LogicalJoin(condition=[AND(=($1, $5), =($2, $6))], joinType=[left])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20)
NOT NULL], EXPR$10=[$1])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], X=[$3],
EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
+ LogicalJoin(condition=[AND(=($1, $4), =($2, $5))], joinType=[left])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20)
NOT NULL])
LogicalValues(tuples=[[{ 'a', 1 }]])
LogicalFilter(condition=[<=($3, 1)])
LogicalProject(X=[$2], EXPR$1=[$3], EXPR$00=[$4], rn=[ROW_NUMBER()
OVER (PARTITION BY $3, $4)])
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index c36f7ad59b..aafc9c11ef 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1770,15 +1770,15 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()],
EXPR$1=[SUM($0)])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], C0=[$4], F=[$5])
- LogicalJoin(condition=[AND(=($0, $6), =($3, $7))], joinType=[inner])
- LogicalProject(C=[$0], D=[$1], C0=[$0], $f3=[+($0, $1)])
+LogicalProject(C=[$0], D=[$1], C0=[$3], F=[$4])
+ LogicalJoin(condition=[AND(=($0, $5), =($2, $6))], joinType=[inner])
+ LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalProject(C=[$1], F=[*($0, $1)], C2=[$1], $f3=[$2])
+ LogicalProject(C=[$1], F=[*($0, $1)], C2=[$1], $f2=[$2])
LogicalJoin(condition=[=($2, *($0, $1))], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
LogicalAggregate(group=[{0, 1}])
- LogicalProject(C=[$0], $f3=[+($0, $1)])
+ LogicalProject(C=[$0], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
]]>
</Resource>
@@ -1796,18 +1796,18 @@ cross join lateral
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$3])
- LogicalJoin(condition=[AND(=($0, $4), =($2, $5))], joinType=[inner])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+LogicalProject(C=[$0], N=[$2])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ LogicalProject(C=[$0], $f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
- LogicalProject(N=[+($0, $1)], EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalProject(N=[+($0, $1)], EXPR$0=[+($0, $1)], $f1=[$2])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalProject(EXPR$0=[$1], $f1=[$1])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 3 }]])
LogicalAggregate(group=[{0}])
- LogicalProject($f2=[+($0, 1)])
+ LogicalProject($f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
@@ -5222,9 +5222,9 @@ LogicalProject(C=[$0], D=[$1], C0=[$2])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], D0=[$4], C0=[$5])
- LogicalJoin(condition=[AND(=($0, $6), =($2, $5))], joinType=[left])
- LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)], C0=[$0])
+LogicalProject(C=[$0], D=[$1], D0=[$3], C0=[$4])
+ LogicalJoin(condition=[AND(=($0, $5), =($2, $4))], joinType=[left])
+ LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
LogicalJoin(condition=[true], joinType=[inner])
@@ -5247,18 +5247,18 @@ as r(n) on c=n]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$3])
- LogicalJoin(condition=[AND(=($0, $3), =($2, $4))], joinType=[left])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+LogicalProject(C=[$0], N=[$2])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ LogicalProject(C=[$0], $f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
- LogicalProject(EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalProject(EXPR$0=[+($0, $1)], $f1=[$2])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalProject(EXPR$0=[$1], $f1=[$1])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 3 }]])
LogicalAggregate(group=[{0}])
- LogicalProject($f2=[+($0, 1)])
+ LogicalProject($f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/lateral.iq
b/core/src/test/resources/sql/lateral.iq
index 5c82727b89..4c4ffbe170 100644
--- a/core/src/test/resources/sql/lateral.iq
+++ b/core/src/test/resources/sql/lateral.iq
@@ -244,4 +244,98 @@ where job = 'MANAGER';
!ok
+# 3 test cases for [CALCITE-7646] CorrelateProjectExtractor
+# does not handle nested field accesses cor0.field0.field1.
+
+# All queries use LATERAL, which converts directly to a Correlate.
+# The results were validated on Postgres
+
+!use scott
+
+select t.dd, t.x
+from dept d,
+lateral (select d.deptno as dd, u.x
+ from unnest(array[d.deptno + 100]) as u(x)) as t
+where d.dname = 'SALES';
++----+-----+
+| DD | X |
++----+-----+
+| 30 | 130 |
++----+-----+
+(1 row)
+
+!ok
+
+select t.dd, t.dd1, t.x
+from dept d,
+lateral (select d.deptno as dd, d.deptno + 1 as dd1, u.x
+ from unnest(array[1, 2]) as u(x)) as t
+where d.dname = 'SALES';
++----+-----+---+
+| DD | DD1 | X |
++----+-----+---+
+| 30 | 31 | 1 |
+| 30 | 31 | 2 |
++----+-----+---+
+(2 rows)
+
+!ok
+!if (use_old_decorr) {
+# The correlated computation d.deptno + 1 (DD1) has been extracted into the
left
+# input of the EnumerableNestedLoopJoin, as $f3. The right input,
+# UNNEST(ARRAY[1, 2]), references no correlation variable, so decorrelation
+# replaces the Correlate with a join.
+EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableHashJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..2=[{exprs}], DEPTNO=[$t0],
$f3=[$t1])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t0, $t3)],
expr#5=['SALES':VARCHAR(14)], expr#6=[=($t1, $t5)], DEPTNO=[$t0], $f3=[$t4],
$condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableUncollect
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[2],
expr#3=[ARRAY($t1, $t2)], EXPR$0=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t0, $t3)],
expr#5=['SALES':VARCHAR(14)], expr#6=[=($t1, $t5)], DEPTNO=[$t0], $f3=[$t4],
$condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+!}
+
+# COALESCE(d.path, ARRAY[CAST(NULL AS INTEGER)]) converts to
+# CASE(IS NOT NULL($cor0.PATH), $cor0.PATH, ARRAY(null:INTEGER)). The constant
+# ARRAY(null:INTEGER) operand must not prevent extracting the CASE to the
+# left input of the Correlate operator
+select d.deptno, t.x
+from (select deptno,
+ case when deptno = 10 then array[deptno, deptno + 1] end as path
+ from dept) as d,
+lateral (select * from unnest(coalesce(d.path, array[cast(null as integer)]))
as u(x)) as t
+order by d.deptno, t.x;
++--------+----+
+| DEPTNO | X |
++--------+----+
+| 10 | 10 |
+| 10 | 11 |
+| 20 | |
+| 30 | |
+| 40 | |
++--------+----+
+(5 rows)
+
+!ok
+!if (use_old_decorr) {
+# The entire CASE produced by COALESCE has been extracted into the left input
of the
+# EnumerableCorrelate, as $f2. The right input reads the array through
$cor0.$f2.
+# The query cannot be decorrelated because of the remaining Uncollect.
+EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0], X=[$t2])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[10], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[+($t0, $t6)],
expr#8=[ARRAY($t3, $t7)], expr#9=[null:INTEGER NOT NULL ARRAY],
expr#10=[CASE($t5, $t8, $t9)], expr#11=[IS NOT NULL($t10)],
expr#12=[CAST($t10):INTEGER NOT NULL ARRAY NOT NULL],
expr#13=[CAST($t12):INTEGER ARRAY NOT NULL], expr#14=[null:INTEGER],
expr#15=[ARRAY($t14)], expr#16=[CASE($t11, $t13, $t15)], DEPTNO=[$t0],
$f2=[$t16])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableUncollect
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[$cor0], expr#2=[$t1.$f2],
EXPR$0=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+!plan
+!}
+
+!set planner-rules original
+
# End lateral.iq
