WL#2110: Stored Procedures: Implement SIGNAL

MySQL will implement the standard SQL (SQL:2003) SIGNAL
statement, which is used to signal a rule violation
(i.e. an error or a warning) during routine execution.

History:
Discussion of SIGNAL has been going on since 2003.
At the Malta conference it was said to be "due for 5.1".
In the Santa Cruz Dev-Mt Offsite meeting (2006-08), Brian
and Monty made some time estimates and statements about
dependencies (see "Progress Reports" for details).
In the Customer Advisory Board meeting (2006-12), customers
said that SIGNAL even without RESIGNAL would be useful.

Rationale:
There are feature requests. It's in the standard.
It's been part of the plan for some time, and it's
on the roadmap's "must have" list now for version 6.x.
There's no other good user-controlled way to report errors.

Compatibility:
IBM DB2 supports SIGNAL in the standard-SQL manner.
Sybase SQL Anywhere supports it in a slightly less standard manner.
Oracle and SQL Server don't support SIGNAL, but do 
provide similar functionality for diagnostic checking 
within stored procedures (Oracle provides the 
RAISE  statement; SQL Server the 
RAISERROR  statement).

We don't need yet to store all signal information in the
diagnostics area in the server, since the only way to retrieve that
data is with GET DIAGNOSTICS (WL#2111), which might come later.
The WL dependencies are:
- SIGNAL can be implemented by itself,
- RESIGNAL depends on SIGNAL
- GET DIAGNOSTICS depends on both SIGNAL and RESIGNAL for testing reasons.

Function
--------

SIGNAL is the way that we say "return error" or
"return warning" in standard SQL.
Without it, users have to resort to measures
like "deliberately refer to a nonexistent table" in
order to cause a routine to return an error. That's
inelegant, and gives too little control over the
error's characteristics (errno, type, sqlstate, message).
SIGNAL provides error/warning information to a handler,
to an outer portion of the application, or to the client.

It's part of ANSI/ISO non-core Feature P002 Computational completeness.

Syntax
------

 ::=
SIGNAL {  |  } [ SET  ]

 ::=
SQLSTATE [VALUE] 

 ::=
 [ { , }... ]

 ::=
 = 

 ::=
{   CLASS_ORIGIN | SUBCLASS_ORIGIN | CONSTRAINT_CATALOG 
  | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME
  | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
  | MESSAGE_TEXT | MYSQL_ERRNO }

The SET clause is optional. If it is included, it may contain
multiple signal items, in a comma-delimited list of  =  pairs. The standard document says
" = ".
For us that will mean variables (DECLAREd variables, @variables,
@@variables), parameters (i.e. input parameters of functions
or procedures), and literals, and not NULLs. We discussed allowing
 of any sort as a MySQL extension, but that will not
happen. By the way, a character literal may include an _introducer.

Each  may be specified only once
in the SET clause. Otherwise, the implementor will decide an
appropriate error. Tentatively the implementor has decided on
"Duplicate condition information item: ...".

SIGNAL condition_name is illegal unless the condition is defined
with SQLSTATE. In other words:
DECLARE x CONDITION FOR 1234;
SIGNAL x;
will cause an error. The implementor will decide what the error is.
Tentatively the implementor has decided on
"SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE".

Examples:
CREATE PROCEDURE p ()
BEGIN
  DECLARE specialty CONDITION FOR SQLSTATE '12345';
  IF @a = 0 THEN SIGNAL SQLSTATE VALUE '12345'; END IF;
  IF @a = 1 THEN SIGNAL specialty; END IF;
  IF @a = 2 THEN SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Hi!'; END IF;
  SIGNAL SQLSTATE '03000' SET MESSAGE_TEXT = 'Hi!', MYSQL_ERRNO = 12345;
  END//

Extension: SIGNAL is legal outside compound statements
------------------------------------------------------

Technically, SIGNAL is non-preparable so it could only
appear within a compound statement (BEGIN/END block), which
would limit its use to within a stored procedure, function, trigger,
or event. (Or a dynamic compound statement, see WL#3696.)
However, we will allow SIGNAL outside compound statements.

So these statements are legal right after a mysql-client prompt:
SIGNAL SQLSTATE '77777';
CREATE TRIGGER t BEFORE INSERT ON t FOR EACH ROW SIGNAL SQLSTATE '77777';
CREATE EVENT e ON SCHEDULE EVERY 1 SECOND DO SIGNAL SQLSTATE '77777';

That does not mean
PREPARE stmt1 FROM 'SIGNAL ...';
is legal. "Preparing any statement" is a separate worklog task.

Rules
-----

If the statement is:
SIGNAL ;
then the  specified has to be a condition that 
was declared in some scope that applies to the SIGNAL statement. 
If the  is declared multiple times, the 
declaration with the most local scope applies. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
  IF divisor = 0 THEN
    BEGIN
      DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
      SIGNAL divide_by_zero;
      END;
    END IF;
  END

If the statement is:
SIGNAL ;
then the SQLSTATE specified will be used to signal the condition
specified. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
  IF divisor = 0 THEN 
    SIGNAL SQLSTATE '22012';
    END IF;
  END
The SQLSTATE value should not start with '00'. Otherwise, the
implementor will decide an appropriate error. Tentatively the
implementor has chosen "Bad SQLSTATE ...".

When SIGNAL is executed, it has five effects, in the following 
order:

   (1) First, the diagnostics area is completely cleared.
          So if the SIGNAL is in a DECLARE HANDLER then any
          pending errors or warnings are gone. So is 'row count'.

   (2) Second, certain items in the diagnostics area are set 
          to specific values, as follows:
          a) NUMBER is set to 1 (one).
          b) MORE is set to 'N'.
          c) COMMAND_FUNCTION is set to 'SIGNAL'.
          d) DYNAMIC_FUNCTION is set to a zero-length string.
          e) CLASS_ORIGIN, SUBCLASS_ORIGIN, CONSTRAINT_CATALOG,
            CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME,
            TABLE_NAME, COLUMN_NAME, CURSOR_NAME, and MESSAGE_TEXT are each
            set to a zero-length string.
          f) MESSAGE_LENGTH and MESSAGE_OCTET_LENGTH are each set to 0 (zero).
          /* MySQL doesn't yet use any of the above, except "message"
             so the implementor may ignore this. */

   (3) Third, if the statement is "SIGNAL ", 
          then the CONDITION_IDENTIFIER value for the first condition area 
          within the diagnostics area is set to ; otherwise
          CONDITION_IDENTIFIER is set to a zero-length string.
          /* MySQL doesn't yet use CONDITION_IDENTIFIER
             so the implementor may ignore this. */

   (4) Fourth, if the statement is "SIGNAL ",
          or with a  that corresponds to an SQLSTATE value 
          (e.g. with DECLARE divide_by_zero CONDITION FOR '22012'), then the 
          RETURNED_SQLSTATE value for the first condition area within the 
          diagnostics area is set to that SQLSTATE; otherwise 
          RETURNED_SQLSTATE is set to a zero-length string.
          /* This is always the case, in our implementation. */

   (5) Fifth, an implicit RESIGNAL statement is executed, causing the
          signaled exception condition to be raised in the diagnostics
          area.
      -- (a) If SIGNAL includes a SET clause, then the RESIGNAL 
              statement executed is: 
              RESIGNAL SET ;
      -- Otherwise, the RESIGNAL statement executed is:
              RESIGNAL;
         /* This is just "for the record". The idea of the implicit
            RESIGNAL is merely that: that's how handlers are activated
            in standard SQL. The implementor may ignore this. */

