[SPARK-34514] Push down limit for LEFT SEMI and LEFT ANTI join

• Type: Improvement
• Status: Resolved
• Priority: Trivial
• Resolution: Fixed
• Affects Version/s: 3.1.0
• Fix Version/s: 3.2.0
• Component/s: SQL
• Assignee: Cheng Su
• Reporter: Cheng Su
• Created: 24/Feb/21 03:26
• Updated: 15/Jan/22 13:05
• Resolved: 24/Feb/21 10:23

Description

I found out during code review of https://github.com/apache/spark/pull/31567( https://github.com/apache/spark/pull/31567#discussion_r577379572 ), where we can push down limit to the left side of LEFT SEMI and LEFT ANTI join, if the join condition is empty.

Why it's safe to push down limit:

The semantics of LEFT SEMI join without condition:

(1). if right side is non-empty, output all rows from left side.

(2). if right side is empty, output nothing.

 

The semantics of LEFT ANTI join without condition:

(1). if right side is non-empty, output nothing.

(2). if right side is empty, output all rows from left side.

 

With the semantics of output all rows from left side or nothing (all or nothing), it's safe to push down limit to left side.

NOTE: LEFT SEMI / LEFT ANTI join with non-empty condition is not safe for limit push down, because output can be a portion of left side rows.

 

Physical operator for LEFT SEMI / LEFT ANTI join without condition - https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/joins/BroadcastNestedLoopJoinExec.scala#L200-L204 .

Issue Links

• relates to: SPARK-37917 Push down limit 1 for right side of left semi/anti join
• links to: [Github] Pull Request #31630 (c21)