Bug #9074 STORED PROC: The scope of every handler declared is not properly applied
Submitted: 9 Mar 2005 15:34 Modified: 7 Apr 2005 8:49
Reporter: Disha Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.3-alpha-nt OS:Windows (Windows Server 2003)
Assigned to: Bugs System CPU Architecture:Any

[9 Mar 2005 15:34] Disha
Description:
The scope of every handler declared is not properly applied.

How to repeat:
Repro Steps : 	
	1. 	Connect to the MySQL server as 'root'
	2.	drop procedure if exists h1//
		drop table if exists t1//
	3.	Create table t1(w char UNIQUE, x char)//
		INSERT INTO t1 VALUES ('a', 'b')//      
		CREATE PROCEDURE h1 () 
		BEGIN
			DECLARE x1, x2, x3, x4, x5, x6 INT DEFAULT 0;

			BEGIN    
				DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET x5 = 1;      
				INSERT INTO t1 VALUES ('a', 'b');      
				set x6 = 1;      
			END;

			begin1_label: begin
			  
				DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET x1 = 1;      
				INSERT INTO t1 VALUES ('a', 'b');      
				set x2 = 1;      
				
				begin2_label: begin  
				
					DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET x3 = 1;         
					set x4= 1;         
					INSERT INTO t1 VALUES ('a','b');
					set x4= 0;
				
				end begin2_label;

			end begin1_label;

			select x1, x2, x3, x4, x5, x6;
		END;//

	4.	call h1()//

		
			
Expected Results :  All the variables should be set to 1. That is, calling h1 gives the following result.
		+----+----+----+----+----+----+
		| x1  | x2  | x3  | x4  | x5  | x6 |
		+----+----+----+----+----+----+
		|  1   |  1  |  1  |  1   |  1  |  1  |
		+----+----+----+----+----+----+
		1 row in set (0.00 sec)

		Query OK, 0 rows affected (0.00 sec)
		
		

Actual Results :    The nested handler is not considered. Following is the result of the call h1() statement:

		+----+----+----+----+----+----+
		| x1  | x2  | x3  | x4  | x5  | x6  |
		+----+----+----+----+----+----+
		|  1   |  1  |  0  |  0   |  1  |  1  |
		+----+----+----+----+----+----+
		1 row in set (0.00 sec)

		Query OK, 0 rows affected (0.00 sec)
[7 Apr 2005 8:49] Per-Erik Martin
This is a duplicate of BUG#6900.
[8 Apr 2005 15:51] Bugs System
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/23809