GroupBy fails with ambiguous column error when where clause filters on a table that shares the aggregated column #28084
Description
Bug description
I'm running a groupBy query, aggregating a column (in the example named age). When I include a where clause that refers to another table on a 1:1 relation, and the other table also includes an age column, Postgres throws an error about the age column being ambiguous.
> ts-node ./src/queries.ts
/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:237
throw new PrismaClientUnknownRequestError(message, {
^
PrismaClientUnknownRequestError:
Invalid `prisma.teacher.groupBy()` invocation in
/Users/dick/work/prisma-txn-order/src/queries.ts:15:48
12
13 async function main() {
14
→ 15 const groupedTeachers = await prisma.teacher.groupBy(
Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "42702", message: "column reference \"age\" is ambiguous", severity: "ERROR", detail: None, column: None, hint: None }), transient: false })
at ei.handleRequestError (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:237:13)
at ei.handleAndLogRequestError (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:174:12)
at ei.request (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:143:12)
at async a (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/getPrismaClient.ts:833:24) {
clientVersion: '6.16.1'
}
Severity
🔹 Minor: Unexpected behavior, but does not block development
Reproduction
Running the query with the schema.
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Teacher {
id String @id @default(cuid())
name String
age Int
assistant Assistant?
}
model Assistant {
id String @id @default(cuid())
name String
age Int
teacher Teacher @relation(fields: [teacherId], references: [id])
teacherId String @unique
}
const groupedTeachers = await prisma.teacher.groupBy({
by: ["name"],
_sum: {
age: true,
},
where: {
assistant: {
name: {
contains: "John",
},
},
},
});
Expected vs. Actual Behavior
I would expect the joined tables in the where clause to be namespaced so that it's clear the aggregation applies to the primary table.
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
yes, it was working in 6.14.
Workaround
downgrade to 6.14
Prisma Schema & Queries
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Teacher {
id String @id @default(cuid())
name String
age Int
assistant Assistant?
}
model Assistant {
id String @id @default(cuid())
name String
age Int
teacher Teacher @relation(fields: [teacherId], references: [id])
teacherId String @unique
}
const groupedTeachers = await prisma.teacher.groupBy({
by: ["name"],
_sum: {
age: true,
},
where: {
assistant: {
name: {
contains: "John",
},
},
},
});
Prisma Config
No response
Logs & Debug Info
> queries
> ts-node ./src/queries.ts
prisma:tryLoadEnv Environment variables not found at null +4ms
prisma:tryLoadEnv Environment variables loaded from /Users/dick/work/prisma-txn-order/.env +0ms
prisma:client checkPlatformCaching:postinstall undefined +1ms
prisma:client checkPlatformCaching:ciName undefined +0ms
prisma:client dirname /Users/dick/work/prisma-txn-order/node_modules/.prisma/client +0ms
prisma:client relativePath ../../../prisma +0ms
prisma:client cwd /Users/dick/work/prisma-txn-order/prisma +0ms
prisma:client clientVersion 6.16.1 +0ms
prisma:client:libraryEngine internalSetup +1ms
prisma:client Prisma Client call: +1ms
prisma:client prisma.teacher.groupBy({
select: {
_sum: {
select: {
age: true
}
},
name: true
},
by: [
"name"
],
where: {
assistant: {
name: {
contains: "John"
}
}
}
}) +0ms
prisma:client Generated request: +1ms
prisma:client {
"modelName": "Teacher",
"action": "groupBy",
"query": {
"arguments": {
"by": [
"name"
],
"where": {
"assistant": {
"name": {
"contains": "John"
}
}
}
},
"selection": {
"_sum": {
"arguments": {},
"selection": {
"age": true
}
},
"name": true
}
}
}
+0ms
prisma:client:libraryEngine sending request, this.libraryStarted: false +0ms
prisma:client:engines:resolveEnginePath enginePath /Users/dick/work/prisma-txn-order/node_modules/.prisma/client/libquery_engine-darwin-arm64.dylib.node +0ms
prisma:client:libraryEngine Using driver adapter: '{\n' +
' "adapterName": "@prisma/adapter-pg",\n' +
' "errorRegistry": {\n' +
' "registeredErrors": []\n' +
' },\n' +
' "provider": "postgres"\n' +
'}' +8ms
prisma:client:libraryEngine library starting +10ms
prisma:client:libraryEngine library started +8ms
prisma:driver-adapter:pg [js::query_raw] '{\n' +
' "sql": "SELECT SUM(\\"age\\") AS \\"_sum$age\\", \\"public\\".\\"Teacher\\".\\"name\\" FROM \\"public\\".\\"Teacher\\" LEFT JOIN \\"public\\".\\"Assistant\\" AS \\"j0\\" ON (\\"j0\\".\\"teacherId\\") = (\\"public\\".\\"Teacher\\".\\"id\\") WHERE (\\"j0\\".\\"name\\"::text LIKE $1 AND (\\"j0\\".\\"id\\" IS NOT NULL)) GROUP BY \\"public\\".\\"Teacher\\".\\"name\\" OFFSET $2",\n' +
' "args": [\n' +
' "%John%",\n' +
' 0\n' +
' ],\n' +
' "argTypes": [\n' +
' {\n' +
' "scalarType": "string",\n' +
' "arity": "scalar"\n' +
' },\n' +
' {\n' +
' "scalarType": "bigint",\n' +
' "arity": "scalar"\n' +
' }\n' +
' ]\n' +
'}' +59ms
prisma:driver-adapter:pg Error in performIO: '{\n' +
' "length": 108,\n' +
' "name": "error",\n' +
' "severity": "ERROR",\n' +
' "code": "42702",\n' +
' "position": "12",\n' +
' "file": "parse_relation.c",\n' +
' "line": "797",\n' +
' "routine": "scanRTEForColumn"\n' +
'}' +15ms
driver-adapter-utils [error@wrapAsync] {
"name": "DriverAdapterError",
"cause": {
"originalCode": "42702",
"originalMessage": "column reference \"age\" is ambiguous",
"kind": "postgres",
"code": "42702",
"severity": "ERROR",
"message": "column reference \"age\" is ambiguous"
}
} +26ms
prisma:client:request_handler {
"clientVersion": "6.16.1",
"name": "PrismaClientUnknownRequestError"
} +2ms
/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:237
throw new PrismaClientUnknownRequestError(message, {
^
PrismaClientUnknownRequestError:
Invalid `prisma.teacher.groupBy()` invocation in
/Users/dick/work/prisma-txn-order/src/queries.ts:13:48
10 const prisma = new PrismaClient({adapter})
11
12 async function main() {
→ 13 const groupedTeachers = await prisma.teacher.groupBy(
Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "42702", message: "column reference \"age\" is ambiguous", severity: "ERROR", detail: None, column: None, hint: None }), transient: false })
at ei.handleRequestError (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:237:13)
at ei.handleAndLogRequestError (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:174:12)
at ei.request (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/RequestHandler.ts:143:12)
at async a (/Users/dick/work/prisma-txn-order/node_modules/@prisma/client/src/runtime/getPrismaClient.ts:833:24) {
clientVersion: '6.16.1'
}
Environment & Setup
PostgreSQL + macos
Prisma Version
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
prisma : 6.16.1
@prisma/client : 6.16.1
Computed binaryTarget : darwin-arm64
Operating System : darwin
Architecture : arm64
Node.js : v22.16.0
TypeScript : 5.8.2
Query Engine (Node-API) : libquery-engine 1c57fdcd7e44b29b9313256c76699e91c3ac3c43 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
PSL : @prisma/prisma-schema-wasm 6.16.0-7.1c57fdcd7e44b29b9313256c76699e91c3ac3c43
Schema Engine : schema-engine-cli 1c57fdcd7e44b29b9313256c76699e91c3ac3c43 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Default Engines Hash : 1c57fdcd7e44b29b9313256c76699e91c3ac3c43
Studio : 0.511.0