[
https://issues.apache.org/jira/browse/CALCITE-7502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen reassigned CALCITE-7502:
----------------------------------
Assignee: DongShengHe
> RelToSqlConverter creates invalid sql when converting nested window contains
> SqlCaseWhen
> ----------------------------------------------------------------------------------------
>
> Key: CALCITE-7502
> URL: https://issues.apache.org/jira/browse/CALCITE-7502
> Project: Calcite
> Issue Type: Bug
> Reporter: DongShengHe
> Assignee: DongShengHe
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> SqlImplementor.Result.containsOver() uses manual recursion to detect WINDOW
> nodes in a SQL tree, but only handles SqlSelect and SqlCall. Nodes such as
> SqlCase, SqlNodeList,
> SqlLiteral, and others are silently skipped.
> When a SqlCase expression contains a windowed aggregate (e.g.,
> {code:java}
> CASE WHEN SUM(x) OVER (...) > 1 THEN 1 ELSE 0 END) {code}
> containsOver() returns false. This causes needNewSubQuery() to
> incorrectly conclude that a new sub-query is not required, leading to
> incorrectly merged SELECT clauses with overlapping window functions.
>
> SQL:
>
> {code:java}
> SELECT
> SUM (daily_sales) OVER (PARTITION BY product_name) AS sales
> FROM
> (
> SELECT
> product_name,
> CASE WHEN SUM(product_id) OVER (PARTITION BY product_name) > 0 THEN 1
> ELSE 0 END AS daily_sales
> FROM
> product
> ) subquery;
> {code}
> converted to LogicalPlan
> {code:java}
> LogicalProject(sales=[SUM($1) OVER (PARTITION BY $0)])
> LogicalProject(product_name=[$3], daily_sales=[CASE(>(SUM($1) OVER
> (PARTITION BY $3), 0), 1, 0)])
> JdbcTableScan(table=[[foodmart, product]]) {code}
> and use RelToSqlConverter creates invalide sql, it contains a nested window
> function.
> {code:java}
> SELECT
> SUM(
> CASE WHEN (
> SUM(product_id) OVER (
> PARTITION BY product_name RANGE BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING
> )
> ) > 0 THEN 1 ELSE 0 END
> ) OVER (
> PARTITION BY product_name RANGE BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING
> ) AS sales
> FROM
> foodmart.product
> {code}
>
> Fix:
> Replace the manual recursion with a SqlBasicVisitor that properly traverses
> all SqlCall subtypes, including SqlCase.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
