I’m quite often jumping between MySQL and SQL Server so remembering the quirks and limitations of each system can be difficult. With MySQL, if you attempt to reference a temporary table more than once in the same query, you will encounter the following error;

Error Code : 1137
Can't reopen table: 't1’

The following provides an example of this…

USE test;

CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);

SELECT *
FROM test AS t1
INNER JOIN test AS t2
     ON t1.Id = t2.Id;

It’s not just self-joins that have this issue UNIONS do as well;

SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test AS t2;

There’s a thread over on Stackoverflow discussing this problem. Here’s a solution I commonly use to get around the problem;

USE test;

# Create temp table
CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);

# Insert some test data
INSERT INTO test (Id) VALUES (1),(2),(3),(4),(5);

# Clone the table. This will do structure & indices but no data.
CREATE TEMPORARY TABLE test2 LIKE test;

# Insert the data into the new table
INSERT INTO test2
SELECT Id
FROM test;

# Now our queries will work if we use the tables clone
SELECT *
FROM test AS t1
INNER JOIN test2 AS t2
      ON t1.Id = t2.Id;

SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test2 AS t2;

# Clean up
DROP TEMPORARY TABLE IF EXISTS test;
DROP TEMPORARY TABLE IF EXISTS test2;