Bug #16832 ALGORITHM missing from "information_schema.views"
Submitted: 27 Jan 2006 9:56 Modified: 23 Jul 2006 4:10
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0 OS:Any (All)
Assigned to: Alexey Botchkov CPU Architecture:Any

[27 Jan 2006 9:56] Magnus Blåudd
Description:
The information_schema table "information_schema.views" does not contain information about which algorithm the view uses.

select * from information_schema.views where table_name="v";
TABLE_CATALOG NULL
TABLE_SCHEMA  test
TABLE_NAME    v
VIEW_DEFINITION       select `test`.`t`.`s2` AS `s2`,`test`.`t`.`s1` AS `s1`
from `test`.`t` where (`test`.`t`.`s1` > 5) order by `test`.`t`.`s1`
CHECK_OPTION  NONE
IS_UPDATABLE  NO
DEFINER       root@localhost
SECURITY_TYPE DEFINER

This information is available in "SHOW CREATE VIEW "

How to repeat:
select * from information_schema.views where table_name="v";

Suggested fix:
Add the field ALGORITHM to "information_schema.views"
[27 Jan 2006 11:42] Miguel Solorzano
Thank you for the bug report.
[8 Feb 2006 13:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2317
[13 Jun 2006 10:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7561
[13 Jun 2006 10:18] Sergei Glukhov
ok to push
[13 Jun 2006 12:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7564
[23 Jul 2006 4:03] Paul Dubois
Noted in 5.0.25 changelog.

The VIEW_DEFINITION column of the INFORMATION_SCHEMA VIEWS table now
contains information about the view algorithm.
[4 Jan 2015 22:22] Mikhail Gavrilov
CREATE TABLE `test` (
  `a` MEDIUMTEXT,
  `b` MEDIUMTEXT
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE 
ALGORITHM = MERGE
VIEW `view_test2` AS
(SELECT * FROM test);

SELECT  *
FROM `information_schema`.`VIEWS` IS_V
	WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name IN('view_test2')

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  VIEW_DEFINITION                                                                CHECK_OPTION  IS_UPDATABLE  DEFINER         SECURITY_TYPE  CHARACTER_SET_CLIENT  COLLATION_CONNECTION  
-------------  ------------  ----------  -----------------------------------------------------------------------------  ------------  ------------  --------------  -------------  --------------------  ----------------------
def            test          view_test2  (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`)  NONE          YES           root@localhost  DEFINER        utf8                  utf8_general_ci       

Don't understand how to see view algorithm in VIEW_DEFINITION column of the INFORMATION_SCHEMA VIEWS table.

Seems bug it not fixed yet

SELECT VERSION()

version()  
-----------
5.6.21
[12 Jan 2015 12:19] Magnus Blåudd
Information about which ALGORITHM to use for a particular view was added to the information_schema.VIEW_DEFINITION column in MySQL 5.0, since the ALGORITHM keyword is a MySQL extension it was output within "/* ALGORITHM= */" to make the generated SQL have valid standard SQL syntax.

Later MySQL Version has removed the output of the ALGORITHM= keyword from information_schema.views.

It's however possible to use SHOW CREATE VIEW as long as the sql_mode is not set to ANSI or a "foreign db".

It's not likely an old bug will be resurrected. In my opinion you should file a new bug requesting a new column added to  information_schema.views which should return the currently used ALGORITHM of the view. 

mysql> create table t1(a int);
mysql> create algorithm=merge view v_merge(v1) as select * from t1;

mysql> select * from information_schema.views;
mysql> SHOW CREATE VIEW v_merge; # ALGORITHM= shows here!

mysql> set sql_mode='ORACLE';
mysql> select * from information_schema.views;
mysql> SHOW CREATE VIEW v_merge;
[29 Nov 2016 14:30] Franc Drobnič
Still not fixed. Version 5.6.34.