Condition information items: descriptions
-----------------------------------------

All condition information items except MYSQL_ERRNO are "standard".
MYSQL_ERRNO is a MySQL extension.
MYSQL_ERRNO and MESSAGE_TEXT are the only items that users see in
error returns. The other items are settable, but the settings
have no effect on anything except GET DIAGNOSTICS.

item name             Why             Definition
---------             ---             ----------
CLASS_ORIGIN          standard        VARCHAR(64)
SUBCLASS_ORIGIN       standard        VARCHAR(64)
CONSTRAINT_CATALOG    standard        VARCHAR(64)
CONSTRAINT_SCHEMA     standard        VARCHAR(64)
CONSTRAINT_NAME       standard        VARCHAR(64)
CATALOG_NAME          standard        VARCHAR(64)
SCHEMA_NAME           standard        VARCHAR(64)
TABLE_NAME            standard        VARCHAR(64)
COLUMN_NAME           standard        VARCHAR(64)
CURSOR_NAME           standard        VARCHAR(64)
MESSAGE_TEXT          standard        VARCHAR(128)
MYSQL_ERRNO           MySQL extension UNSIGNED SMALLINT

All items are nullable but in fact you can't assign NULLs.
All CHAR/VARCHAR items are UTF8 (we discussed possible use
of connection character set instead, but nobody liked that).
Since it's UTF8, then it may have 4-byte characters, so
VARCHAR(128) can require 512 bytes. Junk non-UTF8 characters
are not allowed, the implementor may either convert them to
"?"s or disallow them.

