[jira] [Updated] (CALCITE-3950) Doc of SqlGroupingFunction contradicts with its behavior
[
https://issues.apache.org/jira/browse/CALCITE-3950?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jin Xing updated CALCITE-3950:
--
Description:
Currently doc of SqlGroupingFunctions says:
{code:java}
/**
* The {@code GROUPING} function.
*
* 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 need 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]
> Doc of SqlGroupingFunction contradicts with 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.
> *
> * 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 need 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)
[jira] [Updated] (CALCITE-3950) Doc of SqlGroupingFunction contradicts with its behavior
[
https://issues.apache.org/jira/browse/CALCITE-3950?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jin Xing updated CALCITE-3950:
--
Description:
Currently doc of SqlGroupingFunctions says:
{code:java}
/**
* The {@code GROUPING} function.
*
* 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]
was:
Currently doc of SqlGroupingFunctions says:
{code:java}
/**
* The {@code GROUPING} function.
*
* 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 need 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]
> Doc of SqlGroupingFunction contradicts with 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.
> *
> * 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
> g
