Bug #73886 bug #16832 only fixed in 5.0, fix never merged to later versions?

• Submitted: 11 Sep 2014 10:39
• Modified: 12 Sep 2014 5:13
• Reporter: Hartmut Holzgraefe
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: mysql 5.6.20; anything 5.x with x>0?, 5.1.73, 5.5.41, 5.6.22
• OS: Any
• CPU Architecture: Any

[11 Sep 2014 10:39] Hartmut Holzgraefe

Description:
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

[12 Sep 2014 5:13] MySQL Verification Team

Hello Hartmut,

Thank you for the report.

Thanks,
Umesh

[12 Sep 2014 5:15] MySQL Verification Team

// 5.0.96 - includes /* ALGORITHM.. in VIEW_DEFINITION column

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.0.96-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | unknown-linux-gnu            |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> create algorithm=merge view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create algorithm=temptable view v2 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.views;
+---------------+--------------+------------+----------------------------------------------------------------------------+--------------+--------------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                            | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE |
+---------------+--------------+------------+----------------------------------------------------------------------------+--------------+--------------+----------------+---------------+
| NULL          | test         | v1         | /* ALGORITHM=MERGE */ select `test`.`t1`.`id` AS `id` from `test`.`t1`     | NONE         | YES          | root@localhost | DEFINER       |
| NULL          | test         | v2         | /* ALGORITHM=TEMPTABLE */ select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE         | NO           | root@localhost | DEFINER       |
+---------------+--------------+------------+----------------------------------------------------------------------------+--------------+--------------+----------------+---------------+
2 rows in set (0.01 sec)

mysql>

// 5.1.73, 5.5.41, 5.6.22 - /* ALGORITHM.. is missing in VIEW_DEFINITION column

mysql> use test
Database changed
mysql>
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.10 sec)

mysql> create algorithm=merge view v1 as select * from t1;
Query OK, 0 rows affected (0.07 sec)

mysql> create algorithm=temptable view v2 as select * from t1;
Query OK, 0 rows affected (0.07 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       | latin1               | latin1_swedish_ci    |
| NULL          | test         | v2         | select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE         | NO           | root@localhost | DEFINER       | latin1               | latin1_swedish_ci    |
+---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
2 rows in set (0.01 sec)