drizzle-team / drizzle-team/drizzle-orm

[BUG]: Nested Partial Select returns null on left join if first column value is null

Open
#1,603 36 comments 11 reactions 0 assignees View on GitHub
bug priority qb/crud
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.29.1

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

0.19.12

### Describe the Bug

The output of this PostgreSQL select query has a `null` value for the `branding` property. However, there was a successful join in the database.

Furthermore, if the order of the `panelBackground` and `logo` properties are swapped, the query works as expected.

```ts
const org = await db
.select({
name: orgTable.name,
slug: orgTable.slug,
branding: {
logo: orgBrandingTable.logo, // null in database
panelBackground: orgBrandingTable.panelBackground, // "#1a8cff" in database
}
})
.from(orgTable)
.leftJoin(orgBrandingTable, eq(orgTable.id, orgBrandingTable.orgId))
.where(
eq(orgTable.id, session.orgId),
)

console.log(org);
// { name: 'Test org 2', slug: 'test-org-2', branding: null }
```

### Expected behavior

After swapping `panelBackground` and `logo` in the query, this is the (correct) value of `org`

```ts
console.log(org);
// {
// name: 'Test org 2',
// slug: 'test-org-2',
// branding: { panelBackground: '#1a8cff', logo: null }
// }
```

### Environment & setup

It's not a problem with the generated query, because when I execute in pgAdmin, it returns results.

```sql
select "org"."name", "org"."slug", "org_branding"."logo", "org_branding"."panel_background_colour"
from "org"
left join "org_branding"
on "org"."id" = "org_branding"."org_id"
where ("org"."id" = 11)

-- "name","slug","logo","panel_background_colour"
-- "Test org 2","test-org-2",NULL,"#1a8cff"
```

## Versions

* Postgres.js: `v3.3.5`
* PostgreSQL Server: `PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit`
* Node: `v18.17.0`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.