[
https://issues.apache.org/jira/browse/CALCITE-5883?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17749575#comment-17749575
]
Itiel Sadeh commented on CALCITE-5883:
--------------------------------------
Thanks for the comment.
I kind of agree with your conclusion but not totally. I think this behavior is
surprising from the point of view of the user, as they would expect different
results for each row, but the result will be the same. Wouldn't it be better to
require an ORDER BY in that case instead of silently rewriting the query?
Another option might be to put this rewrite behind conformance flag, do you
think it's reasonable?
> Calcite ignores window frames when there is no ORDER BY clause
> --------------------------------------------------------------
>
> Key: CALCITE-5883
> URL: https://issues.apache.org/jira/browse/CALCITE-5883
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.35.0
> Reporter: Itiel Sadeh
> Priority: Major
>
> When specifying window frame without an ORDER BY clause, Calcite will ignore
> it and will generate the frame UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING.
> e.g, calcite will translate
> {code:java}
> SELECT sum(x) over(partition by y ROWS between CURRENT ROW and UNBOUNDED
> FOLLOWING) from t
> {code}
> into
> {code:java}
> SELECT sum(x) over(partition by y RANGE between UNBOUNDED FOLLOWING and
> UNBOUNDED FOLLOWING) from t
> {code}
> There is a comment in the offending code (SqlToRelConverter.java:2207) which
> states that
> ??"without ORDER BY, there must be no bracketing"?? . I've tried to find a
> specification like that in the SQL 2003 standard, but didn't find anything to
> that effect.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
