Bug description

When a MySQL view uses a subquery in the SELECT clause to retrieve an INT column, MySQL returns the value as BIGINT. In Prisma v6, relations based on this column work correctly (likely using loose equality comparison). However, in Prisma v7, the relation returns null instead of the related record, suggesting that v7 is using strict type comparison when matching relation keys.

Important note: Attempting to cast the subquery result to INT in the view definition (e.g., CAST(... AS SIGNED) or CAST(... AS INT)) does not resolve this issue - MySQL still returns the value as BIGINT.

Severity

🚨 Critical: Data loss, app crash, security issue

Reproduction

Repository with minimal reproduction: https://github.com/solokhind/prisma-v7-bug

Setup Steps

1. Clone the repository
2. Run the MySQL setup script:

   sudo mysql < setup.sql

3. Test Prisma v6:

   cd prisma-v6-project
   npm install
   npx prisma generate
   npm start

4. Test Prisma v7:

   cd prisma-v7-project
   npm install
   npx prisma generate
   npm start

Expected vs. Actual Behavior

Expected (Prisma v6 behavior):

[
  {
    "user_id": 1,
    "last_post_id": 1,
    "last_post": {
      "id": 1,
      "user_id": 1,
      "title": "First Post"
    }
  }
]

Actual (Prisma v7 behavior):

[
  {
    "user_id": 1,
    "last_post_id": 1,
    "last_post": null
  }
]

The last_post relation is null in v7 even though last_post_id matches a valid post ID.

Frequency

Consistently reproducible

Does this occur in development or production?

Both development and production

Is this a regression?

Yes, this worked in Prisma 6, broken in Prisma 7

Workaround

Bug doesn't occur with the relationJoins preview feature enabled.

Prisma Schema & Queries

Included in the shared repo.

Prisma Config

Included in the shared repo.

Logs & Debug Info

No response

Environment & Setup

• OS: Ubuntu 20.04 (Linux)
• Database: MySQL 8.0.42
• Node.js version: v22.14.0

Prisma Version

Prisma v6 (Working)

prisma                  : 6.19.2
@prisma/client          : 6.19.2
Computed binaryTarget   : debian-openssl-1.1.x
Operating System        : linux
Architecture            : x64
Node.js                 : v22.14.0
TypeScript              : 5.9.3
Query Engine (Node-API) : libquery-engine c2990dca591cba766e3b7ef5d9e8a84796e47ab7
Preview Features        : views

Prisma v7 (Broken)

prisma               : 7.3.0
@prisma/client       : 7.3.0
Operating System     : linux
Architecture         : x64
Node.js              : v22.14.0
TypeScript           : 5.9.3
Query Compiler       : enabled
PSL                  : @prisma/prisma-schema-wasm 7.3.0-16.9d6ad21cbbceab97458517b147a6a09ff43aa735
Schema Engine        : schema-engine-cli 9d6ad21cbbceab97458517b147a6a09ff43aa735
Preview Features     : views