drizzle-team / drizzle-team/drizzle-orm

[BUG]: Query with relations containing numeric fields are cast to numbers in PostgreSQL

Open
#4,091 3 comments 4 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta db/postgres priority qb/crud
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

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

^0.39.2

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

^0.30.4

### Other packages

_No response_

### Describe the Bug

## What is the undesired behaviour?
Automatic casting from string to number that leads to an absolute loss of precision when using queries with relations.
## To reproduce this bug, create two tables.
```ts

export const ProductORM = pgTable(
'products',
t => ({
id: t.uuid().primaryKey().defaultRandom(),
categoryId: t.uuid('category_id').references((): AnyPgColumn => CategoryORM.id, {
onDelete: 'cascade',
}),

priceUahRetail: numeric('price_uah_retail'),
priceUahWholesaleBig: numeric('price_uah_wholesale_big'),
}),
table => [
check('price_UAH_Retail_check', sql`${table.priceUahRetail} >= '0'`),
check('price_UAH_WholesaleBig_check', sql`${table.priceUahWholesaleBig} >= '0'`),
]
)
export const CategoryORM = pgTable(
'categories',
t => ({
id: t.uuid().primaryKey().defaultRandom(),
})
)
export const categoryRelations = relations(CategoryORM, ({ one, many }) => ({
products: many(ProductORM),
}))
```
Then, fill priceRetail and priceWholesaleBig with numbers such asl `302312.1010` and `1010101010101010101.202020020202020202022020`
let's make a query like
```ts
const q = db.query.CategoryORM.findFirst({
where: eq(CategoryORM.id, categoryId),
with: {
products: {
columns: {
id: true,
priceUahRetail: true,
priceUahWholesaleSmall: true,
},
},
},
}).toSQL()
console.log(q)
```
Drizzle generates the following query:
```sql
SELECT
"CategoryORM"."id",
"CategoryORM_products"."data" AS "products"
FROM
"categories" "CategoryORM"
LEFT JOIN LATERAL (
SELECT
COALESCE(
JSON_AGG(
JSON_BUILD_ARRAY(
"CategoryORM_products"."id",
"CategoryORM_products"."price_uah_retail",
"CategoryORM_products"."price_uah_wholesale_small"
)
),
'[]'::JSON
) AS "data"
FROM
(
SELECT
*
FROM
"products" "CategoryORM_products"
WHERE
(
"CategoryORM_products"."category_id" = "CategoryORM"."id"
)
) "CategoryORM_products"
) "CategoryORM_products" ON TRUE
WHERE
"CategoryORM"."id" = '3065f246-c8a7-433c-83d0-94e04683f964'
```
which produces this result in pgadmin
```
"id" "products"
"3065f246-c8a7-433c-83d0-94e04683f964" "[[""77ae6579-0596-4c6d-a76e-614be15f9bb3"", 302312.1010, 1010101010101010101.202020020202020202022020]]"
```
we can see that value returned by our sql query is correct.

### BUG
However, here is the bug:

```ts
const q = await db.query.CategoryORM.findFirst({
where: eq(CategoryORM.id, categoryId),
with: {
products: {
columns: {
id: true,
priceUahRetail: true,
priceUahWholesaleSmall: true,
},
where: ne(ProductORM.productStatusE, productStatusEnumZod.Values.deleted),
},
},
})
console.log(q)
console.log(typeof q?.products[0]?.priceUahRetail)
console.log(typeof q?.products[0]?.priceUahWholesaleSmall)

we get
{
id: '3065f246-c8a7-433c-83d0-94e04683f964',
products: [
{
id: '77ae6579-0596-4c6d-a76e-614be15f9bb3',
priceUahRetail: 302312.101, // this value was also cropped
priceUahWholesaleSmall: 1010101010101010000
}
]
}
number
number
```
so they were casted to numbers.
Check if Drizzle can return the value as a string.
```ts
const q2 = await db.query.ProductORM.findFirst({
where: eq(ProductORM.id, '77ae6579-0596-4c6d-a76e-614be15f9bb3'),
})
console.log(q2)
console.log(typeof q2?.priceUahRetail)
console.log(typeof q2?.priceUahWholesaleSmall)
---
{
id: '77ae6579-0596-4c6d-a76e-614be15f9bb3',
categoryId: '3065f246-c8a7-433c-83d0-94e04683f964',
priceUahWholesaleSmall: '1010101010101010101.202020020202020202022020',
priceUahRetail: '302312.1010'
}
string
string
```

I think the bug is pretty obvious: the internal mapper, when mapping this array, treats numeric columns as if applying parseFloat() under the hood.
```sql
LEFT JOIN LATERAL (
SELECT
COALESCE(
JSON_AGG(
JSON_BUILD_ARRAY(
"CategoryORM_products"."id",
"CategoryORM_products"."price_uah_retail",
"CategoryORM_products"."price_uah_wholesale_small"
)
),
'[]'::JSON
) AS "data"
```

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.