[CALCITE-6011] Add the planner rule that pushes the Filter past a Window

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: LakeShen
• Reporter: LakeShen
• Created: 18/Sep/23 15:10
• Updated: 10/Nov/23 11:34
• Resolved: 27/Oct/23 10:12

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]]) 
 

 

 

 

Issue Links

• links to: GitHub Pull Request #3439