sql: WITH temp_data AS and recursion alternatives

[timchunght]

I believe as pointed out in an issue about table expressions that WITH data AS (select...) syntax is not supported yet and not to mention the postgres WITH recursive syntax. We are really trying to replace postgres with cockroach but would like to know what the timeline is for recursion. Or if it isn't in the future 1-3months, what will be an alternative to achieve essentially the following in the currently supported subset of SQL expressions implemented in cockroach. Please advise.

with recursive tree(id, name, parent_id) as (
    select n.id, n.name, n.parent_id
    from nodes n
    where n.id = 5
    union all
    select n.id, n.name, n.parent_id
    from nodes n
    join tree t on (n.id = t.parent_id)
)
select *
from tree;

So basically all nodes are connected to another node via parent_id, and given a child, we want to get all of its ancestors.

[petermattis]

Common Table Expressions are planned for the future, but we haven't given them a concrete placement on the roadmap. It is almost certain that they won't be supported in the next 1-3 months in a stable release as we're planning for a 6 month cadence for stable releases. The alternative to using a CTE (i.e. WITH or WITH RECURSIVE) is to perform the iteration/recursion on the client side in your application code.

@knz I believed you investigated how hard it would be to implement CTEs. Anything to add here?

[jordanlewis]

#7029 tracks the general common table expression implementation.

[timchunght]

@petermattis Certainly, recursion is not easy to implement. We are trying to avoid having to do recursion, but might have to now. Hopefully these CTEs can be added soon. Thanks for clarifying.

[knz]

So to clarify from the engineering perspective:

• adding support for non-recursive CTEs is a relatively (compared to the next item, but still non-trivial) small change to our execution engine, which was already on our roadmap for one of the upcoming releases this year
• adding support for recursive CTEs would imply a large rewriting of our execution engine, which we are considering already (it's needed for other features we plan too) but for which we can't set a timeline just yet. I don't think we can commit to a particular timeline unless a strong business case arises that would justify shuffling around our roadmap.

[jordanlewis]

Closing as we now have #21085 to track recursive CTEs specifically.