WL#2265: Stored Procedures: Implement RESIGNAL

MySQL will implement the standard SQL (SQL:2008) RESIGNAL
statement, which is used to pass forward an error or warning 
condition which is trapped in a handler of a compound
statement.

This is a subtask needed for stored procedures. It was 
decided at the Malta conference that this feature does 
not need to be implemented for the MySQL 5.0 release. 
For a while we said "it is now due for 5.1". Now we say
it is on the roadmap as a must-have item for 6.x.

Assume that WL#2110 (SIGNAL) will happen first.

Rationale
---------

Standard requirement:
RESIGNAL is part of SQL:2008 non-core feature P002
Computational Completeness. Additionally the standard
requires support of "implicit RESIGNAL" but that is
not one of the requirements for this task.

Needed functionality:
Right now there's no way to both handle an error
and return the error message -- by executing an SQL
statement within the handler, we destroy the
information that caused the handler's activation.
Also, it would make some procedures shorter if one
could handle part of a situation, then pass the
condition 'up the line' to another handler.

Compatibility:
IBM DB2 supports RESIGNAL in the standard-SQL manner.
Oracle doesn't support RESIGNAL syntax, but does provide 
similar functionality: use RAISE without an exception
name to "reraise an exception".
SQL Server does not support an equivalent functionality
other than the RAISERROR  statement, which
is closer to SIGNAL than to RESIGNAL.

In the Santa Cruz Dev-Mt Offsite meeting (2006-08), Brian
Aker and Monty Widenius made some time estimates and statements about
dependencies (see "Progress Reports" for details).
Marc Alff may have different estimates.

RESIGNAL passes on the condition (error or warning) information
that is available during execution of a condition handler
inside a compound statement inside a stored procedure, function,
trigger, or event. RESIGNAL may change some information
before passing it on.

Syntax
------

The succinct explanation is:
RESIGNAL [  |  ] [ SET  ];
which causes three possibilities that we will treat in separate sections.

RESIGNAL alone:
RESIGNAL;

RESIGNAL with new signal information:
RESIGNAL SET ;

RESIGNAL with condition name or sqlstate value, and possibly new signal information:
RESIGNAL {  |  } [ SET  ];

There are explanations of , ,
, and  in
WL#2110 Stored Procedures: Implement SIGNAL. Unless otherwise
stated, the rules for those items are the same as in WL#2110.

Diagnostics areas
-----------------

A diagnostics area contains one or more condition areas.
A condition area contains condition information items,
such as MYSQL_ERRNO or MESSAGE_TEXT.

In standard SQL there is a stack of diagnostics areas.
When a handler takes control, it pushes the top of the stack.
So during handler execution there are two diagnostics areas:
1. The current diagnostics area, which starts as a copy of the
last diagnostics area, but will be overwritten by the first
procedure statement in the handler.
2. The last diagnostics area, which has the condition areas that
were set up before the handler took control.

How many diagnostics areas can be in the diagnostics area stack?
The implementor will decide. A fixed limit is okay,
provided that it's documented or discoverable.

How many condition areas can be in one diagnostics area?
We answer this question by saying that, in principle, we
want the condition area list to be analogous to existing
MySQL structures. Therefore the maximum is @@max_error_count.

RESIGNAL alone
--------------

The simple statement
RESIGNAL;
means "pass on with no change".
Specifically: restore the last diagnostics area and make it
the current diagnostics area, that is, "pop" the diagnostics
area stack.

For example:
DELIMITER //
SET @error_count = 0//
SET @a = 0//
DROP TABLE IF EXISTS xx//
DROP PROCEDURE IF EXISTS p//
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL; END IF;
    END;
  DROP TABLE xx;
  END//
CALL p()//
The "DROP TABLE xx;" statement fails.
The diagnostics area stack looks like this:
"1. ERROR 1051 (42S02): Unknown table 'xx'".
Now we enter the exit handler.
It starts by pushing the top of the stack.
The diagnostics area stack now looks like this:
"1. ERROR 1051 (42S02): Unknown table 'xx'
 2. ERROR 1051 (42S02): Unknown table 'xx'".
