<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="temporary-table-problems">
      </a>
      B.3.6.2 TEMPORARY Table Problems
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053357808">
  </a>
  <p>
   Temporary tables created with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TEMPORARY
          TABLE
    </code>
   </a>
   have the following limitations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TEMPORARY
      </code>
      tables are supported only by
              the
      <code class="literal">
       InnoDB
      </code>
      ,
      <code class="literal">
       MEMORY
      </code>
      ,
      <code class="literal">
       MyISAM
      </code>
      , and
      <code class="literal">
       MERGE
      </code>
      storage engines.
     </p>
    </li>
    <li class="listitem">
     <p>
      Temporary tables are not supported for NDB Cluster.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
       <code class="literal">
        SHOW TABLES
       </code>
      </a>
      statement
              does not list
      <code class="literal">
       TEMPORARY
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      To rename
      <code class="literal">
       TEMPORARY
      </code>
      tables,
      <code class="literal">
       RENAME TABLE
      </code>
      does not work. Use
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      instead:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10208070"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> old_name <span class="token keyword">RENAME</span> new_name<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You cannot refer to a
      <code class="literal">
       TEMPORARY
      </code>
      table
              more than once in the same query. For example, the
              following does not work:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84574502"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> temp_table <span class="token keyword">JOIN</span> temp_table <span class="token keyword">AS</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
     <a class="indexterm" name="idm46045053339376">
     </a>
     <a class="indexterm" name="idm46045053337888">
     </a>
     <p>
      The statement produces this error:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa26280767"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 1137: Can't reopen table: 'temp_table'</code></pre>
     </div>
     <p>
      You can work around this issue if your query permits use
              of a common table expression (CTE) rather than a
      <code class="literal">
       TEMPORARY
      </code>
      table. For example, this
              fails with the
      <span class="errortext">
       Can't reopen table
      </span>
      error:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56639970"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TEMPORARY</span> <span class="token keyword">TABLE</span> t <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> col_a<span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">AS</span> col_b<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">AS</span> t1 <span class="token keyword">JOIN</span> t <span class="token keyword">AS</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      To avoid the error, use a
      <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
       <code class="literal">
        WITH
       </code>
      </a>
      clause that defines a
              CTE, rather than the
      <code class="literal">
       TEMPORARY
      </code>
      table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58507375"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">WITH</span> cte <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> col_a<span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">AS</span> col_b<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte <span class="token keyword">AS</span> t1 <span class="token keyword">JOIN</span> cte <span class="token keyword">AS</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The
      <span class="errortext">
       Can't reopen table
      </span>
      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. It may occur for temporary tables created
              outside stored functions and referred to across multiple
              calling and callee functions.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a
      <code class="literal">
       TEMPORARY
      </code>
      is created with the same
              name as an existing non-
      <code class="literal">
       TEMPORARY
      </code>
      table, the non-
      <code class="literal">
       TEMPORARY
      </code>
      table is
              hidden until the
      <code class="literal">
       TEMPORARY
      </code>
      table is
              dropped, even if the tables use different storage engines.
     </p>
    </li>
    <li class="listitem">
     <p>
      There are known issues in using temporary tables with
              replication. See
      <a class="xref" href="replication-features-temptables.html" title="19.5.1.31 Replication and Temporary Tables">
       Section 19.5.1.31, “Replication and Temporary Tables”
      </a>
      , for
              more information.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
