Bad assumption in SQL generated by query with sort parameters

[lmsurpre]

Describe the bug
Albert found that the sort tests are not working for him. I wasn't able to reproduce the issue on Derby, but after looking at the SQL being generated, I think we have an issue.

The search query is currently getting split into two parts:

1. searchForId which uses all the search parameters to select the resources and sorts the list of resource ids by the sort parameters

SELECT R.RESOURCE_ID,MIN(S1.QUANTITY_VALUE) FROM Observation_RESOURCES R 
	JOIN Observation_LOGICAL_RESOURCES LR ON R.LOGICAL_RESOURCE_ID=LR.LOGICAL_RESOURCE_ID AND R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID   
	LEFT OUTER JOIN Observation_QUANTITY_VALUES S1 ON (S1.PARAMETER_NAME_ID=401 AND S1.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID) 
	WHERE R.IS_DELETED <> 'Y' AND R.LOGICAL_RESOURCE_ID IN 
(SELECT LOGICAL_RESOURCE_ID FROM Observation_TOKEN_VALUES  
	WHERE (PARAMETER_NAME_ID=216 AND ((TOKEN_VALUE = ?)))) AND ...)
GROUP BY R.RESOURCE_ID 
ORDER BY MIN(S1.QUANTITY_VALUE) asc 
NULLS LAST OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

2. searchById which returns the actual resources

SELECT R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, R.IS_DELETED, R.DATA, LR.LOGICAL_ID FROM Observation_RESOURCES R, Observation_LOGICAL_RESOURCES LR WHERE R.LOGICAL_RESOURCE_ID = LR.LOGICAL_RESOURCE_ID AND R.RESOURCE_ID IN (1727,1784,1774,...)] 

Derby seems to always return the resources in the same order provided in that IN clause, but in general we can't rely on that.

To Reproduce
Only reproducable in Db2?

Expected behavior
The sort query should be written in a way to ensure the results are always in the proper order.

Additional context
I found https://stackoverflow.com/a/866521/161022 which confirms the faulty assumption made in our SQL and proposes a potential fix.