Description:
The SHOW CREATE VIEW command (and thus mysqldump) produces invalid syntax for VIEWS that were created with a ROLL UP clause. The VIEW query appears to have C function names like `rollup_sum_switcher` and `rollup_group_item` inserted into it.
Importantly, backups generated with mysqldump will not show an error but will fail when restored due to this bug. Failure may only be noticed later when a user attempts to access the view.
I've confirmed this bug on 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 but the bug is also reported to be an issue since > 18.0.18 on Mac see here: https://stackoverflow.com/questions/64985354/mysql-8-0-18-dump-restore-problems-with-views...
How to repeat:
DROP DATABASE IF EXISTS rollup_test;
CREATE DATABASE rollup_test;
USE rollup_test;
DROP TABLE IF EXISTS tmp_table_rollup;
CREATE TABLE tmp_table_rollup (
id bigint(20) NOT NULL AUTO_INCREMENT,
groupKey varchar(250) DEFAULT NULL,
groupValue varchar(250) DEFAULT NULL,
PRIMARY KEY (id)
);
DROP VIEW IF EXISTS tmp_view_rollup;
CREATE VIEW tmp_view_rollup
AS
SELECT groupKey,
COUNT(id) AS groupCount
FROM tmp_table_rollup
GROUP BY groupKey
WITH ROLLUP;
SHOW CREATE VIEW tmp_view_rollup;
# Output is:
# CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `tmp_view_rollup` AS select rollup_group_item(`tmp_table_rollup`.`groupKey`,0) AS `groupKey`,rollup_sum_switcher(count(`tmp_table_rollup`.`id`)) AS `groupCount` from `tmp_table_rollup` group by `tmp_table_rollup`.`groupKey` with rollup
# Note the view definition contains C function names such as `rollup_sum_switcher` and `rollup_group_item` instead of the proper ROLL UP query. This bug causes mysqldump to produce invalid backups.
