Bug #36510 Stored Procedures: mysql_error_code 0 should be illegal
Submitted: 5 May 2008 18:02 Modified: 25 Nov 16:35
Reporter: Paul DuBois
Status: Closed
Category:Server: Parser Severity:S3 (Non-critical)
Version:5.0 and up OS:Any
Assigned to: Marc Alff Target Version:
Triage: D4 (Minor)

[5 May 2008 18:02] Paul DuBois 
Description:
This is a follow-on to Bug#8759, which prohibits SQLSTATE '00000' in handlers, on the
basis that '00000' signifies success, not an error.

Given that a mysql_error_code of 0 is equivalent to SQLSTATE '00000', it too should be
prohibited, but currently is not.

How to repeat:
delimiter //

# this correctly fails, after the fix for Bug#8759

DROP PROCEDURE IF EXISTS SP1//
CREATE PROCEDURE SP1()
BEGIN
DECLARE Continue HANDLER FOR SQLSTATE '00000' SET @var2 = 1;
SET @x=1;
SELECT @var2;
END//

# this incorrectly does not fail

DROP PROCEDURE IF EXISTS SP1//
CREATE PROCEDURE SP1()
BEGIN
DECLARE Continue HANDLER FOR 0 SET @var2 = 1;
SET @x=1;
SELECT @var2;
END//
[5 May 2008 23:07] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46371

ChangeSet@1.2582, 2008-05-05 15:07:36-06:00, malff@lambda.hsd1.co.comcast.net. +4 -0
  Bug#36510 (Stored Procedures: mysql_error_code 0 should be illegal)
  
  This fix is a follow up on Bug 8759.
  DECLARE HANDLER can catch exceptions using several syntaxes:
  - by SQLSTATE (SQL Standard)
  - by error number (MySQL)
  Catching succesful completion by error number, aka catching error 0,
  is illegal, and is now prevented.
  
  Also, the SQL standard defines the completion condition as a class of
  SQLSTATE, so that any state of class '00' and any subclass is a completion
  condition. Such completion conditions (for example, '00123') can not be
  caught by an exception handler in SQL.
[13 Aug 2008 23:52] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51583

2763 Marc Alff	2008-08-13
      Bug#36510 (Stored Procedures: mysql_error_code 0 should be illegal)
      
      This fix is a follow up on 8759.
      DECLARE HANDLER can catch exceptions using several syntaxes:
      - by SQLSTATE (SQL Standard)
      - by error number (MySQL)
      Catching succesful completion by error number, aka catching error 0,
      is illegal, and is now prevented.
      
      Also, the SQL standard defines the completion condition as a class of
      SQLSTATE, so that any state of class '00' and any subclass is a completion
      condition. Such completion conditions (for example, '00123') can not be
      caught by an exception handler in SQL.
[25 Aug 2008 21:57] Paul DuBois 
Noted in 6.0.7 changelog.

The parser incorrectly allowed MySQL error code 0 to be specified for
a condition handler. (This is incorrect because the condition must be
a failure condition and 0 indicates success.)
[28 Aug 2008 22:16] Bugs System 
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version
source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 23:26] Bugs System 
Pushed into 6.0.7-alpha  (revid:marc.alff@sun.com-20080813215218-gaqftrkzk9jrahfq)
(version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)
[20 Nov 23:43] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91178

2929 Konstantin Osipov	2009-11-21
      Backport the test caes for Bug#36510 from 6.0-codebase.
     @ mysql-test/t/sp-error.test
        Backport test of Bug#36510 (the bug itself is fixed by other
        backports, inc. SINGAL code).
[20 Nov 23:46] Konstantin Osipov 
Queued in 5.6 (next-mr-runtime)
[25 Nov 14:33] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (version
source revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha)
(pib:13)
[25 Nov 14:34] Bugs System 
Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version
source revid:kostja@sun.com-20091120224257-1x4j0dl1t865gqnl) (merge vers: 5.6.0-beta)
(pib:13)
[25 Nov 16:35] Paul DuBois 
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.