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

No ALGORITHM information in I_S.VIEWS

    XMLWordPrintable

    Details

      Description

      See http://bugs.mysql.com/bug.php?id=73886
      and older http://bugs.mysql.com/bug.php?id=16832

      "ALGORITHM missing from "information_schema.views"

      The 5.0 solution back then was not to add an ALGORITHM column as requested, but to add the ALGORITHM as comment to the I_S.VIEWS.VIEW_DEFINITION column as e.g.

      /* ALGORITHM=MERGE */ select `test`.`t1`.`id` AS `id` from `test`.`t1`

      I can verify that these comments are shown by MySQL 5.0.45, but not e.g. MySQL 5.1.67 or MySQL 5.6.20 ...

      ... and I also think that a new ALGORITHM column would have been the better solution as with the comment approach parsing of the VIEW_DEFINITION result would be necessary to extract the ALGORITHM instead of just saying

      SELECT ALGORITHM FROM I_S.VIEWS WHERE ...

      How to repeat:

      mysql> drop table t1;
      Query OK, 0 rows affected (0.00 sec)
       
      mysql> create table t1(id int primary key);
      Query OK, 0 rows affected (0.05 sec)
       
      mysql> create algorithm=merge view v1 as select * from t1;
      ERROR 1050 (42S01): Table 'v1' already exists
      mysql> drop view v1;
      Query OK, 0 rows affected (0.00 sec)
       
      mysql> drop view v2;
      ERROR 1051 (42S02): Unknown table 'v2'
      mysql> create algorithm=merge view v1 as select * from t1;
      Query OK, 0 rows affected (0.04 sec)
       
      mysql> create algorithm=temptable view v2 as select * from t1;
      Query OK, 0 rows affected (0.04 sec)
       
      mysql> select * from information_schema.views
          -> ;
      +---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
      | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                  | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
      +---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
      | NULL          | test         | v1         | select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE         | YES          | root@localhost | DEFINER       | utf8                 | utf8_general_ci      |
      | NULL          | test         | v2         | select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE         | NO           | root@localhost | DEFINER       | utf8                 | utf8_general_ci      |
      +---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
      2 rows in set (0.01 sec)

      Both views only differ by their name in the INFORMATION_SCHEMA.VIEWS table, the difference in the ALGORITHM used is invisible when relying on INFORMATION_SCHEMA unless using a 5.0.x version ... (with x>=25)

      Suggested fix:
      Add an ALGORITHM column to the VIEWS table as originally requested

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: