MySQL Bugs: #61749: I_S.VIEW table information incomplete

Bug #61749	I_S.VIEW table information incomplete
Submitted:	5 Jul 2011 12:06	Modified:	5 Jul 2011 13:46
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0+, 5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
This is a 'spin-off' from 
http://bugs.mysql.com/bug.php?id=54139
http://bugs.mysql.com/bug.php?id=61718

.. where I concluded that the workaround was to SELECT FROM I_S in the particular scenario described in the other reports listed .  But this is unfortunately not quite possible. There is no record of the ALGORITHM clause in I_S.VIEWS. Instead I_S.VIEW table has an IS_UPDATABLE column but it is not posible to reconstruct the original ALGORITHM clause. 

Documentation references:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html
http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

The interesting parts for the discussion here can be summarized like this:

* If user sets ALGORITHM to TEMPTABLE user is 100% in control. A TEMPTABLE will be used and I_S will return IS_UPDATABLE = NO for the VIEW

* If user sets ALGORITHM to MERGE it may not be possible to avoid TEMPTABLE and the server will make it UNDEFINED (and issue a warning). I_S will return IS_UPDATABLE = NO for the VIEW

* If user sets ALGORITHM UNDEFINED (explicitly) or server does (implicitly) then the server decides whether it will use a TEMPTABLE or not. I_S may return IS_UPDATABLE = NO or = YES for the VIEW depending on server's decision in this respect.

So what we get from I_S.VIEWS is *what it is in effect after creation* - but in summary the original VIEW definition cannot be recreated from I_S (and the other bugs show cases where it cannot from SHOW CREATE EITHER)

How to repeat:
See above. 

Suggested fix:
1) Fix http://bugs.mysql.com/bug.php?id=54139 etc.
2) Put an ALGORITHM column in I_S.VIEWS

Fixed typo in synopsis.

Indeed, ALGORITHM clause is missing even in 5.5:

mysql> select * from information_schema.views where table_name like 'vvv%'\G 
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: vvv
     VIEW_DEFINITION: select 1 AS `a`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: vvv2
     VIEW_DEFINITION: select 1 AS `a`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 3. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: vvv3
     VIEW_DEFINITION: select 1 AS `a`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
3 rows in set (0.01 sec)

There is no way to see from I_S that first query was created without ALGORITHM, while third was created with ALGORITHM=TEMPTABLE:

mysql> show create view vvv3\G
*************************** 1. row ***************************
                View: vvv3
         Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vvv3` AS select 1 AS `a`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)