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

Add the planner rule that pushes the Filter past a Window

    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.36.0
    • core

    Description

      The Filter condition could be pushed past the Window when condition used columns is window partition columns.

      For example:

      SELECT 
        * 
      FROM 
        (
          SELECT 
            custkey, 
            orderkey, 
            rank() OVER (
              PARTITION BY custkey 
              ORDER BY 
                orderdate ASC
            ) 
          FROM 
            orders
        ) 
      WHERE 
        custkey = 0 
        AND orderkey > 0 

      The plan tree:

      LogicalProject(custkey=[0], orderkey=[$1], EXPR$2=[$2])
        LogicalFilter(condition=[AND(=($0, 0), >($1, 0))])
          LogicalProject(custkey=[$1], orderkey=[$0], EXPR$2=[$3])
            LogicalWindow(window#0=[window(partition {1} order by [2] aggs [RANK()])])
              LogicalProject(ORDERKEY=[$0], CUSTKEY=[$1], ORDERDATE=[$4])
                LogicalTableScan(table=[[tpch, ORDERS]]) 

      Because the window partition columns is custkey,so the condition `custkey = 0 ` could be pushed down the LogicalWindow.

      After that,the plan is :

       
      LogicalProject(custkey=[0], orderkey=[$1], EXPR$2=[$2])
        LogicalFilter(condition=[>($1, 0)])
          LogicalProject(custkey=[$1], orderkey=[$0], EXPR$2=[$3])
            LogicalWindow(window#0=[window(partition {1} order by [2] aggs [RANK()])])
              LogicalFilter(condition=[=($1, 0)])
                LogicalProject(ORDERKEY=[$0], CUSTKEY=[$1], ORDERDATE=[$4])
                  LogicalTableScan(table=[[tpch, ORDERS]]) 
       

       

       

       

      Attachments

        Issue Links

          Activity

            People

              shenlang LakeShen
              shenlang LakeShen
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: