Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-22181

ReplaceExceptWithFilter if one or both of the datasets are fully derived out of Filters from a same parent

    Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1.1, 2.2.0
    • Fix Version/s: 2.3.0
    • Component/s: Optimizer, SQL
    • Labels:
      None

      Description

      While applying Except operator between two datasets, if one or both of the datasets are purely transformed using filter operations, then instead of rewriting the Except operator using expensive join operation, we can rewrite it using filter operation by flipping the filter condition of the right node.

      Example:

         SELECT a1, a2 FROM Tab1 WHERE a2 = 12 EXCEPT SELECT a1, a2 FROM Tab1 WHERE a1 = 5
         ==>  SELECT DISTINCT a1, a2 FROM Tab1 WHERE a2 = 12 AND (a1 is null OR a1 <> 5)
      

      For more details please refer: this post

        Attachments

          Activity

            People

            • Assignee:
              Sathiya Sathiya Kumar
              Reporter:
              Sathiya Sathiya Kumar
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: