findMany with distinct on one field and orderBy on a different field generates invalid PostgreSQL SQL.

[therealbrad]

Description

PostgreSQL requires that DISTINCT ON expressions match the leftmost ORDER BY expressions, but ZenStack generates them on different fields.

findMany with distinct on one field and orderBy on a different field generates invalid PostgreSQL SQL. PostgreSQL requires that DISTINCT ON expressions match the leftmost ORDER BY expressions, but ZenStack generates them on different fields.

Reproduction

Given any schema.zmodel with a model that has createdAt and title fields (e.g., a standard model with @@allow/@@deny rules):

import { ZenStackClient } from '@zenstackhq/orm';
import { PostgresDialect } from '@zenstackhq/orm/dialects/postgres';
import { Pool } from 'pg';

const client = new ZenStackClient(schema, {
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: '...' }) }),
});

// This fails:
await client.someModel.findMany({
  distinct: ["title"],
  orderBy: { createdAt: "desc" },
});
// Error: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

// These work fine:
await client.someModel.findMany({ distinct: ["title"] }); // distinct only
await client.someModel.findMany({ orderBy: { createdAt: "desc" } }); // orderBy only
await client.someModel.findMany({ distinct: ["title"], orderBy: { title: "asc" } }); // same field

Expected Behavior

Prisma handles this by automatically prepending the DISTINCT ON field to the ORDER BY clause. ZenStack should do the same, generating:

SELECT DISTINCT ON ("title") ... ORDER BY "title", "createdAt" DESC

Actual Behavior

ZenStack generates:

SELECT DISTINCT ON ("title") ... ORDER BY "createdAt" DESC

Which PostgreSQL rejects with error 42P10: SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

Environment

• @zenstackhq/orm: 3.5.2
• @zenstackhq/cli: 3.5.2
• Database: PostgreSQL 15 (checked latest docs and the limitation still applies)
• Node.js: 24.x

Impact

This is an ORM-level bug that affects any findMany with distinct + orderBy on different fields, regardless of whether PolicyPlugin is used. In practice, without PolicyPlugin $setAuth() is a no-op and queries bypass DISTINCT ON generation entirely, masking the bug. When PolicyPlugin is enabled (to enforce @@allow/@@deny access control), these queries surface the error and crash.

[ymc9]

Fixed in 3.5.6