SPJ joins in the outer join component of MERGE queries

[dev-goyal]

Query engine

Spark on EMR

Question

In a MERGE query, is there a reason the inner join (WHEN MATCHED) is SPJ, but the left outer join (WHEN NOT MATCHED) is ShuffledHashJoin? Can post an example, but wondering if this is expected behaviour for some reason?

[RussellSpitzer]

So if I remember correctly the root of the issue is this. To do a SPJ we first align things based on partitions

[ Source Partition ] === [Destination Partition]

But Sometimes this balancing is not good so we want to be able to break up one of the two sides here into multiple pieces. To do that we split one of the sides up into multiple parts

[Source partition] === [Destination Partition Part 1]
[Source partition] === [Destination Partition Part 2]
[Source partition] === [Destination Partition Part 3]

For a "WHEN MATCHED" this is good, because if any task succeeds we can perform the action on the result. Each task can still be treated completely independently.

For "WHEN NOT MATCHED" we have a problem because then we can only apply the change if all of the tasks do not match for a given expression. We don't have a mechanism for doing a reduction over key (? or something like that) after doing our checks. This means we can't do our SPJ optimization and have to do a full shuffle.

I think I have that all right, but I'm mostly remembering our discussion when this was originally being implemented.

[dev-goyal]

Very helpful to know, thanks!

[github-actions]

This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.

[github-actions]

This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'