Bug #14042 Workaround for "empty" ELSE in CASE not documented
Submitted: 14 Oct 2005 22:24 Modified: 13 Jan 2008 11:09
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:5.0+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: case, ELSE, stored procedures
Triage: D4 (Minor)

[14 Oct 2005 22:24] Roland Bouman
Description:
A CASE statement (not CASE expression) like this:

CASE variable
    WHEN value1 THEN 
        ...; 
        ...;
    ..
    ..
    WHEN valueX THEN
        ...; 
        ...;
END CASE;

will give the error 

ERROR 1339 (20000): Case not found for CASE statement 

when the variable takes on a variable that is not covered by any of the WHEN's. This behaviour is not documented properly. The docs at:

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

just say that:

The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END

If the behaviour to generate an error condition in case none of the WHEN's match is by design (wich would be a different behaviour as compared to IF..THEN), then it would very nice to be able to write an "empty" ELSE within the CASE statement.

How to repeat:
delimiter go

create procedure p_Case()
begin
declare v int default 1;
case v
    when 2 then select v;
end case;
end;
go

call p_case() 
go

ERROR 1339 (20000): Case not found for CASE statement 

Suggested fix:
what we would want to write is:

create procedure p_Case()
begin
declare v int default 1;
case v
    when 2 then select v;
    else
end case;
end;
go

but this gives:

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 'end c
ase;
end' at line 6

Alternatively, a NOOP statement could be introduced, like the oracle NULL statement (not NULL value, NULL statement). Of course, this is very ugly, it would be better to just write an empty ELSE, or to decide not to generate an error in case the CASE variable does not match any WHEN.
[27 Nov 2005 16:52] Roland Bouman
Hi, i am using empty BEGIN..END statements now as NOOP statement (thanks Valeriy). Which is fine by me.
[2 Dec 2005 14:34] Roland Bouman
changed the synopsis
[2 Dec 2005 14:53] Per-Erik Martin
This would not be standard; a statement is required in the ELSE branch.
[4 Dec 2005 14:02] Valeriy Kravchuk
No, empty ELSE clause in non-standard. Look:

<case statement> ::=
<simple case statement>
| <searched case statement>

<simple case statement> ::=
CASE <case operand>
<simple case statement when clause>...
[ <case statement else clause> ]
END CASE
...
<case statement else clause> ::= ELSE <SQL statement list>

So, please, use BEGIN END trick, if you do not want to put anything useful into ELSE:

mysql> create procedure p_Case() begin declare v int default 1; case v     when 2 then select v; else begin end; end case; end;//
Query OK, 0 rows affected (0,00 sec)

mysql> call p_case();//
Query OK, 0 rows affected (0,00 sec)
[4 Dec 2005 14:21] Roland Bouman
ok, fair enough.

Thanks again Valeriy
[17 Dec 2007 10:34] Jon Stephens
This should be mentioned in the documentation, so I've re-opened as a Docs bug and assigned it to myself.
[13 Jan 2008 11:09] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.