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

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


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new bd5fb2eed696 [SPARK-60721][SQL] Reject assignments to metadata columns 
in DSv2 UPDATE and MERGE
bd5fb2eed696 is described below

commit bd5fb2eed69652bffd2463071ef4dcd57c79fef9
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Thu Oct 29 21:40:20 2026 -0700

    [SPARK-60721][SQL] Reject assignments to metadata columns in DSv2 UPDATE 
and MERGE
    
    ### What changes were proposed in this pull request?
    
    This PR makes DSv2 UPDATE and MERGE fail analysis when an assignment 
targets a metadata column of the target table, instead of silently ignoring the 
assignment.
    
    The assignment keys of UPDATE and MERGE are resolved against the target 
table including its metadata columns, so a key such as `_partition` can resolve 
to a metadata column. `ResolveRowLevelCommandAssignments` then aligns the 
assignments with the table attributes using 
`AssignmentUtils.alignUpdateAssignments` and 
`AssignmentUtils.alignInsertAssignments`, which produce one value per table 
attribute, so an assignment to a metadata column is dropped.
    
    `ResolveRowLevelCommandAssignments` now reports assignments whose key 
refers to a metadata column with the existing 
`DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS` error, which the 
alignment already uses for other invalid assignments such as multiple 
assignments to the same column. A key refers to a metadata column if its 
attribute carries the metadata column marker (`MetadataAttribute.isValid`), so 
the check does not depend on expression IDs.
    
    The rule checks the keys of an UPDATE or MERGE on a table that supports 
row-level operations as soon as they are resolved, without waiting for the rest 
of the command. So a `MERGE WITH SCHEMA EVOLUTION` fails before 
`ResolveSchemaEvolution` changes the target table, which happens before the 
assignments are aligned.
    
    This PR also adds an internal legacy config, 
`spark.sql.legacy.ignoreMetadataColumnAssignments` (default `false`). When it 
is `true`, Spark does not reject these assignments, as before. The SQL 
migration guide documents the new behavior and the config.
    
    ### Why are the changes needed?
    
    The values of metadata columns are produced by the data source. DSv2 
row-level operations pass the metadata values they read (or nulls, see 
`MetadataColumn.PRESERVE_ON_UPDATE`) back to the writer and have no way to 
write a value that a statement assigns to a metadata column. However, DSv2 
UPDATE and MERGE statements that assign to a metadata column succeed, apply the 
other assignments and silently drop the assigned value. For instance, with the 
in-memory row-level table of `RowLevelOp [...]
    
    ```sql
    UPDATE cat.ns1.test_table SET id = -1, index = 5 WHERE pk = 1
    ```
    
    sets `id` to `-1` and ignores `index = 5`. The same happens with:
    - `WHEN MATCHED` and `WHEN NOT MATCHED BY SOURCE` `UPDATE SET` clauses and 
`WHEN NOT MATCHED` `INSERT` clauses of MERGE, including insert-only MERGE.
    - The DataFrame merge API, e.g. `whenNotMatched().insert(...)` with 
`"_partition" -> lit("x")`.
    - `MERGE WITH SCHEMA EVOLUTION` with `UPDATE SET *` or `INSERT *` when the 
source has a column named like a metadata column of the target, e.g. `index`. 
The expanded assignment resolves to the metadata column, so the schema is not 
evolved and the values of the source column are dropped.
    
    The same happens when the command is resolved in a later analyzer 
iteration, e.g. because of an implicit cast in a condition such as `WHERE id < 
1.5`.
    
    All of these happen with group-based and delta-based operations, including 
operations that split updates and operations that require no metadata columns. 
For comparison, `INSERT INTO cat.ns1.test_table (pk, id, dep, index) VALUES 
(...)` already fails with `INSERT_COLUMN_ARITY_MISMATCH.TOO_MANY_DATA_COLUMNS`.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. DSv2 UPDATE and MERGE statements that assign to a metadata column of 
the target table now fail instead of ignoring the assignment. For the example 
above:
    
    ```
    [DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS] Cannot resolve 
