drizzle: negated operators (not_in, not_equals, not_like) on to-many paths use ANY-row semantics instead of NO-row (should compile to NOT EXISTS)

Describe the Bug

On the SQL adapters, negated operators (not_in, not_equals, not_like) against a sub-field reached through a to-many path — an array field's sub-field, or a hasMany relationship — match a document when any one of its rows fails the positive condition, instead of when no row matches it.

Since a to-many field with 2+ rows virtually always has some row that differs from the queried value, these clauses degrade to matching (nearly) every document. db-mongodb has the correct semantics for the same query ($nin/$ne against an array match only when no element matches), so the adapters silently disagree.

Mechanism, from current main (packages/drizzle/src/queries):

1. When a where path traverses an array or hasMany relationship, getTableColumnFromPath.ts pushes a LEFT JOIN to the rows table (eq(parent.id, subTable._parentID), marked isOneToMany: true) and resolves the path to a column on the joined table.
2. parseParams.ts applies the operator constraint directly to that joined column in the flat WHERE — identically for every operator, with no awareness that the column is on the "many" side of a join.
3. selectDistinct.ts dedupes the row fan-out.

That architecture gives EXISTS semantics ("some row matches") to every operator. For positive operators (equals, in, like, contains) that is the desired meaning on a to-many path. For negated operators it is wrong: per-row negation yields "at least one row differs" where the only useful meaning is "no row matches".

Concrete example (the nested-docs case from payloadcms/payload#17658): every doc's breadcrumbs array contains a self-row plus ancestor rows, so

where[breadcrumbs.doc][not_in][0]=391

compiles to roughly

SELECT DISTINCT c.id
FROM categories c
LEFT JOIN categories_breadcrumbs br ON br._parent_id = c.id
WHERE br.doc_id NOT IN (391)   -- per-row: true for any non-391 breadcrumb row

which matches every document (including all descendants of 391, each of which plainly carries a doc: 391 breadcrumb row in the same response). What it should compile to is the De Morgan form:

SELECT c.id
FROM categories c
WHERE NOT EXISTS (
  SELECT 1 FROM categories_breadcrumbs br
  WHERE br._parent_id = c.id
    AND br.doc_id IN (391)
)

The same applies to not_equals and not_like on any array sub-field or hasMany relationship, on db-postgres, db-vercel-postgres, and db-sqlite alike (shared packages/drizzle query builder).

Reproduction Steps

No custom code needed beyond an array field:

1. Any SQL adapter. A collection posts with an array field tags: [{ name: 'tag', type: 'text' }].
2. Create doc A with tags ["x", "y"], doc B with tags ["y"], doc C with no tags.
3. Query where[tags.tag][not_equals]=x (or where[tags.tag][not_in][0]=x).

Expected (and what db-mongodb returns for the equivalent data): B and C — documents where no tag is x.
Actual on SQL adapters: A, B — A matches because its y row satisfies the per-row <> 'x', and C is dropped because the LEFT JOIN produces a NULL row that fails the constraint. Both halves are wrong, in opposite directions.

The NOT EXISTS form fixes both at once: A is excluded (a row matches x), and C matches naturally (no rows at all), without the ad-hoc IS NULL OR ... wrappers parseParams.ts currently applies only to flat columns.

Impact / prior reports

• payloadcms/payload#17658 — plugin-nested-docs' default parentFilterOptions relies on 'breadcrumbs.doc': { not_in: [id] }, which this bug makes completely inert on Postgres: the parent picker offers the doc's own descendants, editors create circular references from the default UI, and the save then hangs in the plugin's unguarded ancestor walk (#16517).
• #5248 (2024) — filterOptions with not_in returning wrong results on Postgres once a relationship has a value; closed without a fix, reporter confirmed it still reproduced, now locked. Consistent with this root cause.
• PR #17769 works around the nested-docs symptom by resolving descendant IDs with a positive lookup and excluding by plain id: { not_in } — the right call at the plugin level precisely because it stops depending on these semantics. But the adapter bug remains for every user-written query or filterOptions using a negated operator on a to-many path, and nothing errors — queries just silently return wrong result sets.

Suggested direction

When the operator is not_in/not_equals/not_like and the resolved path crossed one or more isOneToMany joins, compile the positive form of the constraint into a correlated subquery over the joined table chain (the correlation predicate already exists as the join's condition) and wrap it in Drizzle's notExists(), instead of registering the join on the outer query.

Known complications, for whoever picks this up:

• getTableColumnFromPath eagerly mutates the shared joins array, so the join(s) added for a negated path need to be relocated into the subquery — but only when no other operator on the same path in the same where clause still needs the outer join.
• Needs to compose with nested arrays (chained joins), localized sub-tables (_locale conditions), hasMany relationships via the _rels table, blocks, and the polymorphic-relationship branch in parseParams.ts.
• It is a silent behavior change for any query currently compensating for the broken semantics, so it wants regression coverage across all three SQL adapters and probably a release note.

Environment Info

payload: 3.80.0
@payloadcms/db-postgres: 3.80.0
next: 16.2.1
Node: 22.22.1
Database: PostgreSQL 18

Reproduces on current main — the operator handling in packages/drizzle/src/queries/parseParams.ts has no to-many awareness for negated operators, and notExists is unused anywhere in packages/drizzle.