search

drizzle-team / drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅
https://orm.drizzle.team
Apache License 2.0
24.82k stars 659 forks source link

[BUG]: TiDB Driver Generates Unsupported LEFT JOIN LATERAL Queries in Relational Query #3623

Open inaridiy opened 6 days ago

inaridiy commented 6 days ago

Report hasn't been filed before.

What version of drizzle-orm are you using?

0.35.3

What version of drizzle-kit are you using?

0.26.2

Other packages

@tidbcloud/serverless@0.2.0

Describe the Bug

When using DrizzleORM to perform a relational query with TiDB, SQL containing LEFT JOIN LATERAL is automatically generated, leading to a syntax error in TiDB. Since TiDB does not support LATERAL joins, the query fails.

Undesired Behavior

Starting from drizzle-orm@0.28, DrizzleORM generates LEFT JOIN LATERAL in relational query SQL. However, this syntax is not supported by TiDB, causing a syntax error.

Reference: DrizzleORM v0.28.0 release notes

Expected Behavior

For TiDB, queries should be generated without using LEFT JOIN LATERAL, similar to how the MySQL2 driver operates in Planetscale mode.

I think it might be a good idea to enable Planetscale mode in the TiDB driver internally, but I'm not sure. I am just now calling mysql-core code!

inaridiy commented 6 days ago

It would be good to change the last argument of TiDBServerlessDatabase from default to plansclae. I'm using the forced replacement for now, but it works fine.

I'll build a PR later.

https://github.com/drizzle-team/drizzle-orm/blob/599da5ef7a037904989069596c8879690f9c5322/drizzle-orm/src/tidb-serverless/driver.ts#L54-L56

inaridiy commented 6 days ago

PR opened https://github.com/drizzle-team/drizzle-orm/pull/3629