MySQL – Temporary Table & Common Table Expression ( CTE ) – Error – “Can’t reopen table” ( Error 1137 )

Background

There is a small error that one can run into when using Temporary Tables and Common Table Expression ( CTE ).

Error

Error – Can’t reopen table

Error Image

err.sql.error.1137.01.20200813.0547PM

Error Text


SQL Error [1137] [HY000]: Can't reopen table: 'tblTempCustomerTop'

Can't reopen table: 'tblTempCustomerTop'

Scenario

Common Table Expression

SQL


with cteTop
as
(
    select *

    from   sakila.customer c 

    order by
        c.customer_id asc

    limit 5

)
, cteBottom
as
(
    select *

    from   sakila.customer c 

    order by
        c.customer_id desc

    limit 5

)

select *

from   cteTop cteT

union

select * 

from   cteBottom cteB

;

Result

output.select.fetch.02.20200814.1247PM

 

Temporary Table & Common Table Expression

Query:- Successful

SQL


use sakila;

start transaction;

    /*
     * Drop Temporary Table tblTempCustomer
    */
    drop temporary table if exists tblTempCustomerTop;

    drop temporary table  if exists tblTempCustomerBottom;

    /*
     * Create Temporary Table tblTempCustomerTop
     * Fetch top 5 records based on customer_id
     * sequenced based on ascending
    */

    create temporary table tblTempCustomerTop

    as

    select *

    from   sakila.customer tblSC

    order by
            customer_id asc

    limit 5
    ;

    /*
     * Create Temporary Table tblTempCustomerTop
     * Fetch top 5 record based on customer_id
     * sequenced based on descending
    */

    create temporary table tblTempCustomerBottom

    as

    select *

    from   sakila.customer tblSC

    order by
            customer_id desc

    limit 5

    ;


    /*
     * Access common table expression
    */

    with cteTop
    as
    (
        select *

        from   tblTempCustomerTop

    )
    , cteBottom
    as
    (
        select *

        from   tblTempCustomerBottom

    )

    select *

    from   cteTop cteT

    union

    select * 

    from   cteBottom cteB

    ;

    /*
     * Drop Temporary Table tblTempCustomer
    */
    drop temporary table if exists tblTempCustomerTop;

    drop temporary table if exists tblTempCustomerBottom;

rollback;

Result

output.select.fetch.01.20200813.0600PM

Query – Failed

SQL


use sakila;

start transaction;

    /*
     * Drop Temporary Table tblTempCustomer
    */
    drop temporary table if exists tblTempCustomerTop;

    drop temporary table  if exists tblTempCustomerBottom;

    create temporary table tblTempCustomerTop

    as

    select *

    from   sakila.customer tblSC

    order by
            customer_id asc

    limit 5
    ;

    create temporary table tblTempCustomerBottom

    as

    select *

    from   sakila.customer tblSC

    order by
            customer_id desc

    limit 5

    ;

    with cteTop
    as
    (
        select *

        from   tblTempCustomerTop

    )
    , cteBottom
    as
    (
        select *

        from   tblTempCustomerBottom

    )

    select *

    from   cteTop cteT

    union

    select * 

    -- from   cteBottom cteB
    from   cteTop cteB

    ;

    /*
     * Drop Temporary Table tblTempCustomer
    */
    drop temporary table if exists tblTempCustomerTop;

    drop temporary table if exists tblTempCustomerBottom;

rollback;

Output

Output – Image

err.sql.error.1137.02.20200813.0605PM

 

MySQL Version  Tested

Here are the Versions of MySQL Systems we tested against:-

  1. 8.0.21

Source Code Control

GitHub

Gist

DanielAdeniji / sakila.customer.cte.twiceReferenced.sql
Link

Summary

It is not such an easy ditch to get caught in.

But, here is a quick read:-

  1. In a Query
    • Create Common Table Expression ( CTE )
    • Have the CTE reference a temporary table
    • The reference to the CTE should occur more than once

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s