[BUG] [Postgres/Kysely] "missing FROM-clause entry" when including relation with specific @@allow policy

[Natansal]

Issue Description

I am encountering a missing FROM-clause entry error when running a findMany query that includes a relation (User -> Association).

The issue seems to be caused by a specific Access Policy on the related model (Association) that compares a field to a static string (or potentially an injected value). ZenStack generates a SQL query where it attempts to use the outer alias (User$associations$sub) inside the inner LATERAL subquery scope, where that alias is not yet defined.

Reproduction

1. Schema (schema.zmodel)

model User {
    id String @id @default(uuid())
    associations Association[]

    @@allow('all', true)
}

model Association {
    id String @id @default(uuid())
    userId String
    user User @relation(fields: [userId], references: [id])
    organizationId String?

    // 💥 THE TRIGGER: This policy causes the SQL generation error
    @@allow('read', organizationId == 'some-static-uuid')
    
    // (Note: It also happens if checking against auth() or other conditions, 
    // but this specific structure triggers the alias scoping bug)
}

2. The Query
Running a standard findMany with an include:

const users = await db.user.findMany({
    where: { id: 'some-user-id' },
    include: {
        associations: true // <--- Trigger
    }
});

Error Log

kysely:error: error: missing FROM-clause entry for table "User$associations$sub"
    at .../pg/lib/client.js:624:17
    ...
    at PostgresConnection.executeQuery (.../kysely/dist/cjs/dialect/postgres/postgres-driver.js:93:49)
    ...
    at FindOperationHandler.read (.../zenstack/orm/src/client/crud/operations/base.ts:304:23)

ERROR [ExceptionsHandler] ORMError: Failed to execute query: error: missing FROM-clause entry for table "User$associations$sub"

The Malformed SQL

The generated SQL attempts to filter by User$associations$sub.organizationId inside the subquery that defines User$associations$sub.

LEFT JOIN LATERAL (
  SELECT ...
  FROM (
    SELECT ...
    FROM "public"."association" as "Association"
    -- ❌ BUG: "User$associations$sub" is not defined in this scope. 
    -- It is defined as the alias for the *result* of this subquery (see line below).
    WHERE "User$associations$sub"."organizationId" = $94 
  ) as "User$associations$sub" -- 👈 Alias defined here
  WHERE "User$associations$sub"."userId" = "User"."id"
) as "User$associations" ON true

Environment

• Database: PostgreSQL
• Adapter: Kysely (via @zenstackhq/orm)
• Framework: NestJS
• Packages:
• @zenstackhq/orm: 3.3.3 (or your current version)
• zenstack: 3.3.3
• kysely: 0.28.11
• pg: 8.18.0

Possible Cause

It appears the Kysely query generator is injecting the Access Policy filter (WHERE organizationId = ...) using the outer alias (User$associations$sub) instead of the inner table name (Association) when constructing the LATERAL JOIN subquery. Postgres scoping rules prevent the inner query from seeing the alias assigned to its own result.