/rest/api/1.0/repos returning duplicate/missing results based on limit used

This does not present itself on HSQL. It has been reproduced on postgres 9.3.1, Stash 3.2.x.

Symptoms are some subset of repositories are missing and have been replaced by repositories already in the results when returning after the first page. The total number of records returned in equal to the number of visible repositories. Symptoms are present only when returning all repositories (ie not filtered by project).

See attached scripts for test data generation and API querying and analysis.

From the postgres documentation we have:

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

HSQL likely survives by not having complex query planning.

The natural ordering is only on the name column so we have (effectively) unordered rows w.r.t. project_id It looks like we will need to update the ordering to be on name and project_id. We don't notice this in project repo lists since repos are constrained to unique names within a repository.