5

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.

1

1 Answer 1

7

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

1
  • Ok thanks, I had read about the aliases thing somewhere else but hadn't seen this page. That's quite a nasty limitation but ah well...
    – Sean Airey
    Jun 26, 2013 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.