A recursive common table expression (CTE) is a CTE that references itself. The CTE repeatedly executes, returns subsets of data, until it returns the complete result set.
A recursive CTE is useful in querying hierarchical data such as organization charts where one employee reports to a manager and each component itself also consists of many other components.
You can work around this in MemSQL today, but it takes a lot of extra code to do so.
Note: A member of the MemSQL team has filed this feature request. We are adding in several features to this forum that we believe are popular with our users to engage and better understand the needs of the MemSQL community.
I would also very much like to see this supported as missing triggers and missing recursive CTEs are the two reasons why we can’t run our application database on SingleStore yet.
The latest release candidate build of 8.0 supports recursive CTEs. See the announcement about the 8.0 RC. Feedback is appreciated.
I use 8 version
But sometimes i got error
Unsupported recursive common table expression query shape: Recursive CTE depends on a streaming result table outside of the dependency cycle.
The most interesting thing is that i don’t change my query, i only update value in this table to any value after this, i return to initial value. And my query become to work
But if connect to db with new client, I got this error again
The 8.0.5 patch for 8.0 makes that case work.