[
https://issues.apache.org/jira/browse/CALCITE-2757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
KrishnaKant Agrawal updated CALCITE-2757:
-----------------------------------------
Description:
SELECT DISTINCT sum(x) OVER (PARTITION BY y) FROM t
is valid (per SQL standard) but
SELECT sum(x) OVER (PARTITION BY y)
FROM t
GROUP BY sum(x) OVER (PARTITION BY y)
is not. For example, given the query
select sum(deptno) over (partition by loc)
from dept
group by sum(deptno) over (partition by loc);
Oracle gives
ORA-00934: group function is not allowed here
Therefore we should generate a sub-query, something like this:
SELECT c1
FROM (
SELECT sum(deptno) OVER (PARTITION BY loc)
FROM dept) AS t
GROUP BY c1;
This will be achieved by Adding a new condition for seting the needNew Flag in
SqlImplemontor.Builder.builder() as true in case there are Aggregate Expression
being passed as Group By Keys.
was:
SELECT DISTINCT sum(x) OVER (PARTITION BY y) FROM t
is valid (per SQL standard) but
SELECT sum(x) OVER (PARTITION BY y)
FROM t
GROUP BY sum(x) OVER (PARTITION BY y)
is not. For example, given the query
select sum(deptno) over (partition by loc)
from dept
group by sum(deptno) over (partition by loc);
Oracle gives
ORA-00934: group function is not allowed here
Therefore we should generate a sub-query, something like this:
SELECT c1
FROM (
SELECT sum(deptno) OVER (PARTITION BY loc)
FROM dept) AS t
GROUP BY c1;
> DISTINCT not being handled correctly in RelToSqlConverter
> ---------------------------------------------------------
>
> Key: CALCITE-2757
> URL: https://issues.apache.org/jira/browse/CALCITE-2757
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: KrishnaKant Agrawal
> Assignee: Julian Hyde
> Priority: Major
>
> SELECT DISTINCT sum(x) OVER (PARTITION BY y) FROM t
> is valid (per SQL standard) but
> SELECT sum(x) OVER (PARTITION BY y)
> FROM t
> GROUP BY sum(x) OVER (PARTITION BY y)
> is not. For example, given the query
> select sum(deptno) over (partition by loc)
> from dept
> group by sum(deptno) over (partition by loc);
> Oracle gives
> ORA-00934: group function is not allowed here
> Therefore we should generate a sub-query, something like this:
> SELECT c1
> FROM (
> SELECT sum(deptno) OVER (PARTITION BY loc)
> FROM dept) AS t
> GROUP BY c1;
>
> This will be achieved by Adding a new condition for seting the needNew Flag
> in SqlImplemontor.Builder.builder() as true in case there are Aggregate
> Expression being passed as Group By Keys.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
