Geek: the string “null” causes problems...

Geek: the string “null” causes problems...  This StackOverflow post reminded me of a crazy problem we had at my last company (ServiceNow).  I don't know if they ever fixed this...

The problem started with a special feature built into the database layer that the entire product is built upon.  The database layer has a feature that allows the values of fields of any type to be set with a string value.  Thus, the string “54” could be used to put the integer value 54 into a numeric field.  The challenge came with the SQL value “null”.  If you know anything at all about SQL, you know that value is rather important.  But how do you square the database layer's “all values can be represented with a string”  notion with the value “null”?  The solution chosen was to make the string “NULL” special: if the database layer saw that string, it would translate it into a SQL “null”, and vice versa.  This seemed to work quite well.

Now fast-forward a couple of years.  I was working with a large financial customer, directly with their IT lead, Gary S., who called me one day to complain about a peculiar problem they were having.  They had two employees who simply could not be entered into their system – they'd type the name in, click “Submit”, and the record never showed up in the database.  Gary noted, however, that there was one interesting thing in common between these two employees: their last names were “Null”.  I knew that Gary's company had an internal standard that called for all names to be entered in all upper-case, so they were entering those names as “NULL” – and of course, that would trigger the special behavior described above.

Gary “fixed” their problem by putting an asterisk after the employee's name, and distributing a note to the system's users to alert them to the problem.  Over the years I worked there, I heard of several other customers who had the same exact problem.  Changing that special behavior would be very difficult for ServiceNow at this point, as that special string (“NULL”) is buried in innumerable scripts, including many written by their customers...