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.2042 06/02/15 17:28:34 pem@stripped +2 -0
Additional tests for nested handlers added to sp.test.
A follow-up to BUG#15011 (already fixed).
mysql-test/t/sp.test
1.175 06/02/15 17:28:30 pem@stripped +392 -1
Additional tests for nested handlers.
mysql-test/r/sp.result
1.186 06/02/15 17:28:30 pem@stripped +333 -2
Updated results for new handler tests.
# 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: pem.mysql.com
# Root: /extern/mysql/5.0/bug15011/mysql-5.0-runtime
--- 1.185/mysql-test/r/sp.result 2006-02-06 14:09:07 +01:00
+++ 1.186/mysql-test/r/sp.result 2006-02-15 17:28:30 +01:00
@@ -1413,8 +1413,6 @@
5
drop function `foo`|
drop function if exists t1max|
-Warnings:
-Note 1305 FUNCTION t1max does not exist
create function t1max() returns int
begin
declare x int;
@@ -1470,6 +1468,339 @@
foo 1
drop table t3|
drop function getcount|
+drop table if exists t3|
+drop procedure if exists h_ee|
+drop procedure if exists h_es|
+drop procedure if exists h_en|
+drop procedure if exists h_ew|
+drop procedure if exists h_ex|
+drop procedure if exists h_se|
+drop procedure if exists h_ss|
+drop procedure if exists h_sn|
+drop procedure if exists h_sw|
+drop procedure if exists h_sx|
+drop procedure if exists h_ne|
+drop procedure if exists h_ns|
+drop procedure if exists h_nn|
+drop procedure if exists h_we|
+drop procedure if exists h_ws|
+drop procedure if exists h_ww|
+drop procedure if exists h_xe|
+drop procedure if exists h_xs|
+drop procedure if exists h_xx|
+create table t3 (a smallint primary key)|
+insert into t3 (a) values (1)|
+create procedure h_ee()
+deterministic
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Outer (bad)' as 'h_ee';
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Inner (good)' as 'h_ee';
+insert into t3 values (1);
+end;
+end|
+create procedure h_es()
+deterministic
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Outer (good)' as 'h_es';
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+ select 'Inner (bad)' as 'h_es';
+insert into t3 values (1);
+end;
+end|
+create procedure h_en()
+deterministic
+begin
+declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+select 'Outer (good)' as 'h_en';
+begin
+declare x int;
+declare continue handler for sqlstate '02000' -- no data
+select 'Inner (bad)' as 'h_en';
+select a into x from t3 where a = 42;
+end;
+end|
+create procedure h_ew()
+deterministic
+begin
+declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+select 'Outer (good)' as 'h_ew';
+begin
+declare continue handler for sqlwarning
+select 'Inner (bad)' as 'h_ew';
+insert into t3 values (123456789012);
+end;
+delete from t3;
+insert into t3 values (1);
+end|
+create procedure h_ex()
+deterministic
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Outer (good)' as 'h_ex';
+begin
+declare continue handler for sqlexception
+select 'Inner (bad)' as 'h_ex';
+insert into t3 values (1);
+end;
+end|
+create procedure h_se()
+deterministic
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+select 'Outer (bad)' as 'h_se';
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Inner (good)' as 'h_se';
+insert into t3 values (1);
+end;
+end|
+create procedure h_ss()
+deterministic
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+select 'Outer (bad)' as 'h_ss';
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+select 'Inner (good)' as 'h_ss';
+insert into t3 values (1);
+end;
+end|
+create procedure h_sn()
+deterministic
+begin
+-- Note: '02000' is more specific than NOT FOUND ;
+-- there might be other not found states
+declare continue handler for sqlstate '02000' -- no data
+select 'Outer (good)' as 'h_sn';
+begin
+declare x int;
+declare continue handler for not found
+select 'Inner (bad)' as 'h_sn';
+select a into x from t3 where a = 42;
+end;
+end|
+create procedure h_sw()
+deterministic
+begin
+-- data exception - numeric value out of range
+declare continue handler for sqlstate '22003'
+ select 'Outer (good)' as 'h_sw';
+begin
+declare continue handler for sqlwarning
+select 'Inner (bad)' as 'h_sw';
+insert into t3 values (123456789012);
+end;
+delete from t3;
+insert into t3 values (1);
+end|
+create procedure h_sx()
+deterministic
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+select 'Outer (good)' as 'h_sx';
+begin
+declare continue handler for sqlexception
+select 'Inner (bad)' as 'h_sx';
+insert into t3 values (1);
+end;
+end|
+create procedure h_ne()
+deterministic
+begin
+declare continue handler for not found
+select 'Outer (bad)' as 'h_ne';
+begin
+declare x int;
+declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+select 'Inner (good)' as 'h_ne';
+select a into x from t3 where a = 42;
+end;
+end|
+create procedure h_ns()
+deterministic
+begin
+declare continue handler for not found
+select 'Outer (bad)' as 'h_ns';
+begin
+declare x int;
+declare continue handler for sqlstate '02000' -- no data
+select 'Inner (good)' as 'h_ns';
+select a into x from t3 where a = 42;
+end;
+end|
+create procedure h_nn()
+deterministic
+begin
+declare continue handler for not found
+select 'Outer (bad)' as 'h_nn';
+begin
+declare x int;
+declare continue handler for not found
+select 'Inner (good)' as 'h_nn';
+select a into x from t3 where a = 42;
+end;
+end|
+create procedure h_we()
+deterministic
+begin
+declare continue handler for sqlwarning
+select 'Outer (bad)' as 'h_we';
+begin
+declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+select 'Inner (good)' as 'h_we';
+insert into t3 values (123456789012);
+end;
+delete from t3;
+insert into t3 values (1);
+end|
+create procedure h_ws()
+deterministic
+begin
+declare continue handler for sqlwarning
+select 'Outer (bad)' as 'h_ws';
+begin
+-- data exception - numeric value out of range
+declare continue handler for sqlstate '22003'
+ select 'Inner (good)' as 'h_ws';
+insert into t3 values (123456789012);
+end;
+delete from t3;
+insert into t3 values (1);
+end|
+create procedure h_ww()
+deterministic
+begin
+declare continue handler for sqlwarning
+select 'Outer (bad)' as 'h_ww';
+begin
+declare continue handler for sqlwarning
+select 'Inner (good)' as 'h_ww';
+insert into t3 values (123456789012);
+end;
+delete from t3;
+insert into t3 values (1);
+end|
+create procedure h_xe()
+deterministic
+begin
+declare continue handler for sqlexception
+select 'Outer (bad)' as 'h_xe';
+begin
+declare continue handler for 1062 -- ER_DUP_ENTRY
+select 'Inner (good)' as 'h_xe';
+insert into t3 values (1);
+end;
+end|
+create procedure h_xs()
+deterministic
+begin
+declare continue handler for sqlexception
+select 'Outer (bad)' as 'h_xs';
+begin
+-- integrity constraint violation
+declare continue handler for sqlstate '23000'
+ select 'Inner (good)' as 'h_xs';
+insert into t3 values (1);
+end;
+end|
+create procedure h_xx()
+deterministic
+begin
+declare continue handler for sqlexception
+select 'Outer (bad)' as 'h_xx';
+begin
+declare continue handler for sqlexception
+select 'Inner (good)' as 'h_xx';
+insert into t3 values (1);
+end;
+end|
+call h_ee()|
+h_ee
+Inner (good)
+call h_es()|
+h_es
+Outer (good)
+call h_en()|
+h_en
+Outer (good)
+call h_ew()|
+h_ew
+Outer (good)
+call h_ex()|
+h_ex
+Outer (good)
+call h_se()|
+h_se
+Inner (good)
+call h_ss()|
+h_ss
+Inner (good)
+call h_sn()|
+h_sn
+Outer (good)
+call h_sw()|
+h_sw
+Outer (good)
+call h_sx()|
+h_sx
+Outer (good)
+call h_ne()|
+h_ne
+Inner (good)
+call h_ns()|
+h_ns
+Inner (good)
+call h_nn()|
+h_nn
+Inner (good)
+call h_we()|
+h_we
+Inner (good)
+call h_ws()|
+h_ws
+Inner (good)
+call h_ww()|
+h_ww
+Inner (good)
+call h_xe()|
+h_xe
+Inner (good)
+call h_xs()|
+h_xs
+Inner (good)
+call h_xx()|
+h_xx
+Inner (good)
+drop table t3|
+drop procedure h_ee|
+drop procedure h_es|
+drop procedure h_en|
+drop procedure h_ew|
+drop procedure h_ex|
+drop procedure h_se|
+drop procedure h_ss|
+drop procedure h_sn|
+drop procedure h_sw|
+drop procedure h_sx|
+drop procedure h_ne|
+drop procedure h_ns|
+drop procedure h_nn|
+drop procedure h_we|
+drop procedure h_ws|
+drop procedure h_ww|
+drop procedure h_xe|
+drop procedure h_xs|
+drop procedure h_xx|
drop procedure if exists bug822|
create procedure bug822(a_id char(16), a_data int)
begin
--- 1.174/mysql-test/t/sp.test 2006-02-06 14:09:07 +01:00
+++ 1.175/mysql-test/t/sp.test 2006-02-15 17:28:30 +01:00
@@ -1660,7 +1660,7 @@
# Implicit LOCK/UNLOCK TABLES for table access in functions
#
---disable_warning
+--disable_warnings
drop function if exists t1max|
--enable_warnings
create function t1max() returns int
@@ -1702,6 +1702,397 @@
select * from t3|
drop table t3|
drop function getcount|
+
+
+# Test cases for different combinations of condition handlers in nested
+# begin-end blocks in stored procedures.
+#
+# Note that the standard specifies that the most specific handler should
+# be triggered even if it's an outer handler masked by a less specific
+# handler in an inner block.
+# Note also that '02000' is more specific than NOT FOUND; there might be
+# other '02xxx' states, even if we currently do not issue them in any
+# situation (e.g. '02001').
+#
+# The combinations we test are these:
+#
+# Inner
+# errcode sqlstate not found sqlwarning sqlexception
+# Outer +------------+------------+------------+------------+------------+
+#errcode | h_ee (i) | h_es (o) | h_en (o) | h_ew (o) | h_ex (o) |
+#sqlstate | h_se (i) | h_ss (i) | h_sn (o) | h_sw (o) | h_sx (o) |
+#not found | h_ne (i) | h_ns (i) | h_nn (i) | | |
+#sqlwarning | h_we (i) | h_ws (i) | | h_ww (i) | |
+#sqlexception | h_xe (i) | h_xs (i) | | | h_xx (i) |
+# +------------+---------------------------------------------------+
+#
+# (i) means that the inner handler is the one that should be invoked,
+# (o) means that the outer handler should be invoked.
+#
+# ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence
+# no tests for those combinations.)
+#
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists h_ee|
+drop procedure if exists h_es|
+drop procedure if exists h_en|
+drop procedure if exists h_ew|
+drop procedure if exists h_ex|
+drop procedure if exists h_se|
+drop procedure if exists h_ss|
+drop procedure if exists h_sn|
+drop procedure if exists h_sw|
+drop procedure if exists h_sx|
+drop procedure if exists h_ne|
+drop procedure if exists h_ns|
+drop procedure if exists h_nn|
+drop procedure if exists h_we|
+drop procedure if exists h_ws|
+drop procedure if exists h_ww|
+drop procedure if exists h_xe|
+drop procedure if exists h_xs|
+drop procedure if exists h_xx|
+--enable_warnings
+
+# smallint - to get out of range warnings
+# primary key - to get constraint errors
+create table t3 (a smallint primary key)|
+
+insert into t3 (a) values (1)|
+
+create procedure h_ee()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (bad)' as 'h_ee';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_ee';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_es()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (good)' as 'h_es';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (bad)' as 'h_es';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_en()
+ deterministic
+begin
+ declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+ select 'Outer (good)' as 'h_en';
+
+ begin
+ declare x int;
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Inner (bad)' as 'h_en';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_ew()
+ deterministic
+begin
+ declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+ select 'Outer (good)' as 'h_ew';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (bad)' as 'h_ew';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ex()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (good)' as 'h_ex';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (bad)' as 'h_ex';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_se()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (bad)' as 'h_se';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_se';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_ss()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (bad)' as 'h_ss';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (good)' as 'h_ss';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_sn()
+ deterministic
+begin
+ -- Note: '02000' is more specific than NOT FOUND ;
+ -- there might be other not found states
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Outer (good)' as 'h_sn';
+
+ begin
+ declare x int;
+ declare continue handler for not found
+ select 'Inner (bad)' as 'h_sn';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_sw()
+ deterministic
+begin
+ -- data exception - numeric value out of range
+ declare continue handler for sqlstate '22003'
+ select 'Outer (good)' as 'h_sw';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (bad)' as 'h_sw';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_sx()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (good)' as 'h_sx';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (bad)' as 'h_sx';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_ne()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_ne';
+
+ begin
+ declare x int;
+ declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+ select 'Inner (good)' as 'h_ne';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_ns()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_ns';
+
+ begin
+ declare x int;
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Inner (good)' as 'h_ns';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_nn()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_nn';
+
+ begin
+ declare x int;
+ declare continue handler for not found
+ select 'Inner (good)' as 'h_nn';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_we()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_we';
+
+ begin
+ declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+ select 'Inner (good)' as 'h_we';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ws()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_ws';
+
+ begin
+ -- data exception - numeric value out of range
+ declare continue handler for sqlstate '22003'
+ select 'Inner (good)' as 'h_ws';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ww()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_ww';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (good)' as 'h_ww';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_xe()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xe';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_xe';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_xs()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xs';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (good)' as 'h_xs';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_xx()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xx';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (good)' as 'h_xx';
+
+ insert into t3 values (1);
+ end;
+end|
+
+call h_ee()|
+call h_es()|
+call h_en()|
+call h_ew()|
+call h_ex()|
+call h_se()|
+call h_ss()|
+call h_sn()|
+call h_sw()|
+call h_sx()|
+call h_ne()|
+call h_ns()|
+call h_nn()|
+call h_we()|
+call h_ws()|
+call h_ww()|
+call h_xe()|
+call h_xs()|
+call h_xx()|
+
+drop table t3|
+drop procedure h_ee|
+drop procedure h_es|
+drop procedure h_en|
+drop procedure h_ew|
+drop procedure h_ex|
+drop procedure h_se|
+drop procedure h_ss|
+drop procedure h_sn|
+drop procedure h_sw|
+drop procedure h_sx|
+drop procedure h_ne|
+drop procedure h_ns|
+drop procedure h_nn|
+drop procedure h_we|
+drop procedure h_ws|
+drop procedure h_ww|
+drop procedure h_xe|
+drop procedure h_xs|
+drop procedure h_xx|
#
| Thread |
|---|
| • bk commit into 5.0 tree (pem:1.2042) BUG#15011 | pem | 15 Feb |
