drizzle-team / drizzle-team/drizzle-orm

[BUG]: sum() and avg() drop the column's customType codec, max()/min() apply it (pg)

Open
#6,085 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

0.45.2

### What version of `drizzle-kit` are you using?

not involved, runtime bug

### Describe the Bug

`sum()` and `avg()` ignore the codec of the column they aggregate. A `customType` on a `numeric` column is applied on row reads and by `max()`/`min()`, but the sum/avg family hardcodes `.mapWith(String)` and returns the raw driver string.

It's visible in `src/sql/functions/aggregate.ts`, all in the same file:

```ts
export function max(expression: T): ... {
return sql`max(${expression})`.mapWith(is(expression, Column) ? expression : String) as any;
}

export function sum(expression: SQLWrapper): SQL {
return sql`sum(${expression})`.mapWith(String);
}
```

This is not #1146. That one was about the return type being inconvenient, and the answer was that sum is a string on purpose, use `.mapWith(Number)` if you know it fits. Fair enough for plain columns. The problem here is that a codec the user explicitly attached to the column is silently dropped, so whatever a customType is supposed to guarantee on a money column doesn't hold on exactly the queries that compute totals.

Repro (drizzle-orm 0.45.2, pg 8.22.0, Postgres 18):

```ts
const money = customType<{ data: { usd: string }; driverData: string }>({
dataType: () => 'numeric(10, 2)',
fromDriver: (value) => ({ usd: String(value) }),
toDriver: (value) => value.usd,
});

const accounts = pgTable('accounts', {
id: serial('id').primaryKey(),
balance: money('balance').notNull(),
});

await db.insert(accounts).values([{ balance: { usd: '10.10' } }, { balance: { usd: '10.10' } }]);

(await db.select().from(accounts))[0].balance; // { usd: '10.10' } codec applied
(await db.select({ m: max(accounts.balance) }).from(accounts))[0].m; // { usd: '10.10' } codec applied
(await db.select({ s: sum(accounts.balance) }).from(accounts))[0].s; // '20.20' raw string, codec dropped
(await db.select({ a: avg(accounts.balance) }).from(accounts))[0].a; // '10.1000000000000000'
```

Because the result is typed `string | null`, downstream arithmetic compiles and concatenates:

```ts
const [agg] = await db.select({ s: sum(accounts.balance) }).from(accounts);
agg.s! + agg.s!; // '20.2020.20'
agg.s! + 2.5; // '20.202.5'
```

Both compile clean. On a billing or ledger table that's a total that looks fine and is garbage.

The `.mapWith(Number)` workaround from #1146 also has a hole: the mapper isn't applied when the table is empty, so `sum(col).mapWith(Number)` is typed `number` and comes back `null` (related: #571):

```ts
const [e] = await db.select({ s: sum(empty.balance).mapWith(Number) }).from(empty);
e.s; // null, typed number
```

Desired result: when the aggregated expression is a column with a customType, run its `mapFromDriverValue`, same as `max()`/`min()` already do via `.mapWith(expression)`. I understand why that's not done unconditionally, `sum(int4col)` can exceed a JS number and max can't, so I'm not asking for `mapWith(column)` across the board. For customType columns the user owns the decoding, including overflow handling, and dropping their codec silently is the worst of the options.

Contributor guide

Open the contributing guide

Research direction

Start in src/sql/functions/aggregate.ts and run the PostgreSQL reproduction from the issue with a customType numeric column. Compare sum() and avg() with the existing max()/min() behavior; done means aggregated customType columns use their codec while the reported empty-table and plain-column concerns remain addressed.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
74/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.