MySQL Bugs: #11126: nested stored procedures and temporary tables don't mix

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

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

Verified with BK source.

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

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

http://lists.mysql.com/internals/27831

Patch approved by Dmitri

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.

Pushing fix for this bug created a problem with replication, see BUG#12335

The remaining error in the provided test case is caused by Bug#12168.
The entire test case should pass once this bug is fixed.

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.

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).

Noted in 5.0.11 changelog.