You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Jin Xing (Jira)" <ji...@apache.org> on 2020/04/23 13:10:00 UTC
[jira] [Updated] (CALCITE-3950) Doc of SqlGroupingFunction
contradicts its behavior
[ https://issues.apache.org/jira/browse/CALCITE-3950?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jin Xing updated CALCITE-3950:
------------------------------
Summary: Doc of SqlGroupingFunction contradicts its behavior (was: Doc of SqlGroupingFunction contradicts with its behavior)
> Doc of SqlGroupingFunction contradicts its behavior
> ---------------------------------------------------
>
> Key: CALCITE-3950
> URL: https://issues.apache.org/jira/browse/CALCITE-3950
> Project: Calcite
> Issue Type: Bug
> Reporter: Jin Xing
> Priority: Major
>
> Currently doc of SqlGroupingFunctions says:
> {code:java}
> /**
> * The {@code GROUPING} function.
> *
> * <p>Accepts 1 or more arguments.
> * Example: {@code GROUPING(deptno, gender)} returns
> * 3 if both deptno and gender are being grouped,
> * 2 if only deptno is being grouped,
> * 1 if only gender is being groped,
> * 0 if neither deptno nor gender are being grouped.{code}
> But its behavior in agg.iq is as below:
> {code:java}
> # GROUPING in SELECT clause of CUBE query
> select deptno, job, count(*) as c, grouping(deptno) as d,
> grouping(job) j, grouping(deptno, job) as x
> from "scott".emp
> group by cube(deptno, job);
> +--------+-----------+----+---+---+---+
> | DEPTNO | JOB | C | D | J | X |
> +--------+-----------+----+---+---+---+
> | 10 | CLERK | 1 | 0 | 0 | 0 |
> | 10 | MANAGER | 1 | 0 | 0 | 0 |
> | 10 | PRESIDENT | 1 | 0 | 0 | 0 |
> | 10 | | 3 | 0 | 1 | 1 |
> | 20 | ANALYST | 2 | 0 | 0 | 0 |
> | 20 | CLERK | 2 | 0 | 0 | 0 |
> | 20 | MANAGER | 1 | 0 | 0 | 0 |
> | 20 | | 5 | 0 | 1 | 1 |
> | 30 | CLERK | 1 | 0 | 0 | 0 |
> | 30 | MANAGER | 1 | 0 | 0 | 0 |
> | 30 | SALESMAN | 4 | 0 | 0 | 0 |
> | 30 | | 6 | 0 | 1 | 1 |
> | | ANALYST | 2 | 1 | 0 | 2 |
> | | CLERK | 4 | 1 | 0 | 2 |
> | | MANAGER | 3 | 1 | 0 | 2 |
> | | PRESIDENT | 1 | 1 | 0 | 2 |
> | | SALESMAN | 4 | 1 | 0 | 2 |
> | | | 14 | 1 | 1 | 3 |
> +--------+-----------+----+---+---+---+
> (18 rows)
> {code}
>
> The doc needs to be rectified thus to be consistent with query result and the behavior of Hive[1] and PostgreSQL[2]
> [1] [https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup?spm=ata.13261165.0.0.528c6dfcXalQFy#EnhancedAggregation,Cube,GroupingandRollup-Groupingfunction]
> [2] [https://www.postgresql.org/docs/9.5/functions-aggregate.html]
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)