Paginated results incorrect without $sort

[mrobst]

Context

I'm using Feathers Knex with SQL Server (node-mssql).

I have a table with approx 6000 records in with the primary key being a guid. I have this logic (pseduo code)

total = await app.service('jobs').find({ query : {$limit : 0})
batch = 500
calls = total / batch (rounded up)

const asyncIterable = if ( i < calls) return promise.resolve(done: false) else return promise.resolve(done:true)

for await (const num of asyncIterable) 
result = await app.service('jobs').find({ query: { $limit: batch, $skip: num * batch })
// do stuff with result // 

Expected behavior

The for await loop should run 12 times and return all the rows in the table in batches of 500

Actual behavior

The for await loop runs 12 times and returns 500 rows each time but some of the rows are duplicates of rows that have already been returned. De-duplicating the 12 x 500 batches returns approximately 5600 unique rows and not 6000 (the 400 missing rows are consistent across multiple runs).

Working Solution

result = await app.service('jobs').find({ query: { $sort: {jobNumber: -1}, $limit: batch, $skip: num * batch })

Adding the $sort parameter to the query causes the correct rows to be returned.

I should probably know that to return a correct results set with pagination I need to use a sort field, searching the internet returns a number of results relating to this. I suspect that behind the scenes the node-mssql driver is using offset & fetch in SQL server which do need the sort parameter to be reliable.

I'm partly writing this issue in case anyone else is searching for why the returned rows are not correct. Also a couple of questions:
a) the need to use $sort with pagination is not clear in the documentation either here or on the main feathers site (e.g. https://docs.feathersjs.com/api/databases/querying.html#skip) - would you like a PR to add a reference to this?
b) should/could there be any checks in the code to warn or otherwise highlight when a paginated result set is being requested without a sort parameter? i.e. $limit and $skip without $sort?

Thanks
Marcus