[
https://issues.apache.org/jira/browse/CALCITE-1327?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15403150#comment-15403150
]
Gautam Kumar Parai edited comment on CALCITE-1327 at 8/2/16 1:08 AM:
---------------------------------------------------------------------
[~julianhyde] I found a couple of existing issues while debugging this issue.
1. We do not seem to verify for window clause correctness in aggregating scope.
{code}
SELECT sum(empno), max(empno) OVER (order by deptno)
FROM emp
GROUP BY empno
gives error: Expression 'DEPTNO' is not being grouped
SELECT sum(empno), max(empno) OVER w
FROM emp
GROUP BY empno
WINDOW w as (order by deptno)
gives no error:
{code}
Although, the new code fixes the issue.
2. We do not seem to verify for correctness of rank() (maybe other operators?)
prior to verifying aggregating scope checks. We incorrectly think rank()
without over() is an aggregate and proceed to do agg scope checks for the rest.
We should fail first at "OVER clause is necessary for window functions"
This was referred to by [~amansinha100]. I had to modify the existing testcase
but it should not have required to do so.
Do you think we should file separate JIRAs for the two issues?
was (Author: gparai):
[~julianhyde] I found a couple of existing issues while debugging this issue.
1. We do not seem to verify for window clause correctness in aggregating scope.
{code}
SELECT sum(empno), max(empno) OVER (order by deptno)
FROM emp
GROUP BY empno
gives error: Expression 'DEPTNO' is not being grouped
SELECT sum(empno), max(empno) OVER w
FROM emp
GROUP BY empno
WINDOW w as (order by deptno)
gives no error:
{code}
2. We do not seem to verify for correctness of rank() (maybe other operators?)
prior to verifying aggregating scope checks. We incorrectly think rank()
without over() is an aggregate and proceed to do agg scope checks for the rest.
We should fail first at "OVER clause is necessary for window functions"
This was referred to by [~amansinha100]. I had to modify the existing testcase
but it should not have required to do so.
Do you think we should file separate JIRAs for the two issues?
> Nested aggregate windowed query fails
> -------------------------------------
>
> Key: CALCITE-1327
> URL: https://issues.apache.org/jira/browse/CALCITE-1327
> Project: Calcite
> Issue Type: Bug
> Reporter: Gautam Kumar Parai
> Assignee: Gautam Kumar Parai
>
> Regression from CALCITE-750 Allow nested window aggregates.
> Calcite allows illegal queries instead of raising an appropriate error. When
> executing the following query calcite does not raise the following error.
> {code}
> select avg(sum(b)) over (partition by b) from t1;
> ERROR: Expression 'b' is not being grouped
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
