Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.10, 10.5.12, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Description
mysqldump used to have problems generating SQL for views (see MDEV-8028). Looks like they remain for complex views involving CTE and CASE expression deeply nested in the view definition. Consider the following, purely artifical test case based on a structure of a really useful view from one of the customers:
openxs@fc33 maria10.5]$ bin/mysql --socket=/tmp/mariadb.sock
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.5.12-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]> create database db;
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [(none)]> use db;
|
Database changed
|
MariaDB [db]> create or replace view dhv as
|
-> (select * from (
|
-> with recursive h(user, hosts) as
|
-> (select user,
|
-> cast('' as char(500)) from
|
-> ( select user, host from mysql.user
|
-> ) as firstgen where user = ''
|
-> union all
|
-> select nextgen.user,
|
-> cast(case when parent.hosts = ''
|
-> then (concat ('.', cast(nextgen.host as char)))
|
-> else (concat(parent.hosts, '.', cast(nextgen.host as char)))
|
-> end as char)
|
-> from
|
-> ( select user, host from mysql.user where user != ''
|
-> ) as nextgen
|
-> inner join h as parent
|
-> on nextgen.user = parent.user
|
-> )
|
-> select * from h
|
-> ) as dt);
|
Query OK, 0 rows affected (0.037 sec)
|
Do not mind the completely stupid view definition - it's used in purpose to simplify and obfuscate the real one, so it's easy to reproduce the problem.
So, we have this stupid view with proper column names:
MariaDB [db]> select * from dhv;
|
+------+-------+
|
| user | hosts |
|
+------+-------+
|
| | |
|
| | |
|
+------+-------+
|
2 rows in set (0.043 sec)
|
|
|
MariaDB [db]> desc dhv;
|
+-------+--------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+--------------+------+-----+---------+-------+
|
| user | char(80) | YES | | NULL | |
|
| hosts | varchar(500) | YES | | NULL | |
|
+-------+--------------+------+-----+---------+-------+
|
2 rows in set (0.002 sec)
|
|
|
MariaDB [db]> show create view dhv\G
|
*************************** 1. row ***************************
|
View: dhv
|
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`openxs`@`localhost` SQL SECURITY DEFINER VIEW `dhv` AS (select `dt`.`user` AS `user`,`dt`.`hosts` AS `hosts` from (with recursive h(`user`,`hosts`) as (select `firstgen`.`user` AS `user`,cast('' as char(500) charset utf8) AS `hosts` from (select `user`.`User` AS `user`,`user`.`Host` AS `host` from `mysql`.`user`) `firstgen` where `firstgen`.`user` = '' union all select `nextgen`.`user` AS `user`,cast(case when `parent`.`hosts` = '' then concat('.',cast(`nextgen`.`host` as char charset utf8)) else concat(`parent`.`hosts`,'.',cast(`nextgen`.`host` as char charset utf8)) end as char charset utf8) AS `cast(case when parent.hosts = ''
|
then (concat ('.', cast(nextgen.host as char)))
|
else (concat(parent.hosts, '.', cast(nextgen.host as char)))
|
end as char)` from ((select `user`.`User` AS `user`,`user`.`Host` AS `host` from `mysql`.`user` where `user`.`User` <> '') `nextgen` join `h` `parent` on(`nextgen`.`user` = `parent`.`user`)))select `h`.`user` AS `user`,`h`.`hosts` AS `hosts` from `h`) `dt`)
|
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
1 row in set (0.002 sec)
|
But you can already spot the problem above, see this column alias generated for some reason:
AS `cast(case when parent.hosts = ''
|
then (concat ('.', cast(nextgen.host as char)))
|
else (concat(parent.hosts, '.', cast(nextgen.host as char)))
|
end as char)`
|
Surely if we dump and try to reload, it fails:
[openxs@fc33 maria10.5]$ bin/mysqldump --socket=/tmp/mariadb.sock db > /tmp/db.sql
|
[openxs@fc33 maria10.5]$ vi /tmp/db.sql
|
[openxs@fc33 maria10.5]$ bin/mysql --socket=/tmp/mariadb.sock db < /tmp/db.sql
|
ERROR 1166 (42000) at line 44: Incorrect column name 'cast(case when parent.hosts = ''
|
then (concat ('.', cast(nextgen.host as char))) '
|
[openxs@fc33 maria10.5]$
|
It seems to be more a server problem, as we can see from SHOW CREATE VIEW. Simpler cases seems to be covered by the fix to MySQL bug #65388 we merged, but not this complex one.