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 2f131156b94e [SPARK-60878][SQL] Fix wrong results by dynamic partition 
pruning in group-based DELETE and UPDATE
2f131156b94e is described below

commit 2f131156b94e3377b6c25286e74df5a5d3861936
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Wed Nov 4 09:13:02 2026 -0800

    [SPARK-60878][SQL] Fix wrong results by dynamic partition pruning in 
group-based DELETE and UPDATE
    
    ### What changes were proposed in this pull request?
    
    This PR aims to fix a correctness issue of group-based row-level DELETE and 
UPDATE. `DataSourceV2Strategy` no longer puts the `DynamicPruningSubquery` 
filters of `PartitionPruning` into the runtime filters of a read of a 
group-based row-level operation, that is, a read of a `RowLevelOperationTable` 
whose operation does not implement `SupportsDelta`. Such a read keeps only the 
group filter of `RowLevelOperationRuntimeGroupFiltering`. Delta-based 
operations and other reads keep dynamic  [...]
    
    ### Why are the changes needed?
    
    This is a correctness issue. With the default configurations, a group-based 
DELETE deletes nothing and a group-based UPDATE duplicates rows, without any 
error, when the condition has a `NOT EXISTS` subquery on a runtime filter 
column, such as an identity partition column. When 
`spark.sql.optimizer.runtime.rowLevelOperationGroupFilter.enabled` is `false`, 
a group-based UPDATE with an `IN` or `EXISTS` subquery on such a column also 
duplicates rows.
    
    A group-based operation replaces the groups that its configured scan 
returns, and all reads of the operation share that scan. 
`RowLevelOperationRuntimeGroupFiltering` adds its group filter to every read of 
the scan. However, `PartitionPruning` plans a `DynamicPruningSubquery` from the 
join of one read, and this filter of one read also prunes the shared scan at 
runtime.
    - With a `NOT EXISTS` subquery, the read of the rows to copy over filters 
