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 4e9750ad7b [CALCITE-7856] MERGE with a correlated sub-query in UPDATE
SET resolves a target column to the same-named source column
4e9750ad7b is described below
commit 4e9750ad7bb30670143e1fef8cb626da8979ca70
Author: Iurii Gerzhedovich <[email protected]>
AuthorDate: Wed Oct 14 14:20:18 2026 +0300
[CALCITE-7856] MERGE with a correlated sub-query in UPDATE SET resolves a
target column to the same-named source column
---
.../calcite/sql/validate/SqlValidatorImpl.java | 25 ++++++++++++++++++++--
.../apache/calcite/sql2rel/SqlToRelConverter.java | 5 ++++-
.../apache/calcite/test/SqlToRelConverterTest.java | 11 ++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 23 ++++++++++++++++++++
4 files changed, 61 insertions(+), 3 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 85d7bc8bb4..fd48733f8c 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -2128,6 +2128,20 @@ && requireNonNull(((SqlNumericLiteral)
node).bigDecimalValue())
}
}
+ /** Shuttle that copies every call and identifier of a tree, so that the copy
+ * shares no mutable node with the original. */
+ private static class DeepCopyShuttle extends SqlShuttle {
+ @Override public @Nullable SqlNode visit(SqlCall call) {
+ final CallCopyingArgHandler argHandler = new CallCopyingArgHandler(call,
true);
+ call.getOperator().acceptCall(this, call, false, argHandler);
+ return argHandler.result();
+ }
+
+ @Override public @Nullable SqlNode visit(SqlIdentifier id) {
+ return id.clone(id.getParserPosition());
+ }
+ }
+
private static void rewriteMerge(SqlMerge call) {
SqlNodeList selectList;
SqlUpdate updateStmt = call.getUpdateCall();
@@ -2135,9 +2149,16 @@ private static void rewriteMerge(SqlMerge call) {
// if we have an update statement, just clone the select list
// from the update statement's source since it's the same as
// what we want for the select list of the merge source -- '*'
- // followed by the update set expressions
+ // followed by the update set expressions. The list must be a deep copy:
+ // a shallow clone would share sub-queries (and their identifiers) with
+ // the update's own source select, and the validator keeps only one
+ // scope per node, so correlated references would be resolved against
+ // the wrong scope.
SqlSelect sourceSelect =
SqlNonNullableAccessors.getSourceSelect(updateStmt);
- selectList =
SqlNode.clone(SqlNonNullableAccessors.getSelectList(sourceSelect));
+ selectList =
+ (SqlNodeList) requireNonNull(
+ SqlNonNullableAccessors.getSelectList(sourceSelect)
+ .accept(new DeepCopyShuttle()));
} else {
// otherwise, just use select *
selectList = new SqlNodeList(SqlParserPos.ZERO);
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 828c3d94ab..0223c778a6 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -4917,8 +4917,11 @@ private RelNode convertMerge(SqlMerge call) {
Util.skip(project.getProjects(), nSourceFields));
}
+ // Keep the correlation variables set by the source projection: the SET
+ // expressions may contain correlated sub-queries.
relBuilder.push(join)
- .project(projects);
+ .project(projects, ImmutableList.of(), false,
+ ((LogicalProject) mergeSourceRel).getVariablesSet());
return LogicalTableModify.create(targetTable, catalogReader,
relBuilder.build(), LogicalTableModify.Operation.MERGE,
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 9dae64864f..4b26c3f284 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -4135,6 +4135,17 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).ok();
}
+ /** Tests a MERGE whose UPDATE contains a correlated sub-query referencing a
+ * target column that has the same name as a source column. */
+ @Test void testMergeUpdateCorrelatedSubQuery() {
+ final String sql = "merge into empnullables e\n"
+ + "using (select * from emp) t\n"
+ + "on e.empno = t.empno\n"
+ + "when matched then update\n"
+ + "set sal = (select d.deptno from dept d where d.deptno = e.deptno)";
+ sql(sql).withExpand(false).ok();
+ }
+
@Test void testSelectView() {
// translated condition: deptno = 20 and sal > 1000 and empno > 100
final String sql = "select * from emp_20 where empno > 100";
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 36200c6707..21db30b73e 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -5966,6 +5966,29 @@ LogicalTableModify(table=[[CATALOG, SALES,
EMPNULLABLES]], operation=[MERGE], up
LogicalFilter(condition=[IS NULL($7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMergeUpdateCorrelatedSubQuery">
+ <Resource name="sql">
+ <![CDATA[merge into empnullables e
+using (select * from emp) t
+on e.empno = t.empno
+when matched then update
+set sal = (select d.deptno from dept d where d.deptno = e.deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
updateColumnList=[[SAL]], flattened=[true])
+ LogicalProject(variablesSet=[[$cor0]], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17], $f9=[$SCALAR_QUERY({
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($0, $cor0.DEPTNO0)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
</TestCase>
