Auto-merge from mysql-trunk-bugfixing.

******
This patch fixes the following bugs:
  - Bug#5889: Exit handler for a warning doesn't hide the warning in
    trigger
  - Bug#9857: Stored procedures: handler for sqlwarning ignored
  - Bug#23032: Handlers declared in a SP do not handle warnings generated
    in sub-SP
  - Bug#36185: Incorrect precedence for warning and exception handlers

The problem was in the way warnings/errors during stored routine execution
were handled. Prior to this patch the logic was as follows:

  - when a warning/an error happens: if we're executing a stored routine,
    and there is a handler for that warning/error, remember the handler,
    ignore the warning/error and continue execution.

  - after a stored routine instruction is executed: check for a remembered
    handler and activate one (if any).

This logic caused several problems:

  - if one instruction generates several warnings (errors) it's impossible
    to choose the right handler -- a handler for the first generated
    condition was chosen and remembered for activation.

  - mess with handling conditions in scopes different from the current one.

  - not putting generated warnings/errors into Warning Info (Diagnostic
    Area) is against The Standard.

The patch changes the logic as follows:

  - Diagnostic Area is cleared on the beginning of each statement that
    either is able to generate warnings, or is able to work with tables.

  - at the end of a stored routine instruction, Diagnostic Area is left
    intact.

  - Diagnostic Area is checked after each stored routine instruction. If
    an instruction generates several condition, it's now possible to take a
    look at all of them and determine an appropriate handler.

mysql-test/r/signal.result:
  Update result file:
    1. handled conditions are not cleared any more;
    2. reflect changes in signal.test
mysql-test/r/signal_demo3.result:
  Update result file: handled conditions are not cleared any more.
  Due to playing with max_error_count, resulting warning lists
  have changed.
mysql-test/r/sp-big.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/r/sp-bugs.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/r/sp-code.result:
  Update result file:
    1. handled conditions are not cleared any more.
    2. add result for a new test case in sp-code.test.
mysql-test/r/sp-error.result:
  Update result file:
    1. handled conditions are not cleared any more.
    2. add result for a new test case in sp-error.test.
mysql-test/r/sp.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/r/sp_trans.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/r/strict.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/r/view.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/funcs_1/r/innodb_storedproc_02.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/funcs_1/r/memory_storedproc_02.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/funcs_1/r/myisam_storedproc_02.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/funcs_1/r/storedproc.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/rpl/r/rpl_row_sp005.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/suite/rpl/r/rpl_row_trig003.result:
  Update result file: handled conditions are not cleared any more.
mysql-test/t/signal.test:
  Make a test case more readable in the result file.
mysql-test/t/sp-code.test:
  Add a test case for Bug#23032 checking that
  No Data takes precedence on Warning.
mysql-test/t/sp-error.test:
  Adding test cases for:
    - Bug#23032
    - Bug#36185
    - Bug#5889
    - Bug#9857
mysql-test/t/sp.test:
  Fixing test case to reflect behavioral changes made by the patch.
sql/sp_head.cc:
  Reset the per-statement warning count before executing
  a stored procedure instruction.
  
  Move to a separate function code which checks the
  completion status of the executed statement and searches
  for a handler.
  
  Remove redundant code now that search for a handler is
  done after execution, errors are always pushed.
sql/sp_pcontext.h:
  Remove unused code.
sql/sp_rcontext.cc:
  - Polish sp_rcontext::find_handler(): use sp_rcontext::m_hfound instead
    of an extra local variable;
  
  - Remove sp_rcontext::handle_condition();
  
  - Introduce sp_rcontext::activate_handler(), which prepares
    previously found handler for execution.
  
  - Move sp_rcontext::enter_handler() code into activate_handler(),
    because enter_handler() is used only from there;
  
  - Cleanups;
  
  - Introduce DBUG_EXECUTE_IF() for a test case in sp-code.test
sql/sp_rcontext.h:
  - Remove unused code
  - Cleanups
sql/sql_class.cc:
  Merge THD::raise_condition_no_handler() into THD::raise_condition().
  After the patch raise_condition_no_handler() was called
  in raise_condition() only.
sql/sql_class.h:
  Remove raise_condition_no_handler().
sql/sql_error.cc:
  Remove Warning_info::reserve_space() -- handled conditions are not
  cleared any more, so there is no need for RESIGNAL to re-push them.
sql/sql_error.h:
  Remove Warning_info::reserve_space().
