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)
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)