Bug #20001 Support for temp-tables in INFORMATION_SCHEMA
Submitted: 22 May 2006 14:57 Modified: 23 May 2006 17:21
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.x OS:Any (any)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[22 May 2006 14:57] Peter Laursen
Description:
I am aware that 'show tables' does not return temporary tables.
However with the MySQL 5.x INFORMATION_SCHEMA it should be possible to add a category 'TEMP_TABLE' to the `TABLES`.`TABLE_TYPE` column of INFORMATION_SCHEMA.

That would make it possible for GUI-clients to query I-S and show a symbol for such temporary table. And to avoid the behaviour displayed below.

How to repeat:
Create table tbl1 (id integer);
Create temporary table tbl1 (id integer);

Create temporary table tbl2 (id integer);
Create table tbl2 (id integer);

/*
(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

Then also this behaviour could be avoided:
*/

insert into tbl1 values (7);
insert into tbl2 values (8);

/*
(1 row(s) affected)
(0 ms taken)

.. errhh .. where did they go ?
well actually they did both went into the non-TEMP_TABLES ..
*/

Suggested fix:
Use INFORMATION_SCHEMA to store information about temporary tables.  And avoid creating a temp-table and a non-temp-table named identically.

I post as a 'feature request' .. but I think it is rather somewhere in the 'grey area' between a bug and a feature request ..
[22 May 2006 15:01] Peter Laursen
edit: should have been:

insert into tbl1 values (7);
insert into tbl2 values (8);

/*
(1 row(s) affected)
(0 ms taken)

(1 row(s) affected)
(0 ms taken)
*/
[22 May 2006 15:10] Valeriy Kravchuk
Thank you for a feature request. It sounds reasonable. But I am not sure it will be implemented soon (or at all), because temporary tables are session-private, while INFORMATION_SCHEMA should be availabe for reading... Let's developers decide.
[22 May 2006 15:19] Peter Laursen
OK .. that is true of course.
So another implementation probably will have to be considered.

I think it is a little bit dangerous that you can create a temp-table named identically to an 'ordinary' table and insert data .. thinking that it goes into the temp table, but actually it goes to the permanent table.

Another (maybe?) acceptable solution could be that temp-tables take precedense over permanent tables.
[23 May 2006 17:21] Peter Laursen
This:

create temporary table t1 (id integer);
create table t1 (id integer);
insert into t1 values (7);
drop table t1;
show tables;
/*
Tables_in_test
---------------
t1 
-- shows that temporary table is dropped first
*/
select * from t1;
/* 
empty set 
-- shows that data were inserted into the 'ordinary' table
*/
insert into t1 values (19);
/* (1 row(s) affected) -- there is still a table to insert into ... */
drop table t1;
insert into t1 values (33);
/*
Error Code : 1146
Table 'test.t1' doesn't exist
-- now got rid of the temporary one too!
*/

.. shows that with DROP TABLE and INSERT precedence is different.
but after all a good thing that it is not the 'ordinary table that is dropped first!

I don't think it should be possible to create two identically named tables - whether temporary or not!  

And I still request a way to query the existence of temporary tables!
[15 Mar 2007 1:03] Stephen Gornick
I am confused ... doesn't the reference manual describe how to determine if the table is temporary?  

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

http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
[4 Oct 2008 20:54] Konstantin Osipov
Bug#33274 No way to report temporary tables was marked a duplicate of this bug.
[6 Aug 2010 20:47] Morgan Tocker
This is now in percona-server:

mysql> CREATE temporary table test.a (a int);
mysql> select * from information_schema.TEMPORARY_TABLES\G
*************************** 1. row ***************************
    SESSION_ID: 5
  TABLE_SCHEMA: test
    TABLE_NAME: a
        ENGINE: MyISAM
          NAME: #sql10d8_5_0
    TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
   DATA_LENGTH: 0
  INDEX_LENGTH: 1024
   CREATE_TIME: 2010-08-06 16:28:03
   UPDATE_TIME: 2010-08-06 16:28:03
[14 Nov 2013 21:27] Sheeri Cabral
This can be done, and has been patched into Percona. Putting this in should be easy and possible and a huge win. 

http://venublog.com/2010/02/03/show-temporary-tables/
[24 Mar 2015 11:11] Shane Bester
http://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html