📋

Description

This error signifies that a Common Table Expression (CTE) intended to be recursive is structured incorrectly. MySQL requires that the non-recursive (anchor) part of a recursive CTE must be defined before any recursive query blocks.

💬

Error Message

Recursive Common Table Expression '%s' should have one or more non-recursive query blocks followed by one or more recursive ones

🔍

Known Causes

3 known causes

⚠️

Non-recursive part defined after recursive

The initial non-recursive (anchor) member of the recursive CTE is placed after the recursive member, violating the required syntax order.

⚠️

Missing non-recursive query block

The recursive CTE entirely lacks an initial non-recursive query block, which is essential for establishing the base case and termination.

⚠️

Incorrect `UNION ALL` ordering

When combining the non-recursive and recursive parts with `UNION ALL`, they are arranged in the wrong sequence, putting the recursive part first.

🛠️

Solutions

3 solutions available

1. Correct CTE Structure: Non-Recursive Part First easy

Ensure your CTE's non-recursive part precedes the recursive part.

1

Identify the Common Table Expression (CTE) causing the error. The error message usually specifies the CTE name (e.g., `my_recursive_cte`).

2

Examine the CTE definition. It must start with a `SELECT` statement that does NOT reference the CTE itself. This is the non-recursive anchor member.

3

Following the non-recursive part, there should be one or more `UNION ALL` or `UNION` clauses. Each of these should introduce the recursive member(s).

4

The recursive member(s) are `SELECT` statements that *do* reference the CTE defined earlier. They must appear *after* the initial non-recursive part and any intermediate `UNION` clauses.

WITH RECURSIVE cte_name (col1, col2) AS (
    -- Non-recursive anchor member (must come first)
    SELECT initial_value_col1, initial_value_col2 FROM source_table WHERE condition
    UNION ALL
    -- Recursive member(s) (must come after anchor)
    SELECT cte_name.col1 + 1, source_table.col2 FROM cte_name JOIN source_table ON cte_name.id = source_table.parent_id WHERE cte_name.col1 < limit_value
)
SELECT * FROM cte_name;

2. Separate Anchor and Recursive Members Explicitly easy

Clearly distinguish the initial query from the recursive queries using UNION ALL.

1

Locate the CTE definition that is triggering the 'Recursive CTE Order' error.

2

Ensure the very first `SELECT` statement within the CTE is the 'anchor' or 'base' case. This query should not reference the CTE itself.

3

Use `UNION ALL` (or `UNION`) to connect the anchor member to the subsequent recursive member(s).

4

The recursive member(s) are `SELECT` statements that reference the CTE. These must follow the anchor member and the `UNION ALL` operator.

WITH RECURSIVE my_cte AS (
    -- Anchor member: No reference to my_cte
    SELECT 1 AS level, 'Root' AS node_name
    UNION ALL
    -- Recursive member: References my_cte
    SELECT cte.level + 1, CONCAT(cte.node_name, ' -> Child')
    FROM my_cte cte
    WHERE cte.level < 5
)
SELECT * FROM my_cte;

3. Review CTE Syntax for Multiple Recursive Parts medium

If using multiple recursive parts, ensure they are correctly sequenced after the non-recursive part.

1

Identify the CTE with the ordering issue.

2

Verify that the initial `SELECT` statement (anchor member) is the absolute first part of the CTE definition.

3

Each subsequent `SELECT` statement that references the CTE must be preceded by a `UNION ALL` operator and must follow the anchor member or a previous recursive member.

4

A common mistake is placing a recursive part before the anchor or incorrectly structuring multiple recursive parts. The structure should always be: `ANCHOR UNION ALL RECURSIVE_1 UNION ALL RECURSIVE_2 ...`

WITH RECURSIVE hierarchy AS (
    -- Anchor Member
    SELECT id, parent_id, name, 1 AS level FROM employees WHERE parent_id IS NULL
    UNION ALL
    -- First Recursive Member
    SELECT e.id, e.parent_id, e.name, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
    WHERE h.level < 10
    UNION ALL
    -- Second Recursive Member (if needed, but typically not for simple hierarchies)
    -- This example shows incorrect placement if it were recursive and placed first.
    -- For correct structure, it must follow the first recursive part.
    SELECT ... FROM hierarchy ... -- This would be valid if it followed the first recursive part.
)
SELECT * FROM hierarchy;

🔗

Related Errors

5 related errors

MySQL Error 3574: Recursive CTE Order