Similarly to how the title of this post is ambiguous (which I bet most of you haven’t realized until now), it can happen that the code we write is interpreted by the system in a way we did not intend. In ServiceNow, such pitfalls are common when you work with complex GlideRecord queries that use a combination of AND and OR operators, which by the way come in two flavors.

This does not only apply to custom code. Some of the base system’s features that deal with database queries also behave in a way I find rather questionable. For example, it is good to know that using the so-called “Go to” search in lists with a top-level OR condition yields unexpected results. Want to give it a try? Check out these simple steps in my Telegram channel.

With the introduction of GlideQuery, ServiceNow has tried to address some of the shortcomings of the classical GlideRecord API. Among other advantages (such as better type handling, improved code readability, and protection against common performance issues), it is supposed to help developers “quickly diagnose query errors with additional checks and clear error messages“. For instance, it automatically identifies certain queries as ambiguous. Let’s have a look at a couple of examples.

Example 1

If you place a where (i.e., AND) clause after a set of conditions wrapped into an orWhere block (which is GlideRecord’s way to express a top-level OR), GlideQuery identifies the query as ambiguous and errors out, which I can fully understand:

var fred = new GlideQuery('sys_user')
.where('user_name', 'fred.luddy')
.orWhere(new GlideQuery()
.where('first_name', 'Fred')
.where('last_name', 'Luddy')
)
.where('email', 'fred.luddy@example.com')
.toGlideRecord();

gs.info(fred.getEncodedQuery());

Example 2

The same happens if you place a simple orWhere (i.e., dependent OR) clause inside an orWhere block that contains more than one where clause (although this time the ambiguity is arguable, in my opinion):

var fred = new GlideQuery('sys_user')
.where('user_name', 'fred.luddy')
.orWhere(new GlideQuery()
.where('first_name', 'Fred')
.where('last_name', 'Luddy')
.orWhere('email', 'fred.luddy@example.com')
)
.toGlideRecord();

gs.info(fred.getEncodedQuery());

Example 3

However, this pretty ambiguous query with a simple orWhere clause immediately following an orWhere block gets processed without any error, but the output it generates is rather questionable:

var fred = new GlideQuery('sys_user')
.where('user_name', 'fred.luddy')
.orWhere(new GlideQuery()
.where('first_name', 'Fred')
.where('last_name', 'Luddy')
)
.orWhere('email', 'fred.luddy@example.com')
.toGlideRecord();

gs.info(fred.getEncodedQuery());

Do you agree that this one is ambiguous too? Can you guess what the output is?

Conclusion

While I appreciate ServiceNow’s attempts to add some guardrails for protection against ambiguous or simply inefficient database queries, knowing about those guardrails is by no means an excuse for sloppy coding. Learn to spot ambiguities. Both in your code and in natural language. They occur more often than you might think.

And do test your code thoroughly. Always. Without any exceptions or excuses. Then test it again. And then one more time. Or maybe twice. Don’t take anything for granted and don’t trust anyone. Neither GlideRecord, nor GlideQuery, nor the Docs (which you may have already learned not to do if you have been following my blog for a while). Not even j2js if you know what I mean, even though that one was conjured by ServiceNow’s Senior Wizard about 15 years ago and has been working flawlessly ever since.

As I learned from a recent support case, ServiceNow doesn’t have the habit of including information about known errors into the API documentation, which means you can by default assume that every API is broken until you have proven the opposite.