MySQL Bugs: #36510: Stored Procedures: mysql_error

Bug #36510	Stored Procedures: mysql_error_code 0 should be illegal
Submitted:	5 May 2008 16:02	Modified:	7 Mar 2010 18:38
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

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//

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.

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.

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.)

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.7-alpha  (revid:marc.alff@sun.com-20080813215218-gaqftrkzk9jrahfq) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)

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).

Queued in 5.6 (next-mr-runtime)

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)

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)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.