bk commit into 5.0 tree (pem:1.1893) BUG#9074

[pem]

Below is the list of changes that have just been committed into a local
5.0 repository of pem. When pem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1893 05/04/08 17:31:37 pem@stripped +3 -0
  Fixed BUG#6900: Stored procedure inner handler ignored
    and BUG#9074: STORED PROC: The scope of every handler declared is not properly applied

  sql/sp_rcontext.cc
    1.20 05/04/08 17:31:32 pem@stripped +4 -4
    Corrected logic for finding condition handlers - pick the innermost one.

  mysql-test/t/sp.test
    1.102 05/04/08 17:31:32 pem@stripped +83 -0
    New test cases for BUG#6900 and BUG#9074 (duplicates).

  mysql-test/r/sp.result
    1.107 05/04/08 17:31:31 pem@stripped +65 -0
    New test cases for BUG#6900 and BUG#9074 (duplicates).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	pem
# Host:	mysql.comhem.se
# Root:	/home/pem/work/mysql-5.0

--- 1.106/mysql-test/r/sp.result	Thu Apr  7 09:57:43 2005
+++ 1.107/mysql-test/r/sp.result	Fri Apr  8 17:31:31 2005
@@ -2779,4 +2779,69 @@
 3.2000
 drop procedure bug8937|
 delete from t1|
+drop procedure if exists bug6900|
+drop procedure if exists bug9074|
+drop procedure if exists bug6900_9074|
+drop table if exists t3|
+create table t3 (w char unique, x char)|
+insert into t3 values ('a', 'b')|
+create procedure bug6900()
+begin
+declare exit handler for sqlexception select '1';
+begin
+declare exit handler for sqlexception select '2';
+insert into t3 values ('x', 'y', 'z');
+end;
+end|
+create procedure bug9074()
+begin
+declare x1, x2, x3, x4, x5, x6 int default 0;
+begin    
+declare continue handler for sqlstate '23000' set x5 = 1;      
+insert into t3 values ('a', 'b');      
+set x6 = 1;      
+end;
+begin1_label:
+begin
+declare continue handler for sqlstate '23000' set x1 = 1;      
+insert into t3 values ('a', 'b');      
+set x2 = 1;      
+begin2_label:
+begin  
+declare exit handler for sqlstate '23000' set x3 = 1;         
+set x4= 1;         
+insert into t3 values ('a','b');
+set x4= 0;
+end begin2_label;
+end begin1_label;
+select x1, x2, x3, x4, x5, x6;
+end|
+create procedure bug6900_9074(z int)
+begin
+declare exit handler for sqlstate '23000' select '23000';
+begin
+declare exit handler for sqlexception select 'sqlexception';
+if z = 1 then
+insert into t3 values ('a', 'b');
+else
+insert into t3 values ('x', 'y', 'z');
+end if;
+end;
+end|
+call bug6900()|
+2
+2
+call bug9074()|
+x1	x2	x3	x4	x5	x6
+1	1	1	1	1	1
+call bug6900_9074(0)|
+sqlexception
+sqlexception
+call bug6900_9074(1)|
+23000
+23000
+drop procedure bug6900|
+drop procedure bug9074|
+drop procedure bug6900_9074|
+drop table t3|
 drop table t1,t2;

--- 1.101/mysql-test/t/sp.test	Thu Apr  7 09:57:43 2005
+++ 1.102/mysql-test/t/sp.test	Fri Apr  8 17:31:32 2005
@@ -3399,6 +3399,89 @@
 
 
 #
+# BUG#6900: Stored procedure inner handler ignored
+# BUG#9074: STORED PROC: The scope of every handler declared is not
+#                        properly applied
+#
+--disable_warnings
+drop procedure if exists bug6900|
+drop procedure if exists bug9074|
+drop procedure if exists bug6900_9074|
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (w char unique, x char)|
+insert into t3 values ('a', 'b')|
+
+create procedure bug6900()
+begin
+  declare exit handler for sqlexception select '1';
+
+  begin
+    declare exit handler for sqlexception select '2';
+
+    insert into t3 values ('x', 'y', 'z');
+  end;
+end|
+
+create procedure bug9074()
+begin
+  declare x1, x2, x3, x4, x5, x6 int default 0;
+
+  begin    
+    declare continue handler for sqlstate '23000' set x5 = 1;      
+
+    insert into t3 values ('a', 'b');      
+    set x6 = 1;      
+  end;
+
+ begin1_label:
+  begin
+    declare continue handler for sqlstate '23000' set x1 = 1;      
+
+    insert into t3 values ('a', 'b');      
+    set x2 = 1;      
+				
+   begin2_label:
+    begin  
+      declare exit handler for sqlstate '23000' set x3 = 1;         
+
+      set x4= 1;         
+      insert into t3 values ('a','b');
+      set x4= 0;
+    end begin2_label;
+  end begin1_label;
+
+  select x1, x2, x3, x4, x5, x6;
+end|
+
+create procedure bug6900_9074(z int)
+begin
+  declare exit handler for sqlstate '23000' select '23000';
+
+  begin
+    declare exit handler for sqlexception select 'sqlexception';
+
+    if z = 1 then
+      insert into t3 values ('a', 'b');
+    else
+      insert into t3 values ('x', 'y', 'z');
+    end if;
+  end;
+end|
+
+call bug6900()|
+call bug9074()|
+call bug6900_9074(0)|
+call bug6900_9074(1)|
+
+drop procedure bug6900|
+drop procedure bug9074|
+drop procedure bug6900_9074|
+drop table t3|
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.19/sql/sp_rcontext.cc	Fri Apr  8 16:05:11 2005
+++ 1.20/sql/sp_rcontext.cc	Fri Apr  8 17:31:32 2005
@@ -80,24 +80,24 @@
       break;
     case sp_cond_type_t::state:
       if (strcmp(sqlstate, cond->sqlstate) == 0 &&
-	  (found < 0 || m_handler[found].cond->type > sp_cond_type_t::number))
+	  (found < 0 || m_handler[found].cond->type > sp_cond_type_t::state))
 	found= i;
       break;
     case sp_cond_type_t::warning:
       if ((sqlstate[0] == '0' && sqlstate[1] == '1' ||
 	   level == MYSQL_ERROR::WARN_LEVEL_WARN) &&
-	  (found < 0 || m_handler[found].cond->type > sp_cond_type_t::state))
+	  found < 0)
 	found= i;
       break;
     case sp_cond_type_t::notfound:
       if (sqlstate[0] == '0' && sqlstate[1] == '2' &&
-	  (found < 0 || m_handler[found].cond->type > sp_cond_type_t::state))
+	  found < 0)
 	found= i;
       break;
     case sp_cond_type_t::exception:
       if ((sqlstate[0] != '0' || sqlstate[1] > '2') &&
 	  level == MYSQL_ERROR::WARN_LEVEL_ERROR &&
-	  (found < 0 || m_handler[found].cond->type > sp_cond_type_t::state))
+	  found < 0)
 	found= i;
       break;
     }