MySQL Bugs: #20001: Support for temp-tables in INFORMATION

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

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 ..

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)
*/

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.

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.

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!

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

Bug#33274 No way to report temporary tables was marked a duplicate of this bug.

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

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/

http://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html