Stored Procedure returns “Error Code: 1054. Unknown column 'schema.table.col' in 'field list'” when Creating different temporary table on same session

When using a stored procedure to dynamically generate a table based on configuration and return a result set (SELECT) with the records in that table, the second call to that procedure to generate a different table structure returns no records and it reports a missing column from a previous temporary table of the same name.

I tried this with MariaDB 10.3 and 10.1.21 and received the same result. I have minimized my code here to the minimum to demonstrate the error after trying several variations of single and multiple sub-procedures.

I also tried using some transaction control with COMMITS after executing the process, before trying to start the process with a different parameter, but got the same results.

I would expect that I can pass a parameter to a stored procedure to generate a temporary table, and return the records of that temporary table. Even if I call that same procedure multiple times with different parameters on the same session.

The actual results are that when the stored procedure is called to generate the temporary table with a different table structure, it returns this error complaining about the column missing from the temporary table created in the previous invocation of that same stored procedure.

Error Code: 1054. Unknown column 'mySchema.aTable.the_column' in 'field list'

The only way I have found to prevent this error is a. ending the jdbc connection and ending the server session b. recompiling one of the stored procedures in the call stack

Recompiling is not viable. And ending the session seems unreasonable. This seems like a defect. But would be interested to find if there is some way to get this to work.

DROP PROCEDURE IF EXISTS CreateATable;

DELIMITER $$ 

CREATE PROCEDURE CreateATable( _TableType tinyint )

BEGIN  

    DROP TEMPORARY TABLE IF EXISTS aTable;

    IF _TableType = 1 THEN

        SET @SQL_Statement := 

        CONCAT(

        'CREATE TEMPORARY TABLE aTable (', 

        'the_id bigint, ',

        'the_column varchar(100) ',

        ') engine=INNODB',

        ';');

    ELSE

        SET @SQL_Statement := 

        CONCAT(

        'CREATE TEMPORARY TABLE aTable (', 

        'the_id bigint, ',

        'the_other_column varchar(100) ',

        ') engine=INNODB',

        ';');

    END IF;

    PREPARE stmtCreateTable FROM @SQL_Statement;

    EXECUTE stmtCreateTable;    

    DEALLOCATE PREPARE stmtCreateTable; 

    SET @SQL_Statement := NULL;

END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS GetATable;

DELIMITER $$ 

CREATE PROCEDURE GetATable()

BEGIN

    CALL CreateATable( 1 );

    SELECT * FROM aTable;

    CALL CreateATable( 2 );

    SELECT * FROM aTable;

END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS GetATable2;

DELIMITER $$ 

    CREATE PROCEDURE GetATable2(_TableType tinyint)

    BEGIN

        CALL CreateATable( _TableType );

        SELECT * FROM aTable;

    END$$

    DELIMITER ;

/*

Test execution script starts here

*/

-- Just CALL Create for one and Select

CALL CreateATable( 1 );

DESCRIBE aTable;

SELECT * FROM aTable;

CALL CreateATable( 2 );

DESCRIBE aTable;

SELECT * FROM aTable;

-- -> no errors

-- now CALL procedure to Create and Select from two different temp tables

CALL GetATable();

-- -> no errors

-- now CALL procedure to CREATE AND SELECT from ONE temp table definition using a parameter to select

CALL GetATable2(1);

CALL GetATable2(2);

-- Error Code: 1054. Unknown column 'mySchema.aTable.the_column' in 'field list'

user from stackoverflow.com
https://stackoverflow.com/questions/57030705/stored-procedure-returns-error-code-1054-unknown-column-schema-table-col-in

suggested a work-around is to use EXECUTE IMMEDIATE in GetATable2 (untested by me, yet)