LocalAPI: paginated query with a filter on a date 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 a filter on a date 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 20 and the filter on this date field, some pages will get 15 results, some other 13, 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 dates to help you.

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

Update

I manage to reproduce the issue from the payload version 3.35.1 to 3.37.0. I didn't test earlier versions.

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. You can modify the queries or LIMIT constant in src/app/(frontend)/page.tsx file in order to test mutliple setups
10. If there's no ID present in both arrays, try to delete all articles and restart the server to recreate new data or change LIMIT constant to a higher value.

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

Not sure, area: core, db-postgres

Environment Info

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

[github-actions]

🚀 This is included in version v3.38.0

[github-actions]

This issue has been automatically locked.
Please open a new issue if this issue persists with any additional detail.