Rudimentary Things to Remember – Topic 1

[Raghu Ram Y]

***Most of the readers might be aware of this topic, however sharing it as it is a critical issue for the folks who are not aware of this topic***

Have you ever run a query and you think is good enough but ultimately it affects the database badly? If so, go through this article which helps you to avoid this mistake next time.

Scenario : Count number of incidents which are created after certain date.

Expected Result : 2

Original Result : 134

Code :

var agg = new GlideAggregate('incident');
agg.adddEncodedQuery("sys_created_on>javascript:gs.dateGenerate('2022-02-27','20:59:59')");
agg.addAggregate('COUNT');
agg.query();
agg.next();
gs.print(agg.getAggregate('COUNT'));

 Output

*** Script: 134

Reason : If we provide any query in incorrect format in such scenarios the invalid part of the query will get ignored and executes the remaining part, so same thing was happened in the above scenario where instead of “addEncodedQuery” I have used “adddEncodedQuery”(Extra ‘d’ letter in it), due to this that line got ignored and it executed the remaining part which results in total count of incidents.

How can we override it : We can handle it by using this system property glide.invalid_query.returns_no_rows. When this property is true, invalid queries always return no rows.

Steps to create system property:

1. Navigate to <baseurl>/sys_properties_list.do
2. Click on New and fill with the below parameters:
3. • Name: glide.invalid_query.returns_no_rows
   • Type: true | false
4. Value: true

Thanks for reading the blog and do provide your inputs/suggestions if any.

Hope you find this article helpful. Don’t forget to Mark it Helpful / Bookmark.

Thanks,
Raghu Ram.

[Allen Andreas]

Hi,

Nice article. Just to offer supplementary information where this has been covered before: https://community.servicenow.com/community?id=community_blog&sys_id=620deaa5dbd0dbc01dcaf3231f961981

One of the main takeaways we'd want people to understand with this system property and setting it to true, is that it will affect the platform as a whole. So since this differs from most every other instance a developer/system admin would come into contact with (because that system property is default "false" and is usually never set to "true"), you'd want to ensure this is properly documented and your team(s) understand this if you so choose to set this to true.

Alternatives to implementing an instance wide change such as what has been mentioned above would be something like this:

https://community.servicenow.com/community?id=community_question&sys_id=0cb3cf65dbd8dbc01dcaf3231f96... -- which is essentially creating an "extension" to GlideRecord to sort of bake in that validation process or...

GlideQuery: https://developer.servicenow.com/dev.do#!/reference/api/sandiego/server/no-namespace/GlideQueryAPI

So overall, nice article, but it's nice to present the pros and cons and then alternatives people can use as well as the overall context is that we don't want our queries to ignore invalid pieces, so there's a few ways to address that.

And then the utmost way is to verify your queries properly before implementing them, haha.

Please mark reply as Helpful, if applicable. Thanks!

[Raghu Ram Y]

Noted, Thanks!