CTE Support

[ljmclaney]

I was wondering if CTE (Common Table Expressions) are supported? I have a query that's running fine locally (MySQL 8.0.27) but the same query fails on PlanetScale.

1105 unknown error: syntax error at position 5 near 'with'.

Thanks!

[fooforge]

@ljmclaney No, I'm afraid. Vitess does not support window functions or common table expressions yet. We're actively working on closing the compatibility gap, but I cannot say if or when we're going to get to CTE at this time.

[emyann]

Hello! Double checking if you have an ETA to support CTE/rCTE ?

[ayrton]

Vitess v19 supports non-recursive CTEs (ref), recursive CTEs are not supported yet. The majority of databases on PlanetScale are still on Vitess v18, if you need CTEs you can best contact support to ask if your database can already be updated to v19.

[ayrton]

Recursive CTEs are supported by Vitess v21 (ref). It'll take a while before this is GA in PlanetScale but it's coming.

[sweetbasil]

Hi, any updates for supporting CTE here?

[ayrton]

The majority of databases on PlanetScale are still on Vitess v18, if you need CTEs you can best contact support to ask if your database can already be updated to v19.

[orware]

As of early 2025, the current update for this question is that essentially all branches should be on a Vitess 21 or newer build which has support for both the non-recursive CTE support added within Vitess 19 together with the experimental support for recursive CTEs that arrived in Vitess 21:
https://vitess.io/blog/2024-03-06-announcing-vitess-19/#query-compatibility-enhancements
https://vitess.io/blog/2024-10-29-announcing-vitess-21/#recursive-common-table-expressions-ctes

[rebaz94]

Hi @orware Is this enabled by default? I'm encountering an error when using a CTE in a DELETE statement:
VT12001: unsupported: WITH expression in DELETE statement.

[orware]

@rebaz94,

Yes, the CTE support should be available by default but it looks like using it within the context of a DELETE statement is not allowed since I see the following references in the Vitess code:
https://github.com/vitessio/vitess/blob/0a6f982bca73269042f5809ed7ee53c7acc836eb/go/vt/vtgate/planbuilder/delete.go#L36
https://github.com/vitessio/vitess/blob/0a6f982bca73269042f5809ed7ee53c7acc836eb/go/vt/vtgate/planbuilder/testdata/unsupported_cases.json#L200

So it does appear that combination may currently be unsupported for the time being, along with another case when attempting to use a CTE with an UPDATE statement.

The primary use case for the CTE support is likely focused on SELECT queries based on that information.

[rebaz94]

Thank you for the clarification!

[patrickomeara]

Hi @orware is this still the case?

I'd there somewhere we can follow UPDATE and DELETE support in CTEs?

[orware]

@patrickomeara,

Looking at the current versions of those two files linked previously it still appears to be the case:
https://github.com/vitessio/vitess/blob/main/go/vt/vtgate/planbuilder/delete.go#L36
https://github.com/vitessio/vitess/blob/main/go/vt/vtgate/planbuilder/testdata/unsupported_cases.json#L203-L211

[patrickomeara]

Thanks @orware I'll keep an eye on these files.