This topic describes recursive query that is supported by AnalyticDB for MySQL clusters of version 3.1.4 and later. Recursive query is used for hierarchical queries. Recursive query allows a WITH query to reference the output of the WITH query.

Syntax

The Common Table Expression (CTE) is defined by using the WITH clause. For more information about how to use the WITH clause, see WITH. Syntax:
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
Parameters:
  • cte_name: the name of a single CTE. This parameter can be used as a table to reference in the statement that contains the WITH clause.
  • subquery: the subquery of the CTE. This parameter generates a result set of the CTE and must be enclosed in parentheses ().
If the subquery of the CTE references the name of the CTE, the expression is recursive. Example:
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
After the query is executed, the statement generates the following result. The result is a single column that contains a simple linear sequence:
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
Recursive CTE structure
  • If CTEs in the WITH clause are recursive, the CTEs must include recursive keywords.
  • A recursive CTE subquery consists of two parts that are separated by UNION[ALL] or UNION DISTINCT.
    SELECT ...      -- return initial row set
    UNION ALL
    SELECT ...      -- return additional row sets

    The first SELECT statement generates the initial row or rows of the CTE and does not reference the CTE name. The second SELECT statement generates other rows and recurs by referencing the CTE name in the FROM clause. When this part does not generate new rows, the recursion ends. Therefore, the recursive CTE consists of a non-recursive SELECT and a recursive SELECT. Each SELECT part can be the union of multiple SELECT statements.

Syntax limits of recursive CTE subqueries

  • Recursive SELECT does not support the agg function, window function, GROUP BY, ORDER BY, DISTINCT, and LIMIT. LIMIT has the same restrictions as MySQL. LIMIT is supported in MySQL versions later than 8.0.19. LIMIT is not supported by AnalyticDB for MySQL.
  • Different column definitions in subqueries and CTEs are not supported. For example, in the fibonacci sequence, the second and third columns in the CTE are fib_n and next_fib_n. These columns are the third and second columns in the SELECT statement.
    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    (
      SELECT 1, 0, 1
      UNION ALL
      SELECT n + 1, next_fib_n, fib_n + next_fib_n
        FROM fibonacci WHERE n < 10
    )
    SELECT * FROM fibonacci;
  • If the types of clauses in the SELECT subqueries are different, clauses are cast as the non-recursive part of the SELECT statement.

Scenarios

  1. Generate date series
    The CTE can generate a series of consecutive dates. You can use the CTE to generate summaries. Summaries include a row of all dates in the series and include dates that are not represented in summary data. The following example generates a date range series and calculates the sum of the sales for each date in the range:
    WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT * FROM dates;WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
    FROM dates LEFT JOIN sales ON dates.date = sales.date
    GROUP BY dates.date
    ORDER BY dates.date;
  2. Traverse hierarchical data
    You can query all nodes that are connected to a specific node based on the tree structure:
    WITH RECURSIVE employee_paths (id, name, path) AS
    (
      SELECT id, name, CAST(id AS CHAR(200))
        FROM employees
        WHERE manager_id IS NULL
      UNION ALL
      SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
        FROM employee_paths AS ep JOIN employees AS e
          ON ep.id = e.manager_id
    )
    SELECT * FROM employee_paths ORDER BY path;

Limit on the recursion of the CTE

For the recursive CTE, the recursive query part must include conditions to terminate the recursion. MAX_RECURSION_DEPTH specifies the maximum CTE depth. The default value is 10. If the default value is exceeded, an error is returned. You can use a hint to adjust the size. We recommend that you do not use hints. This may affect the execution efficiency. If the limit is adjusted, the value cannot exceed 30. Otherwise, an error may be reported because the number of recursive layers is large.

Set the maximum number of recursive layers to 20. Example:
/*+MAX_RECURSION_DEPTH=20*/ SQL