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

[oscarhermoso]

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.

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

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.

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

[john-griffin]

We also just hit this on drizzle-orm 0.29.3 and drizzle-kit 0.20.13.

[algora-pbc]

💎 $50 bounty created by john-griffin 👉 To claim this bounty, submit your pull request on Algora 📝 Before proceeding, please make sure you can receive payouts in your country 💵 Payment arrives in your account 2-5 days after the bounty is rewarded 💯 You keep 100% of the bounty award 🙏 Thank you for contributing to drizzle-team/drizzle-orm!

👉 Add a bounty • Share on socials

[john-griffin]

[codingarchitect-wq]

/attempt #1603

[Moocar]

We just hit this bug in 0.28.6. I tried upgrading to version 0.29.3 but to no avail. Our work-around for now is to only load fields at the top-level. And then manually map over the results to convert them into the nested structure.

[xriter]

Also just hit this. Still present in 0.29.4 ☹️