ClickHouse DB::Exception: Too deep recursion

The "DB::Exception: Too deep recursion" error in ClickHouse indicates that an internal recursion depth limit has been exceeded during query processing. The TOO_DEEP_RECURSION error code is a safety mechanism that prevents stack overflow crashes by limiting how deeply ClickHouse's internal algorithms can recurse when analyzing, optimizing, or executing a query.

Impact

The query is immediately aborted when this limit is hit. Because this error relates to internal processing rather than a user-configurable resource limit, it often points to a fundamentally problematic query structure. In rare cases, it can indicate a bug in ClickHouse itself. The error prevents potential segfaults or server crashes that would result from an actual stack overflow.

Common Causes

1. Extremely complex expressions with deep nesting that cause recursive AST traversal to exceed safe limits
2. Recursive data type definitions or deeply nested data structures (e.g., highly nested JSON parsed into nested Tuple types)
3. Complex query optimization passes that recurse deeply through large query plans
4. Circular or near-circular view dependencies causing deep expansion chains
5. Edge cases in query rewriting or macro expansion that produce unexpectedly deep structures

Troubleshooting and Resolution Steps

1. Simplify the query. Reduce nesting of expressions, subqueries, and function calls:

   -- Break deeply nested expressions into separate steps
   CREATE TEMPORARY TABLE intermediate AS
   SELECT complex_expression_part1 AS val FROM my_table;
   
   SELECT further_processing(val) FROM intermediate;
   

2. Check for circular view references. Although ClickHouse should detect these, complex chains of views can approach the recursion limit:

   SELECT name, as_select FROM system.tables
   WHERE engine = 'View' AND database = 'my_db';
   

3. Reduce data type nesting. If working with deeply nested JSON or Tuple structures, flatten them:

   -- Instead of deeply nested Tuples:
   -- Tuple(a Tuple(b Tuple(c Tuple(...))))
   -- Flatten to:
   -- a_b_c_value Type
   

4. Increase the stack size if the recursion depth is only slightly beyond the limit. This is done at the OS level, not within ClickHouse:

   ulimit -s unlimited
   

   Then restart ClickHouse. Use this approach with caution.

5. Update ClickHouse. If the error appears for queries that should not be problematic, it may be a bug that has been fixed in a newer version. Check the ClickHouse changelog for relevant fixes.

6. Try disabling specific optimizations to see if the optimizer is the source of deep recursion:

   SET optimize_move_to_prewhere = 0;
   SET optimize_aggregation_in_order = 0;
   SELECT ...;
   

Best Practices

• Keep expressions and data structures reasonably flat in their nesting.
• Avoid creating long chains of views that reference other views.
• Use temporary tables to materialize intermediate results rather than constructing monolithic queries.
• Monitor ClickHouse release notes for bug fixes related to recursion issues.
• When encountering this error on seemingly simple queries, consider filing a bug report with the ClickHouse project.

Frequently Asked Questions

Q: Is there a setting to increase the recursion depth limit?
A: There is no single user-facing setting to control this. The recursion limit is tied to internal constants and the available stack size. Increasing the OS-level stack size (ulimit -s) can help in some cases, but this is not a recommended long-term solution.

Q: Can this error indicate a bug in ClickHouse?
A: Yes. If a relatively simple query triggers this error, it may be a bug in the query optimizer or AST processing. Check the ClickHouse GitHub issues and consider reporting your case with a reproducible example.

Q: How is TOO_DEEP_RECURSION different from TOO_DEEP_AST?
A: TOO_DEEP_AST is checked against the parsed query structure before execution and is controlled by max_ast_depth. TOO_DEEP_RECURSION is an internal safety check during various processing stages (optimization, execution) and relates to the actual call stack depth rather than the AST structure alone.

Q: Does this error affect server stability?
A: The error itself is a protective measure that prevents instability. By catching the excessive recursion before a stack overflow occurs, ClickHouse avoids crashing. However, if you encounter this frequently, it suggests your workload needs restructuring.