This is an automated email from the ASF dual-hosted git repository.

dongjoon-hyun pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new f646a6ebf454 [SPARK-60978][SQL] Fix wrong results by DSv2 DELETE, 
UPDATE and MERGE when column names differ only by case
f646a6ebf454 is described below

commit f646a6ebf454141f890d9add8e7a16b377a15c33
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Thu Nov 5 22:49:39 2026 -0800

    [SPARK-60978][SQL] Fix wrong results by DSv2 DELETE, UPDATE and MERGE when 
column names differ only by case
    
    ### What changes were proposed in this pull request?
    
    This PR aims to fix a correctness issue of DSv2 row-level DELETE, UPDATE 
and MERGE. `RewriteRowLevelCommand.findColOrdinal` now looks for the column 
with exactly the given name first, and falls back to `conf.resolver` only when 
there is no such column.
    
    The fallback is still needed because a connector may refer to a column in a 
different case, e.g. the row ID `pk` of a column `PK`. 
`V2ExpressionUtils.resolveRef` gives the resolved attribute the name of the 
reference.
    
    ### Why are the changes needed?
    
    This is a correctness issue. When a table has columns whose names differ 
only by case, such as `salary` and `SALARY`, an UPDATE, a MERGE or a 
group-based DELETE in a case-insensitive session, which is the default, writes 
the value of one column into the other without any error. The statement does 
not need to refer to these columns, so nothing is ambiguous.
    
    `RewriteRowLevelCommand` builds the row projections of `ReplaceData` and 
`WriteDelta` by looking up the ordinal of each table column in the query output 
by name. `findColOrdinal` returned the first column that `conf.resolver` 
matches, so `SALARY` got the ordinal of `salary`. For example, the row 
projection of the UPDATE below uses the ordinals `[1, 2, 2, 4]` instead of `[1, 
2, 3, 4]`.
    
    ```scala
    // 
spark.sql.catalog.cat=org.apache.spark.sql.connector.catalog.InMemoryRowLevelOperationTableCatalog
    // Create the table in a case-sensitive session.
    spark.conf.set("spark.sql.caseSensitive", "true")
    sql("CREATE TABLE cat.ns1.t (pk INT NOT NULL, salary INT, SALARY INT, dep 
STRING) PARTITIONED BY (dep)")
    sql("INSERT INTO cat.ns1.t VALUES (1, 100, 200, 'hr'), (2, 300, 400, 'hr')")
    spark.conf.set("spark.sql.caseSensitive", "false")
    
    // Issue 1: It should keep `SALARY`, but the table ends up with (3, 100, 
100, hr) and (2, 300, 300, hr).
    sql("UPDATE cat.ns1.t SET pk = 3 WHERE pk = 1")
    
    // Issue 2: On the same initial table, it should keep (2, 300, 400, hr), 
but the table ends up with
    // (2, 300, 300, hr).
    sql("DELETE FROM cat.ns1.t WHERE pk <= 1")
    
    // Issue 3: On the same initial table, it should keep `SALARY`, but the 
table ends up with
    // (1, 100, 100, x) and (2, 300, 300, hr).
    Seq(1).toDF("pk").createOrReplaceTempView("src")
    sql("MERGE INTO cat.ns1.t t USING src s ON t.pk = s.pk WHEN MATCHED THEN 
UPDATE SET dep = 'x'")
    ```
    
    The results above are read with `spark.sql.caseSensitive=true`.
    
    - Group-based operations write wrong values into both the updated rows and 
the rows that they copy over. Delta-based operations write wrong values into 
the updated rows.
    - Delta-based DELETE writes no data columns, and an insert-only MERGE does 
not use these projections, so they are not affected.
    - If `SALARY` is `NOT NULL` and `salary` is nullable, the UPDATE fails with 
a misleading `INSERT_COLUMN_ARITY_MISMATCH.TOO_MANY_DATA_COLUMNS` error instead.
    - The lookup was added by 
