postgres case-insensitive filtering with equals uses ILIKE

[shawnjones253]

Bug description

For Postgres, when using a filter with equals and mode: "insensitive", the query uses ILIKE.

How to reproduce

model BillingCustomer {
  id                 String          @id @default(uuid())
  email           String
}

await prisma.billingCustomer.findMany({
        where: {
          email: {
            equals: "ab",
            mode: "insensitive",
          },
        },
      });

console.log
    prisma:query SELECT "public"."BillingCustomer"."id", "public"."BillingCustomer"."email" FROM "public"."BillingCustomer" WHERE "public"."BillingCustomer"."email" ILIKE $1 OFFSET $2 ["ab",0]

Expected behavior

I'd expect this to do something like:

SELECT x from y where lower(z)='input'

This makes using user input dangerous since you now have to sanitize against % and _ (emails can contain _ so this makes it unusable for this particular use case).

Prisma information

see "how to reproduce"

Environment & setup

• OS: MacOS 13.3.1 (a)
• Database: PostgresQL
• Node.js version: v16.20.0

Prisma Version

prisma                  : 4.16.2
@prisma/client          : 4.16.2
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 4bc8b6e1b66cb932731fb1bdbbc550d1e010de81 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 4bc8b6e1b66cb932731fb1bdbbc550d1e010de81 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.16.1-1.4bc8b6e1b66cb932731fb1bdbbc550d1e010de81
Default Engines Hash    : 4bc8b6e1b66cb932731fb1bdbbc550d1e010de81
Studio                  : 0.484.0

[millsp]

Thanks for opening the issue, I can confirm this.

[janpio]

Just to make the queries more explicit:

Simple, case sensitive query:

const allUsers = await prisma.user.findMany({
  where: {
    email: {
      equals: "lomo@prisma.io",
      // mode: "insensitive",
    },
  },
});

SQL:

SELECT "public"."User"."id", "public"."User"."createdAt", "public"."User"."updatedAt", "public"."User"."email", "public"."User"."name", "public"."User"."age", "public"."User"."country", "public"."User"."role" FROM "public"."User" WHERE "public"."User"."email" = $1 OFFSET $2

And insensitive:

const allUsers = await prisma.user.findMany({
  where: {
    email: {
      equals: "lomo@prisma.io",
      mode: "insensitive",
    },
  },
});

SQL:

SELECT "public"."User"."id", "public"."User"."createdAt", "public"."User"."updatedAt", "public"."User"."email", "public"."User"."name", "public"."User"."age", "public"."User"."country", "public"."User"."role" FROM "public"."User" WHERE "public"."User"."email" ILIKE $1 OFFSET $2

Note though: The suggested that @shawnjones253 posted would not be identical to mode: insensitive, for that to be true you need another lower(...) around the input data as well. Another problem with this approach in general is that any existing simple index on that column will not be used any more - you will need to apply an expression index (which is unfortunately not supported yet by Prisma: #2504).

We could also consider escaping _ and % when equals is being used (vs. contains or something that would expect to pattern match).

[shawnjones253]

is there any update on this?