Bug #8760 Stored Procedures: Invalid SQLSTATE is allowed in a DECLARE ? HANDLER FOR stmt.
Submitted: 24 Feb 2005 0:10 Modified: 28 Feb 2005 18:47
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Microsoft Windows (Windows 2003)
Assigned to: Bugs System CPU Architecture:Any

[24 Feb 2005 0:10] Disha
Description:
Invalid SQL states can be declared in a DECLARE ? HANDLER FOR statement. This shoudl NOT be permitted.

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 '42S0200test' SET @var2 = 1; 
      insert into tnull values(1);
      SELECT @var2;
      END//

 2. Execute the above procedure
     call SP1();

Expected Result
The procedure should not be created. A SQL syntax error should be thrown.

Actual Result 

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

    Query OK, 0 rows affected (0.02 sec)
[24 Feb 2005 9:41] Disha
Updated the synopsis.
[28 Feb 2005 18:47] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html