[
https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17814498#comment-17814498
]
Julian Hyde edited comment on CALCITE-6240 at 2/5/24 8:02 PM:
--------------------------------------------------------------
Regarding the first rewrite - converting ROW_NUMBER on a unique key to 1. Can
this behavior be described by an algebraic property? E.g. ROW_NUMBER applied to
one row always returns 1?
The second rewrite is also desirable, but I think it will require a different
mechanism. (It is related to CALCITE-3181, CALCITE-4687.) Maybe this case
should focus only on the first rewrite.
Related: {{ROW_NUMBER() OVER (PARTITION BY w, x ORDER BY x, y)}} can be
simplified to {{ROW_NUMBER() OVER (PARTITION BY w, x ORDER BY y)}} because
{{x}} has a unique value within any group. This could be applied to any
aggregate function that sorts, e.g. {{LISTAGG()}}.
was (Author: julianhyde):
Can this behavior be described by an algebraic property? E.g. ROW_NUMBER
applied to one row always returns 1?
Related: {{ROW_NUMBER() OVER (PARTITION BY w, x ORDER BY x, y)}} can be
simplified to {{ROW_NUMBER() OVER (PARTITION BY w, x ORDER BY y)}} because
{{x}} has a unique value within any group. This could be applied to any
aggregate function that sorts, e.g. {{LISTAGG()}}.
> Remove ROW_NUMBER if OVER PARTITION BY unique key
> -------------------------------------------------
>
> Key: CALCITE-6240
> URL: https://issues.apache.org/jira/browse/CALCITE-6240
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Jiajun Xie
> Assignee: Jiajun Xie
> Priority: Minor
>
> The SQL Only have ROW_NUMBER function.
> {code:java}
> SELECT empno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename
> FROM emp
> ) WHERE rn <= 5{code}
> to
> {code:java}
> SELECT empno, ename FROM emp{code}
>
> If we know every partition count is less than filter count, we can optimize
> {code:java}
> SELECT deptno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename
> FROM emp
> ) WHERE rn <= N
> -- Statistic let we know every department count is less than N{code}
> to
> {code:java}
> SELECT empno, ename FROM emp {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
