can't use date type as primary key

[wvq]

Bug description

If mysql date type field is not a primary key, the create function will success.
But define a Composite Primary Key with Integer and Date, will cause an error

How to reproduce

CREATE TABLE `test`  (
  `test_id` int(11) NOT NULL,
  `test_day` date NOT NULL,
  PRIMARY KEY (`test_id`, `test_day`) USING BTREE
);

import prisma from './src/prisma'

async function create() {
  await prisma.test.create({data: {testId: 1, testDay: new Date()}})
}

create()

output:

BEGIN []
INSERT INTO `testdb`.`test` (`test_id`,`test_day`) VALUES (?,?) [1,2021-08-27 16:00:00 UTC]
SELECT `testdb`.`test`.`test_id`, `testdb`.`test`.`test_day` FROM `testdb`.`test` WHERE (`testdb`.`test`.`test_id` = ? AND `testdb`.`test`.`test_day` = ?) LIMIT ? OFFSET ? [1,2021-08-27 16:00:00 UTC,1,0]
ROLLBACK []
BEGIN []
INSERT INTO `testdb`.`test` (`test_id`,`test_day`) VALUES (?,?) [1,2021-08-27 16:00:00 UTC]
SELECT `testdb`.`test`.`test_id`, `testdb`.`test`.`test_day` FROM `testdb`.`test` WHERE (`testdb`.`test`.`test_id` = ? AND `testdb`.`test`.`test_day` = ?) LIMIT ? OFFSET ? [1,2021-08-27 16:00:00 UTC,1,0]
ROLLBACK []
BEGIN []
INSERT INTO `testdb`.`test` (`test_id`,`test_day`) VALUES (?,?) [1,2021-08-27 16:00:00 UTC]
SELECT `testdb`.`test`.`test_id`, `testdb`.`test`.`test_day` FROM `testdb`.`test` WHERE (`testdb`.`test`.`test_id` = ? AND `testdb`.`test`.`test_day` = ?) LIMIT ? OFFSET ? [1,2021-08-27 16:00:00 UTC,1,0]
ROLLBACK []
/Users/wis/Workspaces/testdb/testprisma/node_modules/@prisma/client/runtime/index.js:36437
        throw new import_engine_core3.PrismaClientUnknownRequestError(message, this.client._clientVersion);
              ^

PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]: 
Invalid `prisma.test.create()` invocation:


  Query createOneTest is required to return data, but found no record(s).
    at RequestHandler.request (/Users/wis/Workspaces/testdb/testprisma/node_modules/@prisma/client/runtime/index.js:36437:15)
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  clientVersion: '2.30.0'
}

Expected behavior

No response

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Test {
  testId  Int      @map("test_id")
  testDay DateTime @db.Date @map("test_day")

  @@id([testId, testDay])
  @@map("test")
}

Environment & setup

• OS: Mac OS
• Database: MySQL
• Node.js version: v16.6.1

Prisma Version

prisma                : 2.30.0
@prisma/client        : 2.30.0
Current platform      : darwin
Query Engine (Binary) : query-engine 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine      : migration-engine-cli 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine  : introspection-core 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary         : prisma-fmt 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash  : 60b19f4a1de4fe95741da371b4c44a92f4d1adcb
Studio                : 0.422.0
Preview Features      : interactiveTransactions

[janpio]

Probably related for SQL Server: #10378

[shankuljain]

FWIW, As mentioned in #10378 , the workaround is removing milliseconds.

async function create() {
  await prisma.test.create({data: {testId: 1, testDay: new Date(new Date().setMilliseconds(0))}})
}

[wvq]

[wvq]

FWIW, As mentioned in #10378 , the workaround is removing milliseconds.

async function create() {
  await prisma.test.create({data: {testId: 1, testDay: new Date(new Date().setMilliseconds(0))}})
}

I knew this is a solution. And I use moment().startOf('day') to resolve this problem.
But if the api is designed by this way, for developers, WTF...

