MySQL Bugs: #16832: ALGORITHM missing from "information

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

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"

Thank you for the bug report.

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

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

ok to push

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

Noted in 5.0.25 changelog.

The VIEW_DEFINITION column of the INFORMATION_SCHEMA VIEWS table now
contains information about the view algorithm.

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

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;

Still not fixed. Version 5.6.34.