Bug #103583 Dump/Restore fails with nested VIEWS having ROLLUP over DISTINCT groups
Description:
Assuming following 2 views
CREATE VIEW v2 AS SELECT 4711 AS XYZ;
CREATE VIEW v1 AS
SELECT
1 AS A
,COUNT(DISTINCT (1)) AS B
FROM v2
GROUP BY A WITH ROLLUP;
Dumping, restoring the dumped file and calling SHOW CREATE VIEW v1; leads to following error:
FAILS with ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct count(distinct 4711)) AS `B` from `dbfail`.`v2` group by `A` with rollu' at line 1
In my understanding the dumpfile looks OK. I assume the import/restore can't handle it.
It works fine
- in 8.0.18
- without the ROLLUP modifier
- or without the DISTINCT count
- or when v2 is a TABLE
- OR SURPRISINGLY, and actually the reason for this post ... The failure depends on the names of the views. More precise, their appearance in the dumpfile! If we switch the names of the views v1<->v2 its OK
How to repeat:
The first part is the working one and the second fails due to switched view names.
SCHEMA=dbok
INNERVIEWNAME=v1
OUTERVIEWNAME=v2
mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \
CREATE DATABASE $SCHEMA; \
USE $SCHEMA; \
CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \
CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \
SHOW CREATE VIEW $OUTERVIEWNAME;"
rm -rf ./$SCHEMA.mysql
mysqldump $SCHEMA > ./$SCHEMA.mysql
mysql $SCHEMA < ./$SCHEMA.mysql
mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;"
SCHEMA=dbfail
INNERVIEWNAME=v2
OUTERVIEWNAME=v1
mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \
CREATE DATABASE $SCHEMA; \
USE $SCHEMA; \
CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \
CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \
SHOW CREATE VIEW $OUTERVIEWNAME;"
rm -rf ./$SCHEMA.mysql
mysqldump $SCHEMA > ./$SCHEMA.mysql
mysql $SCHEMA < ./$SCHEMA.mysql
mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;"
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Assuming following 2 views CREATE VIEW v2 AS SELECT 4711 AS XYZ; CREATE VIEW v1 AS SELECT 1 AS A ,COUNT(DISTINCT (1)) AS B FROM v2 GROUP BY A WITH ROLLUP; Dumping, restoring the dumped file and calling SHOW CREATE VIEW v1; leads to following error: FAILS with ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct count(distinct 4711)) AS `B` from `dbfail`.`v2` group by `A` with rollu' at line 1 In my understanding the dumpfile looks OK. I assume the import/restore can't handle it. It works fine - in 8.0.18 - without the ROLLUP modifier - or without the DISTINCT count - or when v2 is a TABLE - OR SURPRISINGLY, and actually the reason for this post ... The failure depends on the names of the views. More precise, their appearance in the dumpfile! If we switch the names of the views v1<->v2 its OK How to repeat: The first part is the working one and the second fails due to switched view names. SCHEMA=dbok INNERVIEWNAME=v1 OUTERVIEWNAME=v2 mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \ CREATE DATABASE $SCHEMA; \ USE $SCHEMA; \ CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \ CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \ SHOW CREATE VIEW $OUTERVIEWNAME;" rm -rf ./$SCHEMA.mysql mysqldump $SCHEMA > ./$SCHEMA.mysql mysql $SCHEMA < ./$SCHEMA.mysql mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;" SCHEMA=dbfail INNERVIEWNAME=v2 OUTERVIEWNAME=v1 mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \ CREATE DATABASE $SCHEMA; \ USE $SCHEMA; \ CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \ CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \ SHOW CREATE VIEW $OUTERVIEWNAME;" rm -rf ./$SCHEMA.mysql mysqldump $SCHEMA > ./$SCHEMA.mysql mysql $SCHEMA < ./$SCHEMA.mysql mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;"