DB2 makes the wrong choice in an IF statement when I use IF, IN and SIGNAL together. It always choose the first IF.

I think I found out a bug in DB2, but I'd like to know if I'm doing something wrong. I built a procedure with some IF-ELSEIF statements. If the last test in ELSEIF is an IN operator, DB2 always goes to the first condition, even if it is false.

For example:

CREATE PROCEDURE sp_dummy(
IN p_x CHAR(1))
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
BEGIN ATOMIC

IF p_x = 'A' THEN
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'px = a';
ELSEIF p_x = 'B' THEN
SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = 'px = b';
ELSEIF p_x IN ('X','Y') THEN
SIGNAL SQLSTATE '70003' SET MESSAGE_TEXT = 'px in (x,y)';
END IF;
END@

And these returns:

call sp_dummy('A') --> 'px = a'

call sp_dummy('B') --> 'px = b'

call sp_dummy('X') --> 'px = a' it's wrong, it should have returned 'px in (x,y)'

call sp_dummy('Y') --> 'px = a' it's wrong, it should have returned 'px in (x,y)'

My DB2 version is v8.1.0.64 FixPak 7 and the server is Linux (SUSE Enterprise Server 9)

please help me,

thanks.

I tried it with this code:

CREATE PROCEDURE ANDY.sp_dummy ( IN px CHARACTER(1) )
SPECIFIC ANDY.SP_DUMMY
------------------------------------------------------------------------
-- SQL Stored Procedure
-- px
------------------------------------------------------------------------
P1: BEGIN

IF px = 'A' THEN
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'px = a';
ELSEIF px = 'B' THEN
SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = 'px = b';
ELSEIF px IN ('X','Y') THEN
SIGNAL SQLSTATE '70003' SET MESSAGE_TEXT = 'px in (x,y)';
END IF;
END P1

with these results:

DB:LCAD->call andy.sp_dummy('A')
SQL0438N Application raised error with diagnostic text: "px = a".
SQLSTATE=70001
DB:LCAD->call andy.sp_dummy('B')
SQL0438N Application raised error with diagnostic text: "px = b".
SQLSTATE=70002
DB:LCAD->call andy.sp_dummy('X')
SQL0438N Application raised error with diagnostic text: "px in (x,y)".
SQLSTATE=70003
DB:LCAD->call andy.sp_dummy('Y')
SQL0438N Application raised error with diagnostic text: "px in (x,y)".
SQLSTATE=70003
DB:LCAD->call andy.sp_dummy('D')

Return Status = 0


Seems to be working for me. I am using V8.1 FP6 on RH Linux.

Andy

I happen to also have a RH linux V8.1 FP 7 server and this is the results from the same code:

DB:LCAD_GDV->call andy.sp_dummy('A')
SQL0438N Application raised error with diagnostic text: "px = a".
SQLSTATE=70001
DB:LCAD_GDV->call andy.sp_dummy('B')
SQL0438N Application raised error with diagnostic text: "px = b".
SQLSTATE=70002
DB:LCAD_GDV->call andy.sp_dummy('X')
SQL0438N Application raised error with diagnostic text: "px = a".
SQLSTATE=70001
DB:LCAD_GDV->call andy.sp_dummy('Y')
SQL0438N Application raised error with diagnostic text: "px = a".
SQLSTATE=70001
DB:LCAD_GDV->call andy.sp_dummy('D')

Return Status = 0

Obviously something changed from FP6 to FP7. I know that FP7 (aka v8.2) no longer requires a C compiler for SQL stored procedures. Either theres is a bug (which I suspect), in which case I suggest you contact IBM and open a PMR, or due to the change this is not supported properly.

Andy