[SPARK-12532] Join-key Pushdown via Predicate Transitivity

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Later
• Affects Version/s: 1.6.0
• Component/s: Optimizer, SQL
• Labels: SQL
• Assignee: Unassigned
• Reporter: Xiao Li
• Shepherd: Michael Armbrust
• Created: 27/Dec/15 18:52
• Updated: 27/Feb/17 04:55
• Resolved: 12/Jan/16 01:59

Description

"SELECT * FROM upperCaseData JOIN lowerCaseData where lowerCaseData.n = upperCaseData.N and lowerCaseData.n = 3"

== Analyzed Logical Plan ==
N: int, L: string, n: int, l: string
Project [N#16,L#17,n#18,l#19]
+- Filter ((n#18 = N#16) && (n#18 = 3))
   +- Join Inner, None
      :- Subquery upperCaseData
      :  +- LogicalRDD [N#16,L#17], MapPartitionsRDD[17] at beforeAll at BeforeAndAfterAll.scala:187
      +- Subquery lowerCaseData
         +- LogicalRDD [n#18,l#19], MapPartitionsRDD[19] at beforeAll at BeforeAndAfterAll.scala:187

Before the improvement, the optimized logical plan is

== Optimized Logical Plan ==
Project [N#16,L#17,n#18,l#19]
+- Join Inner, Some((n#18 = N#16))
   :- LogicalRDD [N#16,L#17], MapPartitionsRDD[17] at beforeAll at BeforeAndAfterAll.scala:187
   +- Filter (n#18 = 3)
      +- LogicalRDD [n#18,l#19], MapPartitionsRDD[19] at beforeAll at BeforeAndAfterAll.scala:187

After the improvement, the optimized logical plan should be like

== Optimized Logical Plan ==
Project [N#16,L#17,n#18,l#19]
+- Join Inner, Some((n#18 = N#16))
   :- Filter (N#16 = 3)
   :  +- LogicalRDD [N#16,L#17], MapPartitionsRDD[17] at beforeAll at BeforeAndAfterAll.scala:187
   +- Filter (n#18 = 3)
      +- LogicalRDD [n#18,l#19], MapPartitionsRDD[19] at beforeAll at BeforeAndAfterAll.scala:187

Issue Links

• duplicates: SPARK-13219 Pushdown predicate propagation in SparkSQL with join
• links to: [Github] Pull Request #10490 (gatorsmile)