loadAfterMutationEntities builds invalid WHERE (id) IN () for a zero-match updateMany/deleteMany when any afterEntityMutation hook is registered (MySQL) #2884

Environment

• @zenstackhq/orm: 3.9.8 (latest at time of filing)
• Dialect: MySQL (mysql2) — no RETURNING support, so the affected code path always runs
• Also present on 3.9.8's PostgresDialect/SqliteDialect in principle if supportsReturning is false for a given setup, but confirmed specifically on MySQL

Summary

Registering any plugin with an onEntityMutation.afterEntityMutation hook causes every policy-denied (or otherwise zero-row-match) updateMany/deleteMany in the app to throw a SQL syntax error, regardless of which model the hook actually cares about. This is not scoped to the hooked model — hasEntityMutationPluginsWithAfterMutationHooks is a client-wide flag checked before any model name is known.

Root cause

In ZenStackQueryExecutor.loadAfterMutationEntities (src/client/executor/zenstack-query-executor.ts, compiled into dist/index.{mjs,cjs}):

async loadAfterMutationEntities(mutationInfo, queryNode, queryResult, connection, preUpdateIds) {
    if (mutationInfo.action === "delete") return;
    if (this.dialect.supportsReturning) return queryResult.rows;
    else {
        const mutatedIds = InsertQueryNode.is(queryNode)
            ? this.getInsertIds(mutationInfo.model, queryNode, queryResult)
            : preUpdateIds;
        if (mutatedIds) {                      // <-- bug: [] is truthy
            const idFields = requireIdFields(this.client.$schema, mutationInfo.model);
            const eb = expressionBuilder();
            const filter = eb(eb.refTuple(...idFields), "in",
                mutatedIds.map((idObj) => eb.tuple(...idFields.map((idField) => eb.val(idObj[idField])))));
            return await this.loadEntities(mutationInfo.model, WhereNode.create(filter.toOperationNode()), connection, void 0);
        } else {
            console.warn(`Unable to load after-mutation entities for hooks...`);
            return [];
        }
    }
}

getPreUpdateIds legitimately returns [] (not undefined) when the mutation's WHERE clause (e.g. after PolicyPlugin rewrites it to deny access) matches zero rows. But if (mutatedIds) treats an empty array as truthy, so it falls into the "build an IN (...) filter" branch with zero values — producing WHERE (id) IN (), which MySQL rejects as a syntax error. It should instead fall into (something like) the "nothing to reload" path, which currently only handles the undefined/"can't determine ids at all" case.

Minimal repro

Verified against an actual MySQL database (not just read from source) — both that this throws on unpatched 3.9.8, and that the suggested fix below resolves it cleanly. A drop-in regression test using your own test harness:

import { createPolicyTestClient } from '@zenstackhq/testtools';
import { describe, expect, it } from 'vitest';

describe('policy-denied updateMany + afterEntityMutation hook (mysql)', () => {
    it('resolves { count: 0 } instead of throwing a SQL syntax error', async () => {
        const schema = `
        model User {
            id    Int    @id @default(autoincrement())
            email String @unique
            // only an authed caller may update — anonymous is denied, 0 rows match
            @@allow('all', auth() != null)
        }
        `;

        const db = await createPolicyTestClient(schema, {
            provider: 'mysql',
            usePrismaPush: true,
            plugins: [
                {
                    id: 'noop',
                    // a trivial, DO-NOTHING hook is enough to trigger it
                    onEntityMutation: {
                        afterEntityMutation: async () => {},
                    },
                },
            ],
        });

        try {
            await db.$setAuth({ id: 1 }).user.create({ data: { id: 1, email: 'u1@test.com' } });

            const anon = db.$setAuth(undefined);

            // EXPECTED: resolves { count: 0 } (policy denies, zero rows match)
            // ACTUAL (3.9.8): throws — the executor builds `WHERE (id) IN ()`
            await expect(
                anon.user.updateMany({ where: {}, data: { email: 'u2@test.com' } }),
            ).resolves.toMatchObject({ count: 0 });
        } finally {
            await db.$disconnect?.();
        }
    });
});

Only reproduces on a dialect without RETURNING support — MysqlDialect.supportsReturning is false; SqliteDialect/PostgresDialect are both true and never hit the affected branch, so provider: 'mysql' is required for the repro (sqlite/postgres won't show it).

Expected behavior

A policy-denied or otherwise zero-match updateMany/deleteMany should resolve { count: 0 } as normal, with or without an afterEntityMutation hook registered — the hook simply sees zero afterMutationEntities.

Suggested fix

- if (mutatedIds) {
+ if (mutatedIds && mutatedIds.length > 0) {
    ... build IN-list, loadEntities ...
+ } else if (mutatedIds) {
+   // reliably determined ZERO mutated ids — nothing to reload, not the
+   // "can't determine ids at all" case below, so no warning either.
+   return [];
  } else {
    console.warn(`Unable to load after-mutation entities for hooks...`);
    return [];
  }

Happy to open a PR with this fix (+ a regression test mirroring the repro above) if that's welcome — we're currently shipping it as a local patch-package patch on our end.

Related

Not a duplicate of either, but same general area (registering an afterEntityMutation hook silently changes mutation-execution behavior elsewhere, for mutations unrelated to what the hook itself cares about):

• [Bug]: Update on delegate child model fails with "missing FROM-clause entry" when entity mutation plugins are present #2367 — registering any afterEntityMutation hook globally swaps RETURNING "id" → RETURNING *, breaking delegate/child-model updates
• $setAuth() on hook client breaks no-hook-trigger guarantee, causing infinite recursion #2493 — $setAuth() on the hook-provided client breaks the "won't re-trigger hooks" guarantee