MySQL Temporary Table reference limitation

A TEMPORARY table is a table that is bound to a connection and is dropped when that connection is closed. It seems like this would just be a normal table with some meta data attached to it indicating the connection it is bound to. I suppose for some reason it is more complicated than that… seems unnecessarily so…

Anyways, the limitation is that you cannot refer to a given temporary table more than once in a single query.  If you do you will get a “Can’t reopen temporary table” error. So, for instance you cannot insert into a temporary table some data that you select from it in in the same query. This limitation has bit me several times.

The bug is here: http://bugs.mysql.com/bug.php?id=10327

Continue reading