Usually a procedure statement clears the first diagnostics
area (which is also called the "current" diagnostics area).
BEGIN is an exception, it does not clear, it does nothing.
SET is not an exception, it clears, performs the operation,
and then, since it inevitably succeeds, says "success".
The diagnostics area stack now looks like this:
"1. ERROR 0000 (00000): Successful operation
 2. ERROR 1051 (42S02): Unknown table 'xx'".
At this point, if @a = 0, RESIGNAL pops the stack.
The diagnostics area stack now looks like this:
"1. ERROR 1051 (42S02): Unknown table 'xx'".
And that is what the caller sees.
Otherwise, the handler simply ends, which means that
there is no more use for the last diagnostics area
(it has been "handled"), so it can be thrown away.
The diagnostics area stack now looks like this:
"1. ERROR 0000 (00000): Successful operation".

The details make it look complex, but the end users should
like the end result: handlers can execute without
destroying information about the condition that caused
activation of the handler.

RESIGNAL with new signal information
------------------------------------

The statement
RESIGNAL SET ;
means "pass on with change".

As with RESIGNAL alone, the idea is to pop the diagnostics
area stack so that the original information (the information
that existed before entry into the handler) will go out.
With this difference: anything specified in 
changes.

For example:
DELIMITER //
SET @error_count = 0//
DROP TABLE IF EXISTS xx//
DROP PROCEDURE IF EXISTS p//
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SET MESSAGE_TEXT = '!'; END IF;
    END;
  DROP TABLE xx;
  END//
CALL p()//

Remember from the previous section that RESIGNAL alone causes
"1. ERROR 1051 (42S02): Unknown table 'xx'".
But "RESIGNAL SET MESSAGE_TEXT = '!';" causes
"1. ERROR 1051 (42S02): !".
In other words, it changes the message text, and nothing else.

A user could change all the signal information items,
making the first condition area of the diagnostics area
look quite different.

RESIGNAL with condition name or sqlstate value, and possibly new signal information
-----------------------------------------------------------------------------------

The complex statement
RESIGNAL {  |  } [ SET  ]
means "push a condition and pass on with possible change".
Specifically: restore the last diagnostics area and make it
the current diagnostics area, that is, "pop" the diagnostics
area stack. So far, it's the same as what a simple RESIGNAL;
would do: But also: change the diagnostics area depending on
the condition name, sqlstate value, or signal information.

DELIMITER //
SET @error_count = 0//
SET @a = 0//
SET @@max_error_count = 2//
DROP TABLE IF EXISTS xx//
DROP PROCEDURE IF EXISTS p//
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
    END;
  DROP TABLE xx;
  END//
CALL p()//
SHOW ERRORS//

This is the same as the example in section "RESIGNAL without argument",
and the effects are the same, except that if RESIGNAL happens
the current condition area looks different at the end.

In standard SQL, RESIGNAL "pushes" a new condition area.
So at the end the diagnostics area looks like this:
"1. (condition 1) ERROR 5 (45000) Unknown table 'xx'
    (condition 2) ERROR 1051 (42S02): Unknown table 'xx'".

So the result of "CALL p()//" and "SHOW ERRORS//" for this example is:
mysql> CALL p()//
ERROR 5 (45000): Unknown table 'xx'
mysql> show errors//
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error |    5 | Unknown table 'xx'               |
| Error | 1051 | Unknown table 'xx'               |
+-------+------+----------------------------------+
2 rows in set (0.00 sec)

(As discussed elsewhere, the order is reversed in
the current MySQL implementation.)

Note that here we have the old error and a new error in the list.
This deviates from the standard, where a message number should've been
changed in *both* errors, but sqlstate - only in the second one.

Neither Mimer nor DB2 support this exactly as specified in the standard.

Peter believes that our behavior is more logical and more useful, and
suspects an error in the standard. As a Sun delegate in the ANSI
committee he hopes we can suggest a correction.

Handler not active
------------------

RESIGNAL is illegal if a handler is not active.
The appropriate error message is:
0K000 "resignal when handler not active".

For example:
CREATE PROCEDURE p () RESIGNAL; /* no error */
CALL p();                       /* error: 0K000 */

But here is a more difficult example:
DELIMITER //
CREATE FUNCTION f () RETURNS INT
BEGIN
  RESIGNAL;
  RETURN 5;
  END//
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f();
  SIGNAL SQLSTATE '55555';
  END//
At the time the RESIGNAL happens, there is a handler,
even though the RESIGNAL is not inside the handler.