[janpio]

If you close the issue, we won't be able to track this and fix it. We are people and we make mistakes (or overlook details).

[wvq]

If you close the issue, we won't be able to track this and fix it. We are people and we make mistakes (or overlook details).

I'm sorry, commented yestoday, maybe click the wrong button.

[shankuljain]

FWIW, As mentioned in #10378 , the workaround is removing milliseconds.

async function create() {
  await prisma.test.create({data: {testId: 1, testDay: new Date(new Date().setMilliseconds(0))}})
}

I knew this is a solution. And I use moment().startOf('day') to resolve this problem. But if the api is designed by this way, for developers, WTF...

Agreed, just wanted to put this so other folks don't have to figure out themselves like I did.

[janpio]

Internal note: Another case of this is #12783 (but #10378 indeed has the probably correct explanation)

[pimeys]

Still reproducible with 4.3.1.

Debug logs:

  prisma:tryLoadEnv  Environment variables loaded from /prisma-8982/.env +0ms
  prisma:tryLoadEnv  Environment variables loaded from /prisma-8982/.env +2ms
  prisma:client  dirname /prisma-8982/node_modules/.prisma/client +0ms
  prisma:client  relativePath ../../../prisma +0ms
  prisma:client  cwd /prisma-8982/prisma +0ms
  prisma:client  clientVersion 4.3.1 +1ms
  prisma:client  clientEngineType library +0ms
  prisma:client:libraryEngine  internalSetup +0ms
  prisma:client:libraryEngine:loader  Searching for Query Engine Library in /prisma-8982/node_modules/.prisma/client +0ms
  prisma:client:libraryEngine:loader  loadEngine using /prisma-8982/node_modules/.prisma/client/libquery_engine-debian-openssl-1.1.x.so.node +0ms
  prisma:client  Prisma Client call: +47ms
  prisma:client  prisma.test.create({
  data: {
    testId: 1,
    testDay: new Date('2022-09-12T08:56:55.463Z')
  }
}) +1ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  createOneTest(data: {
    testId: 1
    testDay: "2022-09-12T08:56:55.463Z"
  }) {
    testId
    testDay
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +48ms
  prisma:client:libraryEngine  library starting +0ms
  prisma:client:libraryEngine  library started +2ms
  prisma:client:request_handler  PrismaClientUnknownRequestError: Query createOneTest is required to return data, but found no record(s).
    at prismaGraphQLToJSError (/prisma-8982/node_modules/@prisma/client/runtime/index.js:20433:10)
    at LibraryEngine.buildQueryError (/prisma-8982/node_modules/@prisma/client/runtime/index.js:26363:12)
    at LibraryEngine.request (/prisma-8982/node_modules/@prisma/client/runtime/index.js:26297:22)
    at async RequestHandler.request (/prisma-8982/node_modules/@prisma/client/runtime/index.js:29873:24)
    at async PrismaClient._request (/prisma-8982/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/prisma-8982/script.ts:6:3) {
  clientVersion: '4.3.1'
} +0ms
PrismaClientUnknownRequestError: 
Invalid `prisma.test.create()` invocation in
/prisma-8982/script.ts:6:21

  3 const prisma = new PrismaClient()
  4 
  5 async function main() {
→ 6   await prisma.test.create(
Query createOneTest is required to return data, but found no record(s).
    at RequestHandler.handleRequestError (/prisma-8982/node_modules/@prisma/client/runtime/index.js:29913:13)
    at RequestHandler.request (/prisma-8982/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async PrismaClient._request (/prisma-8982/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/prisma-8982/script.ts:6:3) {
  clientVersion: '4.3.1'
}
  prisma:client:libraryEngine  library stopping +34ms
  prisma:client:libraryEngine  library stopped +0ms
  prisma:client:libraryEngine:exitHooks  exit event received: exit +0ms

It's very related to some other similar issues using dates and times.