Bug #18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views

• Submitted: 16 Mar 2006 14:53
• Modified: 31 May 2006 1:10
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.0.19/5.1.8BK
• OS: Any (ALL)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[16 Mar 2006 14:53] Dmitry Lenev

Description:
When definitions of several views using stored functions are processed in order to build contents of I_S.TABLES, information about validity of these view might be wrong.

See "how to repeat" section for clarification.

How to repeat:
drop database mysqltest;
create database mysqltest;
\u mysqltest
create table t1 (i int);
create function f1 () returns int return (select max(i) from t1);
create view v1 as select f1();
create table t2 (id int);
create function f2 () returns int return (select max(i) from t2);
create view v2 as select f2();
select table_name, table_type, table_comment from information_schema.tables where table_schema='mysqltest';
#+------------+------------+---------------+
#| table_name | table_type | table_comment |
#+------------+------------+---------------+
#| t1         | BASE TABLE |               |
#| t2         | BASE TABLE |               |
#| v1         | VIEW       | VIEW          |
#| v2         | VIEW       | VIEW          |
#+------------+------------+---------------+

drop table t2;
select table_name, table_type, table_comment from information_schema.tables where table_schema='mysqltest';
# The fact that v2 is invalid is not reported!!!
#+------------+------------+---------------+
#| table_name | table_type | table_comment |
#+------------+------------+---------------+
#| t1         | BASE TABLE |               |
#| v1         | VIEW       | VIEW          |
#| v2         | VIEW       | VIEW          |
#+------------+------------+---------------+

drop table t1;
select table_name, table_type, table_comment from information_schema.tables where table_schema='mysqltest';
# Note the difference between v1 and v2 !!!
#+------------+------------+----------------------------------------------------------------------------------+
#| table_name | table_type | table_comment                                                                    |
#+------------+------------+----------------------------------------------------------------------------------+
#| v1         | VIEW       | View 'mysqltest.v1' references invalid table(s) or column(s) or #function(s) or d |
#| v2         | VIEW       | VIEW                                                                             |
#+------------+------------+----------------------------------------------------------------------------------+

[16 Mar 2006 16:14] MySQL Verification Team

Thank you for the bug report.

[18 Mar 2006 8:51] Reggie Burnett

Jim, 

Be sure and have Gluh be one of your reviewers.  Thanks!

[29 Mar 2006 12:18] Bugs System

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/4273

[25 Apr 2006 5:47] Bugs System

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/5452

[18 May 2006 11:00] Bugs System

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/6561

[29 May 2006 15:18] Konstantin Osipov

Approved by email (second reviewer).

[30 May 2006 5:39] Bugs System

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/7001

[30 May 2006 5:43] Sergei Glukhov

fixed in 5.0.23

[31 May 2006 1:10] Paul DuBois

Noted in 5.0.23 changelog.

INFORMATION_SCHEMA.TABLES provided inconsistent info about invalid
views. This could cause server crashes or result in incorrect data
being returned for queries that attempt to obtain information from
INFORMATION_SCHEMA tables about views using stored functions.