Bug #11720 Client hangs if non existing value is provided to CASE statement, in SP.
Submitted: 4 Jul 2005 12:05 Modified: 11 Jul 2005 16:37
Reporter: Disha Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7-beta OS:Any (any)
Assigned to: Bugs System CPU Architecture:Any

[4 Jul 2005 12:05] Disha
Description:
If a SP contains a CASE statement and an incorrect value is passed to this SP then the client hangs.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create a stored procedure as follows:

   drop procedure if exists sp1//
   create procedure sp1(in i1 int)
   begin
      select i1;
      case i1
         when 1 then select i1*10;
         when 2 then select i1*10;
      end case;
   end//

4. Call the stored procedure as follows:
   call sp1(1)//
   call sp1(3)//

Expected Results: 
1. For the second call, appropriate error should be displayed as case not found.

Actual Results: 
1. The client hangs without any error.
[6 Jul 2005 15:20] MySQL Verification Team
Verified on Linux too.
[11 Jul 2005 16:37] Per-Erik Martin
mysql> drop procedure if exists sp1//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>    create procedure sp1(in i1 int)
    ->    begin
    ->       select i1;
    ->       case i1
    ->          when 1 then select i1*10;
    ->          when 2 then select i1*10;
    ->       end case;
    ->    end//
Query OK, 0 rows affected (0.00 sec)

mysql> call sp1(1)//
+----+
| i1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

+-------+
| i1*10 |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp1(3)//
+----+
| i1 |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

ERROR 1339 (20000): Case not found for CASE statement
mysql> call sp1(4)//
+----+
| i1 |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

ERROR 1339 (20000): Case not found for CASE statement
mysql> select @@version//
+--------------------------------+
| @@VERSION                      |
+--------------------------------+
| 5.0.10-beta-valgrind-max-debug |
+--------------------------------+
1 row in set (0.00 sec)

mysql>