MySQL Bugs: #36073: TEMPORARY TABLE not listed in INFORMATION

Bug #36073	TEMPORARY TABLE not listed in INFORMATION_SCHEMA.TABLES
Submitted:	14 Apr 2008 23:58	Modified:	25 Apr 2008 17:29
Reporter:	Ondra Zizka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.58	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	temporary information_schema

Description:
My TEMPORARY TABLEs are not listed neither in INFORMATION_SCHEMA.TABLES nor SHOW TABLES. MySQL version: 5.0.51a-community-nt-log

How to repeat:
CREATE SCHEMA test_temp;
USE test_temp;
CREATE TABLE base SELECT 1;
CREATE TEMPORARY TABLE temp SELECT 1;
SELECT * FROM temp;
SHOW TABLES;
SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_temp';
SELECT table_name FROM information_schema.tables WHERE table_name = 'temp';
SELECT table_type, COUNT(*) FROM information_schema.tables GROUP BY table_type;

My result:

mysql> CREATE SCHEMA test_temp;
Query OK, 1 row affected (0.03 sec)

mysql> USE test_temp;
Database changed

mysql> CREATE TABLE base SELECT 1;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE temp SELECT 1;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temp;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test_temp |
+---------------------+
| base                |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_temp';
+------------+
| table_name |
+------------+
| base       |
+------------+
1 row in set (0.01 sec)

mysql> SELECT table_name FROM information_schema.tables WHERE table_name = 'temp';
Empty set (0.01 sec)

mysql> SELECT table_type, COUNT(*) FROM information_schema.tables GROUP BY table_type;
+-------------+----------+
| table_type  | COUNT(*) |
+-------------+----------+
| BASE TABLE  |      385 |
| SYSTEM VIEW |       17 |
| VIEW        |        3 |
+-------------+----------+
3 rows in set, 2 warnings (1.11 sec)

Suggested fix:
I suppose that the temporary table should be listed, according to the manual:

http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

"If table is temporary, then TABLE_TYPE = TEMPORARY."

Thank you for a bug report. 

SHOW TABLES should NOT show temporary tables (see http://dev.mysql.com/doc/refman/5.0/en/show-tables.html), but INFORMATION_SCHEMA.TABLES should containt temporary tables created by the user that send the query, it seems.

This is a feature request and a duplicate of
Bug#20001 Support for temp-tables in INFORMATION_SCHEMA

I noticed that bug report, but it seemed to concern something else. It faces as an feature request and says something about insert behavior. This bug report is dedicated only to TEMP TABLES in I_S.

The synopsis of the other bug report is "support for ...", and since it has last comment from March 2007 and support for TEMPORARY TABLEs is mentioned in documentation, I thought that there was the support, but some bug introduced later has broken it.

So if that's really the same bug, be it.

I dare to re-open the bug report and change its category to Documentation, and ask to add a notice to the MySQL manual that temporary tables are not supported in I_S (yet).

Yes, documentation should describe current behaviour, not good intentions for the future.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated INFORMATION_SCHEMA.TABLES description to indicate that it does not list TEMPORARY tables.