Like all programming/development environments, ServiceNow has a boolean data type with the label True/False. When you are defining a new application in ServiceNow, you use these fields in your table to track information with two possible values (two states). Yes/No and True/False are the typical value pairs used. As an example, everyone reading this is familiar with the Active field for incident records. An incident is Active (it is not done being worked) until it is Closed, at which point Active changes from true to false.
When you place a True/False field onto a form, the most frequent way you will see it represented is a checkbox. If it is checked, it has an underlying value of true, and if it is unchecked if it is false. In a list, we will show it as true or false. In the backend relational database, we map this to a data type of tinyint(1), and we specify a value of 0 (false) or 1 (true). However, we do not specify a database constraint of no nulls on this tinyint column. And a null is not the same as 0 or 1.
So, what does this mean in ServiceNow? How do we handle a null true/false field? And why does it matter?
When we implemented this data type, we decided to treat a null value as false. And for the most part, this is a great solution. Consider the checkbox and list representations, above. If the field is null, it will show an empty checkbox, just like if it was really an underlying false (0) value. In the list, it will show as false. Almost all the logic in the system will perform as expected and treat this as a false value. But there has to be a catch, right? Otherwise, there wouldn't be any point to this post.
Three Underlying States/Two Expected Values = Incorrect Results:
The fly in the ointment becomes apparent if you are in a list view of a table with a True/False field, and you group by that field.
Expected result: two rows (one row for all the true values, one row for all the false values).
Actual result: three rows (two false rows, one for field is null and one for field is false, one true row).
An example of this is to navigate to the sys_choice table, and group by the Inactive column; you will see three rows in the grouped list, for the three possible values.
Here is an example from a Eureka instance (with some i18n language plugins enabled):
Wait a minute, why does ServiceNow work that way? You already know the answer: a null is not the same as 0 or 1. We have three possible values (three states) for something which is only supposed to be two possible values. If we don't restrict the database to only two values, this means the necessary query against the database to get everything that is false must be <field> is false OR <field> is null. And you have the same situation if you try to find everything that is not true. You must query for <field> is not true OR <field> is null. These queries are inefficient and need to be avoided. From a performance perspective, you are better off executing two separate queries combining the results.
As a customer, your only option to retrieve all the desired records on a search was to manually compose the inefficient OR query. Also, there have been several other known issues where a UI Action had a null value in a True/False field and the code for finding the UI Action did not execute the OR query. The UI Action was present in the system but would not display as expected.
Our choices to correct the null-is-false issue were:
- Change every query automatically to the database for these fields to include the OR <field> is null to ensure all matching records are selected.
- No longer permit a null value in a True/False field; ensure it is always initialized as false (0), or is set to true (1).
As of Fuji, we implemented the changes for #2, so we no longer insert a null value in a boolean field. The server will force a null value to an actual 0 = false state before doing the database insert. This eliminates the possible incorrect query results without having to resort to an inefficient query. Considering that boolean fields by definition are supposed to be two states, this also eliminates the incorrect implementation of a three-state boolean value.
If you have upgraded from an earlier release and you have any of these null boolean values in your instance, the upgrade does not correct them. Any update of one of these fields will immediately correct the data, though. This is handy in the case of those pesky UI Actions; check the box and save the record, and then uncheck the box and save the record again, and the null false will be converted to a proper false (0) value. An XML Export/XML Import will also immediately correct the records. You may find some of these records in a new Fuji instance as well if you activate certain plugins and load demo data, because not all the demo data was corrected. Any customers who started with ServiceNow after Geneva will probably not have any such records in their instance.
For more information on MySQL query behavior see MySQL SELECT where boolean field is NULL or false.
For more information on implementing a boolean in MySQL see Which MySQL Datatype to use for storing boolean values?.
6 Comments
