Issue Summary

Performing paginated REST API searches on Crowd with PostgreSQL linguistic collations results in duplicated and missing data compared to single-page requests.

Steps to Reproduce

1. Configure a PostgreSQL database with a linguistic collation:

CREATE DATABASE crowd_enus
  WITH OWNER = crowd
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  LOCALE_PROVIDER = 'libc'
  TEMPLATE = template0; 

2. Configure a Crowd instance to use this database and complete the initial setup.

3. Stop Crowd.

4. Execute the attached SQL script (repro_crowd_pagination.sql) - it creates two internal directories, one application mapped to both, and 1000 groups (group names rotate through five separators: space, hyphen, full stop, underscore, none).

5. Start Crowd.

6. Enable "Allow basic authentication on API calls" in Crowd (Configuration > Authentication Methods).

7. Execute a series of paginated REST API calls to /rest/usermanagement/1/search using start-index and max-results to retrieve all groups.

  for START in 0 100 200 300 400 500 600 700 800 900; do
    curl -sS -u 'reprotest:reprotest123' -H 'Accept: application/json' \
      "http://<crowd-host>:<crowd_port>/crowd/rest/usermanagement/1/search\
?entity-type=group&restriction=name%3Dzztest*\
&start-index=${START}&max-results=100"
  done | jq > output-paginated.txt 

8. Execute a single REST API call to retrieve the same set of groups in one page (for example, max-results=10000).

  curl -sS -u 'reprotest:reprotest123' -H 'Accept: application/json' \
    "http://<crowd-host>:<crowd_port>/crowd/rest/usermanagement/1/search\
?entity-type=group&restriction=name%3Dzztest*\
&start-index=0&max-results=10000" | jq > output-single-page.txt 

9. Compare the total number of unique records returned by the paginated calls versus the single-page call.

% grep -w name output-paginated.txt | sort -u | wc -l
% grep -w name output-single-page.txt | sort -u | wc -l

10. For detailed comparison:

% diff <(grep -w name output-paginated.txt | sort) <(grep -w name output-single-page.txt | sort) 

Expected Results

The paginated requests should return the same total number of unique records as the single-page request (for example, 1000 records).

A detailed comparison of the outputs of both requests should show the same group names.

Actual Results

The paginated requests return fewer unique records (for example, 680 records) than the single-page request:

% grep -w name output-paginated.txt | sort -u | wc -l
    680
% grep -w name output-single-page.txt | sort -u | wc -l
    1000  

A detailed comparison of the outputs of both requests indicates that some records are duplicated, while others are skipped or lost during pagination.

Affected endpoints: 

Workaround

• Use a single REST API call with max-results higher than the total number of entities to retrieve the same set of entities in one page. The limit is memory, not a fixed cap — the whole result set is built and re-sorted in server memory (then sent to the client), so only use result sets that comfortably fit in the heap. Memory use can be a problem for large data sets.
• Use a PostgreSQL database configured with the C collation. For example:

CREATE DATABASE crowd_c
WITH OWNER = crowd
ENCODING 'UTF8'
LOCALE_PROVIDER builtin
BUILTIN_LOCALE 'C.UTF-8'
TEMPLATE template0;