| Bug #8759 | Stored Procedures: SQLSTATE '00000' should be illegal | ||
|---|---|---|---|
| Submitted: | 24 Feb 2005 1:07 | Modified: | 2 Apr 2008 21:08 |
| Reporter: | Disha | ||
| Status: | Closed | ||
| Category: | Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.0.2 | OS: | Microsoft Windows (Windows 2003) |
| Assigned to: | Marc Alff | Target Version: | |
| Triage: | D3 (Medium) | ||
[24 Feb 2005 10:39]
Disha
Updated the synopsis.
[25 Feb 2005 4:56]
Miguel Solorzano
With the current BK source the result is: mysql> call SP1(); +-------+ | @var2 | +-------+ | NULL | +-------+ 1 row in set (0.14 sec)
[20 Mar 2008 22: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 19: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 10:21]
Bugs System
Pushed into 5.1.24-rc
[31 Mar 2008 15:58]
Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 21: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 22: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 22: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 23: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).
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)