[
https://issues.apache.org/jira/browse/CALCITE-7502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
DongShengHe updated CALCITE-7502:
---------------------------------
Description:
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.
was:
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 conver it to
{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.
> 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
> Priority: Minor
>
> 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)
