ServiceNow Table API: When it's safe to use the ORDERBY clause?

rogerashfor · ‎10-10-2024

When consuming the ServiceNow Table API, I have noticed that using ORDERBY on non-unique columns can lead to duplicate / missing records when paginating the full result set. It seems other users have reported similar issues to this before:

Re: We are receiving duplicate values while retrie... - ServiceNow Community
Solved: Re: Table REST API returns duplicates / inconsiste... - ServiceNow Community

My understanding is that the API behaves non-deterministically (same input -> different outputs) when ORDERBY is used on non-unique columns, because the system has no implicit / default tiebreaker logic. For example, consider the following case:

A table called u_test_table has the same values for a non-unique column called u_column_1 in the following offsets:

Record offset #2000:
u_column_1 = 10

Record offset #6000:
u_column_1 = 10

Record offset #14000:
u_column_1 = 10

Because there is no implicit tie breaker logic, the API (database engine) is free to choose either one of those 3 records randomly during ordering, which makes it behave non-deterministically. In the worst case, when making the following requests:

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1&sysparm_limit=5000

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1&sysparm_limit=5000&sysparm_offset=5000

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1&sysparm_limit=5000&sysparm_offset=10000

either one of those records (for example, the one at offset #2000) will be returned 3 times (instead of 1 which should be the correct behavior) causing duplicate / missing records.

I have the following questions:

1. Is this assumption / understanding of mine, correct?

2. If this is the actual reason for the problem, then I think it's safe to assume that as long as the sorting criteria (ORDERBY) is unique (contains at least 1 unique column) then this problem should not occur, the API will behave deterministically, and the result set should be complete and accurate? That's because we have explicitly declared a tiebreaker (the unique column)? For example:

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1,sys_id,u_column_2&sysparm_limit=5000

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1,sys_id,u_column_2&sysparm_limit=5000&sysparm_offset=5000

https://roger.service-now.com/api/now/v1/table/u_test_table?sysparm_fields=sys_id,u_column_2,u_column_3,u_column_4&sysparm_query=ORDERBYu_column_1,sys_id,u_column_2&sysparm_limit=5000&sysparm_offset=10000

where u_column_1 and u_column_2 are non-unique columns and sys_id is a unique column. The ORDERBY tiebreaker in this case is sys_id and it will resolve ties in cases when there are records that have the same values in the 2 other non-unique columns. Some information I found that gave me this idea: