Bug #8759 Stored Procedures: SQLSTATE '00000' should be illegal
Submitted: 24 Feb 2005 0:07 Modified: 11 Dec 2009 17:26
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.2 OS:Windows (Windows 2003)
Assigned to: Marc ALFF CPU Architecture:Any

[24 Feb 2005 0:07] Disha
Description:
The SQL state for successful completion '00000' can be declared in a DECLARE ? HANDLER FOR statement. This should NOT be allowed.

How to repeat:
Test Setup:

1. Create database say 'Test'
2. Create table T2(f1 TEXT(20), f2 TEXT(25), f3 DATE, f4 int);
3. Insert into T2 (f1, f2, f3, f4) values ('ABC','XYZ','1990-05-07',100);

    
Repro Steps:

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

 2. Execute the above procedure
     call SP1();

Expected Result
The successful SQL state Handler should not be accepted and evaluated by MySQL

Actual Result

    +-------+
    | @var2 |
    +-------+
    | 1     |
    +-------+
    1 row in set (0.02 sec)

    Query OK, 0 rows affected (0.02 sec)
[24 Feb 2005 9:39] Disha
Updated the synopsis.
[25 Feb 2005 3:56] MySQL Verification Team
With the current BK source the result is:

mysql> call SP1();
+-------+
| @var2 |
+-------+
| NULL  |
+-------+
1 row in set (0.14 sec)
[20 Mar 2008 21:19] Marc ALFF
Verified in 5.1.24-rc-debug-log

It's possible to declare an exception handler for exceptions with a
SQLSTATE value of '00000'.

This is illegal as per the SQL specification,
since 00000 is the successful completion condition.

See ISO/IEC 9075-4:2003 (E),
section 8.2 <sqlstate value>,
syntax rule 5

Test script:

drop procedure if exists broken;

delimiter $$;

create procedure broken()
begin
  declare should_be_illegal condition for sqlstate '00000';
  declare continue handler for should_be_illegal set @x=0;
end$$

delimiter ;$$

create procedure should have failed, but it's accepted.
[21 Mar 2008 18:37] 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/44336

ChangeSet@1.2569, 2008-03-21 12:08:04-06:00, malff@lambda.hsd1.co.comcast.net. +3 -0
  Bug#8759 (Stored Procedures: SQLSTATE '00000' should be illegal)
  
  Fixed the parser to reject SQLSTATE '00000',
  since '00000' is the successful completion condition,
  and can not be caught by an exception handler in SQL.
[28 Mar 2008 9:21] Bugs System
Pushed into 5.1.24-rc
[31 Mar 2008 13:58] Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 19:08] Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs.

The parser incorrectly allowed SQLSTATE '00000' to be specified for a
condition handler. (This is incorrect because the condition must be a
failure condition and '00000' indicates success.)
[12 Jun 2008 20:21] 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/47821
[12 Jun 2008 20:22] 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/47822
[20 Nov 2009 22:41] 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/91176

3721 Konstantin Osipov	2009-11-21
      Remove a duplicate test for Bug#8759 from sp-error.test (bad merge).
     @ mysql-test/r/sp-error.result
        Remove a duplicate test for Bug#8759 from sp-error.test (bad merge).
     @ mysql-test/t/sp-error.test
        Remove a duplicate test for Bug#8759 (bad merge).
[11 Dec 2009 6:01] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[11 Dec 2009 17:26] Paul DuBois
Already fixed in 6.0.x.