summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorAlexander Nozdrin <alik@sun.com>2010-07-30 19:28:36 +0400
committerAlexander Nozdrin <alik@sun.com>2010-07-30 19:28:36 +0400
commita0ab253fbd622429beab6027cd532e3f203188be (patch)
treeeaf5cc36760b4d96b253469ba54007facdbf1bf1 /mysql-test
parent727da39fcd32ce2668efb3cac78237a51931c0cf (diff)
downloadmariadb-git-a0ab253fbd622429beab6027cd532e3f203188be.tar.gz
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.
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/signal.result41
-rw-r--r--mysql-test/r/signal_demo3.result18
-rw-r--r--mysql-test/r/sp-big.result2
-rw-r--r--mysql-test/r/sp-bugs.result2
-rw-r--r--mysql-test/r/sp-code.result57
-rw-r--r--mysql-test/r/sp-error.result247
-rw-r--r--mysql-test/r/sp.result176
-rw-r--r--mysql-test/r/sp_trans.result4
-rw-r--r--mysql-test/r/strict.result2
-rw-r--r--mysql-test/r/view.result2
-rw-r--r--mysql-test/suite/funcs_1/r/innodb_storedproc_02.result44
-rw-r--r--mysql-test/suite/funcs_1/r/memory_storedproc_02.result44
-rw-r--r--mysql-test/suite/funcs_1/r/myisam_storedproc_02.result44
-rw-r--r--mysql-test/suite/funcs_1/r/storedproc.result76
-rw-r--r--mysql-test/suite/rpl/r/rpl_row_sp005.result2
-rw-r--r--mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result2
-rw-r--r--mysql-test/suite/rpl/r/rpl_row_trig003.result6
-rw-r--r--mysql-test/t/signal.test5
-rw-r--r--mysql-test/t/sp-code.test53
-rw-r--r--mysql-test/t/sp-error.test270
-rw-r--r--mysql-test/t/sp.test35
21 files changed, 1105 insertions, 27 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;
#
diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result
index 3e2d084aa0c..2bc9a75a13e 100644
--- a/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result
+++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result
@@ -347,6 +347,8 @@ CALL h1();
-7- 1 1 1 1 1 1
END x1 x2 x3 x4 x5 x6
END 1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
DROP TABLE IF EXISTS tnull;
DROP PROCEDURE IF EXISTS sp1;
CREATE TABLE tnull(f1 int);
@@ -445,6 +447,8 @@ END//
CALL h2();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
SELECT * FROM res_t1;
w x
a b
@@ -550,6 +554,8 @@ exit handler 2
exit handler 2
exit handler 1
exit handler 1
+Warnings:
+Error 1146 Table 'db_storedproc.tqq' doesn't exist
create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');
CREATE PROCEDURE h1 ()
@@ -580,6 +586,8 @@ END//
CALL h1();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
This will fail, SQLSTATE 00000 is not allowed
CREATE PROCEDURE sp1()
begin1_label:BEGIN
@@ -623,6 +631,8 @@ CALL sp2();
NULL
@x2 @x
1 2
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -654,6 +664,8 @@ ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expe
CALL sp2();
-1- @x2 @x
-1- 0 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
SELECT '-3-', @x2, @x;
-3- @x2 @x
-3- 1 1
@@ -696,6 +708,8 @@ CALL sp2();
-2- 1 20
-4- @x2 @x
-4- 11 22
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -762,21 +776,33 @@ SELECT @done, @x;
0 1
INSERT INTO temp VALUES('1', NULL);
CALL sp1();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('2', NULL);
CALL sp2();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('3', NULL);
CALL sp3();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
INSERT INTO temp VALUES('4', NULL);
CALL sp4();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
@@ -885,18 +911,26 @@ SELECT @done, @x;
@done @x
0 1
CALL sp1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp2();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp3();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
CALL sp4();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
@@ -1031,6 +1065,8 @@ SQLSTATE
21000
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-1-', @x;
-1- @x
-1- 6
@@ -1041,18 +1077,24 @@ SQLSTATE
SQLEXCEPTION
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-2-', @x;
-2- @x
-2- 6
CALL sp3();
SQLSTATE
20000
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-3-', @x;
-3- @x
-3- 1
CALL sp4();
SQLSTATE
SQLEXCEPTION
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-4-', @x;
-4- @x
-4- 1
@@ -1335,6 +1377,8 @@ CLOSE cur1;
CLOSE cur2;
END//
CALL sp_outer();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT * FROM temp1;
f0 cnt f1 f2 f3 f4
_sp_out_ 1 a` a` 1000-01-01 -5000
diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_02.result b/mysql-test/suite/funcs_1/r/memory_storedproc_02.result
index 16dde71400e..f18678e3896 100644
--- a/mysql-test/suite/funcs_1/r/memory_storedproc_02.result
+++ b/mysql-test/suite/funcs_1/r/memory_storedproc_02.result
@@ -348,6 +348,8 @@ CALL h1();
-7- 1 1 1 1 1 1
END x1 x2 x3 x4 x5 x6
END 1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
DROP TABLE IF EXISTS tnull;
DROP PROCEDURE IF EXISTS sp1;
CREATE TABLE tnull(f1 int);
@@ -446,6 +448,8 @@ END//
CALL h2();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
SELECT * FROM res_t1;
w x
a b
@@ -551,6 +555,8 @@ exit handler 2
exit handler 2
exit handler 1
exit handler 1
+Warnings:
+Error 1146 Table 'db_storedproc.tqq' doesn't exist
create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');
CREATE PROCEDURE h1 ()
@@ -581,6 +587,8 @@ END//
CALL h1();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
This will fail, SQLSTATE 00000 is not allowed
CREATE PROCEDURE sp1()
begin1_label:BEGIN
@@ -624,6 +632,8 @@ CALL sp2();
NULL
@x2 @x
1 2
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -655,6 +665,8 @@ ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expe
CALL sp2();
-1- @x2 @x
-1- 0 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
SELECT '-3-', @x2, @x;
-3- @x2 @x
-3- 1 1
@@ -697,6 +709,8 @@ CALL sp2();
-2- 1 20
-4- @x2 @x
-4- 11 22
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -763,21 +777,33 @@ SELECT @done, @x;
0 1
INSERT INTO temp VALUES('1', NULL);
CALL sp1();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('2', NULL);
CALL sp2();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('3', NULL);
CALL sp3();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
INSERT INTO temp VALUES('4', NULL);
CALL sp4();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
@@ -886,18 +912,26 @@ SELECT @done, @x;
@done @x
0 1
CALL sp1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp2();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp3();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
CALL sp4();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
@@ -1032,6 +1066,8 @@ SQLSTATE
21000
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-1-', @x;
-1- @x
-1- 6
@@ -1042,18 +1078,24 @@ SQLSTATE
SQLEXCEPTION
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-2-', @x;
-2- @x
-2- 6
CALL sp3();
SQLSTATE
20000
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-3-', @x;
-3- @x
-3- 1
CALL sp4();
SQLSTATE
SQLEXCEPTION
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-4-', @x;
-4- @x
-4- 1
@@ -1336,6 +1378,8 @@ CLOSE cur1;
CLOSE cur2;
END//
CALL sp_outer();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT * FROM temp1;
f0 cnt f1 f2 f3 f4
_sp_out_ 1 a` a` 1000-01-01 -5000
diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result
index 16dde71400e..f18678e3896 100644
--- a/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result
+++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result
@@ -348,6 +348,8 @@ CALL h1();
-7- 1 1 1 1 1 1
END x1 x2 x3 x4 x5 x6
END 1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
DROP TABLE IF EXISTS tnull;
DROP PROCEDURE IF EXISTS sp1;
CREATE TABLE tnull(f1 int);
@@ -446,6 +448,8 @@ END//
CALL h2();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
SELECT * FROM res_t1;
w x
a b
@@ -551,6 +555,8 @@ exit handler 2
exit handler 2
exit handler 1
exit handler 1
+Warnings:
+Error 1146 Table 'db_storedproc.tqq' doesn't exist
create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');
CREATE PROCEDURE h1 ()
@@ -581,6 +587,8 @@ END//
CALL h1();
x1 x2 x3 x4 x5 x6
1 1 1 1 1 1
+Warnings:
+Error 1062 Duplicate entry 'a' for key 'w'
This will fail, SQLSTATE 00000 is not allowed
CREATE PROCEDURE sp1()
begin1_label:BEGIN
@@ -624,6 +632,8 @@ CALL sp2();
NULL
@x2 @x
1 2
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -655,6 +665,8 @@ ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expe
CALL sp2();
-1- @x2 @x
-1- 0 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
SELECT '-3-', @x2, @x;
-3- @x2 @x
-3- 1 1
@@ -697,6 +709,8 @@ CALL sp2();
-2- 1 20
-4- @x2 @x
-4- 11 22
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
@@ -763,21 +777,33 @@ SELECT @done, @x;
0 1
INSERT INTO temp VALUES('1', NULL);
CALL sp1();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('2', NULL);
CALL sp2();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 1
INSERT INTO temp VALUES('3', NULL);
CALL sp3();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
INSERT INTO temp VALUES('4', NULL);
CALL sp4();
+Warnings:
+Warning 1265 Data truncated for column 'f1' at row 1
+Warning 1265 Data truncated for column 'f2' at row 1
SELECT @done, @x;
@done @x
1 0
@@ -886,18 +912,26 @@ SELECT @done, @x;
@done @x
0 1
CALL sp1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp2();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 2
CALL sp3();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
CALL sp4();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT @done, @x;
@done @x
1 1
@@ -1032,6 +1066,8 @@ SQLSTATE
21000
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-1-', @x;
-1- @x
-1- 6
@@ -1042,18 +1078,24 @@ SQLSTATE
SQLEXCEPTION
SQLSTATE
24000
+Warnings:
+Error 1326 Cursor is not open
SELECT '-2-', @x;
-2- @x
-2- 6
CALL sp3();
SQLSTATE
20000
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-3-', @x;
-3- @x
-3- 1
CALL sp4();
SQLSTATE
SQLEXCEPTION
+Warnings:
+Error 1339 Case not found for CASE statement
SELECT '-4-', @x;
-4- @x
-4- 1
@@ -1336,6 +1378,8 @@ CLOSE cur1;
CLOSE cur2;
END//
CALL sp_outer();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT * FROM temp1;
f0 cnt f1 f2 f3 f4
_sp_out_ 1 a` a` 1000-01-01 -5000
diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result
index 082da9938f7..bdb3b285189 100644
--- a/mysql-test/suite/funcs_1/r/storedproc.result
+++ b/mysql-test/suite/funcs_1/r/storedproc.result
@@ -9325,6 +9325,8 @@ insert into t2 values (1);
set @x = 3;
END//
CALL sp1();
+Warnings:
+Error 1136 Column count doesn't match value count at row 1
DROP PROCEDURE sp1;
DROP PROCEDURE IF EXISTS sp1;
Warnings:
@@ -13754,6 +13756,8 @@ END//
CALL sp1();
x y @x
NULL a 3
+Warnings:
+Error 1305 PROCEDURE db_storedproc.nonsexist does not exist
SELECT @v1, @v2;
@v1 @v2
4 a
@@ -14938,6 +14942,8 @@ NULL NULL
NULL NULL
@x @y
NULL NULL
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP PROCEDURE sp1;
Testcase 4.2.63:
@@ -15097,6 +15103,8 @@ END;
fetch cur1 into newf1, newf2, newf4, newf3;
END//
CALL sp1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP PROCEDURE sp1;
Testcase 4.2.70:
@@ -22447,6 +22455,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22464,6 +22474,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1305 PROCEDURE db_storedproc.sp1 does not exist
DROP PROCEDURE h1;
Testcase 4.11.3:
@@ -22483,6 +22495,8 @@ CALL sp1 (1);
set @x=0;
END//
CALL h1();
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
SELECT @x, @x2;
@x @x2
1 1
@@ -22501,6 +22515,8 @@ CALL sp1 (1);
set @x=0;
END//
CALL h1 ();
+Warnings:
+Error 1305 PROCEDURE db_storedproc.sp1 does not exist
SELECT @x, @x2;
@x @x2
1 1
@@ -22525,6 +22541,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22547,6 +22565,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22569,6 +22589,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22586,6 +22608,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1305 PROCEDURE db_storedproc.sp1 does not exist
DROP PROCEDURE h1;
Testcase 4.11.9:
@@ -22605,6 +22629,8 @@ CALL sp1 (1);
set @x=0;
END//
CALL h1();
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
SELECT @x, @x2;
@x @x2
1 1
@@ -22623,6 +22649,8 @@ CALL sp1 (1);
set @x=0;
END//
CALL h1 ();
+Warnings:
+Error 1305 PROCEDURE db_storedproc.sp1 does not exist
SELECT @x, @x2;
@x @x2
1 1
@@ -22647,6 +22675,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22669,6 +22699,8 @@ END//
CALL h1 ();
@x @x2
1 1
+Warnings:
+Error 1318 Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
@@ -22704,6 +22736,8 @@ done
1
done
1
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22777,6 +22811,8 @@ done
0
done
1
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22814,6 +22850,8 @@ done
0
done
1
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22844,6 +22882,8 @@ done
0
done
1
+Warnings:
+Error 1328 Incorrect number of FETCH variables
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22874,6 +22914,8 @@ done
0
done
1
+Warnings:
+Error 1328 Incorrect number of FETCH variables
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22904,6 +22946,8 @@ END//
CALL h1();
done
0
+Warnings:
+Error 1328 Incorrect number of FETCH variables
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22934,6 +22978,8 @@ END//
CALL h1();
done
0
+Warnings:
+Error 1328 Incorrect number of FETCH variables
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22964,6 +23010,8 @@ done
0
done
1
+Warnings:
+Error 1325 Cursor is already open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -22995,6 +23043,8 @@ done
0
done @x
1 1
+Warnings:
+Error 1325 Cursor is already open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23025,6 +23075,8 @@ END//
CALL h1();
done
0
+Warnings:
+Error 1325 Cursor is already open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23055,6 +23107,8 @@ END//
CALL h1();
done
0
+Warnings:
+Error 1325 Cursor is already open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23082,6 +23136,8 @@ END//
CALL h1();
done @x
1 1
+Warnings:
+Error 1326 Cursor is not open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23109,6 +23165,8 @@ END//
CALL h1();
done @x
1 1
+Warnings:
+Error 1326 Cursor is not open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23134,6 +23192,8 @@ set @x=1;
SELECT done, @x;
END//
CALL h1();
+Warnings:
+Error 1326 Cursor is not open
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23159,6 +23219,8 @@ set @x=1;
SELECT done, @x;
END//
CALL h1();
+Warnings:
+Error 1326 Cursor is not open
DROP PROCEDURE IF EXISTS h1;
drop table IF EXISTS res_t1;
drop table IF EXISTS res_t2;
@@ -23189,6 +23251,8 @@ END//
CALL h1();
done @x
1 1
+Warnings:
+Error 1339 Case not found for CASE statement
DROP PROCEDURE IF EXISTS h1;
drop table IF EXISTS res_t1;
drop table IF EXISTS res_t2;
@@ -23219,6 +23283,8 @@ END//
CALL h1();
done @x
1 1
+Warnings:
+Error 1339 Case not found for CASE statement
DROP PROCEDURE IF EXISTS h1;
drop table IF EXISTS res_t1;
drop table IF EXISTS res_t2;
@@ -23247,6 +23313,8 @@ set @x=1;
SELECT done, @x;
END//
CALL h1();
+Warnings:
+Error 1339 Case not found for CASE statement
DROP PROCEDURE IF EXISTS h1;
drop table IF EXISTS res_t1;
drop table IF EXISTS res_t2;
@@ -23275,6 +23343,8 @@ set @x=1;
SELECT done, @x;
END//
CALL h1();
+Warnings:
+Error 1339 Case not found for CASE statement
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
DROP TABLE IF EXISTS res_t2;
@@ -23423,6 +23493,9 @@ CREATE TABLE res_t1(w CHAR, x CHAR);
INSERT INTO res_t1 VALUES('a', 'b');
INSERT INTO res_t1 VALUES('c', 'd');
CALL h1();
+Warnings:
+Warning 1265 Data truncated for column 'w' at row 1
+Warning 1265 Data truncated for column 'x' at row 1
SELECT @done, @x;
@done @x
1 1
@@ -23445,6 +23518,9 @@ CREATE TABLE res_t1(w CHAR, x CHAR);
INSERT INTO res_t1 VALUES('a', 'b');
INSERT INTO res_t1 VALUES('c', 'd');
CALL h1();
+Warnings:
+Warning 1265 Data truncated for column 'w' at row 1
+Warning 1265 Data truncated for column 'x' at row 1
SELECT @done, @x;
@done @x
1 1
diff --git a/mysql-test/suite/rpl/r/rpl_row_sp005.result b/mysql-test/suite/rpl/r/rpl_row_sp005.result
index 01e1970e0df..58c53b394b2 100644
--- a/mysql-test/suite/rpl/r/rpl_row_sp005.result
+++ b/mysql-test/suite/rpl/r/rpl_row_sp005.result
@@ -77,6 +77,8 @@ id2
< ---- Master selects-- >
-------------------------
CALL test.p1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
SELECT * FROM test.t3 ORDER BY id3;
id3 c
1 MySQL
diff --git a/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result b/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result
index 6792a701577..079e750e2eb 100644
--- a/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result
+++ b/mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result
@@ -38,6 +38,8 @@ INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '
END|
CALL p2();
CALL p1();
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP TABLE t1;
DROP TABLE t2;
DROP PROCEDURE p1;
diff --git a/mysql-test/suite/rpl/r/rpl_row_trig003.result b/mysql-test/suite/rpl/r/rpl_row_trig003.result
index 43c2ecde2b4..131af933b41 100644
--- a/mysql-test/suite/rpl/r/rpl_row_trig003.result
+++ b/mysql-test/suite/rpl/r/rpl_row_trig003.result
@@ -69,9 +69,15 @@ INSERT INTO test.t2 VALUES(NULL,0,'Testing MySQL databases is a cool ', 'MySQL C
UPDATE test.t1 SET b1 = 0 WHERE b1 = 1;
INSERT INTO test.t2 VALUES(NULL,1,'This is an after update test.', 'If this works, total will not be zero on the master or slave',1.4321,5.221,0,YEAR(NOW()),NOW());
UPDATE test.t2 SET b1 = 0 WHERE b1 = 1;
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
INSERT INTO test.t1 VALUES(NULL,1,'add some more test data test.', 'and hope for the best', 3.321,5.221,0,YEAR(NOW()),NOW());
DELETE FROM test.t1 WHERE id = 1;
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DELETE FROM test.t2 WHERE id = 1;
+Warnings:
+Error 1329 No data - zero rows fetched, selected, or processed
DROP TRIGGER test.t1_bi;
DROP TRIGGER test.t2_ai;
DROP TRIGGER test.t1_bu;
diff --git a/mysql-test/t/signal.test b/mysql-test/t/signal.test
index bdb6625ba32..4c8e6159371 100644
--- a/mysql-test/t/signal.test
+++ b/mysql-test/t/signal.test
@@ -2408,6 +2408,7 @@ BEGIN
DECLARE EXIT HANDLER FOR x
BEGIN
SELECT '2';
+ SHOW WARNINGS;
RESIGNAL x SET MYSQL_ERRNO = 9999;
END;
@@ -2415,9 +2416,10 @@ 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;
@@ -2431,6 +2433,7 @@ BEGIN
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;
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 247c84fda39..90b4c18895a 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -649,3 +649,56 @@ END//
DELIMITER ;//
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;
+
+--echo #
+--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
+--echo #
+
+--echo
+--echo # - Case 4: check that "No Data trumps Warning".
+--echo
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+SET SESSION debug="+d,bug23032_emit_warning";
+CALL p1();
+SET SESSION debug="-d,bug23032_emit_warning";
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index c8b2595e23d..13ca55a0127 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -2543,3 +2543,273 @@ DROP TABLE t1;
--echo End of 5.1 tests
+--echo #
+--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
+--echo #
+
+--echo
+--echo # - Case 1
+--echo
+
+--disable_warnings
+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;
+--enable_warnings
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+CALL p1();
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+--echo
+--echo # - Case 2
+--echo
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+CALL p1();
+CALL p6();
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+DROP PROCEDURE p5;
+DROP PROCEDURE p6;
+
+--echo
+--echo # - Case 3: check that "Exception trumps No Data".
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+CALL p1();
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#36185: Incorrect precedence for warning and exception handlers
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT, b INT NOT NULL);
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+CALL p1();
+
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
+--echo #
+
+--echo
+--echo # - Case 1
+--echo
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1, 2);
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+UPDATE t1 SET b = 20;
+
+SHOW WARNINGS;
+
+SELECT * FROM t1;
+
+DROP TRIGGER t1_bu;
+DROP TABLE t1;
+
+--echo
+--echo # - Case 2
+--echo
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(b CHAR(1));
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+INSERT INTO t1 VALUES(0);
+
+SHOW WARNINGS;
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TRIGGER t1_bi;
+DROP TABLE t1;
+DROP TABLE t2;
+
+--echo #
+--echo # Bug#9857: Stored procedures: handler for sqlwarning ignored
+--echo #
+
+SET @sql_mode_saved = @@sql_mode;
+SET sql_mode = traditional;
+
+delimiter |;
+
+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|
+
+delimiter ;|
+
+CALL p1();
+SHOW WARNINGS;
+
+CALL p2();
+SHOW WARNINGS;
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+SET sql_mode = @sql_mode_saved;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index da949016a03..11edeaf9811 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5432,6 +5432,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|
--enable_warnings
create table t3 (id int not null)|
@@ -5461,7 +5463,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|
@@ -5472,16 +5474,37 @@ begin
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()|
call bug15231_3()|
+call bug15231_5()|
-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|
+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|
#