drizzle-orm: [BUG]: Broken shifted columns with leftJoin and same column name (on D1)
What version of drizzle-orm are you using?
0.25.4
What version of drizzle-kit are you using?
0.17.6
Describe the Bug
I got broken results with leftJoin with same columns.
Details
My schema (partial)
import { sqliteTable, text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { InferModel } from 'drizzle-orm';
export const users = sqliteTable('users', {
id: integer('id').primaryKey().notNull(),
displayName: text('displayName').notNull(),
});
export const posts = sqliteTable('posts', {
id: integer('id').primaryKey().notNull(),
ownerId: integer('ownerId').references(() => users.id),
title: text('title').notNull(),
content: text('content').notNull(),
});
… and run next query to reduce has many pattern.
const userAndPosts = await db
.select({
user: {
id: users.id, // "users"."id"
},
post: {
id: posts.id, // "posts"."id"
ownerId: posts.ownerId,
title: posts.title,
content: posts.content
}
})
.from(users)
.leftJoin(posts, eq(posts.ownerId, users.id))
.all();
I got broken results.
[
{
user: { id: 13, displayName: 'kotaro chikuba (mizchi)' },
post: { id: 1, ownerId: 'xx', title: 'x', content: undefined }
},
{
user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
post: { id: 1, ownerId: 'xxx', title: 'ueoaueoa', content: undefined }
},
{
user: { id: 2, displayName: 'kotaro chikuba (mizchi)' },
post: {
id: 1,
ownerId: 'xxxeeoa',
title: 'ueoaueoa',
content: undefined
}
}
]
- id is swapped.
- post data is shifted.
Running query is here.
select "users"."id", "posts"."id", "posts"."ownerId", "posts"."title", "posts"."content" from "users" left join "posts" on "posts"."ownerId" = "users"."id"
I think that duplicate id columns have been executed without as, causing the ORM to be misaligned when it repackages them into the object.
If one id is not referenced, this bug is resolved.
Expected behavior
I expect I can use both id and correct columns.
[
{
user: { id: 1, displayName: 'kotaro chikuba (mizchi)' },
post: { id: 13, ownerId: 1, title: 'titile', content: 'body' }
},
]
Environment & setup
I’m using pnpm.
pnpm add drizzle-kit drizzle-orm -D
D1 setup
import { drizzle } from 'drizzle-orm/d1';
export function createClient(db: D1Database) {
return drizzle(db);
}
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Reactions: 24
- Comments: 30 (7 by maintainers)
Links to this issue
Commits related to this issue
- work around D1 bug https://github.com/drizzle-team/drizzle-orm/issues/555 https://github.com/cloudflare/workerd/pull/696 — committed to beeequeue/dota-matches-api by beeequeue 3 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
- refactor: use same code as other adapters on SQLite adapter SQLite adapter required a different implementation due to a D1 bug , but now it is resolved. https://github.com/drizzle-team/drizzle-orm/i... — committed to phanect/icewall by phanect 2 years ago
Good news, my fix landed with
wrangler@3.57.2, and I can confirm that the problem is gone.@AndriiSherman @dankochetov, you can close the issue!
it seems like the types have been released in
4.20240222.0(with a bit of delay). is there a way to get this issue sort of prioritised because especially with Lucia this is a complete blocker when trying to use D1 and drizzle.The problem is that d1 overwrites columns if they share the same key. As it can be seen here
Both
posts.idand"users"."id"are returned by cloudflare asidmeaning that the data is overwritten. A very dirty hack would be to use raw sql statementsThis works as then d1 does not overwrite the data. However this solution is very ugly and it removes type inference for the affected columns
It happens with SQLite too. This query:
Generate with SQL query:
And due to how SQLite itself run the query, rows returns like this:
{id: string}[].I think the problem is on drizzle side(and aliasing will fix it)
I have implemented a type-safe generic function that you can copy if needed.
@cyco130 thank you for the reproduction, looks like there’s still an issue on D1 side. I’ve reported it back to their team: https://github.com/cloudflare/workers-sdk/issues/3160#issuecomment-2010837504
On further testing using select compared to findmany I am not sure if this is a drizzle or D1 issue? I have data tables with some of the same common field column names like create_at, updated_at, id etc
When using select
I get the column data results shift bug and users createdAt and updatedAt fields missing
However when using findmany
I don’t get the bug. Every column is returned where it should be in nicely nested json.
@brenfwd @ElGatoLoco @keisanng Since this issue is mainly about D1, I’ll be closing this as it seems solved for that driver. If you’re experiencing issues with another driver (like Bun SQLite), please open another issue with a reproducible example.
If anyone still has this issue with the D1 driver, feel free to reopen this issue.
I have very similar issue with Bun SQLite driver and the following statement.
Below is the generated SQL.
By looking at the result, it seems you’re getting your references messed up when having multiple join statements, since somehow value of
ingredients.unitends up iningredients.namefield. If I remove one of the joins, everything looks fine.FWIW, also getting this issue with the Bun sqlite driver
I created a reproduction before searching for similar issues. I might as well share it, maybe it can help.
https://github.com/cyco130/drizzle-d1-bug
tracking issue on d1 side
https://github.com/cloudflare/workers-sdk/issues/3160
Seems like this is an issue on d1 side. I’ll create an issue on their repo and will pin it here to track the progress