CTE Query Enters Infinite Loop

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: CTE查询进入死循环

| username: TiDBer_L33essXJ

The project feedback error: “Recursive query aborted after 10011 iterations. Try increasing @cte_max_recursion_depth to a larger value”

| username: zhanggame1 | Original post link

It’s so simple, don’t use CTE. TiDB has quite a few bugs with CTE, especially when used with UNION ALL.

| username: 小龙虾爱大龙虾 | Original post link

Recursive CTE must include UNION ALL, right?

| username: Kongdom | Original post link

First, format the SQL.

WITH RECURSIVE cte_connect_by AS (
    SELECT s.insid, s.supr_inst_ecd FROM xxxxxx s
    WHERE insid = '320000001'
    UNION ALL
    SELECT s.insid, s.supr_inst_ecd FROM cte_connect_by r
    INNER JOIN xxxxxx s ON r.supr_inst_ecd = s.insid
) 
SELECT insid FROM cte_connect_by
ORDER BY insid;

Then, this should be controlled by parameters, try increasing the parameters.

| username: 有猫万事足 | Original post link

This recursion limit of 1000 is already exhausted?

I feel that even if you increase it, the execution will take a very long time. Imagine a tree with 1000 layers. The minimum would be 2 to the power of 1000. It’s too outrageous.
You should evaluate whether this approach is reasonable.

| username: Kongdom | Original post link

Indeed, without knowing the actual table structure and data, it is highly likely that the business logic is looping.

| username: yytest | Original post link

The error message you encountered indicates that your query exceeded the preset maximum recursion depth while performing a recursive operation. In SQL, especially when using Common Table Expressions (CTEs) for recursive queries, each database management system sets a maximum recursion depth limit to prevent infinite recursion from exhausting resources or causing program crashes.

| username: 濱崎悟空 | Original post link

The logic is not complex, no need for CTE~

| username: zhh_912 | Original post link

Increase the maximum recursion depth of CTE