WITH Clause

```
WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;
```

```
┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘
```

But if we add cycle in that graph, previous query will fail with `Maximum recursive CTE evaluation depth` error:

```
INSERT INTO graph VALUES (5, 1, '5 -> 1');

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;
```

```
Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)
```

The standard method for handling cycles is to compute an array of the already visited nodes:

**Example:** Graph traversal with cycle detection

```
WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;
```

```
┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘
```

### Infinite queries[​](#infinite-queries "Direct link to Infinite queries")

It is also possible to use infinite recursive CTE queries if `LIMIT` is used in outer query:

**Example:** Infinite recursive CTE query

```
WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);
```

```
┌─sum(number)─┐
│        5050 │
└─────────────┘
```

- [Common Table Expressions](#common-table-expressions)
  - [Syntax](#common-table-expressions-syntax)- [Example](#common-table-expressions-example)- [Materialized Common Table Expressions](#materialized-common-table-expressions)
    - [Syntax](#materialized-common-table-expressions-syntax)- [When to use](#materialized-cte-when-to-use)- [Examples](#materialized-common-table-expressions-examples)- [Restrictions](#materialized-cte-restrictions)- [Common Scalar Expressions](#common-scalar-expressions)
      - [Syntax](#common-scalar-expressions-syntax)- [Examples](#common-scalar-expressions-examples)- [Recursive Queries](#recursive-queries)
        - [Search order](#search-order)- [Cycle detection](#cycle-detection)- [Infinite queries](#infinite-queries)

Was this page helpful?