Variables in SP do not compare as equal when both are NULL

SQL Apprentice Question
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).

Using the SAMPLE database, tables EMP and EMLOYEE.


In the followng stored procedure, 2 NULL columns (COMM) are selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.


Is there a reason for this?


Statement terminator is @.


-- SET THE COMMISION TO NULL ON TWO SAMPLE TABLES FOR EMPNO '000010'


UPDATE EMP SET COMM = NULL WHERE EMPNO = '000010'@
UPDATE EMPLOYEE SET COMM = NULL WHERE EMPNO = '000010'@


--------------------------------------------------
DROP PROCEDURE TEST_SP@


CREATE PROCEDURE TEST_SP
(
OUT EMP_COMM DECIMAL(9,2),
OUT EMP_SALARY DECIMAL(9,2),
OUT EMPLOYEE_COMM DECIMAL(9,2),
OUT EMPLOYEE_SALARY DECIMAL(9,2),
OUT v_ERRMSG_1 VARCHAR(500),
OUT v_ERRMSG_2 VARCHAR(500)
)
SPECIFIC TEST_SP
RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN


---------------------------------------------
-- Declare variables
---------------------------------------------


DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE at_end SMALLINT DEFAULT 0;


DECLARE A_EMPNO CHAR(6);
DECLARE A_EMP_COMM DECIMAL(9,2);
DECLARE A_EMP_SALARY DECIMAL(9,2);


DECLARE B_EMPNO CHAR(6);
DECLARE B_EMPLOYEE_COMM DECIMAL(9,2);
DECLARE B_EMPLOYEE_SALARY DECIMAL(9,2);


---------------------------------------------
-- Declare exit handlers
---------------------------------------------


DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;


DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;


DECLARE EXIT HANDLER FOR SQLWARNING
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;


---------------------------------------------------------------
-- Begin processing logic
---------------------------------------------------------------


SELECT EMPNO, COMM, SALARY INTO A_EMPNO, A_EMP_COMM, A_EMP_SALARY
FROM DB2INST1.EMP
WHERE EMPNO = '000010';


SELECT EMPNO, COMM, SALARY INTO B_EMPNO, B_EMPLOYEE_COMM, B_EMPLOYEE_SALARY
FROM DB2INST1.EMPLOYEE
WHERE EMPNO = A_EMPNO;


IF A_EMP_COMM = B_EMPLOYEE_COMM THEN
SET V_ERRMSG_1 = 'NULLS COMPARE AS EQUAL';
ELSE
SET V_ERRMSG_1 = 'NULLS DO NOT COMPARE AS EQUAL';
END IF;


IF A_EMP_SALARY = B_EMPLOYEE_SALARY THEN
SET V_ERRMSG_2 = 'NON-NULLS COMPARE AS EQUAL';
ELSE
SET V_ERRMSG_2 = 'NON-NULLS DO NOT COMPARE AS EQUAL';
END IF;


SET EMP_COMM = A_EMP_COMM;
SET EMP_SALARY = A_EMP_SALARY;


SET EMPLOYEE_COMM = B_EMPLOYEE_COMM;
SET EMPLOYEE_SALARY = B_EMPLOYEE_SALARY;


END@


CALL TEST_SP (?,?,?,?,?,?)@


Value of output parameters
--------------------------
Parameter Name : EMP_COMM
Parameter Value : -


Parameter Name : EMP_SALARY
Parameter Value : 52750.00


Parameter Name : EMPLOYEE_COMM
Parameter Value : -


Parameter Name : EMPLOYEE_SALARY
Parameter Value : 52750.00


Parameter Name : V_ERRMSG_1
Parameter Value : NULLS DO NOT COMPARE AS EQUAL


Parameter Name : V_ERRMSG_2
Parameter Value : NON-NULLS COMPARE AS EQUAL


Return Status = 0



Celko Answers
>> Yes, it is clear that DB2 follows the ANSI standard, but it is not clear that the ANSI standard is consistent (as noted above) or is the preferred interpretation, and there are several databases that apparently use different interpretations (unless you set them to be ANSI compliant).<<


GROUPING is not the same as testing for equality. Grouping is done
with groups and equality is for scalars. We debated this in the old
ANSI X3H2 committee decades ago when it was still an issue.

If you want to see if some columns are all NULL, use "COALESCE (c1, c2,
,,cn) IS NULL" on your scalars.