MySQL Bugs: #9563: Temporary tables in Stored Procedures are not visible

Bug #9563	Temporary tables in Stored Procedures are not visible
Submitted:	1 Apr 2005 16:12	Modified:	3 Aug 2005 6:57
Reporter:	Jan Kneschke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux/x86)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
a temporary table created inside of a store procedure is not visible to the stored procedure itself.

How to repeat:
DELIMITER $$
DROP PROCEDURE IF EXISTS test1$$
CREATE PROCEDURE test1()
BEGIN
  DROP TABLE IF EXISTS __test1;
  CREATE TEMPORARY TABLE __test1 (
    a INT
  );
  
  SELECT * FROM __test1;
END$$
CALL test1()$$
## ERROR 1146 (42S02): Table 'pasta.__test1' doesn't exist
DELIMITER ;

the ## was added to make it easier for cut'n'paste

Suggested fix:
Make the temp-table visible.

If you will use DROP TEMPORARY TABLE instead of ordinary DROP TABLE everything
will work...

Also probably it won't harm if we will allow to use ordinary DROP TABLE in stored procedures since it does not requires usual table lock... But this needs additional consideration.

Sorry for confusion. I was trying to say that we probably can allow to use of ordinary DROP TABLE in SP without trying to open and lock it before SP execution...

Fixed by fix for BUG#11126