MySQL Bugs: #61718: Cannot SHOW CREATE VIEW if underlying tabels are ALTERed

Bug #61718	Cannot SHOW CREATE VIEW if underlying tabels are ALTERed
Submitted:	1 Jul 2011 12:00	Modified:	5 Oct 2012 16:02
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc, regression

Description:
If an underlying table changes SHOW CREATE VIEW is broken in 5.1 and 5.5 - but not in 5.0

This ia a problem because the VIEW definition needs to be adjusted.  That can be difficult it you cannot display it as it was created.

Affects various GUI clients.

How to repeat:
SELECT VERSION(); -- 5.0.90-community-nt
USE test;
DROP TABLE IF EXISTS k;
CREATE TABLE k (a VARCHAR(20));
DROP TABLE IF EXISTS l;
CREATE TABLE l (b VARCHAR(20));
CREATE VIEW m AS SELECT k.a, l.b FROM k,l;
ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20);
SHOW CREATE VIEW m; --success
SHOW FULL FIELDS FROM m;
/*
Error Code : 1356
View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them*/

SELECT VERSION(); -- 5.1.57-community
USE test;
DROP TABLE IF EXISTS k;
CREATE TABLE k (a VARCHAR(20));
DROP TABLE IF EXISTS l;
CREATE TABLE l (b VARCHAR(20));
DROP VIEW IF EXISTS m;
CREATE VIEW m AS SELECT k.a, l.b FROM k,l;
ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20);
SHOW CREATE VIEW m;
/*
Error Code : 1356
View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them */
SHOW FULL FIELDS FROM m; -- same error
SELECT * FROM `information_schema`.`VIEWS` WHERE table_name = 'm' -- success!!

So workaround is to SELECT from I_S.

Suggested fix:
Restore 5.0 behaviour. If SELECT from I_S can show the VIEW then also SHOW should.

when DROPPING an underlying table the problem does not occur:

USE test;
DROP TABLE IF EXISTS k;
CREATE TABLE k (a VARCHAR(20));
DROP TABLE IF EXISTS l;
CREATE TABLE l (b VARCHAR(20));
DROP VIEW IF EXISTS m;
CREATE VIEW m AS SELECT k.a, l.b FROM k,l;
DROP TABLE k;
SHOW CREATE VIEW m; -- success

Let me clarify that there is not a bug with SHOW FULL FIELDS of course.  

I included this just to show where the error is correct. It looks like SHOW VIEW invokes some code it should not.

added 'regression' tag.

Change in behavior is easy to reproduce. Compare 5.1:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.1.56-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tt1(c1 int);
Query OK, 0 rows affected (0.17 sec)

mysql> create table tt2(c2 int);
Query OK, 0 rows affected (0.08 sec)

mysql> create view vv as select tt1.c1, tt2.c2 from tt1, tt2;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table tt1 change c1 cc2 varchar(20);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create view vv\G
ERROR 1356 (HY000): View 'test.vv' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them
mysql> select * from vv;
ERROR 1356 (HY000): View 'test.vv' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them
mysql> show fields from vv;
ERROR 1356 (HY000): View 'test.vv' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them
mysql> select * from information_schema.views where table_name = 'vv'\G
*************************** 1. row ***************************
       TABLE_CATALOG: NULL
        TABLE_SCHEMA: test
          TABLE_NAME: vv
     VIEW_DEFINITION: select `test`.`tt1`.`c1` AS `c1`,`test`.`tt2`.`c2` AS `c2`
 from `test`.`tt1` join `test`.`tt2`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set, 1 warning (0.05 sec)

mysql> exit
Bye

to 5.0:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tt1(c1 int);
Query OK, 0 rows affected (0.23 sec)

mysql> create table tt2(c2 int);
Query OK, 0 rows affected (0.06 sec)

mysql> create view vv as select tt1.c1, tt2.c2 from tt1, tt2;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table tt1 change c1 cc2 varchar(20);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create view vv\G
*************************** 1. row ***************************
       View: vv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `vv` AS select `test`.`tt1`.`c1` AS `c1`,`test`.`tt2`.`c2` AS `c2`
from (`tt1` join `tt2`)
1 row in set, 1 warning (0.03 sec)

mysql> select * from vv;
ERROR 1356 (HY000): View 'test.vv' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them

But I doubt 5.0's behavior is any better. What is a potential bug here is that query to I_S does NOT produce error in 5.1, while SELECT and SHOW commands do...

I cannot agree with the conclusion. My points: 

1)
A VIEW may involve a very complex SELECT taking considerable time to work out and even if it in invalid it should be possible to display it as it was created. 

2)
If SELECT FROM I_S can display the VIEW definition as it was created there is no reason why SHOW CREATE should not either.

It is also inconsistent that you may DROP, but not ALTER an underlying table. 

DROP does not affect SHOW CERATE, ALTER does.

looks like a duplicate of http://bugs.mysql.com/bug.php?id=54139

Noted in 5.7.0 changelog.

SHOW CREATE VIEW failed if the tables underlying the view were 
changed.

Bug#76484 show create view won't work if view references invalid column

was marked as a duplicate.