GlideFilter.checkRecord matches both NULL and 0 (non-null zero integer) field with the filter 'field=0' - Known Error

GlideFilter.checkRecord matches both NULL and 0 (non-null zero integer) field with the filter 'field=0' Description If a table contains records where a field has a mix of values including NULL (empty) and 0 (integer) then GlideFilter.checkRecord(gr, 'field=0') will return true for both the NULL and 0 values.

The same query performed with GlideRecord.addEncodedQuery('field=0') will only match return records which have the non-NULL 0 value.

Steps to Reproduce

The problem can be demonstrated with the following script which runs multiple tests against the incident.reopen_count field using encoded queries for =0, ="0", >0, =NULL and ISEMPTY.

At least one record needs to have incident.reopen_count set to 0, and at least one record with a NULL/empty value.

var tableName = 'incident' var fieldName = 'reopen_count'; var queries = ['=0', '="0"', '>0', '=NULL', 'ISEMPTY']; var grResults = []; var crResults = []; var grCount = 0; var matchedCount = 0;

var inc = new GlideAggregate(tableName); inc.addAggregate('COUNT'); inc.query();

var element = inc.getElement(fieldName);

if (element == null) { gs.info("The table '" + tableName + "' does not have a field with the name '" + fieldName + "', please correct the tableName and fieldName variables"); } else { if (inc.next()) { gs.info('Row count for ' + tableName + ' table: ' + inc.getAggregate('COUNT')); gs.info(' '); } while (queries.length > 0) { var queryString = fieldName + queries[0];

gs.info("Testing with Query String: '" + queryString + "'"); gs.info("Executing GlideRecord Query");

grResults = []; var gr = new GlideRecord(tableName); gr.addEncodedQuery(queryString); gr.query(); grCount = gr.getRowCount();

gs.info("GlideRecord Query matched " + grCount + " records"); if (grCount == 0 && (queries[0] == "=0" || queries[0] == "=NULL")) { gs.info("WARNING: There are no records with '" + fieldName + "' set to " + queries[0] + ", please update at least one record to have a real value " + queries[0] + " to ensure this test has at least one non-null value"); } while (gr.next()) { grResults.push(gr.getDisplayValue()); } gs.info("Executing GlideFilter.checkRecord");

crResults = []; matchedCount = 0; gr = new GlideRecord(tableName); gr.query(); while (gr.next()) { if (GlideFilter.checkRecord(gr, queryString)) { matchedCount++; crResults.push(gr.getDisplayValue()); } } gs.info("GlideFilter.checkRecord matched " + matchedCount + " records"); grResults.sort(); crResults.sort();

if (grCount == matchedCount) { var ok = true; for (var i = 0; i if (grResults[i] != crResults[i]) { ok = false; } } if (ok) { gs.info("Results of GlideRecord query and GlideFilter.checkRecord matched with query string '" + queryString + "'"); } else { gs.info("The GlideRecord query and GlideFilter.checkRecord returned the same number of records, but the results did not match"); gs.info("GlideRecord results: " + grResults); gs.info("GlideFilter results: " + crResults); } } else { gs.info("The results of the GlideRecord query and GlideFilter.checkRecord did not return the same results"); gs.info("GlideRecord results: " + grResults); gs.info("GlideFilter results: " + crResults); } queries.shift(); gs.info(' '); } } Results:

*** Script: Row count for incident table: 55 *** Script: *** Script: Testing with Query String: 'reopen_count=0' *** Script: Executing GlideRecord Query *** Script: GlideRecord Query matched 6 records *** Script: Executing GlideFilter.checkRecord *** Script: GlideFilter.checkRecord matched 55 records *** Script: The results of the GlideRecord query and GlideFilter.checkRecord did not return the same results *** Script: GlideRecord results: INC0000057,INC0000058,INC0000059,INC0000060,INC0010002,INC0010003 *** Script: GlideFilter results: INC0000002,INC0000003,INC0000004,INC0000005,INC0000006,INC0000007,INC0000008,INC0000009,INC0000010,INC0000011,INC0000012,INC0000013,INC0000014,INC0000015,INC0000016,INC0000017,INC0000018,INC0000019,INC0000020,INC0000021,INC0000024,INC0000025,INC0000026,INC0000027,INC0000028,INC0000029,INC0000030,INC0000031,INC0000032,INC0000033,INC0000034,INC0000035,INC0000036,INC0000037,INC0000038,INC0000039,INC0000040,INC0000041,INC0000044,INC0000046,INC0000047,INC0000048,INC0000049,INC0000050,INC0000051,INC0000052,INC0000053,INC0000054,INC0000055,INC0000057,INC0000058,INC0000059,INC0000060,INC0010002,INC0010003 *** Script: *** Script: Testing with Query String: 'reopen_count="0"' *** Script: Executing GlideRecord Query *** Script: GlideRecord Query matched 0 records *** Script: Executing GlideFilter.checkRecord *** Script: GlideFilter.checkRecord matched 6 records *** Script: The results of the GlideRecord query and GlideFilter.checkRecord did not return the same results *** Script: GlideRecord results: *** Script: GlideFilter results: INC0000057,INC0000058,INC0000059,INC0000060,INC0010002,INC0010003 *** Script: *** Script: Testing with Query String: 'reopen_count>0' *** Script: Executing GlideRecord Query *** Script: GlideRecord Query matched 0 records *** Script: Executing GlideFilter.checkRecord *** Script: GlideFilter.checkRecord matched 0 records *** Script: Results of GlideRecord query and GlideFilter.checkRecord matched with query string 'reopen_count>0' *** Script: *** Script: Testing with Query String: 'reopen_count=NULL' *** Script: Executing GlideRecord Query *** Script: GlideRecord Query matched 49 records *** Script: Executing GlideFilter.checkRecord *** Script: GlideFilter.checkRecord matched 49 records *** Script: Results of GlideRecord query and GlideFilter.checkRecord matched with query string 'reopen_count=NULL' *** Script: *** Script: Testing with Query String: 'reopen_countISEMPTY' *** Script: Executing GlideRecord Query *** Script: GlideRecord Query matched 49 records *** Script: Executing GlideFilter.checkRecord *** Script: GlideFilter.checkRecord matched 49 records *** Script: Results of GlideRecord query and GlideFilter.checkRecord matched with query string 'reopen_countISEMPTY' *** Script:

When comparing the results of =0 the GlideRecord query will only match records which have a non-null 0 value. However, GlideFilter.checkRecord matches records were the field is either NULL/empty or has a non-null 0 value.

Workaround The behavior of GlideFilter matching both 0 and null with '=0' is a behavior that has been present in the platform as least as far back as Calgary, and is not going to be changed.

Here is a workaround: Note the results of the test using a query string '="0"'. When this was used with GlideFilter it matched the six incidents that had a reopen_count of 0 and did not include the incidents with a null/empty reopen_count. This can be used as a workaround when using GlideFilter if attempting to match specifically on non-empty 0 values and not include null/empty matches.