[BUG]: Invalid SQL query generated for MySQL when using "with" feature

[herbertpimentel]

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.41.0

What version of drizzle-kit are you using?

0.30.6

Other packages

No response

Describe the Bug

Drizzle generates a invalid SQL query for one relation when using the option with to query and load related data

import { drizzle } from "drizzle-orm/mysql2";
import { int, mysqlTable, serial, varchar } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

const states = mysqlTable('states', {
  id: serial().primaryKey(),
  name: varchar({ length: 255 }).notNull()
});

const cities = mysqlTable('cities', {
  id: serial().primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  stateId: int("state_id").references(() => states.id),
});

const citiesRelations = relations(cities, ({ one }) => ({
	state: one(states, {
        fields:[cities.stateId],
        references: [states.id]
    }),
}));

const schema = {states, cities, citiesRelations};

const db = drizzle({
    connection: {
        host: 'localhost',
        port: 3308,
        user: 'root',
        password: 'secret',
        database: 'drizzle_issue',
    },
    casing: 'snake_case', 
    mode: 'default',
    logger: true,
    schema: schema
});

db.query.cities.findMany({ 
    with: { 
        state: true 
    }
});

Query: select `cities`.`id`, `cities`.`name`, `cities`.`state_id`, `cities_state`.`data` as `state` from `cities` `cities` left join lateral (select json_array(`cities_state`.`id`, `cities_state`.`name`) as `data` from (select * from `states` `cities_state` where `cities_state`.`id` = `cities`.`state_id` limit ?) `cities_state`) `cities_state` on true -- params: [1]
C:\DEv\drizzle-mysql-with-issue\node_modules\.pnpm\mysql2@3.14.0\node_modules\mysql2\lib\promise\pool.js:36
    const localErr = new Error();
                     ^
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`cities_state`.`id`, `cities_state`.`name`) as `data` from...' at line 1
    at PromisePool.query (C:\DEv\drizzle-mysql-with-issue\node_modules\.pnpm\mysql2@3.14.0\node_modules\mysql2\lib\promise\pool.js:36:22)
    at MySql2PreparedQuery.execute (C:\DEv\drizzle-mysql-with-issue\node_modules\.pnpm\drizzle-orm@0.41.0_gel@2.0.2_mysql2@3.14.0\node_modules\src\mysql2\session.ts:121:31)
    at QueryPromise.execute (C:\DEv\drizzle-mysql-with-issue\node_modules\.pnpm\drizzle-orm@0.41.0_gel@2.0.2_mysql2@3.14.0\node_modules\src\mysql-core\query-builders\query.ts:155:25)
    at QueryPromise.then (C:\DEv\drizzle-mysql-with-issue\node_modules\.pnpm\drizzle-orm@0.41.0_gel@2.0.2_mysql2@3.14.0\node_modules\src\query-promise.ts:31:15)
    at Object.<anonymous> (C:\Dev\drizzle-mysql-with-issue\src\index.ts:39:52)
    at Module._compile (node:internal/modules/cjs/loader:1469:14)
    at Module.m._compile (C:\Users\Herbert\AppData\Roaming\nvm\v20.18.1\node_modules\ts-node\src\index.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1548:10)
    at Object.require.extensions.<computed> [as .ts] (C:\Users\Herbert\AppData\Roaming\nvm\v20.18.1\node_modules\ts-node\src\index.ts:1621:12)
    at Module.load (node:internal/modules/cjs/loader:1288:32) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'select `cities`.`id`, `cities`.`name`, `cities`.`state_id`, `cities_state`.`data` as `state` from `cities` `cities` left join lateral (select json_array(`cities_state`.`id`, `cities_state`.`name`) as `data` from (select * from `states` `cities_state` where `cities_state`.`id` = `cities`.`state_id` limit 1) `cities_state`) `cities_state` on true',
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`cities_state`.`id`, `cities_state`.`name`) as `data` from...' at line 1"
}

create table states (
	id int auto_increment not null primary key,
	name text
);

create table cities (
	id int auto_increment not null primary key,
	name text,
	state_id int references states(id)
);


insert into states (name) values ('Florida');
insert into states (name) values ('California');

insert into cities (name, state_id) values ('Miami', 1);
insert into cities (name, state_id) values ('Orlando', 1);
insert into cities (name, state_id) values ('Fort Laurdedale', 1);
insert into cities (name, state_id) values ('Tampa', 1);

insert into cities (name, state_id) values ('Los Angeles', 2);
insert into cities (name, state_id) values ('San Francisco', 2);
insert into cities (name, state_id) values ('San Diego', 2);

{
  "name": "drizzle-mysql-with-issue",
  "version": "1.0.0",
  "scripts": {
    "dev": "nodemon ./src/index.ts"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "dependencies": {
    "drizzle-orm": "^0.41.0",
    "mysql2": "^3.14.0"
  },
  "devDependencies": {
    "@types/node": "^22.14.1",
    "drizzle-kit": "^0.30.6",
    "tsx": "^4.19.3"
  }
}