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