[
https://issues.apache.org/jira/browse/CALCITE-6563?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
suibianwanwan updated CALCITE-6563:
-----------------------------------
Description:
For example, test in RelToSqlConverterTest:
{code:java}
@Test void testNestWindow() {
String query = " SELECT " + "RANK() OVER (ORDER BY \"daily_sales\" DESC) AS
\"rank1\" " + "FROM ( SELECT \"product_name\", " + "SUM(\"product_id\") OVER
(PARTITION BY \"product_name\") AS \"daily_sales\" " + "FROM \"product\" )
subquery";
String expected0 = "SELECT RANK() OVER (ORDER BY 2 DESC) AS \"$0\"\n" + "FROM
\"foodmart\".\"product\"";
RuleSet rules =
RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
sql(query).optimize(rules, null) .ok(expected0);
}
{code}
Plan:
{code:java}
LogicalWindow(window#0=[window(order by [1 DESC] aggs [RANK()])])
LogicalProject(product_name=[$3], $1=[$15])
LogicalWindow(window#0=[window(partition {3} aggs [SUM($1)])])
JdbcTableScan(table=[[foodmart, product]])
{code}
The first window is not correctly converted to SQL
was:
For example, test in RelToSqlConverterTest:
{code:java}
@Test void testNestWindow() {
String query = " SELECT " + "RANK() OVER (ORDER BY \"daily_sales\" DESC) AS
\"rank1\" " + "FROM ( SELECT \"product_name\", " + "SUM(\"product_id\") OVER
(PARTITION BY \"product_name\") AS \"daily_sales\" " + "FROM \"product\" )
subquery";
String expected0 = "SELECT RANK() OVER (ORDER BY 2 DESC) AS \"$0\"\n" + "FROM
\"foodmart\".\"product\"";
RuleSet rules =
RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
sql(query).optimize(rules, null) .ok(expected0);
}
{code}
Plan:
{code:java}
LogicalWindow(window#0=[window(order by [1 DESC] aggs [RANK()])])
LogicalProject(product_name=[$3], $1=[$15])
LogicalWindow(window#0=[window(partition {3} aggs [SUM($1)])])
JdbcTableScan(table=[[foodmart, product]])
{code}
The first window is not correctly converted to SQL
> Nested window produce incorrect results in RelToSqlConverter.
> -------------------------------------------------------------
>
> Key: CALCITE-6563
> URL: https://issues.apache.org/jira/browse/CALCITE-6563
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> Priority: Critical
>
> For example, test in RelToSqlConverterTest:
> {code:java}
> @Test void testNestWindow() {
> String query = " SELECT " + "RANK() OVER (ORDER BY \"daily_sales\" DESC) AS
> \"rank1\" " + "FROM ( SELECT \"product_name\", " + "SUM(\"product_id\") OVER
> (PARTITION BY \"product_name\") AS \"daily_sales\" " + "FROM \"product\" )
> subquery";
> String expected0 = "SELECT RANK() OVER (ORDER BY 2 DESC) AS \"$0\"\n" + "FROM
> \"foodmart\".\"product\"";
> RuleSet rules =
> RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
> sql(query).optimize(rules, null) .ok(expected0);
> }
> {code}
> Plan:
> {code:java}
> LogicalWindow(window#0=[window(order by [1 DESC] aggs [RANK()])])
> LogicalProject(product_name=[$3], $1=[$15])
> LogicalWindow(window#0=[window(partition {3} aggs [SUM($1)])])
> JdbcTableScan(table=[[foodmart, product]])
> {code}
> The first window is not correctly converted to SQL
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
