Backport of Bug#36649 to mysql-next-mr

------------------------------------------------------------
revno: 2630.39.3
revision-id: davi.arnaut@sun.com-20081210215359-i876m4zgc2d6rzs3
parent: kostja@sun.com-20081208222938-9es7wl61moli71ht
committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
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.

1 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 1a6494e03ad..47b50b233b3 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -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;