Relation filters and `_count` on a delegate base model give wrong results when the related model is in the same hierarchy

[ErikDakoda]

Version

@zenstackhq/orm on dev at b51d33c4 (after 3.9.7). Same result on SQLite and PostgreSQL 17.

Summary

A relation filter or _count queried through a delegate base model gives a wrong result when the related model is a sub model of the same hierarchy. The same filter queried through the sub model is correct.

Reproduction

model Item {
  id            String @id @default(cuid())
  itemKind      String
  notesAsSource Note[] @relation("NoteSource")

  @@delegate(itemKind)
}

model Task extends Item {}

model Note extends Item {
  sourceId String?
  source   Item?   @relation("NoteSource", fields: [sourceId], references: [id])
}

const task = await db.task.create({ data: {} });
await db.note.create({ data: { sourceId: task.id } });

await db.item.findMany({ where: { notesAsSource: { some: {} } } }); // [] (expected: the task)
await db.item.count({ where: { notesAsSource: { some: {} } } }); // 0 (expected: 1)
await db.item.findMany({ where: { notesAsSource: { none: {} } } }); // both rows (expected: the note only)
await db.item.findMany({ include: { _count: { select: { notesAsSource: true } } } }); // task: 0 (expected: 1)

await db.task.findMany({ where: { notesAsSource: { some: {} } } }); // the task (correct)

A plain self relation without @@delegate works correctly.

Cause

This is the generated SQL for db.item.findMany({ where: { notesAsSource: { some: {} } } }) (SQLite):

select "Item"."id", ... from "Item"
  left join "Task" on "Item"."id" = "Task"."id"
  left join "Note" on "Item"."id" = "Note"."id"
where exists (
  select 1 from "Note" as "$$t1"
    left join "Item" on "$$t1"."id" = "Item"."id"
  where "Item"."id" = "$$t1"."sourceId"
)

The subquery joins Note's base table as "Item" without an alias. The correlation "Item"."id" = "$$t1"."sourceId" then reads the inner "Item" (the note's own base row) instead of the outer row. So it compares the note's id with its own sourceId, which never matches.

Through db.task, the outer table is "Task", so there is no name clash and the result is correct.

This looks like the same shadowing problem that 3.9.5 fixed for access policy rules that traverse a self relation, here in the join that the delegate sub model's base table adds to a relation subquery. A possible fix is to give that join a unique alias, as the subquery already does for "$$t1".

Environment

• Node.js 24.18.0
• pnpm 10.33.0