SQL builder: an outer query's column used inside a subquery renders without its table and binds to the inner table

What happened

In an application we are building on Prisma 8, we wrote a correlated EXISTS with the typed SQL builder. Inside the subquery we read the outer query's column through the top-level shortcut, f.id. The builder rendered it as a bare "id", with no table. Postgres resolves a bare name against the subquery's own table first. The inner table also has an id column, so the condition became actor."contactId" = actor.id. The EXISTS no longer depended on the outer row. There was no type error and no runtime error. The query returned the wrong rows.

The same column written through the table's namespace, f.contact.id, renders as "contact"."id" and returns the right rows.

Reproduction

Versions: @prisma/orm-postgres 8.0.0-rc.12, prisma 8.0.0-rc.17, TypeScript 6.0.3, Node 24.13.0. The database is @electric-sql/pglite 0.5.8, so no server is needed. The same SQL comes out of @prisma/orm-postgres 8.0.0-rc.13-dev.2. The cause below is read from main at d136137.

contract.prisma:

// use prisma-8

enum actor_kind {
  @@type("pg/text@1")
  human = "human"
  agent = "agent"
}

model Contact {
  id   String @id
  name String

  @@map("contact")
}

model Actor {
  id        String     @id
  kind      actor_kind
  contact   Contact?   @relation(fields: [contactId], references: [id])
  contactId String?

  @@map("actor")
}

prisma.config.ts:

import { defineConfig } from '@prisma/orm-postgres/config';
import { definePrismaConfig } from 'prisma/config';

export default definePrismaConfig({
  orm: defineConfig({ contract: './contract.prisma' }),
});

repro.ts:

import { PGlite } from '@electric-sql/pglite';
import { createPostgresAdapter } from '@prisma/orm-postgres/adapter/adapter';
import postgres from '@prisma/orm-postgres/runtime';
import type { Contract } from './contract.d.ts';
import contractJson from './contract.json' with { type: 'json' };

const { sql, contract } = postgres<Contract>({ contractJson });

// Contacts that have at least one actor.
const shortcut = sql.public.contact
  .select('id', 'name')
  .where((f, fns) =>
    fns.exists(sql.public.actor.select('id').where((a, inner) => inner.eq(a.contactId, f.id))),
  );

const namespaced = sql.public.contact
  .select('id', 'name')
  .where((f, fns) =>
    fns.exists(
      sql.public.actor.select('id').where((a, inner) => inner.eq(a.contactId, f.contact.id)),
    ),
  );

const db = new PGlite();
await db.exec(`
  CREATE TABLE contact (id text PRIMARY KEY, name text NOT NULL);
  CREATE TABLE actor (id text PRIMARY KEY, kind text NOT NULL, "contactId" text REFERENCES contact(id));
  INSERT INTO contact VALUES ('c1', 'Ada'), ('c2', 'Bob');
  INSERT INTO actor VALUES ('a1', 'human', 'c1');
`);

const adapter = createPostgresAdapter();
for (const [name, query] of Object.entries({ shortcut, namespaced })) {
  const { sql: text } = adapter.lower(query.build().ast, { contract });
  const { rows } = await db.query(text);
  console.log(name, text, JSON.stringify(rows));
}

Commands:

npm init -y
npm pkg set type=module
npm install @prisma/[email protected] [email protected] @electric-sql/[email protected]
npx prisma contract emit
node repro.ts

Output:

shortcut SELECT "id" AS "id", "name" AS "name" FROM "public"."contact" WHERE EXISTS (SELECT "id" AS "id" FROM "public"."actor" WHERE "contactId" = "id") []
namespaced SELECT "id" AS "id", "name" AS "name" FROM "public"."contact" WHERE EXISTS (SELECT "id" AS "id" FROM "public"."actor" WHERE "contactId" = "contact"."id") [{"id":"c1","name":"Ada"}]

Contact c1 has an actor, so both queries should return it. The first returns nothing.

An update has the same problem. With a third table ask that also has an id column, sql.public.ask.update(...).where((f, fns) => fns.exists(sql.public.contact.select('id').where((c, inner) => inner.eq(c.id, f.id)))) renders WHERE EXISTS (SELECT "id" AS "id" FROM "public"."contact" WHERE "id" = "id"). That is true for every row once contact has any row, so the update changed all three rows of our test ask table instead of none.

What was expected

The outer column renders with its table, as "contact"."id". If that is not possible, the type checker rejects the outer query's top-level field inside the subquery's callback.

Cause

• packages/2-sql/4-lanes/sql-builder/src/runtime/field-proxy.ts:12-16: a top-level field such as f.id becomes IdentifierRef.of(prop). Only a namespaced field such as f.contact.id becomes ColumnRef.of(namespace, column) (line 40).
• packages/2-sql/4-lanes/relational-core/src/ast/types.ts:550-562: IdentifierRef holds only the column name, so the table is lost when the field is read.
• packages/3-targets/6-adapters/postgres/src/core/sql-renderer.ts:675-676: the renderer prints an identifier-ref as a bare quoted name. A column-ref goes through renderColumn (line 510) and keeps its table. The SQLite adapter renders identifier-ref the same way (packages/3-targets/6-adapters/sqlite/src/core/adapter.ts:365); we did not test SQLite.
• packages/2-sql/4-lanes/sql-builder/src/expression.ts:29-37: FieldProxy returns an Expression that does not record which query it came from, so the outer f.id type-checks inside the inner where.

A bare name is correct while the query has one table in scope. It becomes wrong when a closure carries the expression into a subquery, and by then the expression no longer knows its table.

The unit test packages/2-sql/4-lanes/sql-builder/test/runtime/builders.test.ts:469-479 uses this exact pattern, pfns.eq(pf.user_id, f.id), but only checks that the where is an ExistsExpr. The integration test test/integration/test/sql-builder/subquery.test.ts:14 uses the namespaced form on both sides, so it passes.

A related case: the namespaced form also goes wrong when the subquery reads the same table as the outer query. To find contacts that share a name with another contact, fns.exists(sql.public.contact.select('id').where((c, inner) => inner.and(inner.eq(c.name, f.contact.name), inner.ne(c.id, f.contact.id)))) renders FROM "public"."contact" WHERE ("name" = "contact"."name" AND "id" != "contact"."id"). There "contact" names the inner table, so each row is compared with itself and the query returns nothing. Writing the inner table as sql.public.contact.as('other') gives the right rows.

Suggested fix

1. In createFieldProxy, return a ColumnRef for a top-level field that comes from a table in scope, qualified with the one namespace that holds it. MergeScopes already drops names that are ambiguous, so there is at most one. Keep IdentifierRef only for select-list aliases, which orderBy, groupBy and distinctOn need (packages/2-sql/4-lanes/sql-builder/src/runtime/builder-base.ts:395, 425, 459).
2. When a subquery reads a table whose name is already used by the outer query, give the inner table a fresh alias, or refuse the query with an error that asks for .as(...).
3. Add an integration test that runs a correlated EXISTS written with the top-level shortcut, where the inner and outer tables share a column name.

Why it matters

A correlated EXISTS that loses its correlation returns wrong rows, and in an update it changes every row, with no error when the query is built, type-checked or run. The top-level shortcut is the form the shipped docs use in their where examples, and nothing tells the user it is unsafe inside a subquery.