📋

Description

MySQL Error 3573 indicates that a recursive Common Table Expression (CTE) has been defined without the mandatory `UNION` or `UNION ALL` operator. Recursive CTEs require two parts: a non-recursive (anchor) member and a recursive member, which must be combined using one of these set operators to allow the query to iterate and produce results. This error prevents the query from executing successfully.

💬

Error Message

Recursive Common Table Expression '%s' should contain a UNION

🔍

Known Causes

3 known causes

⚠️

Omitted UNION/UNION ALL

The most common cause is simply forgetting to include the `UNION` or `UNION ALL` operator between the anchor member and the recursive member of the CTE.

⚠️

Incorrect CTE Structure

The overall structure of the recursive CTE might be malformed, leading MySQL to identify the absence of the required `UNION` clause.

⚠️

Misunderstanding Recursive CTE Syntax

Lack of familiarity with the specific syntax requirements for MySQL recursive CTEs, particularly the necessity of combining the two members with a `UNION` operator.

🛠️

Solutions

3 solutions available

1. Add UNION ALL to Combine CTE Parts easy

Ensure the recursive CTE correctly combines its base and recursive members with UNION ALL.

1

Identify the recursive CTE definition in your SQL query. A recursive CTE must have a base query and a recursive query that are joined by a UNION or UNION ALL operator.

2

Locate the point where the base query (the non-recursive part) and the recursive query (the part that references the CTE itself) meet. Ensure that a `UNION ALL` or `UNION` operator is present between them.

3

If the `UNION ALL` or `UNION` operator is missing, add it. `UNION ALL` is generally preferred for performance as it doesn't remove duplicates.

WITH RECURSIVE cte_name AS (
  -- Base query
  SELECT ...
  UNION ALL  -- <-- Add this operator
  -- Recursive query
  SELECT ... FROM cte_name WHERE ...
)
SELECT ... FROM cte_name;

2. Verify CTE Structure for Recursion medium

Confirm that the CTE adheres to the standard structure for recursive CTEs in MySQL.

1

A recursive CTE in MySQL has a specific structure: `WITH RECURSIVE cte_name AS ( <base_query> UNION ALL <recursive_query> ) SELECT ... FROM cte_name;`.

2

Review your CTE definition. Ensure it starts with `WITH RECURSIVE`. If it's not intended to be recursive, remove the `RECURSIVE` keyword. If it is recursive, ensure the `UNION ALL` (or `UNION`) is correctly placed between the base and recursive member.

WITH RECURSIVE my_recursive_cte AS (
    -- Base member: This is the starting point.
    SELECT 1 AS n
    UNION ALL
    -- Recursive member: This references the CTE itself.
    SELECT n + 1 FROM my_recursive_cte WHERE n < 10
)
SELECT n FROM my_recursive_cte;

3

Check that the base query and the recursive query have compatible column structures (number of columns and data types).

3. Correct Non-Recursive CTE Syntax easy

If recursion is not intended, remove the 'RECURSIVE' keyword and ensure the CTE is structured correctly.

1

If your CTE is not meant to be recursive, you might have accidentally included the `RECURSIVE` keyword. Remove it.

WITH my_cte AS (
  SELECT column1, column2 FROM my_table WHERE condition
)
SELECT * FROM my_cte;

2

If you intended to use `UNION ALL` for non-recursive purposes (e.g., combining results from multiple queries), ensure it's placed correctly without the `RECURSIVE` keyword.

WITH combined_data AS (
  SELECT col_a FROM table1
  UNION ALL
  SELECT col_b FROM table2
)
SELECT * FROM combined_data;

🔗

Related Errors

5 related errors

MySQL Error 3573: Recursive CTE Missing UNION