Bug #21893 Hierarchical Query Table/Temporary tbl cant be accessed frm a function Win-XP
Submitted: 29 Aug 2006 7:25 Modified: 31 Aug 2006 6:00
Reporter: Rajarshi Biswas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.22 OS:Microsoft Windows (Windows XP)
Assigned to:

[29 Aug 2006 7:25] Rajarshi Biswas
Description:
While fetching data in  Hierarchical way from a Table ( which consists of recursive calls -> please refer STEP 5 -> Procedure 3 ) , 
Table/Temporary table cannot be accessed from a function via a procedure , its is not possible to perform 
operations on that table for operations line  INSERT/DROP etc - it give an error saying the the table doesnt 
exist .Whereas, when the procedure is called alone, the desired result is obtained. 
Importantly , mu application is in Windows XP .

How to repeat:
Step-1. Table Definition 

CREATE TABLE recurse2( 
category_id VARCHAR(20) , 
name VARCHAR(20) NOT NULL, 
parent VARCHAR(20)); 

Step-2. Insert-Data 

INSERT INTO recurse2 
VALUES('p001','ELECTRONICS',NULL),('p0010','2 WAY RADIOS','p006'),('p008','FLASH','p007'), 
('p004','LCD','p002'),('p006','PORTABLE ELECTRONICS','p001'),('p005','PLASMA','p002'), 
('p007','MP3 PLAYERS','p006'),('p003','TUBE','p002'), 
('p002','TELEVISIONS','p001'),('p009','CD PLAYERS','p006'); 

Step - 3) Procedure 1 - setting the recursion depth 

CREATE PROCEDURE sDepth() 
BEGIN 
SET @sql ="SET max_sp_recursion_depth=255"; 
PREPARE stmt FROM @sql; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
END; 
// 

Step - 4) Procedure 2 - Parent Procedure 

CREATE PROCEDURE Prc_001 (start_with VARCHAR(32)) 

BEGIN 
DECLARE temporary_table_exists BOOLEAN; 
BEGIN 
DROP TABLE IF EXISTS Temporary_Table; 
END; 
BEGIN 
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32); 
DECLARE v_level INT; 

CREATE TEMPORARY TABLE Temporary_Table 
(catagory_id VARCHAR(32), name VARCHAR(32), parent VARCHAR(32)); 

SET temporary_table_exists=TRUE; 
SELECT category_id, name 
INTO v_catagory_id, v_name FROM recurse2 
WHERE category_id = start_with limit 1; 

IF v_catagory_id IS NOT NULL THEN 

SET v_level := 1; 

INSERT INTO Temporary_Table VALUES 
(v_catagory_id, v_name, v_parent); 

CALL sDepth(); 

CALL Prc_002(v_catagory_id); 
END IF; 

-- This line is given to view the resultant Temporary Table 

SELECT catagory_id,name,parent FROM Temporary_Table; 

IF temporary_table_exists THEN 
DROP TEMPORARY TABLE Temporary_Table; 
END IF; 
END; 
END; 
// 

Step - 5) Procedure 3 - procedure with a recursive call 

CREATE PROCEDURE Prc_002 (start_with VARCHAR(32)) 
proc: 
BEGIN 
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32); 
DECLARE v_level INT; 

DECLARE done, error BOOLEAN DEFAULT FALSE; 

DECLARE cs CURSOR FOR SELECT category_id, name, parent 
FROM Recurse2 WHERE parent = start_with; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET error = TRUE; 

OPEN cs; 
IF error THEN 
SELECT 'OPEN failed'; LEAVE proc; END IF; 

REPEAT 

SET v_catagory_id=NULL; 

FETCH cs INTO v_catagory_id, v_name, v_parent; 

IF done=FALSE THEN 

IF v_catagory_id IS NOT NULL THEN 

INSERT INTO Temporary_Table VALUES 
(v_catagory_id, v_name, v_parent); 
CALL Prc_002(v_catagory_id); 
END IF; 
END IF; 
UNTIL done = TRUE 
END REPEAT; 
CLOSE cs; 
END; 
// 

Step-6) Running the procedure in MySQL - WORKS FINE and PRODUCES THE DESIRED RESULT 

call Prc_001('p001'); // 

Step- 7) Make a function - Calling the procedure from a function - CAUSE ERROR while running the same 

CREATE FUNCTION fnc_001 () 
returns VARCHAR(32) 
BEGIN 
DECLARE ret VARCHAR(32); 
call Prc_001('p001'); 
return ret; 
END; 
// 

Step-8 )

Select fnc_001 () ; //

ERROR 1146 (42S02): Table 'test.temporary_table' doesn't exist
[29 Aug 2006 16:15] Tonci Grgin
Hello. I will need some more info from you before I can start.
 - What is the MyODBC version you're using, I presume server is 5.0.22?
 - Can you post small but complete C/C++ project demonstrating this behavior?
[30 Aug 2006 0:24] Rajarshi Biswas
I am simply migrating  from Oracle to MySql - and i need a replacement for hierarchical query in MySQL and thats why all this things has been done .
The matter posted is a complete implementation of  hierarchical query in MySQL , its include a dummy table and data also .
It runs when the procedure namely p_001 is called alone, but fails when called from a function.
[31 Aug 2006 1:20] Rajarshi Biswas
Hi  do you have any inputs on the question posted or have your question been answered precisely , or can you able to repeat it .
Please revert ASAP
[31 Aug 2006 6:00] Tonci Grgin
Hi Rajarshi. I agree with your test case but, according to manual (http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html), this is not a bug.
Thanks for your interest in MySQL.