Bug #10820 CASE...END CASE unable to use in function
Submitted: 24 May 2005 10:32 Modified: 24 May 2005 12:34
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.5-beta-nt OS:Windows (Windows xp)
Assigned to: CPU Architecture:Any

[24 May 2005 10:32] Disha
Description:
case...end case can not be used for creating fuction.

How to repeat:
Repro Steps:
  
1. execute the following command.
    delimiter //
    drop function strmon//
    CREATE FUNCTION strmon (mon INT) returns char(3)
    BEGIN 
       RETURN case mon WHEN 1 THEN 'Jan' when 2 THEN 'Feb' end case; 
    END //
 
2. Expected Result: Sucessuful function creation with following statment 
                          'Query OK, 0 rows affected (0.00 sec)'
   
3.Actual Result: ERROR 1064 (42000): You have an error in your SQL syntax; 
                      check the manual that corresponds to your MySQL server version 
                      for the right syntax to use near 'RETURN case mon WHEN 1 
                      THEN 'Jan' when 2 THEN 'Feb' end case; END' at line 3
[24 May 2005 10:52] Vasily Kishkin
According to manual We must use "CASE...END CASE". But sometimes "END CASE" returns an error. You can use "END" instead of "END CASE". But I set status "Verified". Because I think this is bug.
Example of workable code:
CREATE FUNCTION strmon (mon INT) returns char(3) 
BEGIN 
  RETURN CASE mon WHEN 1 THEN 'Jan' when 2 THEN 'Feb' 
 END; 
END //
[24 May 2005 12:34] Per-Erik Martin
You're confusing the CASE *statement* (used only in stored procedures) with the CASE *expression*. They have slightly different syntax, the latter with just END, not END CASE. (And RETURN of course expects the expression CASE)

See sections 12.2 and 19.2.12.2 in the manual.