by `NOT (cond <=> TRUE)`, which becomes `EXISTS (...)` and then a left semi 
join. Its dynamic partition pruning filter keeps only the groups without a 
matching row, so the write does not replace the groups with matching rows.
    - Since [SPARK-43975](https://issues.apache.org/jira/browse/SPARK-43975) 
([v3.5.0](https://github.com/apache/spark/releases/tag/v3.5.0), 2023-09-09), an 
UPDATE with a subquery reads the table twice, once for the updated rows and 
once for the rows to copy over. With an `IN` or `EXISTS` subquery, the read of 
the updated rows becomes a left semi join and gets the dynamic partition 
pruning filter. With the group filter disabled, this filter prunes the shared 
scan, but the read of the copi [...]
    
    ```scala
    // 
spark.sql.catalog.cat=org.apache.spark.sql.connector.catalog.InMemoryRowLevelOperationTableCatalog
    sql("CREATE TABLE cat.ns1.t (pk INT NOT NULL, id INT, salary INT, dep 
STRING) PARTITIONED BY (dep)")
    sql("INSERT INTO cat.ns1.t VALUES (1, 1, 300, 'hr'), (2, 2, 150, 
'software'), (3, 3, 120, 'hr')")
    Seq("hr").toDF("d").createOrReplaceTempView("src")
    
    // Issue 1: It should delete the `software` row, but it deletes nothing.
    sql("DELETE FROM cat.ns1.t t WHERE NOT EXISTS (SELECT 1 FROM src WHERE d = 
t.dep)")
    
    // Issue 2: On the same initial table, it should update the `software` row, 
but the table ends up
    // with both the old and the updated `software` rows.
    sql("UPDATE cat.ns1.t t SET salary = -1 WHERE NOT EXISTS (SELECT 1 FROM src 
WHERE d = t.dep)")
    
    // Issue 3: On the same initial table with the group filter disabled, it 
should update the 2 `hr`
    // rows, but the table ends up with the `software` row twice.
    
spark.conf.set("spark.sql.optimizer.runtime.rowLevelOperationGroupFilter.enabled",
 "false")
    sql("UPDATE cat.ns1.t SET salary = -1 WHERE dep IN (SELECT d FROM src)")
    ```
    
    - Issue 1 and Issue 2 also happen with the group filter disabled, and with 
AQE enabled or disabled.
    - MERGE, and DELETE with an `IN` or `EXISTS` subquery, are not affected, 
since dynamic partition pruning does not apply to their reads.
    - Issue 1 needs only group-based DELETE 
([SPARK-38085](https://issues.apache.org/jira/browse/SPARK-38085)) and the 
group filter 
([SPARK-38959](https://issues.apache.org/jira/browse/SPARK-38959)), which are 
both in [v3.4.0](https://github.com/apache/spark/releases/tag/v3.4.0) 
(2023-04-07).
    
    This issue was reported in [the 
review](https://github.com/apache/spark/pull/60079#discussion_r4465170254) of 
[SPARK-60863](https://issues.apache.org/jira/browse/SPARK-60863), which fixed 
the same kind of issue for scalar subquery runtime filters.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. A group-based DELETE or UPDATE whose condition has a `NOT EXISTS` 
subquery on a runtime filter column now deletes or updates the matching rows. 
With the group filter disabled, a group-based UPDATE with an `IN` or `EXISTS` 
subquery on such a column now writes each row once. Dynamic partition pruning 
no longer prunes the scan of a group-based row-level operation, while the group 
filter still does.
    
    ### How was this patch tested?
    
    Pass the CIs with newly added test cases. The three test cases in 
`GroupBasedDeleteFromTableSuite` and `GroupBasedUpdateTableSuite` fail without 
this PR.
    
    ```
    $ build/sbt 'sql/testOnly *GroupBased* *DeltaBased* 
*MergeIntoDataFrameSuite *TransactionSuite 
*DataSourceV2CatalystRuntimeFilterSuite 
*DataSourceV2EnhancedRuntimePartitionFilterSuite 
*DataSourceV2EnhancedDeleteFilterSuite 
*KeyGroupedPartitioningRuntimeFilterSuite 
*KeyGroupedPartitioningCatalystRuntimeFilterSuite *DynamicPartitionPruning*'
    ```
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60096 from dongjoon-hyun/SPARK-60878.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 .../datasources/v2/DataSourceV2Strategy.scala      | 13 ++++++-
 .../connector/GroupBasedDeleteFromTableSuite.scala | 21 ++++++++++
 .../sql/connector/GroupBasedUpdateTableSuite.scala | 45 ++++++++++++++++++++++
 3 files changed, 77 insertions(+), 2 deletions(-)

diff --git 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2Strategy.scala
 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2Strategy.scala
index 6ba8d9ce5ba9..b348be63d4e7 100644
--- 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2Strategy.scala
+++ 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2Strategy.scala
@@ -27,7 +27,7 @@ import org.apache.spark.internal.LogKeys.EXPR
 import org.apache.spark.sql.catalyst.analysis.{NamedRelation, 
ResolvedIdentifier, ResolvedNamespace, ResolvedPartitionSpec, 
ResolvedPersistentView, ResolvedTable, ResolvedTempView}
 import org.apache.spark.sql.catalyst.catalog.CatalogUtils
 import org.apache.spark.sql.catalyst.expressions
-import org.apache.spark.sql.catalyst.expressions.{And, Attribute, Cast, 
DynamicPruning, Expression, InSet, NamedExpression, Not, Or, PredicateHelper, 
SubqueryExpression}
+import org.apache.spark.sql.catalyst.expressions.{And, Attribute, Cast, 
DynamicPruning, DynamicPruningSubquery, Expression, InSet, NamedExpression, 
Not, Or, PredicateHelper, SubqueryExpression}
 import org.apache.spark.sql.catalyst.expressions.Literal.TrueLiteral
 import org.apache.spark.sql.catalyst.optimizer.UnwrapCastInBinaryComparison
 import org.apache.spark.sql.catalyst.planning.PhysicalOperation
@@ -208,7 +208,16 @@ class DataSourceV2Strategy(session: SparkSession) extends 
Strategy with Predicat
       // dynamicFilters need no such check: a DynamicPruningSubquery over a 
non-deterministic
       // filtering plan is itself non-deterministic, so 
CleanupDynamicPruningFilters has already
       // rewritten it to TrueLiteral by the time we get here.
-      val runtimeFilters = dynamicFilters ++ scalarSubqueryFilters
+      // A group-based read keeps only the group filter, for the reason above. 
PartitionPruning
+      // plans a DynamicPruningSubquery from the join of one read, so it is a 
filter of one read
+      // too. With an IN subquery it lands on the read of the updated rows of 
an UPDATE. With a
+      // NOT EXISTS subquery the negated condition becomes a semi join, so it 
lands on the read
+      // of the rows to copy over.
+      val runtimeFilters = if (groupBasedRead) {
+        dynamicFilters.filterNot(_.isInstanceOf[DynamicPruningSubquery])
+      } else {
+        dynamicFilters ++ scalarSubqueryFilters
+      }
 
       val batchExec = BatchScanExec(relation.output, relation.scan, 
runtimeFilters,
         relation.ordering, relation.relation.table, 
relation.keyGroupedPartitioning)
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedDeleteFromTableSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedDeleteFromTableSuite.scala
index 416cb4f37633..f4db659c3f7e 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedDeleteFromTableSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedDeleteFromTableSuite.scala
@@ -151,6 +151,27 @@ class GroupBasedDeleteFromTableSuite extends 
DeleteFromTableSuiteBase {
     }
   }
 
+  test("SPARK-60878: delete with a NOT EXISTS subquery on the group key") {
+    withTempView("kept_dep") {
+      createAndInitTable("id INT, salary INT, dep STRING",
+        """{ "id": 1, "salary": 300, "dep": 'hr' }
+          |{ "id": 2, "salary": 150, "dep": 'software' }
+          |{ "id": 3, "salary": 120, "dep": 'hr' }
+          |""".stripMargin)
+
+      Seq("hr").toDF("d").createOrReplaceTempView("kept_dep")
+
+      sql(s"DELETE FROM $tableNameAsString t " +
+        "WHERE NOT EXISTS (SELECT 1 FROM kept_dep WHERE d = t.dep)")
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Row(1, 300, "hr") :: Row(3, 120, "hr") :: Nil)
+
+      checkReplacedPartitions(Seq("software"))
+    }
+  }
+
   test("delete runtime group filtering (DPP enabled)") {
     withSQLConf(SQLConf.DYNAMIC_PARTITION_PRUNING_ENABLED.key -> "true") {
       checkDeleteRuntimeGroupFiltering()
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedUpdateTableSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedUpdateTableSuite.scala
index eaf6ab03d9e9..db7fe034e662 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedUpdateTableSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GroupBasedUpdateTableSuite.scala
@@ -222,6 +222,51 @@ class GroupBasedUpdateTableSuite extends 
UpdateTableSuiteBase {
     }
   }
 
+  test("SPARK-60878: update with a subquery on the group key and the group 
filter disabled") {
+    withSQLConf(SQLConf.RUNTIME_ROW_LEVEL_OPERATION_GROUP_FILTER_ENABLED.key 
-> "false") {
+      withTempView("updated_dep") {
+        createAndInitTable("id INT, salary INT, dep STRING",
+          """{ "id": 1, "salary": 300, "dep": 'hr' }
+            |{ "id": 2, "salary": 150, "dep": 'software' }
+            |{ "id": 3, "salary": 120, "dep": 'hr' }
+            |""".stripMargin)
+
+        Seq("hr").toDF().createOrReplaceTempView("updated_dep")
+
+        // dynamic partition pruning applies to the read of the updated rows 
only, so it must not
+        // prune the scan that the read of the copied rows shares
+        sql(s"UPDATE $tableNameAsString SET salary = -1 WHERE dep IN (SELECT * 
FROM updated_dep)")
+
+        checkAnswer(
+          sql(s"SELECT * FROM $tableNameAsString"),
+          Row(1, -1, "hr") :: Row(2, 150, "software") :: Row(3, -1, "hr") :: 
Nil)
+
+        checkReplacedPartitions(Seq("hr", "software"))
+      }
+    }
+  }
+
+  test("SPARK-60878: update with a NOT EXISTS subquery on the group key") {
+    withTempView("kept_dep") {
+      createAndInitTable("id INT, salary INT, dep STRING",
+        """{ "id": 1, "salary": 300, "dep": 'hr' }
+          |{ "id": 2, "salary": 150, "dep": 'software' }
+          |{ "id": 3, "salary": 120, "dep": 'hr' }
+          |""".stripMargin)
+
+      Seq("hr").toDF("d").createOrReplaceTempView("kept_dep")
+
+      sql(s"UPDATE $tableNameAsString t SET salary = -1 " +
+        "WHERE NOT EXISTS (SELECT 1 FROM kept_dep WHERE d = t.dep)")
+
+      checkAnswer(
+        sql(s"SELECT * FROM $tableNameAsString"),
+        Row(1, 300, "hr") :: Row(2, -1, "software") :: Row(3, 120, "hr") :: 
Nil)
+
+      checkReplacedPartitions(Seq("software"))
+    }
+  }
+
   test("update with nondeterministic conditions") {
     createAndInitTable("pk INT NOT NULL, id INT, dep STRING",
       """{ "pk": 1, "id": 1, "dep": "hr" }


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

Reply via email to