You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "KrishnaKant Agrawal (JIRA)" <ji...@apache.org> on 2019/01/02 11:12:00 UTC
[jira] [Updated] (CALCITE-2757) DISTINCT not being handled correctly in RelToSqlConverter

     [ 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 setting the needNew Flag in SqlImplemontor.Builder.builder() as true in case there are Aggregate Expressions 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;

 

This will be achieved by Adding a new condition for setting the needNew Flag in SqlImplemontor.Builder.builder() as true in case there are Aggregate Expressions being passed as Group By Keys.


> 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 setting the needNew Flag in SqlImplemontor.Builder.builder() as true in case there are Aggregate Expressions being passed as Group By Keys.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)