PayloadCMS Regression Reproduction

This repository provides a minimal reproduction of a query failure after upgrading PayloadCMS from v3.37 to v3.38.

The issue manifests as a PostgreSQL error during queries with nested filters (e.g. near geolocation filter).

Steps to Reproduce

1. Clone this repository

git clone https://github.com/gwesterman/payload-3-38-postgres-distinct-error.git
cd payload-3-38-postgres-distinct-error

2. Install dependencies

pnpm install

3. Create a .env file in the root directory and set the following environment variables:

PAYLOAD_SECRET=payload-secret
DATABASE_URI=your-database-uri

4. Start the development server using Payload v3.37

pnpm dev

Then navigate to http://localhost:3000 in your browser.

5. Seed the database

Once the UI opens in your browser, click the Seed Data button to populate the database. This is not strictly necessary to reproduce the issue but helps visualize the query working.

6. Observe the successful query

After seeding, the homepage reloads and displays users fetched using this query:

const users = await payload.find({
  collection: 'users',
  where: {
    'location.point': {
      near: '-74.00597,40.712776,50000', // New York
    },
  },
});

If you seeded the database, you should see a list of users. The query was successful if you do not see a Next.js error page.

7. Upgrade to Payload v3.38

Update all Payload-related dependencies in package.json to 3.38.0:

-  "@payloadcms/db-postgres": "3.37.0",
-  "@payloadcms/next": "3.37.0",
-  "@payloadcms/payload-cloud": "3.37.0",
-  "@payloadcms/richtext-lexical": "3.37.0",
-  "payload": "3.37.0"
+  "@payloadcms/db-postgres": "3.38.0",
+  "@payloadcms/next": "3.38.0",
+  "@payloadcms/payload-cloud": "3.38.0",
+  "@payloadcms/richtext-lexical": "3.38.0",
+  "payload": "3.38.0"

Then re-install:

pnpm install

8. Restart the dev server

pnpm dev

9. See the error

You will now encounter a Next.js error page showing a PostgreSQL error similar to:

error: for SELECT DISTINCT, ORDER BY expressions must appear in select list

10. Revert to Payload v3.37

Update all Payload-related dependencies in package.json back to 3.37.0 and re-install to confirm the error is resolved.

---

Expected Behavior

The query should work as it did in v3.37 and return the matching users.

Actual Behavior

The query fails, showing error: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Related Issues

Could be related to:

• payloadcms/payload#12365