Bug #11126 nested stored procedures and temporary tables don't mix
Submitted: 7 Jun 2005 6:58 Modified: 26 Aug 2005 19:42
Reporter: Richard Knol Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.04 OS:Linux (Linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[7 Jun 2005 6:58] Richard Knol
Description:
I would like to use a stored procedure that calls other stored procedures and use a temporary table to pass the results back and forth. But i discovered the some strange effects.

How to repeat:
If i have a stored procedure that creates a temporary table and then calls another stored procedure that uses the same temporary table, i get an errormessage saying the table does not exist:

drop procedure if exists SubTest;

create procedure SubTest ()
begin
  insert into my_sp_results values(2);
end;

drop procedure if exists Test;

create procedure Test()
begin
  drop temporary table if exists my_sp_results;
  create temporary table my_sp_results (
    tst int
  );
  insert into my_sp_results values(1);
  call SubTest();
end;

call Test();

result:
S0002-1146: Table 'asparagi.my_sp_results' doesn't exist
database: 192.168.210.31: asparagi: Richard

If i create the temporary table first, and then call the tow nested stored procedures that use this temporary table i get an error that the second stored procedure can't reopen the table:

drop temporary table if exists my_sp_results;

create temporary table my_sp_results (
  tst int
);

drop procedure if exists SubTest;

create procedure SubTest ()
begin
  insert into my_sp_results values(2);
end;

drop procedure if exists Test;

create procedure Test()
begin
  insert into my_sp_results values(1);
  call SubTest();
end;

call Test();

result:
S1T00-1137: Can't reopen table: 'my_sp_results'
database: 192.168.210.31: asparagi: Richard

But if i create a real table by the same name first, it works fine:
create table my_sp_results (
  tst int
);

drop procedure if exists SubTest;

create procedure SubTest ()
begin
  insert into my_sp_results values(2);
end;

drop procedure if exists Test;

create procedure Test()
begin
  drop temporary table if exists my_sp_results;
  create temporary table my_sp_results (
    tst int
  );
  insert into my_sp_results values(1);
  call SubTest();
end;

call Test();

select * from my_sp_results;
result:
tst
1
2
2 records fetched

It looks like the 2nd stored procedure (SubTest) checks for the existence of a real table named "my_sp_results" but uses the temporary one

If i drop the real table afterwards and look for the results in the temporary table i get the same results, so the values are inserted in the temporary table as i wanted. The real table just sits there.

call Test();

select * from my_sp_results;
result:
tst
1
2
2 records fetched

drop table my_sp_results;

select * from my_sp_results;
result:
tst
1
2
2 records fetched
[7 Jun 2005 13:31] Miguel Solorzano
Verified with BK source.
[30 Jul 2005 0:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27736
[30 Jul 2005 4:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27738
[3 Aug 2005 6:44] Sergey Petrunya
http://lists.mysql.com/internals/27831
[3 Aug 2005 6:45] Sergey Petrunya
Patch approved by Dmitri
[3 Aug 2005 6:52] Sergey Petrunya
Fix pushed into 5.0.11 tree.
I'll provide documentation about the fix (it will not be just a changelog entry, bug fix was 
more of functionality change).  Docs team, please consider this bug as waiting on me, even though it is in 'Documenting' state.
[3 Aug 2005 17:17] Sergey Petrunya
Pushing fix for this bug created a problem with replication, see BUG#12335
[17 Aug 2005 8:16] Konstantin Osipov
The remaining error in the provided test case is caused by Bug#12168.
The entire test case should pass once this bug is fixed.
[17 Aug 2005 8:23] Konstantin Osipov
Mike,
to close this bug it's documenting prelocking internals is not necessary.
The user-visible side of the fix is that it fixes CREATE and DROP table statements in stored procedures.
This should be mentioned in the ChangeLog.
Please contact Sergey Petrunia for details.
The prelocking algorithm is documented in the code and will be documented in internals.texi in due course.
[26 Aug 2005 8:25] Sergey Petrunya
Notes for the changelog:

DDL Statements are now allowed in PROCEDUREs if the procedure is not invoked
from FUNCTION/TRIGGER.
Also fixed numerous problems with temporary tables created in a routine R being 
inaccessible from the caller routine or another routine that routine R invokes 
(i.e. now one can freely 'pass' temporary tables to/from routines).
[26 Aug 2005 19:42] Paul Dubois
Noted in 5.0.11 changelog.