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 fe37cc5656f0 [SPARK-60721][SQL] Reject assignments to metadata columns
in DSv2 UPDATE and MERGE
fe37cc5656f0 is described below
commit fe37cc5656f0bab7fbd1ca8cf3794c275c7b7639
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]>
---
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 2dd590e6539e..d664602e6fe5 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -66,6 +66,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 330a6a27143b..bc8f7833b19a 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
@@ -9028,6 +9028,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]