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.
with_clause: WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (subquery) [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
- 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 ().
WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte;
+------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
- 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
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.
- 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;
- 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.