Implicit RESIGNAL
-----------------

This section is informative, it does not affect implementation.

In standard SQL, technically, most statements cause raising of
conditions, but not activation of handlers. RESIGNAL causes
activation of handlers. For example, the statement
INSERT INTO no_such_table VALUES (no_such_variable);
has two errors: nonexistent table and nonexistent variable.
DBMSs can "raise" both these errors, that is, they don't
have to stop after seeing no_such_table, they can keep
filling up condition areas. But when it's all over, there
is an "implicit RESIGNAL". This is a short way to indicate
that the rules for handler activation take place.

In fact MySQL does not have to execute an implicit RESIGNAL.
It merely has to accomplish the same effect as if there was
an implicit RESIGNAL.

Walk through the SQL Standard
-----------------------------
The attached file walkthrough_wl2265.txt (Private, MySQL internal
document) is an explanatory appendix for MySQL people who care how
well MySQL's RESIGNAL statement complies with SQL:2008.
The implementor should consider it to be part of the specification.

With Marc assenting and nobody dissenting, Peter decided that
MySQL must depart from the SQL:2008 standard description thus:
For 9075-4 17.3 , do General Rule 5)
before General Rule 4). Thus if both 
and  are specified, the new signal information will
end up in condition area #1.

Other apparent differences between MySQL and other
possible implementations are easy to explain:
* Peter Gulutzan believes the errors are in the wrong
order but there was a decision not to change SHOW
WARNINGS for the sake of SIGNAL/RESIGNAL work.
* Peter maintains that when we 'push' the condition area
for General Rule 5), we do not reset MESSAGE_TEXT.
As he's noted, it depends how one interprets the
words "All occupied condition areas in DA are
stacked such that the i-th condition area is placed
at the position of the i+1-st condition area in DA."
Peter is sure that either interpretation is correct.
There was no objection to using the interpretation
that we do not clear.

Privileges
----------

No privileges required.

Reserved words
--------------

RESIGNAL

Example
-------

DELIMITER //
CREATE PROCEDURE p ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    SELECT 'MAXIMUM SMALLINT VALUE is 32767';
    RESIGNAL;
    END;
  CREATE TABLE t (s1 SMALLINT);
  INSERT INTO t VALUES (32769); /* "out of range" */
  END//
CALL p()//
Result: warning 1264 "Out of range value for column 's1' at row 1"
is caught by the handler, which displays a message.
Assuming that SELECT does not return an error, the caller sees:
mysql> call p()//
+---------------------------------+
| MAXIMUM SMALLINT VALUE is 32767 |
+---------------------------------+
| MAXIMUM SMALLINT VALUE is 32767 |
+---------------------------------+
Query OK, 1 row affected, 1 warning (0.00 sec)
and SHOW WARNINGS will show:
mysql> show warnings//
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

Example
-------

DELIMITER //
CREATE PROCEDURE p1 ()
BEGIN
  DECLARE x CONDITION FOR SQLSTATE '42000';
  DECLARE EXIT HANDLER FOR x
  BEGIN
    SELECT '2';
    RESIGNAL x SET MYSQL_ERRNO = 999;
    END;
  BEGIN
    DECLARE EXIT HANDLER FOR x
    BEGIN
      SELECT '1';
      RESIGNAL x SET SCHEMA_NAME = 'test';
      END;
    SET @@sql_mode=NULL;
    END;
  END//
CREATE PROCEDURE p2 ()
BEGIN
  DECLARE x CONDITION FOR SQLSTATE '42000';
  DECLARE EXIT HANDLER FOR x
  BEGIN
    SELECT '3';
    RESIGNAL x SET MESSAGE_TEXT = 'Hi, I am a useless error message';
    END;
  CALL p1();
  END//
CALL p2()//
Result: the user sees '1', then '2', then '3', then
ERROR 999 (42000): Hi, I am a useless error message
(The actual error number will not be '999' but will be fixed when
the feature is in the main mysql-6.0 tree.)
Notice the effects of handlers within handlers, of
changes in scope for condition names, and how the
condition information keeps going up the line until
eventually it's seen by the caller. The original
error message, "Variable 'sql_mode' can't be set to
the value of 'NULL'", is lost.

References
----------

WL#2110 Stored Procedures: Implement SIGNAL

DB2 manual for RESIGNAL:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005654.htm