[jira] [Comment Edited] (CALCITE-7697) Add a rule to remove constant partition keys from Window

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-7697?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18102964#comment-18102964 ] 

Julian Hyde edited comment on CALCITE-7697 at 8/8/26 12:55 AM:
---------------------------------------------------------------

Consider doing this optimization in {{RelBuilder}}.

You could do the same optimization for {{ORDER BY}} as {{PARTITION BY}}. And furthermore, remove sort keys that are functionally dependent on partition keys or earlier sort keys (e.g. "PARTITION BY x, y ORDER BY x + y, z" can simplify to "PARTITION BY x, y ORDER BY z")

You can very likely share code with previous code that optimizes {{GROUP BY}} and {{ORDER BY}}.


was (Author: julianhyde):
Consider doing this optimization in {{RelBuilder}}.

You could do the same optimization for {{ORDER BY}} as {{PARTITION BY}}. And furthermore, remove sort keys that are functionally dependent on partition by keys (e.g. "PARTITION BY x, y ORDER BY x + y, z" can simplify to "PARTITION BY x, y ORDER BY z")

> Add a rule to remove constant partition keys from Window
> --------------------------------------------------------
>
>                 Key: CALCITE-7697
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7697
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.42.0
>            Reporter: Yu Xu
>            Assignee: Yu Xu
>            Priority: Major
>
> When a window function's PARTITION BY clause references a key that is constant, every row falls into the same partition. The partition key therefore has no effect on the result but still forces the planner (and downstream engines) to perform an unnecessary partitioning/shuffle operation. Calcite currently has no rule to detect and remove such redundant partition keys.
> A partition key can be constant in two common ways:
>   1. *Literal partition key* — the SQL directly partitions by a constant, e.g.
> {code:java}
> SELECT empno, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY sal) FROM emp {code}
>  
>   2. *Predicate-implied constant* — an upstream predicate proves the column is constant, e.g.
>  
> {code:java}
>   SELECT empno, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) FROM emp WHERE deptno = 10
> {code}
>  
>  
>   2. Here deptno is constant for all surviving rows, so PARTITION BY deptno is redundant.
>  The second case is the more valuable one in practice: hand-written PARTITION BY <literal> is rare, but "filter on an equality column, then partition by that column" arises frequently through views, nested subqueries, and predicate push-down.
> we maybe can add a new rule to implement it.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)