Bug #11711 create a view referencing a information schema (temporary) table
Submitted: 3 Jul 2005 14:15 Modified: 30 Aug 2005 4:11
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.7 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[3 Jul 2005 14:15] Roland Bouman 
Description:
The reference manual states that views cannot reference temporary tables. 
The information_schema database contains temporary tables.
I would expect it to be impossible to create a view based on a table from the information schema (wich would be a pity).
It is in fact possible to create view on a table from the information schema. (I consider it a desirable feature to be able to create views that reference tables from the information schema)

How to repeat:
mysql> use test;
Database changed
mysql> select table_type
    -> from   information_schema.tables
    -> where  table_schema = 'information_schema'
    -> and    table_name = 'SCHEMATA'
    -> ;
+------------+
| table_type |
+------------+
| TEMPORARY  |
+------------+
1 row in set (0.00 sec)
mysql> create or replace view s
    -> as
    -> select *
    -> from   information_schema.schemata
    -> ;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Either one of:

1) Document that it is not allowed to reference temporary tables in views, with the exception of those tables in the information schema

2) Use another value for the TABLE_TYPE column of the information_schema.TABLES table for those tables that belong to the information_schema itself
[3 Jul 2005 14:18] Roland Bouman 
MSSQL uses 'SYSTEM TABLE' and 'SYSTEM VIEW' table types.
[17 Jul 2005 19:27] Sergei Golubchik 
Right, INFORMATION_SCHEMA tables should not be reported as "temporary"
[8 Aug 2005 19:58] 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/internals/28029
[23 Aug 2005 16:43] Jim Winstead 
This is fixed in 5.0.12. INFORMATION_SCHEMA objects are now reported as 'SYSTEM VIEW'.
[30 Aug 2005 4:11] Mike Hillyer 
Documented in 5.0.12 changelog:

<listitem><para><literal>INFORMATION_SCHEMA</literal> objects are now reported as a <literal>SYSTEM
VIEW</literal> table type. (Bug #11711)</para></listitem>