240 views

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 < grResults.length; 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.


Related Problem: PRB1037282

Seen In

There is no data to report.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-10-12 04:44:34
Published:2018-10-12