Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6914

Expand join-dependent predicates from disjunction

    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.39.0
    • 1.40.0
    • core

    Description

      For the following SQL:

      select t1.name from t1, t2 
      where t1.id = t2.id 
      and ( 
       (t1.id > 20 and t2.height < 50)  
        or 
       (t1.weight < 200 and t2.sales > 100) 
      ) 

      The disjunction (t1.id > 20 and t2.height < 50) or (t1.weight < 200 and t2.sales > 100) uses t1 and t2, thus cannot be pushed below the join. 
      In fact, we can extract redundant (t1.id > 20 or t1.weight < 200) , (t2.height < 50 or t2.sales > 100) from this predicate by a new rule and push down these two predicates to both side of Join input. The paper Quantifying TPC-H choke points and their optimizations describes this optimization in 4.4 section.
       
      Some notes: 

      1. The expanded redundant predicates can be pushed down, but the original disjunction cannot be removed.
      2. This rule should only be applied once to avoid generate same redundant expression.
      3. This rule should be used before FilterJoinRule and it seems to resolve the TODO in FilterJoinRule.

      Attachments

        Issue Links

          Activity

            People

              dongsl Silun Dong
              dongsl Silun Dong
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: