REST API pagination doesn't make sense

[kcwong]

I get the no. of records in sys_attachment: 

Then I query the last record with this: 

1 record in the response, so far so good.
Now I sort in reverse order: 

No result?

[kcwong]

With the key value method instead of offset, I also run into problem. 
With sysparm_query = table_name = change_request, I get there should be 5126 rows from 

/api/now/v1/stats/${TableName}

With first call using sysparm_limit=5000,sysparm_offset=0, sysparm_query=table_name=change_request^ORDERBYsys_id, I get less than 5000 rows.

Fit last sys_id into second call, I get even less. 
Third call becomes 0 and I'm stuck. 

[kcwong · Accepted solution]

The cause is this:
You request sysparm_query=ORDERBYsys_id, sysparm_limit=1000, sysparm_offset=0.
SN found 2000 in total, sorted by sys_id.
Since offset is 0, SN takes the first 1000. 
Then SN applies ACL to it, removing 5 of them.
So you receive 995. 

For the next request, you do NOT increment offset by 995. Because the 5 removed by ACL still count in the index.
Instead you always increment offset by sysparm_limit.
The total number of record will remain a mystery, x-total-count header will be inaccurate.
So you always make the next call until you receive 0 record. 

This is also why if you increment offset by the actual no. of records returned, you will get duplicate entries from different pages. Because some records removed by ACL go before the duplicated ones by the sorting. 

Pray that you don't ever run into this situation: ACL removes all of sysparm_limit records. If you do, you will have no idea if you have another page or not.