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

No ALGORITHM information in I_S.VIEWS

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

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-7406 get from INFORMATION_SCHEMA original SQL code creating VIEW

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            hholzgra Hartmut Holzgraefe created issue -
            hholzgra Hartmut Holzgraefe made changes -
            Field Original Value New Value
            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:
            {noformat}
            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)
            {/noformat}

            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             		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:
            {noformat}
            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)
            {noformat}

            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
            hholzgra Hartmut Holzgraefe made changes -
            Affects Version/s 10.0.13 [ 16300 ]
            elenst Elena Stepanova made changes -
            Labels upstream
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 5.5 [ 15800 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 5.5.40 [ 17100 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.0.13 [ 16300 ]
            Affects Version/s 5.5.40 [ 17100 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Component/s Views [ 10111 ]
            Fix Version/s 10.1.3 [ 18000 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 53828 ] MariaDB v3 [ 65114 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 65114 ] MariaDB v4 [ 132410 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.