In WITH RECURSIVE, the anchor SELECT produces the first rows, and the recursive SELECT after UNION ALL joins the previous round's rows to find the next, until a round finds nothing. Three new categories deepen the tree, and one query walks it from Programming down:
INSERT INTO categories (name, parent_id) VALUES ('Web', 2), ('PHP', 6), ('Laravel', 7);
WITH RECURSIVE tree AS (
SELECT id, name, 0 AS depth, CAST(name AS CHAR(200)) AS path
FROM categories WHERE id = 2
UNION ALL
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
FROM categories AS c JOIN tree AS t ON c.parent_id = t.id
)
SELECT id, depth, path FROM tree ORDER BY path;
WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n)
SELECT COUNT(*) FROM n;Query OK, 3 rows affected (0.011 sec) Records: 3 Duplicates: 0 Warnings: 0 +------+-------+-----------------------------------+ | id | depth | path | +------+-------+-----------------------------------+ | 2 | 0 | Programming | | 6 | 1 | Programming > Web | | 7 | 2 | Programming > Web > PHP | | 8 | 3 | Programming > Web > PHP > Laravel | +------+-------+-----------------------------------+ 4 rows in set (0.001 sec) ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Anchor on parent_id IS NULL for the whole tree. Column types come from the anchor alone: uncast, path would be VARCHAR(50), and a long enough path fails with error 1406, "Data too long for column 'path'". The recursive part may not use aggregates, window functions, GROUP BY, ORDER BY or DISTINCT. To walk upward, anchor on Laravel 2,157 and join c.id = up.parent_id. Joined to products, the CTE answers "how many products sit anywhere under Books": 6.
The second query has no stop condition, so cte_max_recursion_depth (default 1000) aborts it. Add WHERE i < 3000 and raise the cap for your session only: SET SESSION cte_max_recursion_depth = 5000. A cycle in the data hits the same error; UNION DISTINCT ends such a walk.