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

[inaridiy]

Report hasn't been filed before.

• 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.35.3

What version of drizzle-kit are you using?

0.26.2

Other packages

@tidbcloud/[email protected]

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 [email protected], 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]

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.

drizzle-orm/drizzle-orm/src/tidb-serverless/driver.ts

Lines 54 to 56 in 599da5e

	const session = new TiDBServerlessSession(client, dialect, undefined, schema, { logger });
	const db = new TiDBServerlessDatabase(dialect, session, schema as any, 'default') as TiDBServerlessDatabase<TSchema>;
	(<any> db).$client = client;

[inaridiy]

PR opened #3629

[inaridiy]

Happy new year