We’re almost finished with our in-house rewrite of our accounting system to .net. The complications with making sure all movements of money between accounts are correct and properly audited is a difficult task and one for another article. ;)

I came across a weird little bug this morning where our invoicing code that auto reconciles your bank account based on data you enter in the recon wasn’t working. Essentially what we do is ask the system for any invoices that match an amount of money coming into the bank account and then create the correct postings based on this information. However, all our non-VAT registered companies were not ever having their invoices matched.

Debugging the code I found that the invoice balance coming from the database was always NULL for non-VAT registered companies. A little further digging and the bug was unearthed. Using SUM() on the Amount and VAT columns worked as long as none of the columns were NULL. This is pretty obvious thinking about it, but it missed our testing. 1 + NULL is always NULL. ;)

If you read the SQL documentation, SUM(), AVG() and those type of functions ignore null fields. However, my SQL looked like this:

SELECT SUM(Amount) + SUM(VAT) FROM InvoiceLine.

For non-VAT registered companies, the amount fields would always have values but the VAT fields would always be NULL. Therefore the answer returned is always NULL. We solved this by using the COALESCE() method around the summing of the VAT column: SELECT COALESCE(SUM(VAT),0). COALESCE causes NULL values to be converted to the value that you pass as the second argument to the method. This then allows us to calculate the correct information.

Its a little gotcha that I bet is missed frequently in coding and needs to be carefully looked at by developers. The differences in NULL handling can be very dangerous, especially for financial systems.