Bug #11190 Create temp table inside Stored Proc. cannot reference

• Submitted: 9 Jun 2005 4:11
• Modified: 9 Jun 2005 14:30
• Reporter: Ka Pong Chen
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: beta 5.0.6
• OS: Windows (Windows 2003)
• CPU Architecture: Any

[9 Jun 2005 4:11] Ka Pong Chen

Description:
I create a stored proc, 
Create temp table inside,
but fail to use temp table after create it
within  same stored proc.

How to repeat:
#Please create a table called "table_a"
# then create procedure below
# then call procedure will fail with missing temp_a
# while running "create temporary table temp_b"

DELIMITER \\

DROP PROCEDURE IF EXISTS `db`.`spinitial`\\
CREATE PROCEDURE `db`.`spinitial` ()
BEGIN
drop temporary table if exists temp_a;
drop temporary table if exists temp_b;

create temporary table temp_a
select * from table_a;

create temporary table temp_b
SELECT * FROM  temp_a ;
END\\

DELIMITER ;

Suggested fix:
Allow temporary in one connection.

[9 Jun 2005 11:40] MySQL Verification Team

Related to the same issue as:

http://bugs.mysql.com/bug.php?id=11126

[9 Jun 2005 14:30] Dmitry Lenev

Hi!

Actually I doubt that this is duplicate of bug #11126 which is related to
handling of temporary tables in nested (and thus several) stored routines.

Unfortunately I am unable to repeat problem described.
The following works perfectly for me:

mysql> delimiter $$
mysql> drop table if exists table_a, temp_a, temp_b $$
Query OK, 0 rows affected (0.00 sec)

mysql> create table table_a (id int)$$
Query OK, 0 rows affected (0.00 sec)

mysql> insert into table_a values (1), (2)$$
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop procedure if exists spinitial$$
Query OK, 0 rows affected (0.01 sec)

mysql> create procedure spinitial() begin drop temporary table if exists temp_a; drop temporary table if exists temp_b; create temporary table temp_a select * from table_a; create temporary table temp_b select * from temp_a ; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> call spinitial()$$
Query OK, 0 rows affected (0.01 sec)

mysql> select * from temp_b$$
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> call spinitial()$$
Query OK, 0 rows affected (0.00 sec)

mysql> select * from temp_b$$
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select version()$$    
+------------------+
| version()        |
+------------------+
| 5.0.6-beta-debug |
+------------------+

So I am marking this bug report as "Can't repeat".
Feel free to reopen it if you will be able to provide full repeatable test case for it !