sql/sql_signal.cc:
  Handled conditions are not cleared any more,
  so there is no need for RESIGNAL to re-push them.

10 files changed, 531 insertions, 20 deletions

diff --git a/mysql-test/r/signal.result b/mysql-test/r/signal.result
index 410abffcdf0..67bf9330451 100644
--- a/mysql-test/r/signal.result
+++ b/mysql-test/r/signal.result
@@ -1189,6 +1189,8 @@ end $$
 call test_signal() $$
 Caught by SQLSTATE
 Caught by SQLSTATE
+Warnings:
+Warning	1012	Raising a warning
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1204,6 +1206,8 @@ end $$
 call test_signal() $$
 Caught by number
 Caught by number
+Warnings:
+Warning	1012	Raising a warning
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1219,6 +1223,8 @@ end $$
 call test_signal() $$
 Caught by SQLWARNING
 Caught by SQLWARNING
+Warnings:
+Warning	1012	Raising a warning
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1234,6 +1240,8 @@ end $$
 call test_signal() $$
 Caught by SQLSTATE
 Caught by SQLSTATE
+Warnings:
+Error	1012	Raising a not found
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1249,6 +1257,8 @@ end $$
 call test_signal() $$
 Caught by number
 Caught by number
+Warnings:
+Error	1012	Raising a not found
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1264,6 +1274,8 @@ end $$
 call test_signal() $$
 Caught by NOT FOUND
 Caught by NOT FOUND
+Warnings:
+Error	1012	Raising a not found
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1279,6 +1291,8 @@ end $$
 call test_signal() $$
 Caught by SQLSTATE
 Caught by SQLSTATE
+Warnings:
+Error	1012	Raising an error
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1294,6 +1308,8 @@ end $$
 call test_signal() $$
 Caught by number
 Caught by number
+Warnings:
+Error	1012	Raising an error
 drop procedure test_signal $$
 create procedure test_signal()
 begin
@@ -1309,6 +1325,8 @@ end $$
 call test_signal() $$
 Caught by SQLEXCEPTION
 Caught by SQLEXCEPTION
+Warnings:
+Error	1012	Raising an error
 drop procedure test_signal $$
 #
 # Test where SIGNAL can be used
@@ -1455,6 +1473,7 @@ after RESIGNAL
 after RESIGNAL
 Warnings:
 Warning	1012	Raising a warning
+Warning	1012	Raising a warning
 drop procedure test_resignal $$
 create procedure test_resignal()
 begin
@@ -1509,6 +1528,7 @@ after RESIGNAL
 after RESIGNAL
 Warnings:
 Warning	1264	Out of range value for column 'a' at row 1
+Warning	1264	Out of range value for column 'a' at row 1
 drop procedure test_resignal $$
 create procedure test_resignal()
 begin
@@ -1565,6 +1585,7 @@ before RESIGNAL
 after RESIGNAL
 after RESIGNAL
 Warnings:
+Warning	1012	Raising a warning
 Warning	5555	RESIGNAL of a warning
 drop procedure test_resignal $$
 create procedure test_resignal()
@@ -1625,6 +1646,7 @@ before RESIGNAL
 after RESIGNAL
 after RESIGNAL
 Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
 Warning	5555	RESIGNAL of a warning
 drop procedure test_resignal $$
 create procedure test_resignal()
@@ -2126,6 +2148,9 @@ CALL peter_p2() $$
 ERROR 42000: Hi, I am a useless error message
 show warnings $$
 Level	Code	Message
+Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	9999	Variable 'sql_mode' can't be set to the value of 'NULL'
 Error	9999	Hi, I am a useless error message
 drop procedure peter_p1 $$
 drop procedure peter_p2 $$
@@ -2135,15 +2160,17 @@ DECLARE x CONDITION FOR SQLSTATE '42000';
 DECLARE EXIT HANDLER FOR x
 BEGIN
 SELECT '2';
+SHOW WARNINGS;
 RESIGNAL x SET MYSQL_ERRNO = 9999;
 END;
 BEGIN
 DECLARE EXIT HANDLER FOR x
 BEGIN
 SELECT '1';
+SHOW WARNINGS;
 RESIGNAL x SET
 SCHEMA_NAME = 'test',
-MYSQL_ERRNO= 1231;
+MYSQL_ERRNO= 1232;
 END;
 /* Raises ER_WRONG_VALUE_FOR_VAR : 1231, SQLSTATE 42000 */
 SET @@sql_mode=NULL;
