MySQL Bugs: #39850: temp table problems

Bug #39850	temp table problems
Submitted:	4 Oct 2008 8:14	Modified:	8 Oct 2008 20:11
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
This is related to another reported posted by me: 
http://bugs.mysql.com/bug.php?id=20001

as well as a dicsussion in our (webyog) Forums here:
http://www.webyog.com/forums//index.php?showtopic=4262

I find it very problematic that 
1) an ordinary ('global') table and a temporay ('session') table can have the same name. 
2) that there seems to be no way to SHOW temporary tables

But if is so it should be documented that when they both do exist, the server will execute statements against the temporary table.

How to repeat:
create table tttt(id int);
create temporary table tttt(id int); -- note you can have a normal table and a temporary table named the same!
insert into tttt (id) values (1); -- it inserted but where? no info avail!
select * from tttt; -- there is a table of some kind with data
drop table tttt; -- which one was dropped? no info avail!
select * from tttt; -- empty set - the remaining table has no data!
show tables; -- one row returned so it is the 'normal' `tttt´table that exists
drop table tttt; -- success
show tables; -- empty set

Suggested fix:
First of all I would like a statement (here and in docs) that *it can be relied on* that *always* when identically named ordinary ('global') and temporary ('session') tables, statements referencing that tablename will use the temporary table.

But I also think that duplicate table names is a mess that should be addressed!

As for the documentation, it is mentioned in the "create table"  page at
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

<quote>
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY  table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. 
</quote>

Especially the part:
" without conflicting with each other or with an existing non-TEMPORARY  table of the same name. (The existing table is hidden until the temporary table is dropped.)"

Hope it helps.

It may be that the docs could be rephrased, as I can not see, how the "2 connection" bit relates to the "temporary vs non-temp" bit?

ok that passage I overlooked .. but

1) It is OK that different connections can create same temp table - obviously. But I think a 'global' table and a tmep table should not create if a table wiht the same name si allready avail to the connection. Also I do not understand that the current implementation is described as 'non-conflicting'. I think the opposite - It is definitely 'conflicting' as I cannot access the 'global' table!

2) How do I know that a temporary table exists duplicating a 'global' table? Of course I can execute SHOW TABLES or similar but it is ineffective in terms of application programming to perform tests that should be unnessary because they should have been implemented in server 2) How can I test the existence of a temp table before dropping it? 3) How do I know what temporary tables exist for my connection? All this is not possible. So I need a notebook and a pencil to record all such things if I want to use MySQL (I can have a connection open for weeks and may have created hundreds of temporary tables and forgot which ones were dropped!)?

I read the docs as an attempt to justify an architectural flaw only! I'd prefer docs did not try to decorate the current situation using abstract and positive terms like 'no conflict' (how nice, when described like this - we do not like 'conflicts', right!) but *in plain words* told the limitatations/risks for user/client because of this! Such as the risk to drop a 'global' table if you by accident drop the same tablename twice by human mistake or bug in application code!

Thank you for the reasonable feature request.

Possible solution: add a statement which allow to examine if one created temporary table.