Lino Rosa created CALCITE-6655:
----------------------------------
Summary: 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
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)
