[
https://issues.apache.org/jira/browse/CALCITE-5828?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
LiHao updated CALCITE-5828:
---------------------------
Description:
*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#]
was:
*RelNode to SqlNode, when converting the Order field, a judgment should be
added to determine whether it is in the Window.*
_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|https://issues.apache.org/jira/browse/CALCITE-5724] mentions
this, but it's something like.
[Presto Window
Function|https://prestodb.io/docs/current/functions/window.html?highlight=window%20order#]
> 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)
