Bug #26336 IF statement with nested SQL in stored functions
Submitted: 13 Feb 2007 17:18 Modified: 19 Feb 2007 14:29
Reporter: ILya Kogan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.11, 5.0 BK OS:Linux (Linux, Windows XP Professional)
Assigned to: CPU Architecture:Any
Tags: PROCEDURE, stored function

[13 Feb 2007 17:18] ILya Kogan 
Description:
It seemes that (error) sql statements e.g. UPDATE, which are nested into IF control statement in stored functions & triggers (not procedures) are always executed, 
even when control does not flows into IF. 

How to repeat:
# this will raise an exception

CREATE FUNCTION foo()
  RETURNS INTEGER
BEGIN
  DECLARE v INTEGER DEFAULT 4;
  IF (v = 4) THEN
    SET @check := 1;
  ELSE
    UPDATE wrong_table SET throw_exception = 1;
    SET @check := 2; 
  END IF;
  RETURN @check;
END
/

# this will be executed normally

CREATE PROCEDURE foo1()
BEGIN
  DECLARE v INTEGER DEFAULT 4;
  IF (v = 4) THEN
    SET @check := 1;
  ELSE
    UPDATE wrong_table SET throw_exception = 1;
    SET @check := 2; 
  END IF;
END
[14 Feb 2007 11:15] Sveta Smirnova 
Thank you for the report.

Verified as described using last BK sources. All versions are affected.
[14 Feb 2007 11:16] Sveta Smirnova 
test case

Attachment: bug26336.test (application/octet-stream, text), 922 bytes.
[19 Feb 2007 13:46] Konstantin Osipov 
This bug is a duplicate of Bug#8407 "Stored functions/triggers ignore exception handler".
[19 Feb 2007 13:48] Konstantin Osipov 
Dear Ilya,
Thank you for writing to us.
This bug is a duplicate of Bug#8407, which is in "patch pending" state.
You can track the progress on this problem by subscribing to the older bug report.
[19 Feb 2007 14:29] ILya Kogan 
Thank you for for your fast reply. I have decided to avoid this exception in logic of my functions, and will waiting for future releases. Hope for more powerful support of stored routines