@@ -2156,6 +2183,7 @@ DECLARE x CONDITION for SQLSTATE '42000';
 DECLARE EXIT HANDLER FOR x
 BEGIN
 SELECT '3';
+SHOW WARNINGS;
 RESIGNAL x SET
 MESSAGE_TEXT = 'Hi, I am a useless error message',
 MYSQL_ERRNO = 9999;
@@ -2166,15 +2194,24 @@ $$
 CALL peter_p2() $$
 1
 1
+Level	Code	Message
+Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
 2
 2
+Level	Code	Message
+Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	1232	Variable 'sql_mode' can't be set to the value of 'NULL'
 3
 3
+Level	Code	Message
+Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	1232	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	9999	Variable 'sql_mode' can't be set to the value of 'NULL'
 ERROR 42000: Hi, I am a useless error message
 show warnings $$
 Level	Code	Message
 Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
-Error	1231	Variable 'sql_mode' can't be set to the value of 'NULL'
+Error	1232	Variable 'sql_mode' can't be set to the value of 'NULL'
 Error	9999	Variable 'sql_mode' can't be set to the value of 'NULL'
 Error	9999	Hi, I am a useless error message
 drop procedure peter_p1 $$
diff --git a/mysql-test/r/signal_demo3.result b/mysql-test/r/signal_demo3.result
index aa769659e51..a89ce703d20 100644
--- a/mysql-test/r/signal_demo3.result
+++ b/mysql-test/r/signal_demo3.result
@@ -95,11 +95,11 @@ call proc_1();
 ERROR 45000: Oops in proc_1
 show warnings;
 Level	Code	Message
-Error	1644	Oops in proc_5
-Error	1644	Oops in proc_4
-Error	1644	Oops in proc_3
-Error	1644	Oops in proc_2
-Error	1644	Oops in proc_1
+Error	1051	Unknown table 'oops_it_is_not_here'
+Error	1644	Oops in proc_9
+Error	1644	Oops in proc_8
+Error	1644	Oops in proc_7
+Error	1644	Oops in proc_6
 SET @@session.max_error_count = 7;
 SELECT @@session.max_error_count;
 @@session.max_error_count
@@ -108,13 +108,13 @@ call proc_1();
 ERROR 45000: Oops in proc_1
 show warnings;
 Level	Code	Message
+Error	1051	Unknown table 'oops_it_is_not_here'
+Error	1644	Oops in proc_9
+Error	1644	Oops in proc_8
 Error	1644	Oops in proc_7
 Error	1644	Oops in proc_6
 Error	1644	Oops in proc_5
 Error	1644	Oops in proc_4
-Error	1644	Oops in proc_3
-Error	1644	Oops in proc_2
-Error	1644	Oops in proc_1
 SET @@session.max_error_count = 9;
 SELECT @@session.max_error_count;
 @@session.max_error_count
@@ -123,6 +123,7 @@ call proc_1();
 ERROR 45000: Oops in proc_1
 show warnings;
 Level	Code	Message
+Error	1051	Unknown table 'oops_it_is_not_here'
 Error	1644	Oops in proc_9
 Error	1644	Oops in proc_8
 Error	1644	Oops in proc_7
@@ -131,7 +132,6 @@ Error	1644	Oops in proc_5
 Error	1644	Oops in proc_4
 Error	1644	Oops in proc_3
 Error	1644	Oops in proc_2
-Error	1644	Oops in proc_1
 drop database demo;
 SET @@global.max_error_count = @start_global_value;
 SELECT @@global.max_error_count;
diff --git a/mysql-test/r/sp-big.result b/mysql-test/r/sp-big.result
index 9765508859c..d28b7004330 100644
--- a/mysql-test/r/sp-big.result
+++ b/mysql-test/r/sp-big.result
@@ -46,6 +46,8 @@ end while;
 close cur1;
 end|
 call p1();
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select count(*) from t1;
 count(*)
 256
diff --git a/mysql-test/r/sp-bugs.result b/mysql-test/r/sp-bugs.result
index 2374b433fba..a88c89537e2 100644
--- a/mysql-test/r/sp-bugs.result
+++ b/mysql-test/r/sp-bugs.result
@@ -43,6 +43,8 @@ END|
 SELECT f2 ();
 f2 ()
 NULL