"id = -1", "index = 5" due to data type mismatch:
    - Cannot assign to metadata column 'index' SQLSTATE: 42K09
    ```
    
    `MERGE WITH SCHEMA EVOLUTION` with `UPDATE SET *` or `INSERT *` and a 
source column named like a metadata column of the target fails the same way, so 
the source column has to be renamed or dropped. The statement fails before 
Spark changes the schema of the target table. Without the evolution clause, 
`UPDATE SET *` and `INSERT *` still ignore such a source column as before.
    
    To restore the previous behavior, set 
`spark.sql.legacy.ignoreMetadataColumnAssignments` to `true`.
    
    By code reading, the assignments have been dropped since the alignment of 
UPDATE and MERGE assignments was added in Apache Spark 
[3.5.0](https://github.com/apache/spark/releases/tag/v3.5.0) (2023-09-09), and 
the `UPDATE SET *` and `INSERT *` case since MERGE schema evolution was added 
in Apache Spark [4.1.0](https://github.com/apache/spark/releases/tag/v4.1.0) 
(2025-12-11).
    
    ### How was this patch tested?
    
    Added tests:
    - `UpdateTableSuiteBase`: an unqualified and a qualified assignment to a 
metadata column, and an UPDATE resolved in a later analyzer iteration. With the 
legacy config, an UPDATE ignores the assignment as before.
    - `MergeIntoTableSuiteBase`: `WHEN MATCHED` and `WHEN NOT MATCHED BY 
SOURCE` updates, a `WHEN NOT MATCHED` insert in a MERGE with an update clause 
and in an insert-only MERGE, and a MERGE resolved in a later analyzer 
iteration. A `MERGE WITH SCHEMA EVOLUTION` whose source also has a column 
missing from the target fails before the schema is evolved, also in the 
transaction of the in-memory catalog. With the legacy config, a MERGE ignores 
the assignments as before.
    - `MergeIntoDataFrameSuite`: the same through the DataFrame API `update` 
and `insert`.
    - `MergeIntoSchemaEvolutionExtraSourceColumnTests`: `UPDATE SET *` with 
`INSERT *`, and insert-only `INSERT *`, with a source column named `index` or 
`_partition`. Without the evolution clause, or with the legacy config, the 
source column is ignored as before.
    
    They run in the group-based and delta-based suites, including the split 
update, NoMetadata and NoCodegen variants and the SQL and DataFrame schema 
evolution suites. Without the fix, all 30 test runs that expect the error fail 
because the statements succeed, and the other 32 test runs pass.
    
    ```
    build/sbt "sql/testOnly *UpdateTableSuite 
