[
https://issues.apache.org/jira/browse/CALCITE-800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14631681#comment-14631681
]
Julian Hyde commented on CALCITE-800:
-------------------------------------
You've quoted from the "Concepts" section. About the Concepts it says:
bq. This Clause describes concepts that are, for the most part, specified
precisely in other parts of ISO/IEC 9075.
In any case of discrepancy, the specification in the other part is to be
presumed correct.
Elsewhere it also says
bq. NOTE 299 — Although ROW_NUMBER is non-deterministic, ...
The intent is pretty clear. ROW_NUMBER is non-deterministic. In case of a tie,
it assigns sequence numbers to the tied elements in arbitrary order.
(That is the only difference between it and RANK. "RANK ... ORDER BY ()" would
return 1 for all elements and therefore be useless; "ROW_NUMBER ... ORDER BY
()" or equivalently "ROW_NUMBER" assigns unique sequence numbers, and that is
useful. For example you can use it to delete all but one copy of duplicate rows
in a table. And it sounds as if Postgres botched it, and implemented ROW_NUMBER
as if it were RANK.)
Quite a few parts of SQL are non-deterministic. I mentioned LIMIT without ORDER
BY earlier. And even ORDER BY is non-deterministic if the order key is not
unique.
Can we wind up this discussion now please? I'd rather see a patch with an
implementation then spend more time arguing like lawyers or religious scholars.
> Window function defined within another window function should be invalid
> ------------------------------------------------------------------------
>
> Key: CALCITE-800
> URL: https://issues.apache.org/jira/browse/CALCITE-800
> Project: Calcite
> Issue Type: Bug
> Reporter: Sean Hsuan-Yi Chu
> Assignee: Julian Hyde
>
> For instance,
> {code}
> select sum(deptno) over (order by
> sum(deptno) over(order by deptno))
> from emp
> {code}
> This is an invalid query. However, it passes the validation.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
