Invalid result when using take (LIMIT)

[israelins85]

Description and expected behavior
Using take on enhanced prisma returns different result than prisma for the same expected result.

Exemple code:

• usuarioId on Aluno table is the user id

`
export async function getPrismaEnhanced(req: NextApiRequest, res: NextApiResponse) {
const userId = await userIdFromRequest(req);
let user: auth.Usuario | null = null;

if (userId != null) {
    user = { id: userId };
} else {
    sendError(res, StatusCodes.LOCKED, "LOCKED");
    return;
}

const dispositivoId =
    req.headers["x-dispositivo-id"] ?? req.headers["x-device-id"];
if (dispositivoId != null) {
    prisma.$executeRawUnsafe(`SET var.device_id = '${dispositivoId}'`);
}

const teste = await prisma.aluno.findMany({
    take: 10,
    where: {
        usuarioId: userId,
    },
});

console.log("teste", teste);

const prismaEnhanced = enhance(
    prisma,
    { user: user ?? undefined },
    {
        logPrismaQuery: true,
    },
);

const teste2 = await prismaEnhanced.aluno.findMany({
    take: 10,
});

console.log("teste2", teste2);

return prismaEnhanced;

}`

Logging the statements at prisma level on pure prisma:
SELECT "public"."aluno"."id",
"public"."aluno"."usuarioid",
"public"."aluno"."dthrcriacao",
"public"."aluno"."dthratualizacao",
"public"."aluno"."nome",
"public"."aluno"."sobrenome",
"public"."aluno"."celular",
"public"."aluno"."email",
"public"."aluno"."observacoes",
"public"."aluno"."saldodeaulas",
"public"."aluno"."dtnascimento",
"public"."aluno"."peso",
"public"."aluno"."altura",
"public"."aluno"."removido"
FROM "public"."aluno"
WHERE "public"."aluno"."usuarioid" = $1
ORDER BY "public"."aluno"."id" ASC
LIMIT $2 offset $3

And on enhanced:

SELECT "public"."aluno"."id",
"public"."aluno"."usuarioid",
"public"."aluno"."dthrcriacao",
"public"."aluno"."dthratualizacao",
"public"."aluno"."nome",
"public"."aluno"."sobrenome",
"public"."aluno"."celular",
"public"."aluno"."email",
"public"."aluno"."observacoes",
"public"."aluno"."saldodeaulas",
"public"."aluno"."dtnascimento",
"public"."aluno"."peso",
"public"."aluno"."altura",
"public"."aluno"."removido"
FROM "public"."aluno"
WHERE 1 = 1
ORDER BY "public"."aluno"."id" ASC
LIMIT $1 offset $2

and some records are skipped:
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker
prisma:info [policy] dropping aluno entity due to entity checker

the records from other user...

An workaround is using on enhanced the same where

Environment (please complete the following information):

• ZenStack version: 2.2.3
• Prisma version: 5.15.0
• Database type: 16

Additional context
I guess need be highlighted the fact that the where need be still used;

[ymc9]

Hi @israelins85 , thanks for reporting this. Could you share a ZModel snippet that can be used to reproduce the problem?

[israelins85]

abstract model AbsModelPadrao {
  id              String   @id @default(dbgenerated("uuid_generate_v7()")) @db.Uuid

  dtHrCriacao     DateTime @default(now()) @db.Timestamptz
  dtHrAtualizacao DateTime @default(now()) @updatedAt() @db.Timestamptz
}

model Usuario extends AbsModelPadrao {
  nome              String
  sobrenome         String?
  login             String                @unique
  senha             String                @password @omit
  codIdioma         String                @default("en_US")
  dtFimTeste        DateTime              @db.Date @default(dbgenerated("(CURRENT_DATE + '30 days'::INTERVAL)")) @deny("update", true)

  pin               String?               @omit
  status            UsuarioStatus         @default(NaoAtivado)

  // users can change its own data
  @@allow("all", auth() != null && auth().id == id)

  @@auth()

  alunos            Aluno[]
}

abstract model AbsModelDadosDoUsuario {
  id              String   @id @default(dbgenerated("uuid_generate_v7()")) @db.Uuid
  usuarioId       String   @db.Uuid @default(auth().id)

  dtHrCriacao     DateTime @default(now()) @db.Timestamptz
  dtHrAtualizacao DateTime @default(now()) @updatedAt() @db.Timestamptz

  // relations
  usuario         Usuario  @relation(fields: [usuarioId], references: [id], onDelete: Cascade)

  // allow acess to myself
  @@allow("all", auth() != null && auth().id == usuarioId)

  @@index([usuarioId])
}

model Aluno extends AbsModelDadosDoUsuario {
  nome          String
  sobrenome     String?
  celular       String?
  email         String?
  observacoes   String?
  saldoDeAulas  Int                @default(0)
  dtNascimento  DateTime?          @db.Date
  peso          Float?
  altura        Float?
  removido      Boolean            @default(false)

  @@prisma.passthrough("@@index([nome(ops: raw(\"gin_trgm_ops\"))], type: Gin, name: \"Aluno_nome_idx\")")
}

[ymc9]

Hi @israelins85 , I had a try but couldn't reproduce this issue with v2.2.3. I've shared a project here: https://github.com/ymc9/issue-1519

Could you check what differences it may have with your project? Thanks!

[igorsimko]

Hey, I'm having same issue.

