[
https://issues.apache.org/jira/browse/CALCITE-6655?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6655.
----------------------------------
Fix Version/s: 1.39.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/2e99d5118efbba21c87e7da08b8359ce296e138d]
Thank you for your contribution [~linorosa]
> Aggregation of deeply nested window not detected when unparsing
> ---------------------------------------------------------------
>
> Key: CALCITE-6655
> URL: https://issues.apache.org/jira/browse/CALCITE-6655
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Lino Rosa
> Assignee: Lino Rosa
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.39.0
>
>
> Given a query such as the below:
> {code:java}
> WITH
> window_cte AS (
> SELECT
> foo,
> SUM(baz) OVER (PARTITION BY foo) AS window_agg
> FROM
> sample_data
> )
> SELECT
> SUM(window_agg) AS bazzy
> FROM
> window_cte {code}
> When `baz` is {_}*optional*{_}, Calcite will wrap the inner function within a
> `CASE` statement. Something like:
> {code:java}
> CASE
> WHEN (
> COUNT(baz) OVER (
> PARTITION BY
> foo RANGE BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING
> )
> ) > 0 THEN COALESCE(
> SUM(baz) OVER (
> PARTITION BY
> foo
> ORDER BY
> RAND () NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING
> ),
> 0
> )
> ELSE NULL
> END{code}
> Nothing intrinsically wrong with that substitution. However because of it the
> inner aggregation function `SUM(baz) OVER...` has become +deeply nested+
> under the `CASE` statement.
> Where that becomes an issue is inside `SqlImplemtor#Result#hasNested`. That
> function is supposed to look for nested aggregates, but it only looks at the
> top level. In this case it'll stop at the `CASE` statement, erroneously
> concluding there's no nested aggregate. In the end it'll collapse the cte
> from the original query into a `SUM(CASE(... SUM(...)))` which will fail
> running in dialects such as Spark.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
