drizzle-team / drizzle-team/drizzle-orm

[BUG]: Nested objects deeper than one level are never nullified on a left join

Open
#6,246 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

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

main (`b7862528`)

### Describe the Bug

`mapResultRow` only nullifies a nested object when the selection path is exactly two segments deep:

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/utils.ts#L46

```ts
if (joinsNotNullableMap && is(field, Column) && path.length === 2) {
const objectName = path[0]!;
...
}
```

`orderSelectedFields` builds paths by recursing through plain objects, so a selection can nest arbitrarily deep. Anything deeper than one level never gets an entry in `nullifyMap`, and so is never nullified when the left join matches no row.

One level (works today):

```ts
db.select({
name: orgs.name,
branding: { logo: orgBranding.logo },
}).from(orgs).leftJoin(orgBranding, ...)

// no matching row -> { name: 'x', branding: null } ✅
```

Two levels (inconsistent):

```ts
db.select({
name: orgs.name,
theme: { branding: { logo: orgBranding.logo } },
}).from(orgs).leftJoin(orgBranding, ...)

// no matching row -> { name: 'x', theme: { branding: { logo: null } } }
// expected { name: 'x', theme: { branding: null } }
```

The same join, the same absent row, and the caller gets an object instead of `null` purely because the selection is one level deeper. Code that does `if (row.theme.branding)` to test whether the join matched silently takes the wrong branch.

### Expected behavior

Nullification should depend on whether the joined row exists, not on how deeply the caller chose to nest the selection.

### Suggested fix

Key `nullifyMap` on the object's own path rather than assuming depth 2, and walk to that path when nullifying. That keeps today's behaviour identical for depth-2 selections, since the parent path of `['branding', 'logo']` is just `['branding']`.

Sketch:

```ts
if (joinsNotNullableMap && is(field, Column) && path.length >= 2) {
const objectPath = path.slice(0, -1);
const objectKey = objectPath.join('\u0000');
// ...same candidate logic, keyed on objectKey
}
```

and in the nullify pass, walk `objectPath` and assign `null` to the last segment instead of `result[objectName] = null`.

Deliberately unchanged: an object is nullified only when every one of its own direct columns is null and they all come from the same nullable table. A parent that contains no direct columns of its own — `theme` above — keeps its shape and just holds `branding: null`. That is the conservative reading and avoids guessing at cascade semantics.

### Notes

Found while working on #1603 (submitted as #6245), which is a different defect in the same block: there, the *first* column read decides nullification and a later non-null column never clears the candidacy. The two are independent — #6245 fixes ordering, this one is about depth.

Happy to open a PR for this if you want it fixed the way sketched above, or a different way if you'd rather the cascade behaved differently for parents with no direct columns. Asking first because it's a behaviour change for anyone currently relying on the deep-nesting shape.

Contributor guide

Open the contributing guide

Research direction

Start in drizzle-orm/src/utils.ts at mapResultRow and trace how orderSelectedFields constructs paths and how nullifyMap is applied. Reproduce the shown nested left-join case, then verify that absent joined rows nullify the appropriate nested object while preserving the documented behavior for parents with no direct columns.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
backend-api-design, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.