Getting a "Raw query failed. Code: N/A. Message: error deserializing column 0: a Postgres value was NULL``" error when using Postgres ARRAY_AGG with a nullable column

[ruohola]

Bug description

I'm trying to use Postgres's ARRAY_AGG function with prisma.$queryRaw and get an error, but when I do the same query in psql (=PostgreSQL interactive terminal) I get no error.

How to reproduce

Minimal code that throws shows this issue:

schema.prisma:

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

model MyTable {
  mycolumn Int?

  @@map("mytable")
}

Insert two values with psql:

INSERT INTO mytable VALUES (123), (NULL);

Works fine when querying them in psql

SELECT ARRAY_AGG(mycolumn) FROM mytable;
 array_agg
-----------
 {123,NULL}
(1 row)

Try to do the same with Prisma:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

await prisma.$queryRaw`SELECT ARRAY_AGG(mycolymn) from mytable;`;

We get an error:

Invalid `prisma.queryRaw()` invocation:


  Raw query failed. Code: `N/A`. Message: `error deserializing column 0: a Postgres value was `NULL``

Expected behavior

No error.

Prisma information

Schema included above.

Environment & setup

• OS: Database in a Docker container, running Node.js/Prisma locally on macOS 12.1
• Database: postgres:12.8-alpine3.14 (Docker image)
• Node.js version: v14.18.3

Prisma Version

prisma                  : 3.8.1
@prisma/client          : 3.8.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f
Studio                  : 0.452.0

[ruohola]

I concede, it's impossible to escape backticks in PR titles :D

[matthewmueller]

We appreciate the effort! We have a handful of other raw issues. I see us tackling this all in one go soonish.