+Warnings:
+Error	1305	FUNCTION testdb.f_not_exists does not exist
 DROP SCHEMA testdb;
 USE test;
 #
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 0a0f620b80a..c7ea4cbb311 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -711,6 +711,8 @@ looping	i
 looping	1
 looping	i
 looping	0
+Warnings:
+Error	1062	Duplicate entry '1' for key 'a'
 call proc_26977_works(2);
 do something
 do something
@@ -730,6 +732,8 @@ looping	i
 looping	0
 optimizer: keep hreturn
 optimizer: keep hreturn
+Warnings:
+Error	1062	Duplicate entry '2' for key 'a'
 drop table t1;
 drop procedure proc_26977_broken;
 drop procedure proc_26977_works;
@@ -888,3 +892,56 @@ Pos	Instruction
 4	jump 6
 5	error 1339
 DROP PROCEDURE p1;
+#
+# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
+#
+
+# - Case 4: check that "No Data trumps Warning".
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE c CURSOR FOR SELECT a FROM t1;
+OPEN c;
+BEGIN
+DECLARE v INT;
+DECLARE CONTINUE HANDLER FOR SQLWARNING
+BEGIN
+SELECT "Warning found!";
+SHOW WARNINGS;
+END;
+DECLARE EXIT HANDLER FOR NOT FOUND
+BEGIN
+SELECT "End of Result Set found!";
+SHOW WARNINGS;
+END;
+WHILE TRUE DO
+FETCH c INTO v;
+END WHILE;
+END;
+CLOSE c;
+SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack
+END|
+SET SESSION debug="+d,bug23032_emit_warning";
+CALL p1();
+Warning found!
+Warning found!
+Level	Code	Message
+Warning	1105	Unknown error
+Warning found!
+Warning found!
+Level	Code	Message
+Warning	1105	Unknown error
+Warning found!
+Warning found!
+Level	Code	Message
+Warning	1105	Unknown error
+End of Result Set found!
+End of Result Set found!
+Level	Code	Message
+Warning	1105	Unknown error
+Error	1329	No data - zero rows fetched, selected, or processed
+SET SESSION debug="-d,bug23032_emit_warning";
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 0f658ee831e..7b8364379df 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1344,6 +1344,8 @@ set @in_func := 0;
 select func_20713_a();
 func_20713_a()
 NULL
+Warnings:
+Error	1146	Table 'test.bogus_table_20713' doesn't exist
 select @in_func;
 @in_func
 2
@@ -1351,6 +1353,8 @@ set @in_func := 0;
 select func_20713_b();
 func_20713_b()
 NULL
+Warnings:
+Error	1146	Table 'test.bogus_table_20713' doesn't exist
 select @in_func;
 @in_func
 2
@@ -1714,3 +1718,246 @@ DROP PROCEDURE p1;
 DROP VIEW v1;
 DROP TABLE t1;
 End of 5.1 tests
