ClickHouse DB::Exception: Cannot create recursive function

The "DB::Exception: Cannot create recursive function" error in ClickHouse is raised when you attempt to create a user-defined function (UDF) that references itself in its own definition. ClickHouse explicitly prohibits recursive UDFs because they are lambda-based expressions, not stored procedures, and recursion could lead to infinite execution and stack overflow. The error code is CANNOT_CREATE_RECURSIVE_FUNCTION.

Impact

The CREATE FUNCTION statement fails, and no function is registered. This is a design-time error that prevents potentially dangerous recursive definitions from being saved.

Common Causes

1. Direct self-reference -- the UDF body calls itself by name.
2. Indirect recursion through another UDF -- function A calls function B, and function B calls function A.
3. Porting recursive logic from other databases -- recursive functions or procedures from PostgreSQL, MySQL, or other databases that support them cannot be directly translated to ClickHouse UDFs.
4. Accidental name collision -- the UDF body calls a function with the same name, not intending recursion.

Troubleshooting and Resolution Steps

1. Review the function definition for self-references:

   -- This will fail: direct recursion
   CREATE FUNCTION factorial AS (n) ->
       if(n <= 1, 1, n * factorial(n - 1));
   

2. Rewrite recursive logic iteratively. ClickHouse offers several approaches to replace recursion:

   Using arrayFold for accumulation:

   -- Factorial without recursion
   SELECT arrayFold((acc, x) -> acc * x, range(1, n + 1), toUInt64(1)) AS factorial;
   

   Using arrayMap and arrayReduce:

   SELECT arrayReduce('multiply', arrayMap(x -> x, range(1, 11))) AS factorial_10;
   

3. For tree or graph traversal, use recursive CTEs. ClickHouse supports recursive CTEs (WITH RECURSIVE) which are the proper way to handle hierarchical queries:

   WITH RECURSIVE cte AS (
       SELECT id, parent_id, name, 0 AS depth
       FROM categories
       WHERE parent_id = 0
       UNION ALL
       SELECT c.id, c.parent_id, c.name, cte.depth + 1
       FROM categories c
       JOIN cte ON c.parent_id = cte.id
       WHERE cte.depth < 10
   )
   SELECT * FROM cte;
   

4. Pre-compute recursive results. If the recursion operates on a fixed dataset, materialize the results into a table:

   -- Pre-compute a lookup table instead of using a recursive function
   CREATE TABLE factorials (n UInt8, result UInt64) ENGINE = Memory;
   INSERT INTO factorials VALUES (0, 1), (1, 1), (2, 2), (3, 6), (4, 24), (5, 120);
   

5. Check for indirect recursion. If function A references function B, ensure B does not reference A:

   SELECT name, create_query FROM system.functions
   WHERE origin = 'SQLUserDefined';
   

Best Practices

• Accept that ClickHouse UDFs are simple lambda expressions, not general-purpose stored procedures. Design accordingly.
• Use recursive CTEs for hierarchical data traversal rather than trying to implement recursion through UDFs.
• For mathematical recursion patterns (factorial, Fibonacci, etc.), use array functions or pre-computed lookup tables.
• Keep UDF logic simple and composable -- chain multiple simple UDFs rather than trying to build complex logic into one.

Frequently Asked Questions

Q: Will ClickHouse ever support recursive UDFs?
A: There is no indication this will change. ClickHouse UDFs are designed as lightweight lambda expressions for query reuse, not as a general programming mechanism. Recursive CTEs cover most hierarchical use cases.

Q: Can I use WITH RECURSIVE instead of a recursive UDF?
A: Yes. Recursive CTEs are the correct tool for hierarchical or iterative queries in ClickHouse. They have built-in depth limits to prevent infinite recursion.

Q: How does ClickHouse detect indirect recursion?
A: When creating a function, ClickHouse resolves all function references in the body. If it detects a cycle (A -> B -> A), it raises the CANNOT_CREATE_RECURSIVE_FUNCTION error during creation.

Q: What is the maximum depth for WITH RECURSIVE queries?
A: The default maximum depth is controlled by the max_recursive_cte_evaluation_depth setting. You can increase it if needed, but be cautious about performance and memory usage with deep recursion.