-
Notifications
You must be signed in to change notification settings - Fork 2.1k
Prisma 7.4 breaks cursor pagination when sorted by a nullable field #29254
Copy link
Copy link
Closed
Labels
bug/1-unconfirmedBug should have enough information for reproduction, but confirmation has not happened yet.Bug should have enough information for reproduction, but confirmation has not happened yet.kind/bugA reported bug.A reported bug.
Description
Bug description
A findMany query with cursor pagination and orderBy on a nullable field returns an empty array on the second call with a different cursor.
The first call works correctly. Also the generated query seems to be correct but subsequent calls fetching more pages return no result.
Maybe related to the query cache or issues like those #29176 or #28614
Severity
Reproduction
import "dotenv/config";
import { test, before, after } from "node:test";
import assert from "node:assert/strict";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client.ts";
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! });
const prisma = new PrismaClient({
adapter,
log: [
{
emit: "event",
level: "query",
},
],
});
prisma.$on("query", (e) => {
console.log("Query: " + e.query);
console.log("Params: " + e.params);
});
let items: { id: string; priceNullable: number | null; price: number }[];
let itemsByNullable: typeof items;
const ORDER_BY = [{ price: "asc" as const }, { id: "asc" as const }];
const ORDER_BY_NULLABLE = [
{ priceNullable: "asc" as const },
{ id: "asc" as const },
];
before(async () => {
await prisma.item.deleteMany();
await prisma.item.createMany({
data: [
{ id: "one", price: 10, priceNullable: 10 },
{ id: "two", price: 20, priceNullable: 20 },
{ id: "three", price: 30, priceNullable: 30 },
{ id: "four", price: 40, priceNullable: 40 },
],
});
items = await prisma.item.findMany({ orderBy: ORDER_BY });
itemsByNullable = await prisma.item.findMany({ orderBy: ORDER_BY_NULLABLE });
});
after(async () => {
// await prisma.item.deleteMany();
await prisma.$disconnect();
});
test("1. succeeds if cursor sits at the LOWEST price ordered by non-nullable field", async () => {
console.log("running test 1");
const result = await prisma.item.findMany({
orderBy: ORDER_BY,
cursor: { id: "one" },
skip: 1,
take: 1,
});
assert.equal(result[0].id, "two");
});
test("2. succeeds if cursor sits at the SECOND LOWEST price ordered by non-nullable field", async () => {
console.log("running test 2");
const result = await prisma.item.findMany({
orderBy: ORDER_BY,
cursor: { id: "two" },
skip: 1,
take: 1,
});
assert.equal(result[0].id, "three");
});
test("3. succeeds if cursor sits at the LOWEST price ordered by nullable field", async () => {
console.log("running test 3");
const result = await prisma.item.findMany({
orderBy: ORDER_BY_NULLABLE,
cursor: { id: "one" },
skip: 1,
take: 1,
});
assert.equal(result[0].id, "two");
});
// this test fails unexpectedly
test("4. succeeds if cursor sits at the SECOND LOWEST price ordered by nullable field (BUG)", async () => {
console.log("running test 4");
const result = await prisma.item.findMany({
orderBy: ORDER_BY_NULLABLE,
cursor: { id: "two" },
skip: 1,
take: 1,
});
assert.equal(result.length, 1);
assert.equal(result[0].id, "three");
});Expected vs. Actual Behavior
Should return the next page (id: "three") but returns an empty array
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
Yes, worked in Prisma 5.x and 7.3, broken in 7.4.1
Workaround
No acceptable workaround found
Prisma Schema & Queries
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "postgresql"
}
model Item {
id String @id @default(cuid())
priceNullable Float?
price Float
}Prisma Config
import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
schema: "./prisma/schema.prisma",
datasource: {
url: env("DATABASE_URL"),
},
});Logs & Debug Info
running test 3
prisma:client Prisma Client call: +1ms
prisma:client prisma.item.findMany({
orderBy: [
{
priceNullable: "asc"
},
{
id: "asc"
}
],
cursor: {
id: "one"
},
skip: 1,
take: 1
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
"modelName": "Item",
"action": "findMany",
"query": {
"arguments": {
"orderBy": [
{
"priceNullable": "asc"
},
{
"id": "asc"
}
],
"cursor": {
"id": "one"
},
"skip": 1,
"take": 1
},
"selection": {
"$composites": true,
"$scalars": true
}
}
}
+0ms
prisma:client:clientEngine sending request +0ms
prisma:client:clientEngine query plan cache miss +0ms
prisma:client:clientEngine query plan created {
"type": "dataMap",
"args": {
"expr": {
"type": "process",
"args": {
"expr": {
"type": "query",
"args": {
"type": "templateSql",
"fragments": [
{
"type": "stringChunk",
"chunk": "SELECT \"public\".\"Item\".\"id\", \"public\".\"Item\".\"priceNullable\", \"public\".\"Item\".\"price\" FROM \"public\".\"Item\" WHERE (((\"public\".\"Item\".\"priceNullable\" = (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) OR (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) IS NULL OR \"public\".\"Item\".\"priceNullable\" IS NULL) AND \"public\".\"Item\".\"id\" >= (SELECT \"public\".\"Item\".\"id\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": "))) OR ((\"public\".\"Item\".\"priceNullable\" > (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) OR \"public\".\"Item\".\"priceNullable\" IS NULL OR (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) IS NULL))) ORDER BY \"public\".\"Item\".\"priceNullable\" ASC, \"public\".\"Item\".\"id\" ASC OFFSET "
},
{
"type": "parameter"
}
],
"args": [
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
"0"
],
"argTypes": [
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "bigint",
"dbType": null
}
],
"placeholderFormat": {
"prefix": "$",
"hasNumbering": true
},
"chunkable": false
}
},
"operations": {
"pagination": {
"cursor": {
"id": {
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
}
},
"take": 1,
"skip": 1
},
"distinct": null,
"reverse": false,
"nested": {},
"linkingFields": null
}
}
},
"structure": {
"type": "object",
"serializedName": null,
"fields": {
"id": {
"type": "field",
"dbName": "id",
"fieldType": {
"arity": "required",
"type": "string"
}
},
"priceNullable": {
"type": "field",
"dbName": "priceNullable",
"fieldType": {
"arity": "optional",
"type": "float"
}
},
"price": {
"type": "field",
"dbName": "price",
"fieldType": {
"arity": "required",
"type": "float"
}
}
},
"skipNulls": false
},
"enums": {}
}
} +3ms
prisma:driver-adapter:pg [js::query_raw] '{\n' +
' "sql": "SELECT \\"public\\".\\"Item\\".\\"id\\", \\"public\\".\\"Item\\".\\"priceNullable\\", \\"public\\".\\"Item\\".\\"price\\" FROM \\"public\\".\\"Item\\" WHERE (((\\"public\\".\\"Item\\".\\"priceNullable\\" = (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($1)) OR (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($2)) IS NULL OR \\"public\\".\\"Item\\".\\"priceNullable\\" IS NULL) AND \\"public\\".\\"Item\\".\\"id\\" >= (SELECT \\"public\\".\\"Item\\".\\"id\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($3))) OR ((\\"public\\".\\"Item\\".\\"priceNullable\\" > (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($4)) OR \\"public\\".\\"Item\\".\\"priceNullable\\" IS NULL OR (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($5)) IS NULL))) ORDER BY \\"public\\".\\"Item\\".\\"priceNullable\\" ASC, \\"public\\".\\"Item\\".\\"id\\" ASC OFFSET $6",\n' +
' "args": [\n' +
' "one",\n' +
' "one",\n' +
' "one",\n' +
' "one",\n' +
' "one",\n' +
' "0"\n' +
' ],\n' +
' "argTypes": [\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "bigint",\n' +
' "dbType": null\n' +
' }\n' +
' ]\n' +
'}' +5ms
Query: SELECT "public"."Item"."id", "public"."Item"."priceNullable", "public"."Item"."price" FROM "public"."Item" WHERE ((("public"."Item"."priceNullable" = (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($1)) OR (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($2)) IS NULL OR "public"."Item"."priceNullable" IS NULL) AND "public"."Item"."id" >= (SELECT "public"."Item"."id" FROM "public"."Item" WHERE ("public"."Item"."id") = ($3))) OR (("public"."Item"."priceNullable" > (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($4)) OR "public"."Item"."priceNullable" IS NULL OR (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($5)) IS NULL))) ORDER BY "public"."Item"."priceNullable" ASC, "public"."Item"."id" ASC OFFSET $6
Params: ["one","one","one","one","one","0"]
prisma:client:clientEngine query plan executed +2ms
running test 4
prisma:client Prisma Client call: +1ms
prisma:client prisma.item.findMany({
orderBy: [
{
priceNullable: "asc"
},
{
id: "asc"
}
],
cursor: {
id: "two"
},
skip: 1,
take: 1
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
"modelName": "Item",
"action": "findMany",
"query": {
"arguments": {
"orderBy": [
{
"priceNullable": "asc"
},
{
"id": "asc"
}
],
"cursor": {
"id": "two"
},
"skip": 1,
"take": 1
},
"selection": {
"$composites": true,
"$scalars": true
}
}
}
+0ms
prisma:client:clientEngine sending request +0ms
prisma:client:clientEngine query plan cache hit +0ms
prisma:client:clientEngine query plan created {
"type": "dataMap",
"args": {
"expr": {
"type": "process",
"args": {
"expr": {
"type": "query",
"args": {
"type": "templateSql",
"fragments": [
{
"type": "stringChunk",
"chunk": "SELECT \"public\".\"Item\".\"id\", \"public\".\"Item\".\"priceNullable\", \"public\".\"Item\".\"price\" FROM \"public\".\"Item\" WHERE (((\"public\".\"Item\".\"priceNullable\" = (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) OR (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) IS NULL OR \"public\".\"Item\".\"priceNullable\" IS NULL) AND \"public\".\"Item\".\"id\" >= (SELECT \"public\".\"Item\".\"id\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": "))) OR ((\"public\".\"Item\".\"priceNullable\" > (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) OR \"public\".\"Item\".\"priceNullable\" IS NULL OR (SELECT \"public\".\"Item\".\"priceNullable\" FROM \"public\".\"Item\" WHERE (\"public\".\"Item\".\"id\") = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")) IS NULL))) ORDER BY \"public\".\"Item\".\"priceNullable\" ASC, \"public\".\"Item\".\"id\" ASC OFFSET "
},
{
"type": "parameter"
}
],
"args": [
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
"0"
],
"argTypes": [
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": "TEXT"
},
{
"arity": "scalar",
"scalarType": "bigint",
"dbType": null
}
],
"placeholderFormat": {
"prefix": "$",
"hasNumbering": true
},
"chunkable": false
}
},
"operations": {
"pagination": {
"cursor": {
"id": "one"
},
"take": 1,
"skip": 1
},
"distinct": null,
"reverse": false,
"nested": {},
"linkingFields": null
}
}
},
"structure": {
"type": "object",
"serializedName": null,
"fields": {
"id": {
"type": "field",
"dbName": "id",
"fieldType": {
"arity": "required",
"type": "string"
}
},
"priceNullable": {
"type": "field",
"dbName": "priceNullable",
"fieldType": {
"arity": "optional",
"type": "float"
}
},
"price": {
"type": "field",
"dbName": "price",
"fieldType": {
"arity": "required",
"type": "float"
}
}
},
"skipNulls": false
},
"enums": {}
}
} +0ms
prisma:driver-adapter:pg [js::query_raw] '{\n' +
' "sql": "SELECT \\"public\\".\\"Item\\".\\"id\\", \\"public\\".\\"Item\\".\\"priceNullable\\", \\"public\\".\\"Item\\".\\"price\\" FROM \\"public\\".\\"Item\\" WHERE (((\\"public\\".\\"Item\\".\\"priceNullable\\" = (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($1)) OR (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($2)) IS NULL OR \\"public\\".\\"Item\\".\\"priceNullable\\" IS NULL) AND \\"public\\".\\"Item\\".\\"id\\" >= (SELECT \\"public\\".\\"Item\\".\\"id\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($3))) OR ((\\"public\\".\\"Item\\".\\"priceNullable\\" > (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($4)) OR \\"public\\".\\"Item\\".\\"priceNullable\\" IS NULL OR (SELECT \\"public\\".\\"Item\\".\\"priceNullable\\" FROM \\"public\\".\\"Item\\" WHERE (\\"public\\".\\"Item\\".\\"id\\") = ($5)) IS NULL))) ORDER BY \\"public\\".\\"Item\\".\\"priceNullable\\" ASC, \\"public\\".\\"Item\\".\\"id\\" ASC OFFSET $6",\n' +
' "args": [\n' +
' "two",\n' +
' "two",\n' +
' "two",\n' +
' "two",\n' +
' "two",\n' +
' "0"\n' +
' ],\n' +
' "argTypes": [\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": "TEXT"\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "bigint",\n' +
' "dbType": null\n' +
' }\n' +
' ]\n' +
'}' +3ms
✔ 3. succeeds if cursor sits at the LOWEST price ordered by nullable field (95.70325ms)
Query: SELECT "public"."Item"."id", "public"."Item"."priceNullable", "public"."Item"."price" FROM "public"."Item" WHERE ((("public"."Item"."priceNullable" = (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($1)) OR (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($2)) IS NULL OR "public"."Item"."priceNullable" IS NULL) AND "public"."Item"."id" >= (SELECT "public"."Item"."id" FROM "public"."Item" WHERE ("public"."Item"."id") = ($3))) OR (("public"."Item"."priceNullable" > (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($4)) OR "public"."Item"."priceNullable" IS NULL OR (SELECT "public"."Item"."priceNullable" FROM "public"."Item" WHERE ("public"."Item"."id") = ($5)) IS NULL))) ORDER BY "public"."Item"."priceNullable" ASC, "public"."Item"."id" ASC OFFSET $6
Params: ["two","two","two","two","two","0"]
prisma:client:clientEngine query plan executed +1ms
✖ 4. succeeds if cursor sits at the SECOND LOWEST price ordered by nullable field (BUG) (2.550042ms)
Environment & Setup
- OS: macOS
- Database: PostgreSQL
- Node.js version: v22.20.0
Prisma Version
prisma : 7.4.1
@prisma/client : 7.4.1
Operating System : darwin
Architecture : arm64
Node.js : v22.20.0
TypeScript : 5.9.3
Query Compiler : enabled
PSL : @prisma/prisma-schema-wasm 7.5.0-4.55ae170b1ced7fc6ed07a15f110549408c501bb3
Schema Engine : schema-engine-cli 55ae170b1ced7fc6ed07a15f110549408c501bb3 (at node_modules/.pnpm/@prisma+engines@7.4.1/node_modules/@prisma/engines/schema-engine-darwin-arm64)
Default Engines Hash : 55ae170b1ced7fc6ed07a15f110549408c501bb3
Studio : 0.13.1
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
bug/1-unconfirmedBug should have enough information for reproduction, but confirmation has not happened yet.Bug should have enough information for reproduction, but confirmation has not happened yet.kind/bugA reported bug.A reported bug.