Bug #79044 group by WITH CUBE
Submitted: 31 Oct 2015 19:28 Modified: 31 Mar 11:32
Reporter: Zhe Dong (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: CUBE, GROUP BY, MYSQL SERVER, OLAP, Optimizer, SQL

[31 Oct 2015 19:28] Zhe Dong
Description:
MySQL has limited OLAP features. One of them is GROUP BY feature. Modern SQL database supports WITH ROLLUP/CUBE or more complicated ROLLUP(), CUBE() expressions while MySQL only supports WITH ROLLUP.

I plan to add WITH CUBE option to MySQL GROUP BY feature. The intended behavior should be as described here:
https://technet.microsoft.com/en-us/library/ms175939(v=sql.90).aspx

The grouping() function refered in the article is described in bug #46053. I already submitted a fix for that.

How to repeat:
Assume table t has:
Item                 Color                Quantity                   
-------------------- -------------------- -------------------------- 
Table                Blue                 124                        
Table                Red                  223                        
Chair                Blue                 101                        
Chair                Red                  210      

execute:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM t
GROUP BY Item, Color WITH CUBE
[31 Mar 11:32] MySQL Verification Team
Hello Zhe Dong,

Thank you for the feature request!

regards,
Umesh