[SPARK-43324](https://issues.apache.org/jira/browse/SPARK-43324) 
([v3.5.0](https://github.com/apache/spark/releases/tag/v3.5.0), 2023-09-09) for 
delta-based operations. 
[SPARK-50820](https://issues.apache.org/jira/browse/SPARK-50820) 
([v4.0.0](https://github.com/apache/spark/releases/tag/v4.0.0), 2025-05-19) 
started to use it for group-based operations.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. An UPDATE, a MERGE or a group-based DELETE on a table whose column 
names differ only by case now writes each column's own value, and the `NOT 
NULL` case above succeeds. If such columns also differ in type or nullability, 
the scan side needs 
[SPARK-60979](https://issues.apache.org/jira/browse/SPARK-60979) (#60156) as 
well. This is a long-standing issue in the released versions, including 
[v4.3.0](https://github.com/apache/spark/releases/tag/v4.3.0) (2026-10-27).
    
    ### How was this patch tested?
    
    Pass the CIs with newly added test cases.
    - `SPARK-60978: update keeps columns whose names differ only by case` fails 
without this PR in the five UPDATE suites.
    - `SPARK-60978: delete keeps columns whose names differ only by case` fails 
without this PR in the group-based DELETE suites.
    - `SPARK-60978: merge keeps columns whose names differ only by case` fails 
without this PR in every MERGE suite.
    - `SPARK-60978: update with a row ID column whose name differs in case` 
checks the fallback to `conf.resolver` in the delta-based UPDATE suites. It 
fails if `findColOrdinal` matches exact names only.
    
    ```
    $ build/sbt 'sql/testOnly *BasedUpdateTableSuite 
*BasedNoMetadataUpdateTableSuite *DeltaBasedUpdateAsDeleteAndInsertTableSuite 
*BasedDeleteFromTableSuite *BasedNoMetadataDeleteFromTableSuite 
*BasedMergeIntoTableSuite *BasedNoMetadataMergeIntoTableSuite 
*DeltaBasedMergeIntoTableUpdateAsDeleteAndInsertSuite *MergeIntoDataFrameSuite 
*BasedRowLevelOperationCatalystRuntimeFilterSuite'
    $ build/sbt 'sql/testOnly *BasedMergeIntoSchemaEvolution*Suite 
*AlignUpdateAssignmentsSuite *AlignMergeAssignmentsSuite'
    ```
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60155 from dongjoon-hyun/SPARK-60978.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 .../catalyst/analysis/RewriteRowLevelCommand.scala |  5 +++-
 .../sql/connector/DeleteFromTableSuiteBase.scala   | 17 ++++++++++++
 .../connector/DeltaBasedUpdateTableSuiteBase.scala | 14 ++++++++++
 .../sql/connector/MergeIntoTableSuiteBase.scala    | 31 ++++++++++++++++++++++
 .../spark/sql/connector/UpdateTableSuiteBase.scala | 19 +++++++++++++
 5 files changed, 85 insertions(+), 1 deletion(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommand.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommand.scala
index 3acd090b6d06..6aebbab8cf2b 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommand.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommand.scala
@@ -318,8 +318,11 @@ trait RewriteRowLevelCommand extends Rule[LogicalPlan] {
     ProjectingInternalRow(schema, colOrdinals)
   }
 
+  // prefer the exact name as the plan may have columns whose names differ 
only by case
+  // fall back to the resolver as connectors may refer to columns using a 
different case
   private def findColOrdinal(plan: LogicalPlan, name: String): Int = {
-    plan.output.indexWhere(attr => conf.resolver(attr.name, name))
+    val ordinal = plan.output.indexWhere(_.name == name)
+    if (ordinal != -1) ordinal else plan.output.indexWhere(attr => 
conf.resolver(attr.name, name))
   }
 
   protected def buildOriginalRowIdValues(
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DeleteFromTableSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DeleteFromTableSuiteBase.scala
index 92e427d3de0c..b4698771d54c 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DeleteFromTableSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DeleteFromTableSuiteBase.scala
@@ -303,6 +303,23 @@ abstract class DeleteFromTableSuiteBase extends 
RowLevelOperationSuiteBase {
     checkDeleteMetrics(numDeletedRows = 2, numCopiedRows = 0)
   }
 
+  test("SPARK-60978: delete keeps columns whose names differ only by case") {
+    // such a table can only be created and read in a case-sensitive session
+    withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+      createAndInitTable("pk INT NOT NULL, salary INT, SALARY INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "SALARY": 200, "dep": "hr" }
+          |{ "pk": 2, "salary": 300, "SALARY": 400, "dep": "hr" }
+          |""".stripMargin)
+    }
+
+    // the statement does not name the colliding columns so it is not ambiguous
+    sql(s"DELETE FROM $tableNameAsString WHERE pk <= 1")
+
+    withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+      checkAnswer(sql(s"SELECT * FROM $tableNameAsString"), Row(2, 300, 400, 
"hr") :: Nil)
+    }
+  }
+
   test("delete with IN predicates") {
     createAndInitTable("pk INT NOT NULL, id INT, dep STRING",
       """{ "pk": 1, "id": 1, "dep": "hr" }
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DeltaBasedUpdateTableSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DeltaBasedUpdateTableSuiteBase.scala
index 49e586535a0d..1541fadc3e2d 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DeltaBasedUpdateTableSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DeltaBasedUpdateTableSuiteBase.scala
@@ -55,6 +55,20 @@ abstract class DeltaBasedUpdateTableSuiteBase extends 
UpdateTableSuiteBase {
     checkUpdateMetrics(numUpdatedRows = 1, numCopiedRows = 0)
   }
 
+  test("SPARK-60978: update with a row ID column whose name differs in case") {
+    // the table refers to its row ID column as `pk`
+    createAndInitTable("PK INT NOT NULL, id INT, dep STRING",
+      """{ "PK": 1, "id": 1, "dep": "hr" }
+        |{ "PK": 2, "id": 2, "dep": "software" }
+        |""".stripMargin)
+
+    sql(s"UPDATE $tableNameAsString SET id = -1 WHERE PK = 1")
+
+    checkAnswer(
+      sql(s"SELECT * FROM $tableNameAsString"),
+      Row(1, -1, "hr") :: Row(2, 2, "software") :: Nil)
+  }
+
   test("update with nondeterministic conditions") {
     createAndInitTable("pk INT NOT NULL, id INT, dep STRING",
       """{ "pk": 1, "id": 1, "dep": "hr" }
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoTableSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoTableSuiteBase.scala
index 964a27fc9a2e..480b17271b24 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoTableSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoTableSuiteBase.scala
@@ -1924,6 +1924,37 @@ abstract class MergeIntoTableSuiteBase extends 
RowLevelOperationSuiteBase
     }
   }
 
+  test("SPARK-60978: merge keeps columns whose names differ only by case") {
+    withTempView("source") {
+      // such a table can only be created and read in a case-sensitive session
+      withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+        createAndInitTable("pk INT NOT NULL, salary INT, SALARY INT, dep 
STRING",
+          """{ "pk": 1, "salary": 100, "SALARY": 200, "dep": "hr" }
+            |{ "pk": 2, "salary": 300, "SALARY": 400, "dep": "hr" }
+            |""".stripMargin)
+      }
+
+      Seq(1).toDF("pk").createOrReplaceTempView("source")
+
+      // the statement does not name the colliding columns so it is not 
ambiguous
+      sql(
+        s"""MERGE INTO $tableNameAsString t
+           |USING source s
+           |ON t.pk = s.pk
+           |WHEN MATCHED THEN
+           | UPDATE SET t.dep = 'support'
+           |""".stripMargin)
+
+      withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+        checkAnswer(
+          sql(s"SELECT * FROM $tableNameAsString"),
+          Seq(
+            Row(1, 100, 200, "support"), // update
+            Row(2, 300, 400, "hr"))) // unchanged
+      }
+    }
+  }
+
   test("merge refreshed relation cache") {
     withTempView("temp", "source") {
       withCache("temp") {
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/UpdateTableSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/UpdateTableSuiteBase.scala
index 1f504e268490..04b608a4a24b 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/UpdateTableSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/UpdateTableSuiteBase.scala
@@ -245,6 +245,25 @@ abstract class UpdateTableSuiteBase extends 
RowLevelOperationSuiteBase {
     checkUpdateMetrics(numUpdatedRows = 1, numCopiedRows = 1)
   }
 
+  test("SPARK-60978: update keeps columns whose names differ only by case") {
+    // such a table can only be created and read in a case-sensitive session
+    withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+      createAndInitTable("pk INT NOT NULL, salary INT, SALARY INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "SALARY": 200, "dep": "hr" }
+          |{ "pk": 2, "salary": 300, "SALARY": 400, "dep": "hr" }
+          |""".stripMargin)
+    }
+
+    // the statement does not name the colliding columns so it is not ambiguous
+    sql(s"UPDATE $tableNameAsString SET pk = 3 WHERE pk = 1")
+
+    withSQLConf(SQLConf.CASE_SENSITIVE.key -> "true") {
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Row(3, 100, 200, "hr") :: Row(2, 300, 400, "hr") :: Nil)
+    }
+  }
+
   test("update non-existing records") {
     createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
       """{ "pk": 1, "salary": 100, "dep": "hr" }


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to