Having worked with MySQL, Oracle and MS SQL Server (as well as a few NoSQL database types), I just ran across a random bug that I’ve not seen before and I thought I’d share it.
Obviously, NULL in SQLServer is a placeholder for missing data, and it has its own comparisons IS NULL and IS NOT NULL. But, the problems with this entity (or lack thereof) is that within a script or stored procedure, you cannot use =,<,!=,or <>. The problem happened when an old stored procedure was doing an update statement based on a comparison where one side had a null value that was not checked. When a customer complained about some records not being updated, I determined that the comparison was the issue:
UPDATE Accounts
SET PAID_THRU = a.PAID_THRU
FROM Accounts join Accounts a on Accounts.Company_ID = a.ID
WHERE Accounts.PAID_THRU <> a.PAID_THRU –ERROR HERE IF Accounts.PAID_THRU is NULL
The last line was the problem in this statement, as they needed to be checked for null values separately.
WHERE (Accounts.PAID_THRU <> a.PAID_THRU OR(Accounts.PAID_THRU is null AND a.PAID_THRU is not null))
This is especially tricky when the values are not always or rarely null, so the update does run and update values, but the NULL records will fall through the cracks until someone notices.
One final note: I believe that in MySQL you can use <=> and it will cover cases like this, but in Oracle and MSSQL, you’ve gotta check it yourself!