MySQL Bugs: #21859: Table INSERT /DROP from a function via a procedure

Bug #21859	Table INSERT /DROP from a function via a procedure
Submitted:	28 Aug 2006 6:05	Modified:	31 Aug 2006 1:15
Reporter:	Rajarshi Biswas	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.22	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When  Table/Temporary table accessed from a function via a procedure , its is not possible to perform operations on that table for example INSERT'ing records /DROP'ing that table - is not possible it give an error saying that the table doesnt exist .Whereas, when the procedure is called alone, the desired result is obtained(with a warning). 

How to repeat:
CREATE PROCEDURE Prc_1 () BEGIN
	 DROP TEMPORARY TABLE IF EXISTS tt; 
	CREATE TEMPORARY  TABLE tt (id int); 
	 DROP  TEMPORARY  TABLE IF EXISTS tt; 
	
END//

CREATE FUNCTION Fnc_1 () returns VARCHAR(32) BEGIN 
	call Prc_1 (); 
	return null; 
END//

when called
call Prc_1();// # works and produces the desired result with an warning.
mysql> show warnings; //
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 'tt' |
+-------+------+--------------------+
1 row in set (0.00 sec)

select Fnc_1();// #doesn't works - error occurs saying Table tt doesnt exist.

Suggested fix:
Please suggest

Thank you for the report.

I can not repeat it on Linux using last 5.0-BK sources.

This Bug is found in WINDOWS XP . So, please try in Windows .

I have not last Windows BK built, but sources built about 2 months ago get correct results:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.23-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter |
mysql> delimiter //
mysql> CREATE PROCEDURE Prc_1 () BEGIN
    ->   DROP TEMPORARY TABLE IF EXISTS tt;
    ->  CREATE TEMPORARY  TABLE tt (id int);
    ->   DROP  TEMPORARY  TABLE IF EXISTS tt;
    ->
    -> END//
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql>
mysql>
mysql> CREATE FUNCTION Fnc_1 () returns VARCHAR(32) BEGIN
    ->  call Prc_1 ();
    ->  return null;
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> call Prc_1();
    -> //
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> delimiter ;
mysql> show warnings;
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 'tt' |
+-------+------+--------------------+
1 row in set (0.00 sec)

mysql> select Fnc_1();
+---------+
| Fnc_1() |
+---------+
| NULL    |
+---------+
1 row in set, 1 warning (0.08 sec)

So, please, upgrade MySQL to current version (5.0.24) and if you will can repeat the issue, reopen bug.

As recommended by you , i have checked with Version 5.0.24 then query is succeeding. 
But my need is making hierarchical query in MySQL , ie i have 2 procedures , out of this the second procedure is a recursive procedures.
When i run the 1st procedure , it works fine and gives me the desired result , but when i call the first procedure from a function which in turn calls the second procedure the ERROR exists with version 5.0.24 also .
This is my observation .
I have reported the whole implemetation of this is in a seperated bug report .
please refer 
http://bugs.mysql.com/21893

Please do let me know if you have any inputs on this or not.

Thanks

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.