Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
MariaDB 10.3 and 10.1.21, using MySQL Workbench 6.3.10 and HeidiSQL 9.4.0
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
Activity
That is odd. I am pretty sure when I created the JIRA item I identified the component or some other thing as MariaDB Server.
Also, I did verify that EXECUTE IMMEDIATE is a valid work around. And changed priority from Major to Minor.
Thanks for the report! Reproduced as described on 5.5-10.4 with MyIsam/Innodb, both with
Execute/ Execute IMMEDIATE
delimiter $$;
|
|
create procedure p1( tbl int) |
begin |
drop temporary table if exists tmp1; |
if tbl = 1 then |
set @stmt:= concat('create temporary table tmp1 (', 'id int, ', 'm1 varchar(100) ', ');' ); |
else |
set @stmt:= concat('create temporary table tmp1 (', 'id int, ', 'm2 varchar(100) ', ');'); |
end if; |
execute immediate @stmt; |
set @stmt := null; |
end$$ |
|
create procedure p2(tbl int) |
begin |
call p1(tbl);
|
select * from tmp1; |
end$$ |
delimiter ;$$
|
|
call p2(1);
|
call p2(2);
|
query 'call p2(2)' failed: 1054: Unknown column 'test.tmp1.m1' in 'field list'
|
works as expected on current 10.5 (b65504b8db0e1e16757b1d1a1b288760f4f7af3d)-11.7
Moved to MariaDB Server. This doesn't look like it is a ColumnStore issue.