[jira] [Commented] (CALCITE-5828) When converting the calcite dialect, it is not determined whether the ordinal number can be used in the order by in the window function

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5828?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17742122#comment-17742122 ] 

Julian Hyde commented on CALCITE-5828:
--------------------------------------

I would be surprised if {{ORDER BY <ordinal>}} is valid inside {{OVER}} in any dialect.

In other words, {{isSortByOrdinalInWindow}} should return false by default and may not even be necessary.

> When converting the calcite dialect, it is not determined whether the ordinal number can be used in the order by in the window function
> ---------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5828
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5828
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: LiHao
>            Assignee: LiHao
>            Priority: Major
>
> *Currently, calcite does not judge whether it is in a window function when translating order by. Taking Presto as an example, Presto allows the use of ordinal numbers instead of expression fields in order by outside window functions, but in window functions, it is not allowed to use ordinal, only expression fields can be used.*
> I think a judgment should be added to determine whether it is in the Window when converting the Order field,
>  
> _Example_
> source sql:
> {quote}SELECT RANK() OVER (ORDER BY 1 + "col1")
> FROM "table"
> GROUP BY "col1"
> {quote}
> after calcite:
> {quote}SELECT RANK() OVER (ORDER BY 1 NULLS LAST) AS \"$0\"
> FROM "table"
> GROUP BY "col1"
> {quote}
> expect return:
> {quote}SELECT RANK() OVER (ORDER BY 1 + "col1")
> FROM "table"
> GROUP BY "col1"
> {quote}
> I just investigated Presto, if the order by in the window function is not allowed to use ordinal numbers, but it is possible to write ordinal numbers in ordinary order by.
> So I think calcite should have a judgment in  interface SqlConformance,
> {{boolean isSortByOrdinalInWindow();}}
> In addition, I don't think CALCITE-5724 mentions this, but it's something like.
> [Presto Window Function|https://prestodb.io/docs/current/functions/window.html?highlight=window%20order#]
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)