drizzle-team / drizzle-team/drizzle-orm
[BUG]: sum() and avg() drop the column's customType codec, max()/min() apply it (pg)
- 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
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