MySQL Bugs: #24101: In stored procedure using case statement, if no else logic

Bug #24101	In stored procedure using case statement, if no else logic
Submitted:	8 Nov 2006 19:24	Modified:	11 Dec 2006 11:56
Reporter:	Abby Zhang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any
Tags:	case else, case statement, stored procedure

Description:
In stored procedure, I use case statement but don't have an 'else' part. When I have data that doesn't qualify all the previous 'when' part, the sql got error, but the bad part is it also killed the db server. 

It should just pop out an error message but NOT kill the db server.

Similar error I got at one of my other stored procedure is:
I dropped a table. But my stored precudure is still referencing the table. First, the stored procedure still got compiled(but I think it should at least got error when compiled). Second, when ran the stored procedure, the script encountered an error AND killed the database server. This is also using mysql 5.0.19. When I took out the statement referencing the no-more-existing table, the stored procedure ran O.K. again.

How to repeat:
for bug about case statement:
open carrier_stats_cur;
LOOP1: loop
fetch cursor1_cur into mv_temp_1, mv_temp_2;
 if mv_dataNotFound then
 close cursor1_cur;
 leave LOOP1;
  end if;
 case
   when mv_temp_1 = 'aaa' then
      set mv_total_1 := mv_temp_2;
   when mv_temp_1 = 'bbb' then
      set mv_total_2 := mv_temp_2;

   when mv_temp_1 = 'ccc' then
      set mv_total_3 := mv_temp_2;
   when mv_temp_1 = 'ddd' then
      set mv_total_4 := mv_temp_2
  /* else == try with out the else part first
      set mv_total_else := mv_temp_2;
  */
 end case;

end loop loop1;

for the sql on non-existing table error: create a sp and inside the sp uses a non existing table name. run the sp. the db got killed.

Suggested fix:
It should just pop out an error message but NOT kill the db server.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27. In case of the same problem, please, send us complete test case, with all the CRERATE TABLE and CREATE PROCEDURE statement(s) needed to demonstrate the bug each and every time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".