[BUG]: Cannot use where filter and with statement in the same query when using RDS Data API

[adrian-mohara]

What version of drizzle-orm are you using?

0.30.8

What version of drizzle-kit are you using?

0.20.14

Describe the Bug

When I do a query combining where with with, I get the following error using rds-data-api:

BadRequestException: ERROR: operator does not exist: uuid = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 670; SQLState: 42883

The query works separately, I created another query with only the where statement and it worked, just as it does if I create another query with only the with statement.

I tried the same query with the neon driver and it works without problem, it seems to be only a problem with rds-data-api.

The schema:

export const organization = pgTable('organization', {
    id: serial('id').primaryKey(),
    uuid: uuid('uuid').defaultRandom().notNull().unique(),
    name: varchar('name').notNull(),
    description: text('description'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow().$onUpdate(() => new Date()),
});

export const organizationRelations = relations(organization, ({ many }) => ({
    users: many(user),
    orders: many(order),
    contents: many(content),
    quizzes: many(quiz),
}));

export const order = pgTable('order', {
    id: serial('id').primaryKey(),
    uuid: uuid('uuid').defaultRandom().notNull().unique(),
    creatorId: integer('creator_id').notNull().references(() => user.id),
    ownerId: integer('owner_id').notNull().references(() => user.id),
    organizationId: integer('organization_id').notNull().references(() => organization.id),
    status: varchar('status', { enum: enumValues(OrderStatusEnum) })
    .notNull().default(OrderStatusEnum.Draft).$type<OrderStatusEnum>(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow().$onUpdate(() => new Date()),
});

export const orderRelations = relations(order, ({ one, many }) => ({
    creator: one(user, { fields: [order.creatorId], references: [user.id] }),
    owner: one(user, { fields: [order.ownerId], references: [user.id] }),
    organization: one(organization, { fields: [order.organizationId], references: [organization.id] }),
    ordersContent: many(orderContent),
    ordersQuiz: many(orderQuiz),
}));

The query:

const orderUUID = '077b2bd8-98f1-469c-a49d-d520b3d47978'
const queryResult = await db.query.order.findFirst({
        where: eq(order.uuid, orderUUID),
        with: {
            organization: true
        }
    });

Expected behavior

Able to use where and with in the same query.

Environment & setup

Aurora Postgres Database using RDS-Data-API

[adrian-mohara]

I enabled the logger to display the query directly in the RDS console and it runs correctly, it seems to be an internal drizzle problem.

[adrian-mohara]

I was able to run the query with this workaround using sql to do the explicit cast.

const orderUUID = '077b2bd8-98f1-469c-a49d-d520b3d47978'
const queryResult = await db.query.order.findFirst({
        where: sql`${order.uuid} = CAST(${orderUUID} as UUID)`,
        with: {
            organization: true
        }
});

[lovrozagar]

Up

[valentinbeggi]

Up !

[valentinbeggi]

nvm, seems fixed with 0.32.2 🎉