support `GROUP BY` modifiers

[zz-jason]

Request Description

MySQL and Oracle support ROLLUP modifier, SQL Server also supports CUBE and GROUPING SETS

References:

• MySQL: GROUP BY Modifiers
• Oracle Database:
  • ROLLUP Extension to GROUP BY
  • CUBE Extension to GROUP BY
  • GROUPING Functions
  • GROUPING SETS Expression
• SQL Server: SELECT - GROUP BY

An example in MySQL:

drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);

MySQL > select a, min(b) from t group by a with rollup;
+------+--------+
| a    | min(b) |
+------+--------+
|    1 |      2 |
|    2 |      2 |
|    3 |      2 |
| NULL |      2 |
+------+--------+
4 rows in set (0.01 sec)

Category

Feature

Value

• Value Point: 2

It's hard to tell the value for now. Quoted from https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm:

One of the key concepts in decision support systems is "multi-dimensional analysis": examining the enterprise from all necessary combinations of dimensions. We use the term "dimension" to mean any category used in specifying questions. Among the most commonly specified dimensions are time, geography, product, department, and distribution channel, but the potential dimensions are as endless as the varieties of enterprise activity. The events or entities associated with a particular set of dimension values are usually referred to as "facts." The facts may be sales in units or local currency, profits, customer counts, production volumes, or anything else worth tracking.

I think the value of this request is to improve the usability and performance in Business Intelligence(BI) scenarios. No need to run several aggregate queries to get another level aggregate result. I'm curious about whether are some BI tools rely on this feature.

Workload Estimation

Workload contains coding, documenting, and testing.
1 Point for 1 Person/Work Day

• 60 Points, hard to say, most modifications are on the aggregate operator in execution engine.

[Lloyd-longfei]

My also encountered the same problem with TIDB, and when writing SQL, the following mistakes were made:

Error : line 83 column 35 near " rollup" (total length 3865) 

This problem may directly affect the running of our online production environment, when can you increase the function, or tell us how to solve the problem. @hanfei1991

[zz-jason]

@Lloyd-longfei Thanks for your feed back, I'm sorry to tell you that group by ... with rollup is not on our roadmap now, but we'll support it as soon as possible.

For now, a workaround is to write another sql to get the aggregation summary, see https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html for more detail.

[morgo]

Confirming that this feature is still missing:

mysql> select a, min(b) from t group by a with rollup;
ERROR 1105 (HY000): line 1 column 39 near " rollup" (total length 46)
mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.0-rc.3-385-gbb0e7a18f
Git Commit Hash: bb0e7a18f63b12b167eb1c5ba58bcf4cb6ea7a1b
Git Branch: socket
UTC Build Time: 2018-12-29 10:14:15
GoVersion: go version go1.11.4 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[vkingnew]

this feature is still not support as tidb 2.1.7,and which version to support ROLLUP and CUBE

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.7
Git Commit Hash: f5b52cb
Git Branch: HEAD
UTC Build Time: 2019-03-28 07:54:41
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)