drizzle-team / drizzle-team/drizzle-orm
[BUG]: Nested objects deeper than one level are never nullified on a left join
- 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
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