Cockroach: sql: WITH RECURSIVE (recursive common table expressions)

[jordanlewis]

Split from #7029 which was closed when we merged support for single-use common table expressions in #20359.

Support WITH RECURSIVE, which allows a query to refer to its own output.

A simple example from Postgres's documentation sums the numbers from 1 to 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

The t clause uses itself as a data source, which requires the RECURSIVE modifier to WITH. This feature likely requires temporary tables to perform well.

[turbo]

Recursive CTEs are vital to storing graph or tree structures in a database and are even present in SQLite ;). Here's my specific use case: Calculating the Path of a Row in an Adjacency List When Recursive CTE Are Not Supported

[WestFarmer]

any updates?

please consider add full support for tree structure, such as 'path, level, isLeaf, sort siblings'

[gigatexal]

Just wanted to add my request to have recursive CTEs supported and if accepted a timeline for when to expect it?

[jkassis]

thumbs up for this. recursive queries from the client side could produce lots of network chatter on a big table. consider a hierarchical category system for an online store for a business case. defendants need to be queried for each user search and for category operations.