*UpdateAsDeleteAndInsertTableSuite *MergeIntoTableSuite 
*MergeIntoTableNoCodegenSuite *MergeIntoTableUpdateAsDeleteAndInsertSuite 
*MergeIntoDataFrameSuite *MergeIntoSchemaEvolution*Suite -- -z SPARK-60721"
    ```
    
    Also ran the DSv2 DELETE, UPDATE and MERGE suites (including the 
NoMetadata, split update and NoCodegen variants), the MERGE schema evolution 
suites, `MergeIntoDataFrameSuite`, the row-level runtime filter suites, 
`MetadataColumnSuite`, `PlanResolutionSuite`, `AlignUpdateAssignmentsSuite`, 
`AlignMergeAssignmentsSuite` and `SQLConfSuite` locally.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #59930 from dongjoon-hyun/SPARK-60721.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
    (cherry picked from commit fe37cc5656f0bab7fbd1ca8cf3794c275c7b7639)
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 docs/sql-migration-guide.md                        |   1 +
 .../ResolveRowLevelCommandAssignments.scala        |  40 +++++++-
 .../org/apache/spark/sql/internal/SQLConf.scala    |  12 +++
 .../sql/connector/MergeIntoDataFrameSuite.scala    |  33 ++++++
 ...IntoSchemaEvolutionExtraSourceColumnTests.scala |  60 +++++++++++
 .../sql/connector/MergeIntoTableSuiteBase.scala    | 112 +++++++++++++++++++++
 .../spark/sql/connector/UpdateTableSuiteBase.scala |  38 +++++++
 7 files changed, 295 insertions(+), 1 deletion(-)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 2b0c576517e3..ee39e4cba2b7 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -62,6 +62,7 @@ license: |
 
 - Since Spark 4.4, when a format is specified, `to_timestamp`, 
`try_to_timestamp`, and `to_timestamp_ltz` cast a numeric first argument to a 
string and parse it with the format, for example `to_timestamp(20201231, 
'yyyyMMdd')` returns `2020-12-31 00:00:00`. This matches the existing behavior 
when `spark.sql.timestampType` is `TIMESTAMP_NTZ`. Previously, with the default 
`TIMESTAMP_LTZ` timestamp type, such calls failed with `INTERNAL_ERROR`. Note 
that the string form of the number is par [...]
 - Since Spark 4.4, the Spark Thrift Server logs a warning at startup when 
`hive.server2.enable.doAs` is `true` and `hive.server2.authentication` verifies 
user identities (any recognized type other than `NONE`/`NOSASL`), because 
executor-side data access still runs as the server's own service identity 
rather than the connecting user (SPARK-5159). Set 
`spark.sql.hive.thriftServer.allowIneffectiveDoAs` to `true` to acknowledge the 
limitation and silence the warning. Spark 5.0 is expected to [...]
+- Since Spark 4.4, `UPDATE` and `MERGE` on a Data Source V2 table fail with 
`DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS` when they assign to 
a metadata column of the table. This includes `UPDATE SET *` and `INSERT *` in 
`MERGE WITH SCHEMA EVOLUTION` when the source has a column with the name of a 
metadata column of the target table. Previously, these assignments passed 
analysis and were usually ignored. To restore the previous behavior, set 
`spark.sql.legacy.ignoreMetadata [...]
 
 ## Upgrading from Spark SQL 4.2 to 4.3
 
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveRowLevelCommandAssignments.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveRowLevelCommandAssignments.scala
index 229559d96325..050a4295c288 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveRowLevelCommandAssignments.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveRowLevelCommandAssignments.scala
@@ -20,7 +20,7 @@ package org.apache.spark.sql.catalyst.analysis
 import scala.collection.mutable
 
 import org.apache.spark.sql.AnalysisException
-import org.apache.spark.sql.catalyst.expressions.{Attribute, AttributeMap, 
AttributeReference, Cast}
+import org.apache.spark.sql.catalyst.expressions.{Attribute, AttributeMap, 
AttributeReference, Cast, MetadataAttribute}
 import org.apache.spark.sql.catalyst.expressions.objects.AssertNotNull
 import org.apache.spark.sql.catalyst.plans.logical.{Assignment, DeleteAction, 
InsertAction, LogicalPlan, MergeAction, MergeIntoTable, UpdateAction, 
UpdateTable}
 import org.apache.spark.sql.catalyst.rules.Rule
@@ -28,6 +28,7 @@ import org.apache.spark.sql.catalyst.trees.TreePattern.COMMAND
 import org.apache.spark.sql.catalyst.util.CharVarcharUtils
 import org.apache.spark.sql.errors.QueryCompilationErrors
 import org.apache.spark.sql.execution.datasources.v2.{DataSourceV2Relation, 
ExtractV2Table}
+import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.internal.SQLConf.StoreAssignmentPolicy
 import org.apache.spark.sql.internal.connector.SchemaAlignmentConfig
 import 
