I think I have discovered a bug in the handling of null values (vs NULL
values) passed as parameters to a stored proc.
I have always believed that the database handled NULL and null the
same. The following statement returns the expected results:
select case when NULL is null then 'SAME' else 'DIFFERENT' end from
sysibm.sysdummy1;
returns SAME.
BUT, If you call a proc with lower case null as a parameter, it will
not be treated as a NULL.
Here's an example proc:
CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
SPECIFIC NULLTEST
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
P1: BEGIN
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
if PARM1 IS null THEN
OPEN c1;
ELSE
OPEN c2;
END IF;
END P1
;
CALL RG.NULLTEST(null);
returns PARM1 IS NOT NULL
BUT
CALL RG.NULLTEST(NULL);
PARM1 IS NULL
It's easy enough to code around, but frustrating when the database
violates a basic assumption.
Has anyone else encountered this issue?
Bob