The item name "MYSQL_ERRNO", instead of code or errno or
sqlcode or native_error or error_code or mysql_error_code
or error_number or error_message_number, was chosen by a
vote. The voters were Roland Bouman, Konstantin Osipov.

Condition information items: default values
-------------------------------------------

One always specifies SQLSTATE directly or indirectly,
and the first two letters of SQLSTATE are the class:
'00' success, '01' warning, '02' not found, else error.
We will figure out some of the default values for
condition information items based on the class.

If class = '00'
  /* Illegal */

If class = '01'
  CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
  CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
  CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
  CURSOR_NAME = '';
  MESSAGE_TEXT = 'Unhandled user-defined warning condition';
  MYSQL_ERRNO = ER_SIGNAL_WARN

If class = '02'
  CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
  CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
  CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
  CURSOR_NAME = '';
  MESSAGE_TEXT = 'Unhandled user-defined not found condition';
  MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND

If class > '02'
  If (class is a class listed in SQL standard)
    CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
  else
    CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
  CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
  CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
  CURSOR_NAME = '';
  MESSAGE_TEXT = 'Unhandled user-defined exception condition';
  MYSQL_ERRNO = ER_SIGNAL_EXCEPTION

Thus MySQL is determining default MYSQL_ERRNO based on SQLSTATE class.
DB2 does that too.

Effect on handlers, cursors, statements, and transactions
---------------------------------------------------------

The error class determines how severe the error is.
We ignore sql_mode, that is, 'strict' doesn't matter.
We ignore IGNORE, that is, user-generated errors are never ignored.

If class = '00'
  /* illegal */

If class = '01'
  /* It's a warning. @@warning_count goes up.
     SHOW WARNINGS shows it.
     SQLWARNING handlers catch it.
     If it's unhandled in a function, statements don't end. */

If class = '02'
  /* It's a not found.
     NOT FOUND handlers catch it.
     There's no effect on cursors.
     If it's unhandled in a function, statements end. */

If class > '02' but class <> '40'
  /* It's an exception.
     SQLEXCEPTION handlers catch it.
     If it's unhandled in a function, statements end. */

If class = '40'
  /* Treat this as an ordinary exception.
     It's a severe exception, the Transaction Rollback class.
     In standard SQL: No handlers catch it, and statements
     end if it's unhandled in a function, and transactions end,
     automatic rollback.
     But MySQL won't follow the standard in this respect.
     And MySQL won't have special treatment for other severe
     exceptions ("fatal errors"), e.g. out-of-memory. */

For example:
DELIMITER //
CREATE FUNCTION f () RETURNS INT
BEGIN
  SIGNAL SQLSTATE '01234';
  RETURN 5;
  END//
DELIMITER ;
CREATE TABLE t (s1 INT);
INSERT INTO t VALUES (f());
The result is: a 5 is inserted into table t.
No error or warning appears for the user.
However, as the MySQL Reference Manual says:
"12.5.5.42. SHOW WARNINGS Syntax
Statements that do not use tables and do not generate
messages have no effect on the message list."
So RETURN does not clear. Making message list
more standard-conformant in this respect might
be nice, but isn't a requirement for this worklog.

Nifty Ideas
-----------

The following ideas are nifty, but not standard and
not utterly necessary. So we'll never implement them.

1. SIGNAL MYSQL_ERRNO nnnn;
   Instead of "SIGNAL SQLSTATE ...", say "SIGNAL MYSQL_ERRNO ...".
   Then you can add SQLSTATE as a condition information item,
   so that you can say:
   SIGNAL MYSQL_ERRNO 9999 SET SQLSTATE = '99999';
   We won't support the above.
   But you can have MYSQL_ERRNO and a condition information item,
   so that you can say:
   SIGNAL SQLSTATE '99999' SET MYSQL_ERRNO = 9999;

2. Determine default MESSAGE_TEXT from MYSQL_ERRNO.
   If MYSQL_ERRNO is an error number with an assigned meaning in MySQL,
   then it has an associated mesage. So if SIGNAL specifies MYSQL_ERRNO
   but not MESSAGE_TEXT, then let MESSAGE_TEXT = associated message.
   For example:
   SIGNAL SQLSTATE '3D000' SET MYSQL_ERRNO = 1046;
   Since /include/mysqld_error.h says "#define ER_NO_DB_ERROR 1046"
   MESSAGE_TEXT will be 'No database selected'.
   Or, if language = German, 'Keine Datenbank ausgewählt'.
   Things get trickier if the default message has '%s' in it.
   We won't support the above.

3. Allow multiple conditions. For example:
   SIGNAL SQLSTATE '12345, SQLSTATE '01234';
   We have no use for this while we only support one condition area.
   We won't support the above.

4. Have a condition information item for severity.
   Instead of depending on the SQLSTATE class, the user could state
   that severity = note, warning, error, not found, rollback.
   PostgreSQL has something like this.
   We won't support the above.

5. Have condition information items for any situation which could
   be returned in an error packet or OK packet via the protocol.
   For example: ROW_COUNT.
   We won't support the above.

6. Allow PREPARE stmt1 FROM 'SIGNAL "22222"'.
   We won't support the above.

7. Allow assignment of an error constant to MYSQL_ERRNO.
   For example
   SIGNAL SQLSTATE '12345' SET MYSQL_ERRNO = 1469;
   becomes
   SIGNAL SQLSTATE '12345' SET MYSQL_ERRNO = ER_HOSTNAME.
   We won't support the above.

8. Figure out some special behaviour for IGNORE, see WL#4103.
   Since WL#4103 does not specify using the SQLSTATE class,
   this would have to be a new condition information item:
   IGNORABLE_TYPE = { 0 | 1 | 2 }.
   We won't support the above. No SIGNAL error can be skipped
   due to IGNORE.

9. Allow multiple diagnostics areas. (A standard-conformant
   "diagnostics area stack" is not in any worklog task, but might
   happen someday.) Then allow SIGNAL to state the area, e.g.
   SIGNAL AREA 2 SQLSTATE '12345';
   We won't support the above.

A. Access from Storage Engine API.
   We know that storage engines have some difficulty changing
   or customizing error messages. We will provide a simple routine
   so that any storage engine can do the equivalent of
   SIGNAL SQLSTATE '...' SET MYSQL_ERRNO = ....., MESSAGE_TEXT = '...'.
   We won't support the above.

B. Access from routine written in another language.
   For example, a UDF writer might want to force an error.
   We won't support the above.

C. Allow SIGNAL to cause a note-level warning.
   This could happen with SIGNAL ... SET SEVERITY = 'note';
   We won't support the above.

Privileges
----------

No special privileges are required to execute the SIGNAL statement.

Examples
--------

CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
  IF divisor = 0 THEN 
    SIGNAL divide_by_zero 
    SET MESSAGE_TEXT = 'Problem: Division by zero happened!';  
    END IF;
  END
