# At a local mysql-5.5-next-mr-runtime repository of davi
2915 Davi Arnaut 2009-11-10
Backport of Bug#36649 to mysql-next-mr
------------------------------------------------------------
revno: 2630.39.3
revision-id: davi.arnaut@stripped
parent: kostja@stripped
committer: Davi Arnaut <Davi.Arnaut@stripped>
branch nick: 36649-6.0
timestamp: Wed 2008-12-10 19:53:59 -0200
message:
Bug#36649: Condition area is not properly cleaned up after stored routine invocation
The problem is that the diagnostics area of a trigger is not
isolated from the area of the statement that caused the trigger
invocation. In MySQL terms, it means that warnings generated
during the execution of the trigger are not removed from the
"warning area" at the end of the execution.
Before this fix, the rules for MySQL message list life cycle (see
manual entry for SHOW WARNINGS) did not apply to statements
inside stored programs:
- The manual says that the list of messages is cleared by a
statement that uses a table (any table). However, such
statement, if run inside a stored program did not clear the
message list.
- The manual says that the list is cleared by a statement that
generates a new error or a warning, but this was not the case
with stored program statements either and is changed to be the
case as well.
In other words, after this fix, a statement has the same effect
on the message list regardless of whether it's executed inside a
stored program/sub-statement or not.
This introduces an incompatible change:
- before this fix, a, e.g. statement inside a trigger could
never clear the global warning list
- after this fix, a trigger that generates a warning or uses a
table, clears the global warning list
- however, when we leave a trigger or a function, the caller's
warning information is restored (see more on this below).
This change is not backward compatible as it is intended to make
MySQL behavior similar to the SQL standard behavior:
A stored function or trigger will get its own "warning area" (or,
in standard terminology, diagnostics area). At the beginning of
the stored function or trigger, all messages from the caller area
will be copied to the area of the trigger. During execution, the
message list will be cleared according to the MySQL rules
described on the manual (SHOW WARNINGS entry). At the end of the
function/trigger, the "warning area" will be destroyed along with
all warnings it contains, except that if the last statement of
the function/trigger generated messages, these are copied into
the "warning area" of the caller.
Consequently, statements that use a table or generate a warning
*will* clear warnings inside the trigger, but that will have no
effect to the warning list of the calling (outer) statement.
@ mysql-test/r/sp.result
Fix test case results.
@ mysql-test/r/trigger.result
Fix test case results.
@ mysql-test/t/sp.test
Add test case for Bug#36649
@ mysql-test/t/trigger.test
Add test case for Bug#36649
@ sql/sp_head.cc
Emulate multiple warning areas -- one per stored program instance.
@ sql/sql_parse.cc
Message list reset rules are the same for statements inside
or outside compound statements.
modified:
mysql-test/r/sp.result
mysql-test/r/trigger.result
mysql-test/t/sp.test
mysql-test/t/trigger.test
sql/sp_head.cc
sql/sql_parse.cc
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result 2009-11-02 11:10:04 +0000
+++ b/mysql-test/r/sp.result 2009-11-10 18:08:44 +0000
@@ -6941,6 +6941,101 @@ SELECT * FROM t1 WHERE a = f1();
ERROR 42S02: Table 'test.t_non_existing' doesn't exist
DROP FUNCTION f1;
DROP TABLE t1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1(a INT, b CHAR)
+BEGIN
+IF a > 0 THEN
+CALL p1(a-1, 'ab');
+ELSE
+SELECT 1;
+END IF;
+END|
+SET @save_max_sp_recursion= @@max_sp_recursion_depth;
+SET @@max_sp_recursion_depth= 5;
+CALL p1(4, 'a');
+1
+1
+Warnings:
+Warning 1265 Data truncated for column 'b' at row 1
+Warning 1265 Data truncated for column 'b' at row 1
+Warning 1265 Data truncated for column 'b' at row 1
+Warning 1265 Data truncated for column 'b' at row 1
+SET @@max_sp_recursion_depth= @save_max_sp_recursion;
+DROP PROCEDURE p1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1(a CHAR)
+BEGIN
+SELECT 1;
+SELECT CAST('10 ' as UNSIGNED INTEGER);
+SELECT 1;
+END|
+CALL p1('data truncated parameter');
+1
+1
+CAST('10 ' as UNSIGNED INTEGER)
+10
+1
+1
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+Warning 1292 Truncated incorrect INTEGER value: '10 '
+DROP PROCEDURE p1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+CREATE PROCEDURE p1()
+CALL p2()|
+CREATE PROCEDURE p2()
+CALL p3()|
+CREATE PROCEDURE p3()
+CALL p4()|
+CREATE PROCEDURE p4()
+BEGIN
+SELECT 1;
+SELECT CAST('10 ' as UNSIGNED INTEGER);
+SELECT 2;
+END|
+CALL p1();
+1
+1
+CAST('10 ' as UNSIGNED INTEGER)
+10
+2
+2
+Warnings:
+Warning 1292 Truncated incorrect INTEGER value: '10 '
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a CHAR(2));
+INSERT INTO t1 VALUES ('aa');
+CREATE FUNCTION f1() RETURNS CHAR
+RETURN (SELECT f2())|
+CREATE FUNCTION f2() RETURNS CHAR
+RETURN (SELECT f3())|
+CREATE FUNCTION f3() RETURNS CHAR
+RETURN (SELECT f4())|
+CREATE FUNCTION f4() RETURNS CHAR
+BEGIN
+RETURN (SELECT a FROM t1);
+END|
+SELECT f1();
+f1()
+a
+Warnings:
+Warning 1265 Data truncated for column 'f4()' at row 1
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+DROP TABLE t1;
#
# Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non
# strict SQL mode
=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result 2009-10-12 10:59:55 +0000
+++ b/mysql-test/r/trigger.result 2009-11-10 18:08:44 +0000
@@ -2115,3 +2115,50 @@ s1
DELETE FROM t1;
DROP TABLE t1;
DROP TEMPORARY TABLE t2;
+DROP TRIGGER IF EXISTS trg1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+DECLARE a CHAR;
+SELECT 'ab' INTO a;
+SELECT 'ab' INTO a;
+SELECT 'a' INTO a;
+END|
+INSERT INTO t1 VALUES (1);
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+DROP TRIGGER trg1;
+DROP TABLE t1;
+DROP TRIGGER IF EXISTS trg1;
+DROP TRIGGER IF EXISTS trg2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+DECLARE trg1 CHAR;
+SELECT 'ab' INTO trg1;
+END|
+CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+DECLARE trg2 CHAR;
+SELECT 'ab' INTO trg2;
+END|
+INSERT INTO t1 VALUES (0);
+Warnings:
+Warning 1265 Data truncated for column 'trg1' at row 1
+Warning 1265 Data truncated for column 'trg2' at row 1
+SELECT * FROM t1;
+a
+0
+SHOW WARNINGS;
+Level Code Message
+INSERT INTO t1 VALUES (1),(2);
+Warnings:
+Warning 1265 Data truncated for column 'trg1' at row 1
+Warning 1265 Data truncated for column 'trg2' at row 1
+Warning 1265 Data truncated for column 'trg1' at row 1
+Warning 1265 Data truncated for column 'trg2' at row 1
+DROP TRIGGER trg1;
+DROP TRIGGER trg2;
+DROP TABLE t1;
=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test 2009-11-02 11:10:04 +0000
+++ b/mysql-test/t/sp.test 2009-11-10 18:08:44 +0000
@@ -8295,6 +8295,119 @@ SELECT * FROM t1 WHERE a = f1();
DROP FUNCTION f1;
DROP TABLE t1;
+#
+# Bug#36649: Condition area is not properly cleaned up after stored routine invocation
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1(a INT, b CHAR)
+BEGIN
+ IF a > 0 THEN
+ CALL p1(a-1, 'ab');
+ ELSE
+ SELECT 1;
+ END IF;
+END|
+delimiter ;|
+
+SET @save_max_sp_recursion= @@max_sp_recursion_depth;
+SET @@max_sp_recursion_depth= 5;
+CALL p1(4, 'a');
+SET @@max_sp_recursion_depth= @save_max_sp_recursion;
+
+DROP PROCEDURE p1;
+
+#
+# Ensure that rules for message list clean up are being respected.
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1(a CHAR)
+BEGIN
+ SELECT 1;
+ SELECT CAST('10 ' as UNSIGNED INTEGER);
+ SELECT 1;
+END|
+delimiter ;|
+
+CALL p1('data truncated parameter');
+
+DROP PROCEDURE p1;
+
+#
+# Cascading stored procedure/function calls.
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1()
+ CALL p2()|
+CREATE PROCEDURE p2()
+ CALL p3()|
+CREATE PROCEDURE p3()
+ CALL p4()|
+CREATE PROCEDURE p4()
+BEGIN
+ SELECT 1;
+ SELECT CAST('10 ' as UNSIGNED INTEGER);
+ SELECT 2;
+END|
+delimiter ;|
+
+CALL p1();
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a CHAR(2));
+
+INSERT INTO t1 VALUES ('aa');
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS CHAR
+ RETURN (SELECT f2())|
+CREATE FUNCTION f2() RETURNS CHAR
+ RETURN (SELECT f3())|
+CREATE FUNCTION f3() RETURNS CHAR
+ RETURN (SELECT f4())|
+CREATE FUNCTION f4() RETURNS CHAR
+BEGIN
+ RETURN (SELECT a FROM t1);
+END|
+delimiter ;|
+
+SELECT f1();
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+DROP TABLE t1;
+
--echo #
--echo # Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non
--echo # strict SQL mode
=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test 2009-10-12 10:59:55 +0000
+++ b/mysql-test/t/trigger.test 2009-11-10 18:08:44 +0000
@@ -2425,3 +2425,67 @@ DELETE FROM t1;
DROP TABLE t1;
DROP TEMPORARY TABLE t2;
+
+#
+# Bug#36649: Condition area is not properly cleaned up after stored routine invocation
+#
+
+--disable_warnings
+DROP TRIGGER IF EXISTS trg1;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+
+delimiter |;
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ DECLARE a CHAR;
+ SELECT 'ab' INTO a;
+ SELECT 'ab' INTO a;
+ SELECT 'a' INTO a;
+END|
+delimiter ;|
+
+INSERT INTO t1 VALUES (1);
+
+DROP TRIGGER trg1;
+DROP TABLE t1;
+
+#
+# Successive trigger actuations
+#
+
+--disable_warnings
+DROP TRIGGER IF EXISTS trg1;
+DROP TRIGGER IF EXISTS trg2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+
+delimiter |;
+
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ DECLARE trg1 CHAR;
+ SELECT 'ab' INTO trg1;
+END|
+
+CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+ DECLARE trg2 CHAR;
+ SELECT 'ab' INTO trg2;
+END|
+
+delimiter ;|
+
+INSERT INTO t1 VALUES (0);
+SELECT * FROM t1;
+SHOW WARNINGS;
+INSERT INTO t1 VALUES (1),(2);
+
+DROP TRIGGER trg1;
+DROP TRIGGER trg2;
+DROP TABLE t1;
+
=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc 2009-11-02 14:17:14 +0000
+++ b/sql/sp_head.cc 2009-11-10 18:08:44 +0000
@@ -1083,7 +1083,6 @@ sp_head::execute(THD *thd)
Item_change_list old_change_list;
String old_packet;
Reprepare_observer *save_reprepare_observer= thd->m_reprepare_observer;
-
Object_creation_ctx *saved_creation_ctx;
Warning_info *saved_warning_info, warning_info(thd->warning_info->warn_id());
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-11-02 11:10:04 +0000
+++ b/sql/sql_parse.cc 2009-11-10 18:08:44 +0000
@@ -2065,7 +2065,6 @@ mysql_execute_command(THD *thd)
A better approach would be to reset this for any commands
that is not a SHOW command or a select that only access local
variables, but for now this is probably good enough.
- Don't reset warnings when executing a stored routine.
*/
if ((sql_command_flags[lex->sql_command] & CF_DIAGNOSTIC_STMT) != 0)
thd->warning_info->set_read_only(TRUE);
Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20091110180844-jxelzrotxt2hiwpy.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5-next-mr-runtime branch (davi:2915) Bug#36649 | Davi Arnaut | 10 Nov |
