Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26299

Some views force server (and mysqldump) to generate invalid SQL for their definitions

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration