MySQL Bugs: #69805: SQLWARNING handler does NOT take precedence over NOT FOUND one

Bug #69805	SQLWARNING handler does NOT take precedence over NOT FOUND one
Submitted:	21 Jul 2013 17:30	Modified:	16 Feb 2016 19:17
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	handler, manual, not found, signal, sqlwarning

Description:
This page, http://dev.mysql.com/doc/refman/5.6/en/handler-scope.html, says:

"The precedence of NOT FOUND depends on how the condition is raised:

    Normally, a condition in the NOT FOUND class can be handled by an SQLWARNING or NOT FOUND handler, with the SQLWARNING handler taking precedence if both are present. Normal occurrence of NOT FOUND takes place when a cursor used to fetch a set of rows reaches the end of the data set, or for instances of SELECT ... INTO var_list such that the WHERE clause finds no rows.

    If a NOT FOUND condition is raised by a SIGNAL (or RESIGNAL) statement, the condition can be handled by a NOT FOUND handler but not an SQLWARNING handler."

This is not true based on the test case (from my Facebook friend who does not want to use http://bugs.mysql.com while Oracle still requires a lot of personal information to register) below:

mysql> delimiter //
mysql> CREATE PROCEDURE `t`()
    -> READS SQL DATA
    -> `lbl_t`:
    -> BEGIN
    -> DECLARE `eof` BOOL;
    -> DECLARE `val` TEXT;
    ->
    -> DECLARE `crs` CURSOR FOR
    -> SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;
    ->
    -> -- exit handlers
    ->
    -> DECLARE EXIT HANDLER
    -> FOR NOT FOUND
    -> BEGIN
    -> SELECT 'NOT FOUND';
    -> CLOSE `crs`;
    -> END;
    ->
    -> DECLARE EXIT HANDLER
    -> FOR SQLWARNING
    -> BEGIN
    -> SELECT 'SQLWARNING';
    -> CLOSE `crs`;
    -> END;
    ->
    -> OPEN `crs`;
    ->
    -> -- SIGNAL SQLSTATE '02000';
    ->
    -> `lp`: LOOP
    -> FETCH `crs` INTO `val`;
    -> END LOOP;
    -> END;//
Query OK, 0 rows affected (0.23 sec)

mysql> call t()//
+-----------+
| NOT FOUND |
+-----------+
| NOT FOUND |
+-----------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)

How to repeat:
USE `test`;
delimiter //
DROP PROCEDURE IF EXISTS `t`;
CREATE PROCEDURE `t`()
READS SQL DATA
`lbl_t`:
BEGIN
DECLARE `eof` BOOL;
DECLARE `val` TEXT;

DECLARE `crs` CURSOR FOR
SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;

-- exit handlers

DECLARE EXIT HANDLER
FOR NOT FOUND
BEGIN
SELECT 'NOT FOUND';
CLOSE `crs`;
END;

DECLARE EXIT HANDLER
FOR SQLWARNING
BEGIN
SELECT 'SQLWARNING';
CLOSE `crs`;
END;

OPEN `crs`;

-- SIGNAL SQLSTATE '02000';

`lp`: LOOP
FETCH `crs` INTO `val`;
END LOOP;
END;
delimiter ;

CALL test.t();

Then make your won conclusion, uncomment SIGNAL and may more, etc

Suggested fix:
Either document what is really implemented (IMHO this is better idea) or implement handlers according to the manual.

Hello Valeriy,

Thank you for the report. 
Verified as described.

Thanks,
Umesh

My very personal thoughts:
* error classes are different sets of conditions, I cannot see any intersection between them
* this is true even in SIGNAL case
* for this reason, there is no precedence between NOT FOUND and SQLWARNING (sqlstate can begin with 02 or with 01, not both)

I don't know if the bug is in the docs or in the code, but I'm pretty sure that changing the server's behavior would break many stored procedures. I hope you will change the docs, not the code.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Changed the docs, not the code.