`$with` example in docs error "unsupported: with expression in select statement"

I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause

but executing the same query throws an Error:

unsupported: with expression in select statement

unsupported: with expression in select statement

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

The $with clause looks superb.

Quering with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

Andrii Sherman•5/29/23, 7:13 AM

what database are you using?

mcgrealifeOP•5/29/23, 3:25 PM

I am using mysql via planetscale (sorry i'll mention next time)

Note: the nearly identical subquery (without the $with) in the docs right below the $with section does work.

https://orm.drizzle.team/docs/crud#select-from-subquery

Quering with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

Andrii Sherman•5/29/23, 3:50 PM

I guess it's because PlanetScale doesn't have a support for WITH statements

I guess @Dan Kochetov knows exactly

Dan•5/29/23, 4:00 PM

yes, CTEs are not supported on PS

mcgrealifeOP•5/29/23, 4:16 PM

Ah yes, I see now that error is from PlanetScale, not drizzle.
I should have searched first https://github.com/planetscale/discussion/discussions/270

Thanks to both

GitHub

unsupported: with expression in select statement · planetscale disc...

Hi, When running a recursive CTE, I get the following error message. unsupported: with expression in select statement Is PlanetScale able to or planning support recursive CTEs?