Bug #75405 I can't get from INFORMATION_SCHEMA original SQL code creating VIEW
Submitted: 4 Jan 2015 22:30 Modified: 6 Jan 2015 10:24
Reporter: Mikhail Gavrilov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6.21, 5.6.23, 5.7.6 OS:Any
Assigned to:
Tags: creating, information_schema, SQL code, VIEW

[4 Jan 2015 22:30] Mikhail Gavrilov
Description:
As you can see when I get SQL definition with "SHOW CREATE VIEW" I get body without database `test` as expected. When I get definition from INFORMATION SCHEMA I have definition with database `test`. If I will use this SQL code for create view in different database I would have problems.

How to repeat:
CREATE TABLE `test` (
  `a` MEDIUMTEXT,
  `b` MEDIUMTEXT
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE VIEW `view_test` AS
(SELECT * FROM test);

SELECT  `TABLE_NAME`
	,`TABLE_NAME`
	,`VIEW_DEFINITION`
FROM `information_schema`.`VIEWS` IS_V
	WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'

TABLE_NAME  TABLE_NAME  VIEW_DEFINITION                                                                
----------  ----------  -------------------------------------------------------------------------------
view_test   view_test   (SELECT `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` FROM `test`.`test`)

SHOW CREATE VIEW `view_test`

VIEW       CREATE VIEW                                                                                                                                              character_set_client  collation_connection  
---------  -------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------  ----------------------
view_test  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (SELECT `test`.`a` AS `a`,`test`.`b` AS `b` FROM `test`)  utf8                  utf8_general_ci
[5 Jan 2015 13:27] Peter Laursen
@Mihail.  Nice to see you complaining another place that in our support system! :-)

This has been reported before.  It surprised a lot of people when Views were introduced in MySQL 5.0, and such reports re-surface from time to time.

But the explanations is that the view is *frozen at the very moment it is created* - and SQL standards require this. If you add more columns to the underlying table the new columns are not *and should not be* included in the View.  If you DROP or RENAME columns in the underlying table,  the View is invalidated.  And this is intentional and standardized behaviour according to explanations having appeared here earlier.

-- Peter
--  Webyog
[5 Jan 2015 13:32] Shane Bester
this sounds like http://bugs.mysql.com/bug.php?id=10713 that was apparently rejected!
[6 Jan 2015 10:24] Umesh Shastry
Hello Mikhail,

Thank you for the report.
This behavior exists from 5.0 days but imho VIEW_DEFINITION should match what SHOW CREATE VIEW.. shows.  

Thanks,
Umesh
[6 Jan 2015 10:46] Umesh Shastry
// 5.0.96

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> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `a` MEDIUMTEXT,
    ->   `b` MEDIUMTEXT
    -> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW `view_test` AS
    -> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
       View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
1 row in set (0.00 sec)

mysql> SELECT  `TABLE_NAME`
    -> ,`TABLE_NAME`
    -> ,`VIEW_DEFINITION`
    -> FROM `information_schema`.`VIEWS` IS_V
    -> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'
    -> ;
+------------+------------+---------------------------------------------------------------------------------------------------------+
| TABLE_NAME | TABLE_NAME | VIEW_DEFINITION                                                                                         |
+------------+------------+---------------------------------------------------------------------------------------------------------+
| view_test  | view_test  | /* ALGORITHM=UNDEFINED */ (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`) |
+------------+------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Jan 2015 10:46] Umesh Shastry
// 5.6.23

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `a` MEDIUMTEXT,
    ->   `b` MEDIUMTEXT
    -> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW `view_test` AS
    -> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
                View: view_test
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> SELECT  `TABLE_NAME`
    -> ,`TABLE_NAME`
    -> ,`VIEW_DEFINITION`
    -> FROM `information_schema`.`VIEWS` IS_V
    -> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test';
+------------+------------+-------------------------------------------------------------------------------+
| TABLE_NAME | TABLE_NAME | VIEW_DEFINITION                                                               |
+------------+------------+-------------------------------------------------------------------------------+
| view_test  | view_test  | (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`) |
+------------+------------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Jan 2015 10:46] Umesh Shastry
// 5.6.7

mysql> CREATE TABLE `test` (
    ->   `a` MEDIUMTEXT,
    ->   `b` MEDIUMTEXT
    -> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'test' already exists
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `a` MEDIUMTEXT,
    ->   `b` MEDIUMTEXT
    -> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW `view_test` AS
    -> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
                View: view_test
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> SELECT  `TABLE_NAME`
    -> ,`TABLE_NAME`
    -> ,`VIEW_DEFINITION`
    -> FROM `information_schema`.`VIEWS` IS_V
    -> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'
    -> \G
*************************** 1. row ***************************
     TABLE_NAME: view_test
     TABLE_NAME: view_test
VIEW_DEFINITION: (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`)
1 row in set (0.00 sec)