+#
+# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
+#
+
+# - Case 1
+
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+DROP PROCEDURE IF EXISTS p5;
+DROP PROCEDURE IF EXISTS p6;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+SELECT 1;
+CALL p2();
+END|
+CREATE PROCEDURE p2()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+END|
+CALL p1();
+CAST('10 ' as unsigned integer)
+10
+1
+1
+CAST('10 ' as unsigned integer)
+10
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+# - Case 2
+
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE c INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLWARNING SET c = c + 1;
+CALL p2();
+CALL p3();
+CALL p4();
+SELECT c;
+SELECT @@warning_count;
+SHOW WARNINGS;
+END|
+CREATE PROCEDURE p2()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+END|
+CREATE PROCEDURE p3()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+SELECT 1;
+END|
+CREATE PROCEDURE p4()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+CALL p2();
+END|
+CREATE PROCEDURE p5()
+BEGIN
+SELECT CAST('10 ' as unsigned integer);
+SHOW WARNINGS;
+END|
+CREATE PROCEDURE P6()
+BEGIN
+DECLARE c INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLWARNING SET c = c + 1;
+CALL p5();
+SELECT c;
+END|
+CALL p1();
+CAST('10 ' as unsigned integer)
+10
+CAST('10 ' as unsigned integer)
+10
+1
+1
+CAST('10 ' as unsigned integer)
+10
+CAST('10 ' as unsigned integer)
+10
+c
+3
+@@warning_count
+1
+Level	Code	Message
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+CALL p6();
+CAST('10 ' as unsigned integer)
+10
+Level	Code	Message
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+c
+1
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+DROP PROCEDURE p5;
+DROP PROCEDURE p6;
+
+# - Case 3: check that "Exception trumps No Data".
+
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE c CURSOR FOR SELECT a FROM t1;
+OPEN c;
+BEGIN
+DECLARE v1 INT;
+DECLARE v2 INT;
+DECLARE EXIT HANDLER FOR SQLEXCEPTION
+SELECT "Error caught (expected)";
+DECLARE EXIT HANDLER FOR NOT FOUND
+SELECT "End of Result Set found!";
+WHILE TRUE DO
+FETCH c INTO v1, v2;
+END WHILE;
+END;
+CLOSE c;
+SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack
+END|
+CALL p1();
+Error caught (expected)
+Error caught (expected)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Bug#36185: Incorrect precedence for warning and exception handlers
+#
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1 (a INT, b INT NOT NULL);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'warning';
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'exception';
+INSERT INTO t1 VALUES (CAST('10 ' AS SIGNED), NULL);
+END|
+CALL p1();
+exception
+exception
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '10 '
+Error	1048	Column 'b' cannot be null
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
+#
+
+# - Case 1
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1, 2);
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+DECLARE EXIT HANDLER FOR SQLWARNING
+SET NEW.a = 10;
+SET NEW.a = 99999999999;
+END|
+UPDATE t1 SET b = 20;
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a	b
+10	20
+DROP TRIGGER t1_bu;
+DROP TABLE t1;
+
+# - Case 2
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(b CHAR(1));
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+INSERT INTO t2 VALUES('ab'); # Produces a warning.
+INSERT INTO t2 VALUES('b');  # Does not produce a warning,
+# previous warning should be cleared.
+END|
+INSERT INTO t1 VALUES(0);
+SHOW WARNINGS;
+Level	Code	Message
+SELECT * FROM t1;
+a
+0
+SELECT * FROM t2;
+b
+a
+b
+DROP TRIGGER t1_bi;
+DROP TABLE t1;
+DROP TABLE t2;
+#
+# Bug#9857: Stored procedures: handler for sqlwarning ignored
+#
+SET @sql_mode_saved = @@sql_mode;
+SET sql_mode = traditional;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE CONTINUE HANDLER FOR SQLWARNING
+SELECT 'warning caught (expected)';
+SELECT 5 / 0;
+END|
+CREATE PROCEDURE p2()
+BEGIN
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
+SELECT 'error caught (unexpected)';
+SELECT 5 / 0;
+END|
+CALL p1();
+5 / 0
+NULL
+warning caught (expected)
+warning caught (expected)
+Warnings:
+Warning	1365	Division by 0
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1365	Division by 0
+CALL p2();
+5 / 0
+NULL
+Warnings:
+Warning	1365	Division by 0
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1365	Division by 0
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+SET sql_mode = @sql_mode_saved;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 76d4bf2dea0..243bfb6c07d 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -737,6 +737,8 @@ close c;
 end|
 insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
 call cur1()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t1|
 id	data
 foo	40
@@ -772,6 +774,8 @@ close c1;
 close c2;
 end|
 call cur2()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t3 order by i,s|
 s	i
 bar	3
@@ -861,6 +865,8 @@ end$
 set @@sql_mode = ''|
 set sql_select_limit = 1|
 call modes(@c1, @c2)|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 set sql_select_limit = default|
 select @c1, @c2|
 @c1	@c2
