MySQL Bugs: #14042: Workaround for "empty" ELSE in CASE not documented

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

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.

Hi, i am using empty BEGIN..END statements now as NOOP statement (thanks Valeriy). Which is fine by me.

changed the synopsis

This would not be standard; a statement is required in the ELSE branch.

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)

ok, fair enough.

Thanks again Valeriy

This should be mentioned in the documentation, so I've re-opened as a Docs bug and assigned it to myself.

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.