Description
Using the "is not" operator when filtering out certain values in a choice or reference fields field (for example, subcategory on incident), results in subcategory with empty value being filtered out along with all records that fit the condition.
Steps to Reproduce
- Navigate to Incident > Open.
- Click the menu icon
.
- Select Group by > Category.
- Check the results.
- Expand the filter.
- Add condition [Category] [is not] [Database].
- Check results.
Notice the Database category and the empty results were filtered out.
See also use case in PRB659408/KB0680079
Workaround
This is expected behaviour for modern databases such as MySQL, SQL Server, and Oracle. The way the "not equal to" operator is behaving in the SN platform is congruent with standards-compliant SQL dialect.
Additional information on the SQL operator can be found in the stackoverflow article Not equal <> != operator on NULL
The query will need to have two conditions in OR: <field> is not <value> OR <field> is empty.
Related Problem: PRB688991