@@ -1682,42 +1688,64 @@ end|
 call h_ee()|
 h_ee
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_es()|
 h_es
 Outer (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_en()|
 h_en
 Outer (good)
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
 call h_ew()|
 h_ew
 Outer (good)
 call h_ex()|
 h_ex
 Outer (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_se()|
 h_se
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_ss()|
 h_ss
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_sn()|
 h_sn
 Outer (good)
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
 call h_sw()|
 h_sw
 Outer (good)
 call h_sx()|
 h_sx
 Outer (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_ne()|
 h_ne
 Inner (good)
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
 call h_ns()|
 h_ns
 Inner (good)
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
 call h_nn()|
 h_nn
 Inner (good)
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
 call h_we()|
 h_we
 Inner (good)
@@ -1730,12 +1758,18 @@ Inner (good)
 call h_xe()|
 h_xe
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_xs()|
 h_xs
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 call h_xx()|
 h_xx
 Inner (good)
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 drop table t3|
 drop procedure h_ee|
 drop procedure h_es|
@@ -1884,6 +1918,8 @@ set @x2 = 2;
 close c1;
 end|
 call bug2260()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select @x2|
 @x2
 2
@@ -2027,6 +2063,8 @@ insert into t3 values (123456789012);
 insert into t3 values (0);
 end|
 call bug2780()|
+Warnings:
+Warning	1264	Out of range value for column 's1' at row 1
 select @x|
 @x
 1
@@ -2449,6 +2487,8 @@ declare continue handler for sqlstate 'HY000' begin end;
 select s1 from t3 union select s2 from t3; 
 end|
 call bug4904()|
+Warnings:
+Error	1267	Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_general_ci,IMPLICIT) for operation 'UNION'
 drop procedure bug4904|
 drop table t3|
 drop procedure if exists bug336|
@@ -2588,13 +2628,17 @@ select row_count()|
 row_count()
 1
 call bug4905()|
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 select row_count()|
 row_count()
-0
+-1
 call bug4905()|
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 select row_count()|
 row_count()
-0
+-1
 select * from t3|
 s1
 1
@@ -2615,10 +2659,14 @@ insert into t3 values (1)|
 call bug6029()|
 sqlstate 23000
 sqlstate 23000
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 delete from t3|
 call bug6029()|
 1136
 1136
+Warnings:
+Error	1136	Column count doesn't match value count at row 1
 drop procedure bug6029|
 drop table t3|
 drop procedure if exists bug8540|
@@ -2910,15 +2958,23 @@ end|
 call bug6900()|
 2
 2
+Warnings:
+Error	1136	Column count doesn't match value count at row 1
 call bug9074()|
 x1	x2	x3	x4	x5	x6
 1	1	1	1	1	1
+Warnings:
+Error	1062	Duplicate entry 'a' for key 'w'
 call bug6900_9074(0)|
 sqlexception
 sqlexception
+Warnings:
+Error	1136	Column count doesn't match value count at row 1
 call bug6900_9074(1)|
 23000
 23000
+Warnings:
+Error	1062	Duplicate entry 'a' for key 'w'
 drop procedure bug6900|
 drop procedure bug9074|
 drop procedure bug6900_9074|
@@ -2961,9 +3017,13 @@ delete from t1|
 call bug9856()|
 16
 16
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 call bug9856()|
 16
 16
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 drop procedure bug9856|
 drop procedure if exists bug9674_1|
 drop procedure if exists bug9674_2|
@@ -3194,6 +3254,8 @@ x
 2
 x
 3
+Warnings:
+Error	1326	Cursor is not open
 call bug10961()|
 x
 1
@@ -3201,6 +3263,8 @@ x
 2
 x
 3
+Warnings:
+Error	1326	Cursor is not open
 drop procedure bug10961|
 DROP PROCEDURE IF EXISTS bug6866|
 DROP VIEW IF EXISTS tv|
@@ -3316,7 +3380,11 @@ insert into t1 values
 ('Name4', 13),
 ('Name5', 14)|
 call bug11529()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 call bug11529()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 delete from t1|
 drop procedure bug11529|
 set character set utf8|
@@ -3490,24 +3558,32 @@ end;
 end if;
 end|
 call bug12168('a')|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t4|
 a
 1
 3
 truncate t4|
 call bug12168('b')|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t4|
 a
 2
 4
 truncate t4|
 call bug12168('a')|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t4|
 a
 1
 3
 truncate t4|
 call bug12168('b')|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select * from t4|
 a
 2
@@ -3807,6 +3883,8 @@ end|
 call bug7049_2()|
 Result
 Caught it
+Warnings:
+Error	1062	Duplicate entry '42' for key 'x'
 select * from t3|
 x
 42
@@ -3814,12 +3892,16 @@ delete from t3|
 call bug7049_4()|
 Result
 Caught it
+Warnings:
+Error	1062	Duplicate entry '42' for key 'x'
 select * from t3|
 x
 42
 select bug7049_2()|
 bug7049_2()
 1
+Warnings:
+Error	1062	Duplicate entry '42' for key 'x'
 drop table t3|
 drop procedure bug7049_1|
 drop procedure bug7049_2|
@@ -3947,6 +4029,8 @@ end|
 call bug14845()|
 a
 0
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 drop procedure bug14845|
 drop procedure if exists bug13549_1|
 drop procedure if exists bug13549_2|
@@ -4150,6 +4234,8 @@ end|
 call bug13729()|
 55
 55
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 select * from t3|
 s1
 1
@@ -4186,11 +4272,15 @@ Handler
 boo
 v	isnull(v)
 NULL	1
+Warnings:
+Error	1054	Unknown column 'undefined_var' in 'field list'
 call bug14643_2()|
 Handler
 boo
 Handler
 boo
+Warnings:
+Error	1054	Unknown column 'undefined_var' in 'field list'
 drop procedure bug14643_1|
 drop procedure bug14643_2|
 drop procedure if exists bug14304|
@@ -4514,11 +4604,15 @@ Handler
 error
 End
 done
+Warnings:
+Error	1054	Unknown column 'v' in 'field list'
 call bug14498_2()|
 Handler
 error
 End
 done
+Warnings:
+Error	1054	Unknown column 'v' in 'field list'
 call bug14498_3()|
 v
 maybe
@@ -4526,16 +4620,22 @@ Handler
 error
 End
 done
+Warnings:
+Error	1054	Unknown column 'v' in 'field list'
 call bug14498_4()|
 Handler
 error
 End
 done
+Warnings:
+Error	1054	Unknown column 'v' in 'field list'
 call bug14498_5()|
 Handler
 error
 End
 done
+Warnings:
+Error	1054	Unknown column 'v' in 'field list'
 drop procedure bug14498_1|
 drop procedure bug14498_2|
 drop procedure bug14498_3|
@@ -4546,6 +4646,8 @@ drop procedure if exists bug15231_1|
 drop procedure if exists bug15231_2|
 drop procedure if exists bug15231_3|
 drop procedure if exists bug15231_4|
+drop procedure if exists bug15231_5|
+drop procedure if exists bug15231_6|
 create table t3 (id int not null)|
 create procedure bug15231_1()
 begin
@@ -4568,7 +4670,7 @@ end|
 create procedure bug15231_3()
 begin
 declare exit handler for sqlwarning
-select 'Caught it (wrong)' as 'Result';
+select 'Caught it (correct)' as 'Result';
 call bug15231_4();
 end|
 create procedure bug15231_4()
@@ -4576,6 +4678,20 @@ begin
 declare x decimal(2,1);
 set x = 'zap';
 select 'Missed it (correct)' as 'Result';
+show warnings;
+end|
+create procedure bug15231_5()
+begin
+declare exit handler for sqlwarning
+select 'Caught it (wrong)' as 'Result';
+call bug15231_6();
+end|
+create procedure bug15231_6()
+begin
+declare x decimal(2,1);
+set x = 'zap';
+select 'Missed it (correct)' as 'Result';
+select id from t3;
 end|
 call bug15231_1()|
 1
@@ -4583,19 +4699,29 @@ Before NOT FOUND condition is triggered
 2
 After NOT FOUND condtition is triggered
 xid	xdone
-1	0
+1	1
 Warnings:
 Warning	1329	No data - zero rows fetched, selected, or processed
 call bug15231_3()|
 Result
 Missed it (correct)
+Level	Code	Message
+Warning	1366	Incorrect decimal value: 'zap' for column 'x' at row 1
+Result
+Caught it (correct)
 Warnings:
 Warning	1366	Incorrect decimal value: 'zap' for column 'x' at row 1
-drop table if exists t3|
-drop procedure if exists bug15231_1|
-drop procedure if exists bug15231_2|
-drop procedure if exists bug15231_3|
-drop procedure if exists bug15231_4|
+call bug15231_5()|
+Result
+Missed it (correct)
+id
+drop table t3|
+drop procedure bug15231_1|
+drop procedure bug15231_2|
+drop procedure bug15231_3|
+drop procedure bug15231_4|
+drop procedure bug15231_5|
+drop procedure bug15231_6|
 drop procedure if exists bug15011|
 create table t3 (c1 int primary key)|
 insert into t3 values (1)|
@@ -4613,6 +4739,8 @@ end|
 call bug15011()|
 Handler
 Inner
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 drop procedure bug15011|
 drop table t3|
 drop procedure if exists bug17476|
@@ -4688,6 +4816,8 @@ i
 1
 i
 0
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 drop table t3|
 drop procedure bug16887|
 drop procedure if exists bug16474_1|
@@ -4760,6 +4890,8 @@ declare continue handler for sqlexception begin end;
 select no_such_function();
 end|
 call bug18787()|
+Warnings:
+Error	1305	FUNCTION test.no_such_function does not exist
 drop procedure bug18787|
 create database bug18344_012345678901|
 use bug18344_012345678901|
@@ -5133,6 +5265,8 @@ statement failed
 statement failed
 statement after update
 statement after update
+Warnings:
+Error	1242	Subquery returns more than 1 row
 select * from t3|
 a
 1
@@ -5144,6 +5278,8 @@ statement failed
 statement failed
 statement after update
 statement after update
+Warnings:
+Error	1242	Subquery returns more than 1 row
 select * from t3|
 a
 1
@@ -5176,6 +5312,8 @@ in continue handler
 in continue handler
 reachable code a2
 reachable code a2
+Warnings:
+Error	1242	Subquery returns more than 1 row
 select * from t3|
 a
 1
@@ -5191,6 +5329,8 @@ in continue handler
 in continue handler
 reachable code a2
 reachable code a2
+Warnings:
+Error	1242	Subquery returns more than 1 row
 select * from t3|
 a
 1
@@ -5224,6 +5364,8 @@ in continue handler
 in continue handler
 reachable code a2
 reachable code a2
+Warnings:
+Error	1305	FUNCTION test.no_such_function does not exist
 drop procedure bug8153_proc_a|
 drop procedure bug8153_proc_b|
 drop table t3|
@@ -5792,9 +5934,13 @@ end|
 select func_8407_a()|
 func_8407_a()
 NULL
+Warnings:
+Error	1146	Table 'test.no_such_view' doesn't exist
 select func_8407_b()|
 func_8407_b()
 1500
+Warnings:
+Error	1146	Table 'test.no_such_view' doesn't exist
 drop function func_8407_a|
 drop function func_8407_b|
 drop table if exists table_26503|
@@ -5916,6 +6062,8 @@ looping	i
 looping	0
 leaving handler
 leaving handler
+Warnings:
+Error	1062	Duplicate entry '1' for key 'a'
 call proc_26503_ok_2(2)|
 do something
 do something
@@ -5927,6 +6075,8 @@ looping	i
 looping	4
 leaving handler
 leaving handler
+Warnings:
+Error	1062	Duplicate entry '2' for key 'a'
 call proc_26503_ok_3(3)|
 do something
 do something
@@ -5946,6 +6096,8 @@ looping	i
 looping	0
 leaving handler
 leaving handler
+Warnings:
+Error	1062	Duplicate entry '3' for key 'a'
 call proc_26503_ok_4(4)|
 do something
 do something
@@ -5957,6 +6109,8 @@ looping	i
 looping	4
 leaving handler
 leaving handler
+Warnings:
+Error	1062	Duplicate entry '4' for key 'a'
 drop table table_26503|
 drop procedure proc_26503_ok_1|
 drop procedure proc_26503_ok_2|
@@ -6670,6 +6824,8 @@ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run';
 SELECT x FROM t1;
 END|
 CALL bug29770();
+Warnings:
+Error	1054	Unknown column 'x' in 'field list'
 SELECT @state, @exception;
 @state	@exception
 run	NULL
@@ -6708,6 +6864,8 @@ end;
 end while;
 end//
 call proc_33618(20);
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 drop table t_33618;
 drop procedure proc_33618;
 #
diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result
index f55e535a293..4fa91121f50 100644
--- a/mysql-test/r/sp_trans.result
+++ b/mysql-test/r/sp_trans.result
@@ -99,6 +99,8 @@ return i;
 end|
 set @error_in_func:= 0|
 insert into t1 values (bug10015_6(5)), (bug10015_6(6))|
+Warnings:
+Error	1062	Duplicate entry '1' for key 'PRIMARY'
 select @error_in_func|
 @error_in_func
 1
@@ -524,6 +526,8 @@ until done end repeat;
 close c;
 end|
 call bug14210()|
+Warnings:
+Error	1329	No data - zero rows fetched, selected, or processed
 select count(*) from t4|
 count(*)
 256
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index 4f259fc4d7d..79f8c20a150 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -1190,6 +1190,8 @@ select'a'; insert into t1 values (200); end;|
 call t1();
 a
 a
+Warnings:
+Error	1264	Out of range value for column 'col1' at row 1
 select * from t1;
 col1
 drop procedure t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 96b45f0d5bb..777ac9c258f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3946,6 +3946,8 @@ create view a as select 1;
 end|
 call p();
 call p();
+Warnings:
+Error	1050	Table 'a' already exists
 drop view a;
 drop procedure p;
 #