Example of such model would be:

model Space {
    id             String                   @id @default(cuid())
    createdAt      DateTime                 @default(now())
    updatedAt      DateTime                 @updatedAt
    name           String                   @unique @db.Text
    userAccess     UserAccess[]

    @@allow('read', userAccess?[(userId == auth().id && spaceId == this.id)])
}

model UserAccess {
    id      String  @id @default(cuid())
    user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
    userId  String
    space   Space   @relation(fields: [spaceId], references: [id], onDelete: Cascade)
    spaceId String

    @@allow('read', userId == auth().id)
}

model SpaceBot{
    id        String           @id @default(cuid())
    createdAt DateTime         @default(now())
    updatedAt DateTime         @updatedAt
    name      String
    space     Space            @relation(fields: [spaceId], references: [id])
    spaceId   String


    @@allow('read', space.userAccess?[userId == auth().id]
}

If I do:

db.spaceBot.findMany({take: 1})

and I have many SpaceBots where user has no access to, then result is just empty with one message:

prisma:info [policy] dropping patient entity due to entity checker

Prisma queries are like:

===================
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2
prisma:query SELECT "public"."UserAccess"."id", "public"."UserAccess"."userId", "public"."UserAccess"."spaceId" FROM "public"."UserAccess" WHERE "public"."UserAccess"."spaceId" IN ($1,$2,$3,$4,$5) OFFSET $6
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2

What I noticed is that generated queries do LIMIT and OFFSET furst on all records and then policy filters are applied which can lead to returning not expecting results.

[israelins85]

A workaround for this is always fill the where's with the expected filter of data, forgetting the fact zenstack will do this, because internally the limit runs without any where, and before returned to us they are filtered.

[igorsimko]

Thanks for suggestion. But I thought that's exactly what ZenStack is made for. I have feeling like something is off either with zmodel or there's bug. I'll try to reproduce it in new project and share it here.

[ymc9]

Hey, I'm having same issue.

Example of such model would be:

model Space {
    id             String                   @id @default(cuid())
    createdAt      DateTime                 @default(now())
    updatedAt      DateTime                 @updatedAt
    name           String                   @unique @db.Text
    userAccess     UserAccess[]

    @@allow('read', userAccess?[(userId == auth().id && spaceId == this.id)])
}

model UserAccess {
    id      String  @id @default(cuid())
    user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
    userId  String
    space   Space   @relation(fields: [spaceId], references: [id], onDelete: Cascade)
    spaceId String

    @@allow('read', userId == auth().id)
}

model SpaceBot{
    id        String           @id @default(cuid())
    createdAt DateTime         @default(now())
    updatedAt DateTime         @updatedAt
    name      String
    space     Space            @relation(fields: [spaceId], references: [id])
    spaceId   String


    @@allow('read', space.userAccess?[userId == auth().id]
}

If I do:

db.spaceBot.findMany({take: 1})

and I have many SpaceBots where user has no access to, then result is just empty with one message:

prisma:info [policy] dropping patient entity due to entity checker

Prisma queries are like:

===================
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2
prisma:query SELECT "public"."UserAccess"."id", "public"."UserAccess"."userId", "public"."UserAccess"."spaceId" FROM "public"."UserAccess" WHERE "public"."UserAccess"."spaceId" IN ($1,$2,$3,$4,$5) OFFSET $6
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2

What I noticed is that generated queries do LIMIT and OFFSET furst on all records and then policy filters are applied which can lead to returning not expecting results.

I tried this sample but still couldn't reproduce it. Could you guys see if you still run into it with the latest version?

[igorsimko]

Hey @ymc9, since I can't include real code/model where my problem occurs I missed one additional relation in example which I shared above.

However I managed to replicate an issue in forked repo from your example https://github.com/igorsimko/issue-1519. I can mainly see one difference: as soon as there is && in Collection Predicate Expression, it seems that take is not behaving as I'd expect.
(issue occurs at https://github.com/igorsimko/issue-1519/blob/main/script.ts#L55 and previous queries with take)

Could you have a look at my repo, maybe you can spot what's wrong there.

[igorsimko]

Hey @ymc9, did you by any chance have time to look into example I provided. It seems like quite a important bug to solve or at least to understand why it's behaving in that way. Any feedback would be very much approciated!

[ymc9]

Sorry I missed your reply @igorsimko . I'll check it and update back here.

[ymc9]

This is a tricky problem ... The root comes from the fact that Prisma doesn't support comparing fields from different tables.

For example, in the rule in https://github.com/igorsimko/issue-1519/blob/main/schema.zmodel:

model SpaceEnvironmentBot {
  ...
  @@allow('read', space.userAccess?[userId == auth().id && environmentId == this.environmentId])
}

The environmentId of SpaceEnvironmentBot is compared against environmentId of UserAccess. Since there's no way to achieve this via native Prisma calls, ZenStack falls back to checking the condition post-read, and apparently, this fallback doesn't work with fetching with a limit 🥲 - a fixed number of items are read but some of them are dropped.

I really wish Prisma could implement this feature ... before that, maybe ZenStack has to do repeated fetch - which is more complex and less performant.

Btw, I'm not sure if the cross-model field comparison is a must-have for your rules @igorsimko

[ymc9]

Btw, there's actually a check in ZenStack's validator that should capture comparisons between different models and report an error ... However your rules somehow didn't get caught by the check. I'll check why.