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 869fe15f36 [CALCITE-7411] When a SCALAR_QUERY in PROJECT contains
correlated variables execution fails using TopDownGeneralDecorrelator
869fe15f36 is described below
commit 869fe15f36fa5579f2a1cf289958fa763af36a9b
Author: Zhen Chen <[email protected]>
AuthorDate: Mon Feb 9 10:45:21 2026 +0800
[CALCITE-7411] When a SCALAR_QUERY in PROJECT contains correlated variables
execution fails using TopDownGeneralDecorrelator
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 21 ++++++++-
.../sql2rel/TopDownGeneralDecorrelator.java | 9 +++-
.../org/apache/calcite/test/CoreQuidemTest2.java | 1 -
.../org/apache/calcite/test/RelOptRulesTest.java | 24 ++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 53 +++++++++++++++++++++
core/src/test/resources/sql/measure-paper.iq | 55 ++++++++++++++++++++++
6 files changed, 159 insertions(+), 4 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 8fc83104c1..4622176a07 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -3882,7 +3882,26 @@ private void createAggImpl(Blackboard bb,
// implement the SELECT list
relBuilder.project(projects.leftList(), projects.rightList())
.rename(projects.rightList());
- bb.setRoot(relBuilder.build(), false);
+
+ RelNode tmpProject = relBuilder.build();
+
+ // Check for correlation variables that may be used in the SELECT list
+ final RelNode finalProject;
+ final CorrelationUse correlationUse = getCorrelationUse(bb, tmpProject);
+ if (correlationUse != null) {
+ assert correlationUse.r instanceof Project;
+ // correlation variables have been normalized in correlationUse.r,
+ // we should use expressions in correlationUse.r
+ Project project1 = (Project) correlationUse.r;
+ finalProject = relBuilder.push(tmpProject.getInput(0))
+ .project(project1.getProjects(),
project1.getRowType().getFieldNames(), true,
+ ImmutableSet.of(correlationUse.id))
+ .build();
+ } else {
+ finalProject = tmpProject;
+ }
+
+ bb.setRoot(finalProject, false);
// Tell bb which of group columns are sorted.
bb.columnMonotonicities.clear();
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 6959d56354..50b2006154 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -919,10 +919,15 @@ static List<RexNode> rewrite(
@Override public RexNode visitInputRef(RexInputRef inputRef) {
int newIndex =
requireNonNull(unnestedQuery.oldToNewOutputs.get(inputRef.getIndex()));
- if (newIndex == inputRef.getIndex()) {
+ if (newIndex == inputRef.getIndex()
+ && inputRef.getType().equals(
+
unnestedQuery.r.getRowType().getFieldList().get(newIndex).getType())) {
return inputRef;
}
- return new RexInputRef(newIndex, inputRef.getType());
+ // Use the type from the new row type to handle nullability changes
+ // (e.g., after LEFT JOIN, right-side fields become nullable)
+ return new RexInputRef(newIndex,
+ unnestedQuery.r.getRowType().getFieldList().get(newIndex).getType());
}
@Override public RexNode visitFieldAccess(RexFieldAccess fieldAccess) {
diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest2.java
b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest2.java
index 0164eeea6e..b8ef8562bc 100644
--- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest2.java
+++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest2.java
@@ -47,7 +47,6 @@ public static void main(String[] args) throws Exception {
// TODO: Support measure
paths.remove("sql/measure.iq");
paths.remove("sql/sub-query.iq");
- paths.remove("sql/measure-paper.iq");
return paths;
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 81f07a6f6a..98beae87c7 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -12243,4 +12243,28 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
sql(sql).withPlanner(hepPlanner)
.check();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7411";>[CALCITE-7411]
+ * When a SCALAR_QUERY in PROJECT contains correlated variables execution
fails
+ * using TopDownGeneralDecorrelator</a>. */
+ @Test void testTopDownGeneralDecorrelateForMeasure() {
+ final String sql = "SELECT job,\n"
+ + " (SELECT\n"
+ + " CAST(SUM(i.sal) - SUM(COALESCE(i.comm, 0)) AS DECIMAL(10, 2))
/ SUM(i.sal)\n"
+ + " FROM emp AS i\n"
+ + " WHERE i.job = e.job) AS profitMargin,\n"
+ + " COUNT(*) AS \"count\"\n"
+ + "FROM emp AS e\n"
+ + "GROUP BY job";
+
+ sql(sql)
+ .withRule(
+ CoreRules.PROJECT_SUB_QUERY_TO_MARK_CORRELATE,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
}
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 103744e311..e2f83bf2c1 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -20169,6 +20169,59 @@ LogicalProject(EMPNO=[$0])
LogicalJoin(condition=[IS NOT DISTINCT FROM($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testTopDownGeneralDecorrelateForMeasure">
+ <Resource name="sql">
+ <![CDATA[SELECT job,
+ (SELECT
+ CAST(SUM(i.sal) - SUM(COALESCE(i.comm, 0)) AS DECIMAL(10, 2)) /
SUM(i.sal)
+ FROM emp AS i
+ WHERE i.job = e.job) AS profitMargin,
+ COUNT(*) AS "count"
+FROM emp AS e
+GROUP BY job]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], JOB=[$0], PROFITMARGIN=[$SCALAR_QUERY({
+LogicalProject(EXPR$0=[/(CAST(-($0, $1)):DECIMAL(10, 2), $0)])
+ LogicalAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[SUM($1)])
+ LogicalProject(SAL=[$5], $f1=[$6])
+ LogicalFilter(condition=[=($2, $cor0.JOB)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], count=[$1])
+ LogicalAggregate(group=[{0}], count=[COUNT()])
+ LogicalProject(JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(JOB=[$0], PROFITMARGIN=[$2], count=[$1])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalAggregate(group=[{0}], count=[COUNT()])
+ LogicalProject(JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EXPR$0=[/(CAST(-($0, $1)):DECIMAL(10, 2), $0)])
+ LogicalAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[SUM($1)])
+ LogicalProject(SAL=[$5], $f1=[$6])
+ LogicalFilter(condition=[=($2, $cor0.JOB)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(JOB=[$0], PROFITMARGIN=[$2], count=[$1])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)], joinType=[left])
+ LogicalAggregate(group=[{0}], count=[COUNT()])
+ LogicalProject(JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject($f0=[/(CAST(-($1, $2)):DECIMAL(10, 2), $1)], JOB=[$0])
+ LogicalAggregate(group=[{2}], agg#0=[SUM($0)], agg#1=[SUM($1)])
+ LogicalProject(SAL=[$5], COMM=[$6], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/measure-paper.iq
b/core/src/test/resources/sql/measure-paper.iq
index 190713d811..c64150fdba 100644
--- a/core/src/test/resources/sql/measure-paper.iq
+++ b/core/src/test/resources/sql/measure-paper.iq
@@ -224,6 +224,7 @@ SELECT "prodName",
COUNT(*) AS "count"
FROM "Orders" AS o
GROUP BY "prodName";
+!if (use_old_decorr) {
+----------+--------------+-------+
| prodName | profitMargin | count |
+----------+--------------+-------+
@@ -234,6 +235,20 @@ GROUP BY "prodName";
(3 rows)
!ok
+!}
+
+!if (use_new_decorr) {
++----------+--------------------+-------+
+| prodName | profitMargin | count |
++----------+--------------------+-------+
+| Acme | 0.60 | 1 |
+| Happy | 0.4705882352941176 | 3 |
+| Whizz | 0.6666666666666667 | 1 |
++----------+--------------------+-------+
+(3 rows)
+
+!ok
+!}
# Profit margin for 'Happy' orders for each customer
SELECT "custName",
@@ -505,4 +520,44 @@ FROM
!ok
!}
+# [CALCITE-7411] When a SCALAR_QUERY in PROJECT contains correlated variables
+# execution fails using TopDownGeneralDecorrelator
+!use scott
+SELECT job,
+ (SELECT
+ CAST(SUM(i.sal) - SUM(COALESCE(i.comm, 0)) AS DECIMAL(10, 2)) /
SUM(i.sal)
+ FROM emp AS i
+ WHERE i.job = e.job) AS profitMargin,
+ COUNT(*) AS "count"
+FROM emp AS e
+GROUP BY job;
+!if (use_old_decorr) {
++-----------+--------------+-------+
+| JOB | PROFITMARGIN | count |
++-----------+--------------+-------+
+| ANALYST | 1.000000 | 2 |
+| CLERK | 1.000000 | 4 |
+| MANAGER | 1.000000 | 3 |
+| PRESIDENT | 1.000000 | 1 |
+| SALESMAN | 0.607142 | 4 |
++-----------+--------------+-------+
+(5 rows)
+
+!ok
+!}
+
+!if (use_new_decorr) {
++-----------+--------------------+-------+
+| JOB | PROFITMARGIN | count |
++-----------+--------------------+-------+
+| ANALYST | 1 | 2 |
+| CLERK | 1 | 4 |
+| MANAGER | 1 | 3 |
+| PRESIDENT | 1 | 1 |
+| SALESMAN | 0.6071428571428571 | 4 |
++-----------+--------------------+-------+
+(5 rows)
+
+!ok
+!}
# End measure-paper.iq
