[
https://issues.apache.org/jira/browse/CALCITE-750?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14571711#comment-14571711
]
Aman Sinha commented on CALCITE-750:
------------------------------------
Right, aggregates on top of window aggregates are not allowed, but I think
window aggregates on top of grouped aggregates are allowed. The SQL 2011 spec
has the following:
"An <aggregate function> simply contained in a <window function> shall not
simply contain a <hypothetical set function>."
In another section: "Hypothetical set functions are related to the window
functions RANK, DENSE_RANK, PERCENT_RANK, and CUME_DIST". This suggests that
as long as the aggregate function is not a 'hypothetical set function', it is
allowed.
TPC-DS has several queries that have window aggregates on top of aggregate
expressions (e.g query20, query47).
The workaround of doing the grouped aggregation in the subquery is what we are
doing currently in Drill but would be good to support the original syntax.
> Window aggregate on top of a regular aggregate expression fails
> ---------------------------------------------------------------
>
> Key: CALCITE-750
> URL: https://issues.apache.org/jira/browse/CALCITE-750
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.3.0-incubating
> Reporter: Aman Sinha
> Assignee: Julian Hyde
>
> Window aggregate on top of a regular aggregate expression is a valid query
> but currently fails with a 'aggregate expression cannot be nested' error.
> {code}
> SELECT avg(sum(sal)) over (partition by deptno) from emp group by deptno;
> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Aggregate
> expressions cannot be nested
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
