LocalAPI: paginated query with sorting on a field inside an array field does not retrieve all docs of the queried page

[TheThingsWithin]

Describe the Bug

Trying to query a paginated list of docs with sorting on a field nested into an array field causes the result of the payload.find() to retrieve only a sublist of docs of the queried page. For example, with a limit of 100, some pages will get 76 results, some other 82, etc, ... and the retrieved docs can be present in 2 distinct pages docs. Note that when the pagination is set to false, all the docs are retrieved normally, as it should be.

To reproduce the issue, you can use the blank-starter template i did. I used the payload config onInit function to create 200 articles with random data to save you some time.

If you want to regenerate the data, you can delete all articles from the admin panel and restart your server.

Note: I already encountered this issue (#12263), but with filtering and not sorting, and a fix was found and included in version v3.38

Link to the code that reproduces this issue

https://github.com/TheThingsWithin/payload-paginated-query-issue

Reproduction Steps

1. clone the repo
2. pnpm install
3. copy .env.example into .env and provide a PAYLOAD_SECRET and DATABASE_URI
4. pnpm dev
5. Navigate to admin panel and create an account / log in
6. Naviguate to http://localhost:3000/
7. Notice the differences between the LIMIT constant and docs.length for each pages
8. Notice that some IDs can be present in both page IDs array
9. Notice that if you sort on 2 fields in 2 differents arrays, there's even less number of docs retrieved (ig: 45 of 100)

You can modify the queries, the sorted fields or the LIMIT constant in src/app/(frontend)/page.tsx file in order to test mutliple setups

Which area(s) are affected? (Select all that apply)

Not sure, area: core, db-postgres

Environment Info

Binaries:
  Node: 22.19.0
  npm: N/A
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.59.1
  next: 15.5.4
  @payloadcms/db-postgres: 3.59.1
  @payloadcms/drizzle: 3.59.1
  @payloadcms/graphql: 3.59.1
  @payloadcms/next/utilities: 3.59.1
  @payloadcms/richtext-lexical: 3.59.1
  @payloadcms/translations: 3.59.1
  @payloadcms/ui/shared: 3.59.1
  react: 19.2.0
  react-dom: 19.2.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 11 Pro
  Available memory (MB): 16141
  Available CPU cores: 20

[TheThingsWithin]

Hey,

Is there any updates on this issue ? We have a big project (www.villas.fr) impacted by this issue.

Thanks !

[r1tsuu]

Hey, looking into this!