drizzle-team / drizzle-team/drizzle-orm
[BUG]: LEFT JOIN objects nested at depth 2+ return an all-null object instead of null
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### What version of `drizzle-orm` are you using?
0.44.7 (also present in 0.45.x, 1.0.0-beta.22 and 1.0.0-rc.x — checked in source)
### What version of `drizzle-kit` are you using?
n/a
### Describe the Bug
When a LEFT JOIN result is selected as an object **nested at depth >= 2** (inside another selected object), an unmatched join returns an all-null object `{ id: null, ... }` instead of `null`.
Depth-1 works correctly:
```ts
db.select({ cheque: { id: cheque.id, serial: cheque.serial } })
// unmatched LEFT JOIN -> cheque: null ✓
```
Nesting the same shape one level deeper breaks:
```ts
db.select({
bill: {
id: bill.id,
cheque: { // path ['bill', 'cheque', 'id'] -> depth 2 -> NOT tracked
id: cheque.id,
serial: cheque.serial,
},
},
})
// unmatched LEFT JOIN -> bill.cheque === { id: null, serial: null } ✗ (expected null)
```
Same with `bill.cheque.bank` at depth 3, etc. The generated SQL and raw rows are correct; only the row mapping is wrong. Also order-dependent at depth 1 (see #1603).
### Root cause
`mapResultRow` in `drizzle-orm/src/utils.ts` gates nullify tracking on `path.length === 2`:
```ts
if (joinsNotNullableMap && is(field, Column) && path.length === 2) {
```
`makeDefaultQueryMapper` and `makeJitQueryMapper` (code-generating mapper) have the same `path.length === 2` gate, and the nullify step (`result[objectName] = null`) only handles depth-1 keys.
### Repro
Standalone repro (sqlite/better-sqlite3, zero setup): https://github.com/erfanium/drizzle-nullify-bug
`npm i && npm run repro` -> exits 1 with the all-null object.
### Related
- #1603 — same nullify logic, different failure (depth 1, order-dependent). All open PRs for #1603 retain `path.length === 2`, so none fix this depth >= 2 variant.
- #2050 — the select input types also reject nesting deeper than one level, so the depth-2 shape can't even be written without a cast.
Contributor guide
Research direction
Start in drizzle-orm/src/utils.ts at mapResultRow, then compare the path-length handling in makeDefaultQueryMapper and makeJitQueryMapper. Run the standalone reproduction with npm i && npm run repro and inspect the existing nullify logic. Done means unmatched nested LEFT JOIN objects at depth 2 and deeper map to null without breaking depth-1 behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sqlite, typescript
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100