[BUG]: Select with left joins returning `null` for table with non-null results in reality about drizzle-orm

[VirtuaBoza]

I have determined that the issue is in the logic around here:

drizzle-orm/drizzle-orm/src/utils.ts

Line 43 in 4706ad1

if (joinsNotNullableMap && is(field, Column) && path.length === 2) {

I am able to bypass the issue by re-ordering my patron table schema and ensuring a non-nullable column is the first property listed, like so:

export const patrons = pgTable(
  "patron",
  {
    id: text("id").primaryKey().default(defaultUUID), // <--non-nullable field first
    // eslint-disable-next-line sort-keys/sort-keys-fix
    birthDate: date("birth_date", { mode: "string" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    email: text("email"),
    firstName: text("first_name").notNull(),
    gender: text("gender", { enum: ["FEMALE", "MALE"] }),
    lastName: text("last_name").notNull(),
    oneRosterSourcedId: text("one_roster_sourced_id"),
    organizationId: text("organization_id").notNull(),
    preferredName: text("preferred_name"),
  },
  (t) => ({
    id_by_organization: uniqueIndex().on(t.id, t.organizationId),
    one_roster_id_by_org: uniqueIndex().on(
      t.oneRosterSourcedId,
      t.organizationId,
    ),
  }),
);

[VirtuaBoza]

This change appears to fix the issue, but I'm not confident it doesn't cause some other issue, so I don't really want to bother with a PR. I'll leave that to somebody who knows how to properly test this.

diff --git a/drizzle-orm/src/utils.ts b/drizzle-orm/src/utils.ts
index 07b8290f..ed3f8b53 100644
--- a/drizzle-orm/src/utils.ts
+++ b/drizzle-orm/src/utils.ts
@@ -45,7 +45,8 @@ export function mapResultRow<TResult>(
 						if (!(objectName in nullifyMap)) {
 							nullifyMap[objectName] = value === null ? getTableName(field.table) : false;
 						} else if (
-							typeof nullifyMap[objectName] === 'string' && nullifyMap[objectName] !== getTableName(field.table)
+							typeof nullifyMap[objectName] === 'string' &&
+							(nullifyMap[objectName] !== getTableName(field.table) || value !== null)
 						) {
 							nullifyMap[objectName] = false;
 						}