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 69a934425b03 [SPARK-60878][SQL] Fix wrong results by dynamic partition
pruning in group-based DELETE and UPDATE
69a934425b03 is described below
commit 69a934425b034b5a974e74356882630ac33e959a
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 5ff2302bad8a..09b9dee9ceec 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,
AttributeSet, BloomFilterMightContain, Cast, DynamicPruning, Expression, InSet,
NamedExpression, Not, Or, PredicateHelper, SubqueryExpression}
+import org.apache.spark.sql.catalyst.expressions.{And, Attribute,
AttributeSet, BloomFilterMightContain, 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
@@ -233,7 +233,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
+ }
// Decide storage-partitioned join here, once, so a rebuild of the
planned node (for instance
// the copy PlanAdaptiveDynamicPruningFilters makes at stage creation)
does not read the
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]