MySQL Bugs: #103134: GROUPING-SETS-ROLLUP-CUBE-NOT-WORKING

Bug #103134	GROUPING-SETS-ROLLUP-CUBE-NOT-WORKING
Submitted:	27 Mar 2021 16:04	Modified:	31 Mar 2021 11:37
Reporter:	khang nguyen	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S7 (Test Cases)
Version:	8.0.23	OS:	Windows (Microsoft Windows 10 Home)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\Rimon\AppData\Roaming\MySQL\Workbench\log\wb.log]

I can't use functions like GROUPING SETS, ROLLUP, CUBE in MYSQL Workbench. I keep getting ErrorCode: 1064

How to repeat:
Whenever I use GROUPING SETS, ROLLUP, CUBE functions in query.

Hello khang nguyen,

Thank you for the report and feedback.
I'm not seeing any syntax errors at my end while using those modifiers. Could you please provide exact SQL statements causing the issue at your end? Thank you.

regards,
Umesh

Thanks Umesh for replying. I hope you are well.

My CUBE query:
SELECT warehouse, product, SUM(quantity)
FROM inventory
GROUP BY CUBE(warehouse, product)
ORDER BY warehouse, product;

My GROUPING SETS query:
SELECT warehouse, product, SUM (quantity) qty
FROM inventory
GROUP BY GROUPING SETS(
        (warehouse, product),
        (warehouse),
        (product),
        ()
    );

For ROLLUP I made it. I discovered it must be with the syntax WITH ROLLUP because I use MySQL Workbench 8.0 CE. However, I don't know about those above.

I tried it again. I got an ErrorCode: 1064. Syntax error near CUBE.

SELECT Continent, Region, SUM(LifeExpectancy)
FROM country
GROUP BY CUBE(Continent, Region)
ORDER BY Continent, Region ;

Imho this is expected WB behavior as there is no GROUPING SETS()/CUBE() function but just GROUPING() - https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html after Bug #46053 (implemented after WL#1979).  
Are you using MS SQL or MySQL? Could you please confirm from MySQL CLI(command line) and post the output here? Thank you!

Thank you very much. I use MySQL Command Line so It worked out fine for me. Now I know why.

SELECT product_name, sum(amount) FROM ta GROUP BY CUBE (product_name) LIMIT 0, 25

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(product_name) LIMIT 0, 25' at line 1

I don't know why the error is coming. please explain with example