[ 
https://issues.apache.org/jira/browse/SPARK-61640?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Peter Toth updated SPARK-61640:
-------------------------------
    Affects Version/s: 3.5.9
                       4.0.5
                       4.1.4
                       4.2.1
                       4.3.0
                           (was: 5.0.0)
          Description: 
A MERGE must fail when several source rows match the same target row. 
{{RewriteMergeIntoTable}} checks this in {{MergeRowsExec}}, within each task, 
on a {{monotonically_increasing_id()}} row ID of the target. For that it puts a 
{{NO_BROADCAST_AND_REPLICATION}} hint on the target, since all matches of a 
target row must be in the same task.

Only {{JoinSelection}} reads that hint. The partially clustered distribution of 
a storage-partitioned join 
({{spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled}}) 
picks the side to replicate from the join type and the sizes. A delta-based 
MERGE joins the target and the source with an Inner or a RightOuter join, so 
when the target is the smaller side it is replicated. Two source rows that 
match the same target row can then be in different tasks. The check misses 
them, and the MERGE updates the target row twice instead of failing.

A group-based MERGE uses a LeftOuter or a FullOuter join, where the target is 
never replicated.

Repro with the in-memory row-level tables. The target is delta-based and the 
source has one row per split. Both are partitioned by {{dep}}.
* target: {{(1, 100, 'hr')}}, {{(2, 200, 'y')}}
* source: {{(1, 101, 'hr')}}, {{(1, 102, 'hr')}}, and 30 rows with {{dep = 'x'}}

{code:sql}
MERGE INTO target t USING source s
ON t.dep = s.dep AND t.pk = s.pk
WHEN MATCHED THEN UPDATE SET salary = s.salary
WHEN NOT MATCHED THEN INSERT *
{code}

With {{spark.sql.sources.v2.bucketing.enabled=true}}, 
{{spark.sql.requireAllClusterKeysForCoPartition=false}}, and broadcast and AQE 
off:

||Case||partiallyClusteredDistribution.enabled=false||partiallyClusteredDistribution.enabled=true||
|Delta-based, upsert (RightOuter)|cardinality error|no error, the target has 
(1, 101, 'hr') and (1, 102, 'hr')|
|Delta-based, update only (Inner)|cardinality error|no error, the target has 
(1, 101, 'hr') and (1, 102, 'hr')|
|Group-based, both|cardinality error|cardinality error|


> Fix wrong results when a partially clustered storage-partitioned join 
> replicates the MERGE target
> -------------------------------------------------------------------------------------------------
>
>                 Key: SPARK-61640
>                 URL: https://issues.apache.org/jira/browse/SPARK-61640
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 3.5.9, 4.3.0, 4.2.1, 4.0.5, 4.1.4
>            Reporter: Peter Toth
>            Priority: Major
>
> A MERGE must fail when several source rows match the same target row. 
> {{RewriteMergeIntoTable}} checks this in {{MergeRowsExec}}, within each task, 
> on a {{monotonically_increasing_id()}} row ID of the target. For that it puts 
> a {{NO_BROADCAST_AND_REPLICATION}} hint on the target, since all matches of a 
> target row must be in the same task.
> Only {{JoinSelection}} reads that hint. The partially clustered distribution 
> of a storage-partitioned join 
> ({{spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled}}) 
> picks the side to replicate from the join type and the sizes. A delta-based 
> MERGE joins the target and the source with an Inner or a RightOuter join, so 
> when the target is the smaller side it is replicated. Two source rows that 
> match the same target row can then be in different tasks. The check misses 
> them, and the MERGE updates the target row twice instead of failing.
> A group-based MERGE uses a LeftOuter or a FullOuter join, where the target is 
> never replicated.
> Repro with the in-memory row-level tables. The target is delta-based and the 
> source has one row per split. Both are partitioned by {{dep}}.
> * target: {{(1, 100, 'hr')}}, {{(2, 200, 'y')}}
> * source: {{(1, 101, 'hr')}}, {{(1, 102, 'hr')}}, and 30 rows with {{dep = 
> 'x'}}
> {code:sql}
> MERGE INTO target t USING source s
> ON t.dep = s.dep AND t.pk = s.pk
> WHEN MATCHED THEN UPDATE SET salary = s.salary
> WHEN NOT MATCHED THEN INSERT *
> {code}
> With {{spark.sql.sources.v2.bucketing.enabled=true}}, 
> {{spark.sql.requireAllClusterKeysForCoPartition=false}}, and broadcast and 
> AQE off:
> ||Case||partiallyClusteredDistribution.enabled=false||partiallyClusteredDistribution.enabled=true||
> |Delta-based, upsert (RightOuter)|cardinality error|no error, the target has 
> (1, 101, 'hr') and (1, 102, 'hr')|
> |Delta-based, update only (Inner)|cardinality error|no error, the target has 
> (1, 101, 'hr') and (1, 102, 'hr')|
> |Group-based, both|cardinality error|cardinality error|



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

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

Reply via email to