Result: "CALL p(0)" will cause an error with SQLSTATE '22012'
and message "Problem: Division by zero happened!".

CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'An error happened';
    END;
  DROP TABLE no_such_table;
  END
Result: "CALL p()" will reach the DROP TABLE statement.
Since there is no table named no_such_table, the error
handler comes into play. The error handler destroys the
original ("no such table") error and makes a new error,
with SQLSTATE value '99999' and message "An error happened".

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

SIGNAL

Other DBMSs
-----------

DB2 follows the standard.

Informix has RAISE EXCEPTION n,n,'text'.

InterBase has "CREATE EXCEPTION exception_name; ...
EXCEPTION exception_name".

Oracle has "DECLARE exception_name EXCEPTION; ...
RAISE exception_name", and an EXCEPTION section
at end of procedure where one can dump messages.

SQL Server has RAISERROR ('text',n,n).

Sybase has RAISERROR n 'text', but Sybase SQL Anywhere has SIGNAL.

PostgreSQL follows Oracle, but also has tricks for "severity levels".

Changes to the manual
---------------------

The documentation should recommend that users choose '45000'
for a generic SQLSTATE, which means "unhandled user-defined exception".

The MySQL Reference Manual mentions SIGNAL statements twice:
"The SIGNAL and RESIGNAL  statements are not currently supported."

"31.4.12:  Do stored procedures have a statement for raising application
errors?
Not in MySQL 6.0. We intend to implement the SQL standard SIGNAL and
RESIGNAL statements in a future MySQL release."

Notes that may affect documentation ...

Inevitably we will see bogus bug reports about some
matters that are not due to WL#2110 implementation.
The only recourse is to document and patiently explain.
* SHOW WARNINGS does not display errors / warnings in
order by condition item number. People will think that
the first message is the "raised" condition. But it's not.
This will be handled when/if we decide what to do about
the relationship between SHOW and standard conditions.
* Setting MESSAGE_TEXT to a value containing characters
outside the ASCII repertoire will appear not to work.
But it does. This will be handled by fixing BUG#1406.
See email thread 
* Statements like
CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW RESIGNAL;
may appear bizarre because RESIGNAL apparently isn't in a
handler. But it doesn't matter. The point is that RESIGNAL
doesn't have to be technically "in", i.e. contained in, a
handler declaration. The requirement is that a handler
must be active.

References
----------

WL#2111 Stored Procedures: Implement GET DIAGNOSTICS
WL#2265 Stored Procedures: Implement RESIGNAL

BUG#11661 Raising Exceptions from within stored procedures: Support for SIGNAL
statement (Feature request)

BUG#16999 RAISEERROR or similar (Feature request)

Email mentioning row_count and signal:
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=52&mail=21762

Dev-private thread "Re: SIGNAL / RESIGNAL / GET DIAGNOSTICS", starting with
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=23812

"SIGNAL and RESIGNAL statements in condition handlers"
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/c0009026.htm

"Sybase SQL Anywhere User's Guide: Errors and warnings in procedures and triggers"
http://manuals.sybase.com/onlinebooks/group-pbarc/conn5/sqlug/@Generic__BookTextView/14095;pt=12051/*

"RAISERROR" (SQL Server 2008)
http://technet.microsoft.com/en-us/library/ms178592(SQL.100).aspx

"SIGNAL" (Oracle OLAP)
http://www.stanford.edu/dept/itss/docs/oracle/10g/olap.101/b10339/x_reserved020.htm

"Declaring Exceptions and Exception Handling Routines"
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_packages.htm#sthref763

"How PL/SQL Exceptions Are Raised"
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/errors.htm#sthref1391

"Handling Run-Time PL/SQL Errors"
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_packages.htm#sthref758

"Errors and Messages" (PostgreSQL)
http://www.postgresql.org/docs/8.3/static/plpgsql-errors-and-messages.html

CSC#2266 statement to abort store procedures
https://support.mysql.com/view.php?id=2266