drizzle-team / drizzle-team/drizzle-orm

[BUG]: LEFT JOIN objects nested at depth 2+ return an all-null object instead of null

Open
#6,175 0 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.