MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' I'm receiving an error ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' when executing a query similar to the following:

USE database_name;

CREATE TEMPORARY TABLE temp_table (status varchar(20));
INSERT INTO temp_table (status)
    SELECT status
    FROM client_contractor_status
    WHERE type = 'process';

SELECT table1.col1,
       table1.status,
       (SELECT
            COUNT(*)
        FROM
            table2
        RIGHT OUTER JOIN
            temp_table
            ON table2.status = temp_table.status
        WHERE table2.col1 = table1.col1
        ) AS counter
FROM
    table1

RIGHT OUTER JOIN
    temp_table
    ON table1.status = temp_table.status


I'm (just about) aware of the limitation that says you can't access a temporary table by two different aliases, however I haven't aliased it at all.

Is it being aliased automagically because it's in a sub-query? And if so, how can this be fixed?

Thanks.
This is a known and well documented issue:


  
  You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql&gt; SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

  
  This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.
  


One workaround that comes to mind would be to use a "normal" table as a temporary storage. You would probably need to use a session identifier to make the approach work in a multi-user environment.