Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20096

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

    XMLWordPrintable

    Details

      Description

      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)

        Attachments

        1. CreateATable.sql
          0.8 kB
        2. GetATable.sql
          0.2 kB
        3. GetATable2.sql
          0.2 kB
        4. Test.sql
          0.5 kB

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            kevin.harrison Kevin Harrison
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: