Bug #48637 ERROR 22012 Division by 0 handled by handler for sqlstate 23000 (ER_DUP_ENTRY)

• Submitted: 9 Nov 2009 13:32
• Modified: 9 Jun 2014 17:03
• Reporter: Szymon Sobczak
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.88, 5.1.37
• OS: Any (Windows, Mac OS X)
• CPU Architecture: Any
• Tags: error handling, handler

[9 Nov 2009 13:32] Szymon Sobczak

Description:
I use MySQl server with sql_mode = ERROR_FOR_DIVISION_BY_ZERO. I call stored procedure A(), that contains handler for SQLSTATE 23000 (duplicate entry). Then the procedure A() calls procedure B(), which does division by zero. But instead of getting "ERROR 22012 Division by 0" from database, the handler for duplicate entry is being activated.

How to repeat:
sql_mode = ERROR_FOR_DIVISION_BY_ZERO;

DELIMITER |

CREATE TABLE Test (
	Id INTEGER NOT NULL AUTO_INCREMENT,
		PRIMARY KEY(Id),
	Val1 INTEGER NOT NULL,
	Val2 INTEGER NOT NULL
)|

CREATE PROCEDURE B()
BEGIN
	UPDATE Test SET Val1 = Val1 / Val2;
END|

CREATE PROCEDURE A ()
BEGIN
	DECLARE EXIT HANDLER
		FOR SQLSTATE '23000' # (ER_DUP_ENTRY) 
		BEGIN
			SELECT 'Duplication handled!';
		END;
	
	CALL B();
END|

DELIMITER ;

INSERT INTO Test (Id, Val1, Val2) VALUES (1, 1, 0);
CALL A();

I expect from this code to execute Val1/Val2, which means 1/0 and "ERROR 22012 Division by 0", but it activates the handler, prints "Duplication handled!" and finishes succesfully.

Suggested fix:
The "How to repeat" example should not activate the handler. Instead it should finish with "ERROR 22012 Division by 0".

[9 Nov 2009 15:59] Valeriy Kravchuk

Verified just as described also with 5.0.88 on Mac OS X:

mysql> CALL A();
+----------------------+
| Duplication handled! |
+----------------------+
| Duplication handled! | 
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1365
Message: Division by 0
1 row in set (0.00 sec)

mysql> CALL B();
ERROR 1365 (22012): Division by 0
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.88-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------+
| @@sql_mode                                   |
+----------------------------------------------+
| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO | 
+----------------------------------------------+
1 row in set (0.01 sec)

[9 Jun 2014 17:03] Paul DuBois

Noted in 5.6.4 changelog.

If an error occurred in a context where different handlers were
present at different levels of nesting, an outer handler could be
chosen rather than the innermost one.