[
https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17814506#comment-17814506
]
Julian Hyde commented on CALCITE-6240:
--------------------------------------
Also related to CALCITE-5406, because
{code:java}
SELECT DISTINCT ON (x)
x, y, z
FROM t{code}
is equivalent to
{code:java}
SELECT x, y, z
FROM (
SELECT x, y, z, ROW_NUMBER() OVER (PARTITION BY x) AS r
FROM t)
WHERE r = 1{code}
> 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)
