Optimizer: Allow predicate pushdown through window function partition columns

[manishpatel00]

Description

Currently, sqlglot.optimizer.pushdown_predicates completely blocks pushing down predicates into Select nodes if they contain any Window expressions.

has_window_expression = any(
    select for select in node.selects if find_in_scope(select, exp.Window)
)

However, it is mathematically safe to push down predicates through window functions if and only if the predicate strictly filters on columns that are part of the PARTITION BY clause of all window functions in that scope. Filtering on partition boundaries does not affect window calculations within the partitions.

Example

WITH cte AS (
  SELECT x, y, ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS rn FROM t
)
SELECT * FROM cte WHERE x = 1;

Currently, x = 1 is not pushed down. It safely can be.

Proposed Solution

Modify nodes_for_predicate to check if the predicate references only a subset of the partition keys of all window functions in the select node. If so, allow the pushdown.

[georgesittas]

This is out of scope for the core team, closing for now.