[BUG]: findFirst and findMany queries using the 'with' statement can't parse the models if the related table has a geomtery column

[masons40]

What version of drizzle-orm are you using?

0.32.1

What version of drizzle-kit are you using?

0.23.0

Describe the Bug

Using the 'with' statement in the findFirst and findMany queries results in this error "RangeError: Offset is outside the bounds of the DataView" if the related table has a geometry column.

Steps to reproduce: Two tables with the following relationship

export const reviews = createTable('reviews', {
  id: serial('id').primaryKey().notNull(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).default(sql`CURRENT_TIMESTAMP`),
  draft: boolean('draft').default(true).notNull(),
  hotelId: integer('hotel_id')
    .references(() => hotels.id, { onDelete: 'cascade' })
    .notNull(),
});

export const reviewsRelations = relations(reviews, ({ one }) => ({
  hotel: one(hotels, { fields: [reviews.hotelId], references: [hotels.id] }),
}))

export const hotels = createTable(
  'hotels',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).default(sql`CURRENT_TIMESTAMP`),
    location: geometry('location', { type: 'point', mode: 'xy', srid: 4326 })
  },
  (hotel) => ({
    spatialIndex: index('spatial_index').using('gist', hotel.location),
  }),
);

export const hotelRelations = relations(hotels, ({ many, one }) => ({
  reviews: many(reviews),
}));

running the following queries should result in the error stated above.

const reviews = await db.query.reviews.findMany({
    where: and(eq(reviews.draft, false)),
    with: {
      hotel: true,
    },
  });

const review = await db.query.reviews.findFirst({
    where: and(eq(reviews.id, 320)),
    with: {
      hotel: true,
    },
  });

It appears that the generated SQL for both the findFirst and findMany queries works fine

-- findFirst
select "reviews"."id", "reviews"."created_at", "reviews"."updated_at", "reviews"."hotel_id", "reviews"."draft", "reviews_hotel"."data" as "hotel" from "reviews" "reviews" left join lateral (select json_build_array("reviews_hotel"."id", "reviews_hotel"."name", "reviews_hotel"."created_at", "reviews_hotel"."updated_at", "reviews_hotel"."location", "reviews_hotel"."rating") as "data" from (select * from "hotels" "reviews_hotel" where "reviews_hotel"."id" = "reviews"."hotel_id" limit 1) "reviews_hotel") "reviews_hotel" on true where "reviews"."id" = 320 limit 1 

-- findMany
select "reviews"."id", "reviews"."created_at", "reviews"."updated_at", "reviews"."hotel_id", "reviews"."draft", "reviews_hotel"."data" as "hotel" from "reviews" "reviews" left join lateral (select json_build_array("reviews_hotel"."id", "reviews_hotel"."name", "reviews_hotel"."created_at", "reviews_hotel"."updated_at", "reviews_hotel"."location") as "data" from (select * from "hotels" "reviews_hotel" where "reviews_hotel"."id" = "reviews"."hotel_id" limit 1) "reviews_hotel") "reviews_hotel" on true where "reviews"."draft" = false 

It works works fine if I use the standard select method with a leftJoin on the hotels table.

const reviewResults = await db
    .select()
    .from(reviews)
    .leftJoin(hotels, eq(hotels.id, reviews.hotelId))
    .where(and(eq(reviews.draft, false)))
    .limit(6)
    .orderBy(desc(reviews.updatedAt));

It appears it could be a parsing issue with the findFirst and findMany queries if using the 'with' statement on a table with a geometry column.

Expected behavior

Both the findFirst and findMany queries should return the review object and related hotel object correctly.

Environment & setup

Next.js project using a Supabase postgres DB with the PostGIS extension enabled.

[chris-allen]

Same issue here. If you don't need the geometry column in your context, you can explicitly omit it. For the example above, it would look something like:

const reviews = await db.query.reviews.findMany({
  with: { hotel: { columns: { id: true, name: true } } },
});

Thanks @masons40 for providing the workaround using db.select().