org.apache.spark.sql.internal.connector.SchemaAlignmentConfig.AnsiStoreAssignmentCastCheck
@@ -44,6 +45,9 @@ object ResolveRowLevelCommandAssignments extends 
Rule[LogicalPlan] {
 
   override def apply(plan: LogicalPlan): LogicalPlan = 
plan.resolveOperatorsWithPruning(
     _.containsPattern(COMMAND), ruleId) {
+    case MetadataColumnAssignments(assignments, errors) =>
+      throw 
QueryCompilationErrors.invalidRowLevelOperationAssignments(assignments, errors)
+
     case u: UpdateTable if !u.skipSchemaResolution && u.resolved && 
u.rewritable && !u.aligned =>
       validateStoreAssignmentPolicy()
       val (newTable, metadataAttrMap) = cleanAttrMetadata(u.table)
@@ -82,6 +86,40 @@ object ResolveRowLevelCommandAssignments extends 
Rule[LogicalPlan] {
       resolveAssignments(m)
   }
 
+  // The values of metadata columns are produced by the data source and cannot 
be written, so
+  // assignments to metadata columns are rejected unless the legacy behavior 
is enabled. The keys
+  // are checked as soon as they are resolved, so that the command fails 
before schema evolution
+  // changes the target table.
+  private object MetadataColumnAssignments {
+    def unapply(plan: LogicalPlan): Option[(Seq[Assignment], Seq[String])] = {
+      val assignmentsToCheck = plan match {
+        case _ if 
conf.getConf(SQLConf.LEGACY_IGNORE_METADATA_COLUMN_ASSIGNMENTS) =>
+          Nil
+        case u: UpdateTable if !u.skipSchemaResolution && u.rewritable =>
+          Seq(u.assignments)
+        case m: MergeIntoTable if !m.skipSchemaResolution && m.rewritable =>
+          val actions = m.matchedActions ++ m.notMatchedActions ++ 
m.notMatchedBySourceActions
+          actions.collect {
+            case UpdateAction(_, assignments, _) => assignments
+            case InsertAction(_, assignments) => assignments
+          }
+        case _ =>
+          Nil
+      }
+      assignmentsToCheck.iterator
+        .map(assignments => (assignments, 
metadataColumnAssignmentErrors(assignments)))
+        .find { case (_, errors) => errors.nonEmpty }
+    }
+  }
+
+  private def metadataColumnAssignmentErrors(assignments: Seq[Assignment]): 
Seq[String] = {
+    assignments.map(_.key).filter(_.resolved).flatMap(_.references)
+      .filter(attr => MetadataAttribute.isValid(attr.metadata))
+      .map(_.name)
+      .distinct
+      .map(name => s"Cannot assign to metadata column '$name'")
+  }
+
   private def shouldAlignAssignments(m: MergeIntoTable): Boolean = {
     !m.skipSchemaResolution && m.resolved && !m.aligned
   }
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 9ccbc9e12f9c..692aac02e565 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -8957,6 +8957,18 @@ object SQLConf {
       .booleanConf
       .createWithDefault(false)
 
+  val LEGACY_IGNORE_METADATA_COLUMN_ASSIGNMENTS =
+    buildConf("spark.sql.legacy.ignoreMetadataColumnAssignments")
+      .internal()
+      .doc("When true, UPDATE and MERGE on a Data Source V2 table do not 
reject assignments to " +
+        "metadata columns of the table, as in Spark 4.3 and earlier, where 
such assignments " +
+        "passed analysis and were usually ignored. When false, such 
assignments fail analysis, " +
+        "because the values of metadata columns are produced by the data 
source.")
+      .version("4.4.0")
+      .withBindingPolicy(ConfigBindingPolicy.SESSION)
+      .booleanConf
+      .createWithDefault(false)
+
   val TIME_TYPE_ENABLED =
     buildConf("spark.sql.timeType.enabled")
       .internal()
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoDataFrameSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoDataFrameSuite.scala
index a627b33d2e45..f2722c976b91 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoDataFrameSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoDataFrameSuite.scala
@@ -935,6 +935,39 @@ class MergeIntoDataFrameSuite extends 
RowLevelOperationSuiteBase {
     }
   }
 
+  test("SPARK-60721: merge with assignments to metadata columns") {
+    withTempView("source") {
+      createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "dep": "hr" }
+          |{ "pk": 2, "salary": 200, "dep": "software" }
+          |""".stripMargin)
+
+      Seq((1, "hr"), (3, "hr")).toDF("pk", 
"dep").createOrReplaceTempView("source")
+
+      def mergeBuilder: MergeIntoWriter[Row] = spark.table("source")
+        .mergeInto(tableNameAsString, $"source.pk" === col(tableNameAsString + 
".pk"))
+
+      Seq(
+        mergeBuilder.whenMatched().update(Map("index" -> lit(5))) -> "index",
+        mergeBuilder.whenNotMatched().insert(Map(
+          "pk" -> col("source.pk"),
+          "salary" -> lit(0),
+          "dep" -> col("source.dep"),
+          "_partition" -> lit("x"))) -> "_partition"
+      ).foreach { case (writer, metadataCol) =>
+        val e = intercept[AnalysisException](writer.merge())
+        assert(e.getCondition == 
"DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS")
+        assert(e.getMessage.contains(s"Cannot assign to metadata column 
'$metadataCol'"))
+      }
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Seq(
+          Row(1, 100, "hr"), // unchanged
+          Row(2, 200, "software"))) // unchanged
+    }
+  }
+
   test("merge refreshed relation cache") {
     withTempView("temp", "source") {
       withCache("temp") {
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoSchemaEvolutionExtraSourceColumnTests.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoSchemaEvolutionExtraSourceColumnTests.scala
index fb120d6e887a..878252a94edf 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoSchemaEvolutionExtraSourceColumnTests.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/MergeIntoSchemaEvolutionExtraSourceColumnTests.scala
@@ -18,6 +18,7 @@
 package org.apache.spark.sql.connector
 
 import org.apache.spark.sql.Row
+import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types._
 
 /**
@@ -476,6 +477,65 @@ trait MergeIntoSchemaEvolutionExtraSourceColumnTests 
extends MergeIntoSchemaEvol
     )
   }
 
+  // The target exposes metadata columns `index` and `_partition`
+  testEvolution("SPARK-60721: source has extra column named like a target 
metadata column" +
+    " with set all columns")(
+    targetData = Seq(
+      (1, 100, "hr"),
+      (2, 200, "software")
+    ).toDF("pk", "salary", "dep"),
+    sourceData = Seq(
+      (1, 150, "hr", 10),
+      (3, 350, "hr", 30)
+    ).toDF("pk", "salary", "dep", "index"),
+    clauses = Seq(updateAll(), insertAll()),
+    expectErrorContains = "Cannot assign to metadata column 'index'",
+    expectedWithoutEvolution = Seq(
+      (1, 150, "hr"),
+      (2, 200, "software"),
+      (3, 350, "hr")).toDF("pk", "salary", "dep")
+  )
+
+  testEvolution("SPARK-60721: source has extra column named like a target 
metadata column" +
+    " with insert all")(
+    targetData = Seq(
+      (1, 100, "hr"),
+      (2, 200, "software")
+    ).toDF("pk", "salary", "dep"),
+    sourceData = Seq(
+      (1, 150, "hr", "p1"),
+      (3, 350, "hr", "p3")
+    ).toDF("pk", "salary", "dep", "_partition"),
+    clauses = Seq(insertAll()),
+    expectErrorContains = "Cannot assign to metadata column '_partition'",
+    expectedWithoutEvolution = Seq(
+      (1, 100, "hr"),
+      (2, 200, "software"),
+      (3, 350, "hr")).toDF("pk", "salary", "dep")
+  )
+
+  testEvolution("SPARK-60721: source has extra column named like a target 
metadata column" +
+    " with set all columns in legacy mode")(
+    targetData = Seq(
+      (1, 100, "hr"),
+      (2, 200, "software")
+    ).toDF("pk", "salary", "dep"),
+    sourceData = Seq(
+      (1, 150, "hr", 10),
+      (3, 350, "hr", 30)
+    ).toDF("pk", "salary", "dep", "index"),
+    clauses = Seq(updateAll(), insertAll()),
+    expected = Seq(
+      (1, 150, "hr"),
+      (2, 200, "software"),
+      (3, 350, "hr")).toDF("pk", "salary", "dep"),
+    expectedWithoutEvolution = Seq(
+      (1, 150, "hr"),
+      (2, 200, "software"),
+      (3, 350, "hr")).toDF("pk", "salary", "dep"),
+    confs = Seq(SQLConf.LEGACY_IGNORE_METADATA_COLUMN_ASSIGNMENTS.key -> 
"true")
+  )
+
   // When assigning s.bonus to existing t.salary, bonus column should NOT be 
added to schema
   testEvolution("source has extra column -" +
     "should not evolve when assigning existing target column from extra source 
column")(
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 9dd664b9cb74..964a27fc9a2e 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
@@ -928,6 +928,118 @@ abstract class MergeIntoTableSuiteBase extends 
RowLevelOperationSuiteBase
     }
   }
 
+  test("SPARK-60721: merge with assignments to metadata columns") {
+    withTempView("source") {
+      createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "dep": "hr" }
+          |{ "pk": 2, "salary": 200, "dep": "software" }
+          |""".stripMargin)
+
+      Seq((1, "hr"), (3, "hr")).toDF("pk", 
"dep").createOrReplaceTempView("source")
+
+      Seq(
+        "WHEN MATCHED THEN UPDATE SET t.salary = -1, t.index = 5" -> "index",
+        "WHEN NOT MATCHED BY SOURCE THEN UPDATE SET t._partition = 'x'" -> 
"_partition",
+        """WHEN MATCHED THEN UPDATE SET salary = -1
+          |WHEN NOT MATCHED THEN INSERT (pk, salary, dep, _partition)
+          | VALUES (s.pk, 0, s.dep, 'x')""".stripMargin -> "_partition",
+        // insert-only MERGE
+        "WHEN NOT MATCHED THEN INSERT (pk, salary, dep, index) VALUES (s.pk, 
0, s.dep, 7)" ->
+          "index",
+        // the implicit cast in the condition resolves the command in a later 
analyzer iteration
+        "WHEN MATCHED AND t.salary < 150.5 THEN UPDATE SET index = 5" -> 
"index"
+      ).foreach { case (clauses, metadataCol) =>
+        val e = intercept[AnalysisException] {
+          sql(
+            s"""MERGE INTO $tableNameAsString t
+               |USING source s
+               |ON t.pk = s.pk
+               |$clauses
+               |""".stripMargin)
+        }
+        assert(e.getCondition == 
"DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS")
+        assert(e.getMessage.contains(s"Cannot assign to metadata column 
'$metadataCol'"))
+      }
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Seq(
+          Row(1, 100, "hr"), // unchanged
+          Row(2, 200, "software"))) // unchanged
+    }
+  }
+
+  test("SPARK-60721: merge ignores assignments to metadata columns in legacy 
mode") {
+    withTempView("source") {
+      createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "dep": "hr" }
+          |{ "pk": 2, "salary": 200, "dep": "software" }
+          |""".stripMargin)
+
+      Seq((1, "hr"), (3, "hr")).toDF("pk", 
"dep").createOrReplaceTempView("source")
+
+      withSQLConf(SQLConf.LEGACY_IGNORE_METADATA_COLUMN_ASSIGNMENTS.key -> 
"true") {
+        sql(
+          s"""MERGE INTO $tableNameAsString t
+             |USING source s
+             |ON t.pk = s.pk
+             |WHEN MATCHED THEN
+             | UPDATE SET t.salary = -1, t.index = 5
+             |WHEN NOT MATCHED THEN
+             | INSERT (pk, salary, dep, _partition) VALUES (s.pk, 0, s.dep, 
'x')
+             |""".stripMargin)
+      }
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Seq(
+          Row(1, -1, "hr"), // updated
+          Row(2, 200, "software"), // unchanged
+          Row(3, 0, "hr"))) // new
+    }
+  }
+
+  test("SPARK-60721: merge with schema evolution rejects assignments to 
metadata columns" +
+      " before evolving the schema") {
+    withTempView("source") {
+      createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
+        """{ "pk": 1, "salary": 100, "dep": "hr" }
+          |{ "pk": 2, "salary": 200, "dep": "software" }
+          |""".stripMargin)
+
+      // `bonus` is missing from the target, so schema evolution would add it
+      Seq((1, 150, "hr", 10, 1000), (3, 350, "hr", 30, 3000))
+        .toDF("pk", "salary", "dep", "index", "bonus")
+        .createOrReplaceTempView("source")
+
+      Seq(
+        "WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *" -> 
"index",
+        "WHEN MATCHED THEN UPDATE SET bonus = s.bonus, _partition = s.dep" -> 
"_partition"
+      ).foreach { case (clauses, metadataCol) =>
+        val e = intercept[AnalysisException] {
+          sql(
+            s"""MERGE WITH SCHEMA EVOLUTION INTO $tableNameAsString t
+               |USING source s
+               |ON t.pk = s.pk
+               |$clauses
+               |""".stripMargin)
+        }
+        assert(e.getCondition == 
"DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS")
+        assert(e.getMessage.contains(s"Cannot assign to metadata column 
'$metadataCol'"))
+        // the schema is not evolved, not even in the aborted transaction
+        assert(catalog.lastTransaction.currentState == Aborted)
+        val txnTable = catalog.lastTransaction.catalog.txnTables(ident)
+        assert(txnTable.columns().map(_.name).toSeq == Seq("pk", "salary", 
"dep"))
+      }
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Seq(
+          Row(1, 100, "hr"), // unchanged
+          Row(2, 200, "software"))) // unchanged
+    }
+  }
+
   test("merge with one NOT MATCHED BY SOURCE clause") {
     withTempView("source") {
       createAndInitTable("pk INT NOT NULL, salary INT, dep STRING",
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 f52ff2406755..1f504e268490 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
@@ -546,6 +546,44 @@ abstract class UpdateTableSuiteBase extends 
RowLevelOperationSuiteBase {
     checkUpdateMetrics(numUpdatedRows = 1, numCopiedRows = 0)
   }
 
+  test("SPARK-60721: update with assignments to metadata columns") {
+    createAndInitTable("pk INT NOT NULL, id INT, dep STRING",
+      """{ "pk": 1, "id": 1, "dep": "hr" }
+        |{ "pk": 2, "id": 2, "dep": "software" }
+        |""".stripMargin)
+
+    Seq(
+      s"UPDATE $tableNameAsString SET index = 5 WHERE pk = 1" -> "index",
+      s"UPDATE $tableNameAsString t SET t.id = -1, t._partition = 'x' WHERE 
t.pk = 1" ->
+        "_partition",
+      // the implicit cast in the condition resolves the command in a later 
analyzer iteration
+      s"UPDATE $tableNameAsString SET index = 5 WHERE id < 1.5" -> "index"
+    ).foreach { case (updateStmt, metadataCol) =>
+      val e = intercept[AnalysisException](sql(updateStmt))
+      assert(e.getCondition == 
"DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS")
+      assert(e.getMessage.contains(s"Cannot assign to metadata column 
'$metadataCol'"))
+    }
+
+    checkAnswer(
+      sql(s"SELECT * FROM $tableNameAsString"),
+      Row(1, 1, "hr") :: Row(2, 2, "software") :: Nil)
+  }
+
+  test("SPARK-60721: update ignores assignments to metadata columns in legacy 
mode") {
+    createAndInitTable("pk INT NOT NULL, id INT, dep STRING",
+      """{ "pk": 1, "id": 1, "dep": "hr" }
+        |{ "pk": 2, "id": 2, "dep": "software" }
+        |""".stripMargin)
+
+    withSQLConf(SQLConf.LEGACY_IGNORE_METADATA_COLUMN_ASSIGNMENTS.key -> 
"true") {
+      sql(s"UPDATE $tableNameAsString SET id = -1, index = 5 WHERE pk = 1")
+    }
+
+    checkAnswer(
+      sql(s"SELECT * FROM $tableNameAsString"),
+      Row(1, -1, "hr") :: Row(2, 2, "software") :: Nil)
+  }
+
   test("update with IN subqueries") {
     withTempView("updated_id", "updated_dep") {
       createAndInitTable("pk INT NOT NULL, id INT, dep STRING",


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

Reply via email to