[
https://issues.apache.org/jira/browse/CALCITE-3897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17091887#comment-17091887
]
Rui Wang commented on CALCITE-3897:
-----------------------------------
After read relevant sections in SQL standard, I think SQL standard says:
1. RANGE requires ORDER BY (either a direct ORDER BY or a ORDER BY from an
existing window name), and sort key in ORDER BY must be a single key.
2. Does not say ROWS requires ORDER BY.
So Calcite should throw exception when there is no ORDER BY for RANGE only.
> Windowed aggregate with bracket (ROWS or RANGE) without ORDER BY is invalid
> ---------------------------------------------------------------------------
>
> Key: CALCITE-3897
> URL: https://issues.apache.org/jira/browse/CALCITE-3897
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> I believe that windowed aggregate with bracket (ROWS or RANGE) without ORDER
> BY is invalid. If so, we should throw.
> [~danny0405]
> [reports|https://github.com/apache/calcite/pull/1896#discussion_r403420946]
> that the syntax is invalid on MSSQL 2017.
> Someone should check the SQL standard. If it is invalid in the SQL standard
> (but only if) the validator should give an error.
> I am a little ambivalent. A missing {{ORDER BY}} is basically sorting by zero
> columns, an of course it's not deterministic, but even with {{ORDER BY}}, the
> ordering may not be deterministic (e.g. apply {{ORDER BY deptno}} to the
> {{Emp}} table). So we should go with the SQL standard.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
