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: Assigned Account 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.