NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE
Assumes good intermediate level knowledge and/or familiarity of Scripting in ServiceNow.
I have worked with ServiceNow encoded queries for several years now, using them to express more complex query logic. I usually copy what I need from the list view query builder breadcrumbs, though I've also rolled my own from scratch. Despite all this experience, I noticed something odd recently that caught me a bit by surprise.
To set the stage here, let me explain a couple of things about normal order of precedence.
First, let's review the state table for AND and OR evaluation:
TRUE AND TRUE: TRUE
TRUE AND FALSE: FALSE
FALSE AND TRUE: FALSE
FALSE AND FALSE: FALSE
TRUE OR TRUE: TRUE
TRUE OR FALSE: TRUE
FALSE OR TRUE: TRUE
FALSE OR FALSE: FALSE
Next, let's note that the order of precedence for evaluation is always:
- Inside the parenthesis
- AND
- OR
So, for example:
(TRUE OR FALSE) AND (FALSE OR FALSE)
Would evaluate to:
(TRUE) AND (FALSE)
Would evaluate to:
FALSE
Almost ALL computer and database languages follow this paradigm.
Now that we've established that, let's get back to my recent revelation. I did my usual copying of an encoded query from query builder breadcrumbs, but this time I noticed that the copied query did not appear to reflect the correct order of precedence. Instead, it seemed to be evaluating the OR operators first and then the AND operators!
My query in query builder:
Copying the query to use in an encoded query statement:
Some Coding Notes:
- I echoed out the encoded query after each of these examples to make sure that something wasn't being re-interpreted in each case. It was not. In every case used in this article, the query was echoed back as expected -- no surprise reconstructions.
- I ran all of these queries using Scripts - Background, but they can also be executed just as easily using Scheduled Job.
- Remember that with ServiceNow GlideRecords, if an error is encountered in an .addQuery, the statement is ignored.
- If an error is encountered when evaluating an .addEncodedQuery, everything including and after the error is ignored.
So, for the following script the ANDs should be handled first, and the ORs second.
(active = true AND priority = 1) OR (priority =3 AND state = 1) OR state = 2
That would be the implied order-of-precedence:
// Query as it was originally copied. This gives the correct result so it would appear that the order of precedence is backwards!!!
var incidents = new GlideRecord('incident');
incidents.addEncodedQuery('active=true^priority=1^ORpriority=3^state=1^ORstate=2');
incidents.query();
gs.info('---> Original: ' + incidents.getRowCount()); // 82 found
gs.info('---> Encoded Query: ' + incidents.getEncodedQuery());
Results:
*** Script: ---> Original: 82
*** Script: ---> Encoded Query: active=true^priority=1^ORpriority=3^state=1^ORstate=2
I was a bit surprised, then, that it returned the same number of records that my list view query had returned. This should not have been the case if the order of precedence was properly followed, So, I decided to slap a few parenthesis in to make it more readable and to force it to behave correctly.
// Query re-written with logical parenthesis to force the order. This does something completely wonky!
var incidents = new GlideRecord('incident');
incidents.addEncodedQuery('active=true^(priority=1^ORpriority=3)^(state=1^ORstate=2)');
incidents.query();
gs.info('Paren 1st: ' + incidents.getRowCount());
gs.info(incidents.getEncodedQuery());
Results:
QueryEventLogger: Invalid query detected, please check logs for details [Unknown field (priority in table incident]
Invalid query detected, stack trace below [Unknown field (priority in table incident] Query [active=true^(priority=1^ORpriority=3)^(state=1^ORstate=2)] com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:56)
...
*** Script: Paren 1st: 232
*** Script: active=true^(priority=1^ORpriority=3)^(state=1^ORstate=2)
This should have worked! Instead it just returned a count of all Active records. It would appear that everything after the first "^" was ignored! Could it be that the parenthesis were causing an error in the encoded query?
Next, I decided to rewrite the original encoded query as a true GlideRecord query.
// Query re-written in GlideRecord form to validate results. This is the correct precedence and actually mimics the original query builder.
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addQuery('priority','1').addOrCondition('priority','3');
incidents.addQuery('state','1').addOrCondition('state','2');
incidents.query();
gs.info('GlideRecord: ' + incidents.getRowCount()); // 82
gs.info(incidents.getEncodedQuery());
Results:
*** Script: GlideRecord: 82
*** Script: active=true^priority=1^ORpriority=3^state=1^ORstate=2
This came back with the same 82 records. Since this query actually demonstrates the correct order of precedence, it confirmed that the encoded queries were being evaluated in reverse order of precedence.
Undaunted, I decided to break out the active query into its own line and isolate the parenthesis portion of the query.
// Query re-written with logical parenthesis to force the order.
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addEncodedQuery('(priority=1^ORpriority=3)^(state=1^ORstate=2)');
incidents.query();
gs.info('Paren 2nd: ' + incidents.getRowCount());
gs.info(incidents.getEncodedQuery());
Results:
QueryEventLogger: Invalid query detected, please check logs for details [Unknown field (priority in table incident]
Invalid query detected, stack trace below [Unknown field (priority in table incident] Query [(priority=1^ORpriority=3)^(state=1^ORstate=2)] com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:56)
*** Script: Paren 2nd: 232
*** Script: active=true^(priority=1^ORpriority=3)^(state=1^ORstate=2)
Bang! Confirmed! The encoded query was completely ignored, and the results returned 232 active records. So, it turns out that parenthesis are an illegal "operator" in encoded queries! They actually cause an error and everything from the first parenthesis on is ignored. Nice.
I decided to do some more digging and check one more possibility. This time, I put parenthesis around my active check.
// Query re-written with logical parenthesis to force the order.
var incidents = new GlideRecord('incident');
incidents.addEncodedQuery('(active=true)^((priority=1^ORpriority=3)^(state=1^ORstate=2))');
incidents.query();
gs.info('Paren 3rd: ' + incidents.getRowCount());
gs.info(incidents.getEncodedQuery());
Results:
QueryEventLogger: Invalid query detected, please check logs for details [Unknown field (active in table incident] Invalid query detected, please check logs for details [Unknown field (state in table incident]
Invalid query detected, stack trace below [Unknown field (state in table incident] Query [(priority=1^ORpriority=3)^(state=1^ORstate=2)] com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:56)
Invalid query detected, stack trace below [Unknown field (active in table incident]
Query [(active=true)^((priority=1^ORpriority=3)^(state=1^ORstate=2))]
com.glide.db.QueryEventLogger.logInvalidQuery(QueryEventLogger.java:56)
*** Script: Paren 3rd: 7238
*** Script: (active=true)^((priority=1^ORpriority=3)^(state=1^ORstate=2))
...and this caused the entire encoded query line to error out and all the records in the table were returned! This second test confirmed, without a doubt, that parenthesis definitely are illegal in encoded query statements. Wow!
Okay, I thought, let's try this a different way and see if multiple encoded queries work.
// Query rewritten to force the ANDs to work correctly
var incidents = new GlideRecord('incident');
incidents.addEncodedQuery('active=true');
incidents.addEncodedQuery('priority=1^ORpriority=3');
incidents.addEncodedQuery('state=1^ORstate=2');
incidents.query();
gs.info('Forced: ' + incidents.getRowCount()); // 82
gs.info(incidents.getEncodedQuery());
Results:
*** Script: Forced: 82
*** Script: active=true^priority=1^ORpriority=3^state=1^ORstate=2
They did, and it stands to reason that, since there were no parenthesis, my order of precedence is obvious to a follow-on maintenance coder.
Finally, I had to verify if the records returned were actually REAL. I decided to break out the combination of queries represented by the original list view query.
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addQuery('priority', '1');
incidents.addQuery('state', '1');
incidents.query();
var one = incidents.getRowCount();
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addQuery('priority', '1');
incidents.addQuery('state', '2');
incidents.query();
var two = incidents.getRowCount();
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addQuery('priority', '3');
incidents.addQuery('state', '1');
incidents.query();
var three = incidents.getRowCount();
var incidents = new GlideRecord('incident');
incidents.addActiveQuery();
incidents.addQuery('priority', '3');
incidents.addQuery('state', '2');
incidents.query();
var four = incidents.getRowCount();
gs.info('Breakout: ' + (one + two + three + four)); //for a total of 82
Results:
*** Script: Breakout: 82
There you go. 82 records were found. That test verified the list view AND confirmed in yet another way that the order of precedence in encoded queries is reversed.
At this point, I went back to the breadcrumbs and looked carefully, and I noticed something that had never caught my eye before: The breadcrumbs listing looks like this:
All>Active = true>Priority = 1 - Critical .or. Priority = 3 - Moderate>State = New .or. State = Active
Notice the "greater than" symbols? And how the order of precedence is preserved? OR is first, then the ">" (which represent "AND")!
So, in summary, when using the encoded query breadcrumb builder in ServiceNow, you need to remember that:
- The order of precedence is reversed (ORs are evaluated first, then ANDs).
- Parenthesis cannot be used to change the order of precedence, and using parenthesis actually causes an error that does not manifest when running the GlideRecord query.
Learn More:
Check out these resources to learn more about order of precedence and ServiceNow encoded queries:
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 05-21-2016 08:06 AM
I updated the code and brought the article into alignment with my new formatting standard.
NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS. AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.
THIS IS THE RE-PUBLICATION OF MY ARTICLE FROM July 14, 2015 ON THE CLOUDSHERPAS SERVICENOW SCRIPTING 101 BLOG.
3 Comments
