MySQL Bugs: #13818: SHOW CREATE VIEW / TABLE and information

Bug #13818	SHOW CREATE VIEW / TABLE and information_schema.views fail for invalid view
Submitted:	6 Oct 2005 22:02	Modified:	22 Nov 2005 18:27
Reporter:	Kolbe Kegel
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.14	OS:	Linux (Linux)
Assigned to:	Sergey Gluhov	Target Version:

Description:
SHOW CREATE VIEW fails with an error when the definition of the view is invalid:

ERROR 1356 (HY000): View 'test.view1' references invalid table(s) or column(s) or
function(s)

The information cannot be retrieved from information_schema.views either.

This is undesirable, since it makes it impossible to review the view definition to
determine which invalid table(s) or column(s) or function(s) are referenced in the view
definition. This essentially renders the view "lost", since nothing can be done to
retrieve its original definition.

How to repeat:
CREATE TABLE table1 (col1 int, col2 int);
CREATE VIEW view1 AS SELECT col2 FROM table1;
ALTER TABLE table1 DROP col2;
SHOW CREATE VIEW view1;
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='view1';
SHOW WARNINGS;

Suggested fix:
Permit SHOW CREATE VIEW, SHOW CREATE TABLE, and retrieval of metadata from
information_schema even for invalid views. A warning should be issued in all cases if the
view is invalid.

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/internals/31434

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/internals/31788

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/internals/32172

Fixed in 5.0.17

Noted in 5.0.17 changelog.