Oracle's handling of NULL still surprises...

On a client site today I came across a large piece of code that had been running for four years, written by some senior people, and it looked very correct. However, it was failing to carry out all the work it was designed to do. My job was to resolve this one, and a fair bit of investigation and head scratching followed. After a long period of time the failing code was found. It was a DELETE statement of the form :

DELETE emp WHERE (employee_id, department) IN
(SELECT employee_id, department FROM emp WHERE name = 'SMITH'
UNION
SELECT employee_id, department WHERE name = 'JONES');

only a lot more complicated.

I have seen code like this a fair bit. The above example should delete all people from the emp table whose name is either 'SMITH' or 'JONES'. However, that is not what it does. Witness the following from SQL*Plus:


SQL> SELECT 1 FROM dual WHERE (1,2) IN (SELECT 1,2 FROM dual);
1
----------
1

All good so far. But this surprised me :


SQL> SELECT 1 FROM dual WHERE (1,null) IN (SELECT 1,null FROM dual);
1
----------


Yup, it didn't find a result. When doing the comparison of the result sets here, Oracle compares NULLs using equals, not IS.

Looking back at the first example above, if the employee_id or department is NULL in any row in the emp table then that record is NOT going to get deleted by this statement even if the name is 'SMITH' or 'JONES'. A silent fail.

The solution in my clients case was to use NVL() to assign the employee_id and department a value in the DELETE clause and both of the SELECTs. The correct rows were then deleted.