MariaDB Tutorial / Query Hierarchies and Prevent Cycles with Recursive CTEs

A recursive common table expression, or recursive CTE, starts with an initial row set and repeatedly follows relationships to produce further rows. A reliable hierarchy query needs an anchor, a stopping rule, sufficiently wide path columns, cycle handling, and an explicit final sort.

Know when recursion adds value

An ordinary CTE names a query step. A recursive CTE also references that name inside its own definition: the anchor creates the first rows, and the recursive member finds the next rows from the preceding step. This example combines them with UNION ALL. Break Queries into Named Steps with WITH and CTEs introduces statement-scoped intermediate results; Design Single and Composite Indexes for Real Queries helps examine the access cost of locating children by parent key.

Category trees and organization charts can have variable depth, making recursive traversal useful. A fixed two-level relationship may be simpler with a JOIN. A large hierarchy also needs an index for child lookup and a deliberate depth bound. The MariaDB recursive CTE overview explains the iterative computation and anchor-column types.

Prepare a six-row category tree

Run this setup in the dedicated appops_lab schema using an administrator or a lab account with schema-creation privileges. If category_tree already exists, inspect the prior exercise before repeating its creation. The foreign key enforces the existence of a parent; it does not prove that the complete hierarchy is acyclic.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
  category_id INT NOT NULL PRIMARY KEY,
  parent_id INT NULL,
  category_name VARCHAR(40) NOT NULL,
  INDEX ix_parent (parent_id),
  CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
  (1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
  (4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');

Catalog contains Books and Tools. Books contains SQL and Python; Tools contains Editors. The parent_id index supports finding children during traversal. With only six rows, this lab demonstrates relationships and correctness rather than making general speed claims.

Run a complete query with depth and path tracking

WITH RECURSIVE tree AS (
  SELECT category_id, parent_id, category_name, 0 AS depth,
         CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
         CAST(category_name AS CHAR(1000)) AS name_path
  FROM category_tree WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
         CONCAT(t.visited, c.category_id, '/'),
         CONCAT(t.name_path, ' > ', c.category_name)
  FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
  WHERE t.depth < 8
    AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;

The first SELECT returns category 1 at depth zero. The recursive SELECT finds children whose parent_id matches the current category and increments depth. The visited path surrounds numeric identifiers with slashes, so checking ID 1 does not accidentally match ID 11.

Both path columns are explicitly cast to CHAR(1000) in the anchor. If an initial short string determines a narrow column type, later concatenated paths can overflow it. This example assumes integer IDs, short names, and a maximum depth of eight. Recalculate the width and bounds for larger real-world paths instead of treating 1000 as a universal safe value.

Only the final ORDER BY depth, category_id guarantees this presentation order. Do not infer display order from recursive evaluation. Sorting a text path also produces lexical ordering, which can differ from the numeric ordering of category IDs.

Change one condition and inspect the scope

Change only t.depth < 8 to t.depth < 1 and rerun the query. It includes the depth-zero anchor and its immediate children: IDs 1, 2, and 3. Restore the bound to eight afterward. Changing the anchor to category_id = 2 instead returns the Books subtree: IDs 2, 4, and 5, with Books now at depth zero.

A result cut off by the depth limit is not necessarily a complete hierarchy. Determine whether the actual structure is deeper before increasing the bound. The server’s max_recursive_iterations variable is another limit on repeated computation; it does not replace a business stopping rule or validate the parent relationships.

Introduce a cycle and roll it back

In the same connection, run these two statements and then rerun the complete recursive query above. Another connection might not see the uncommitted parent change.

START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;

Assigning parent 4 to category 1 creates a 1 → 2 → 4 → 1 cycle. Because this query explicitly starts at category 1, its visited-ID test excludes the return to that already visited row and still finishes with six rows. Restore the original data immediately after observing it:

ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;

The parent must be NULL again. A query anchored on parent_id IS NULL could miss an entire cyclic component after its root disappears. Therefore, avoiding revisits during one traversal does not prove that the complete database has no cycles. MariaDB also offers CYCLE ... RESTRICT syntax; the explicit path here makes the traversal rule visible.

Check correctness before using a production hierarchy

• Confirm that the anchor selects exactly the intended root or roots.
• Check missing parents, self-parent rows, cycles, and unreachable components as different conditions.
• Choose a maximum depth and calculate a compatible path width.
• Inspect the child-lookup index and EXPLAIN, and reconcile the resulting row count with the expected relationships.

To clean up, confirm that this article’s table is disposable and run DROP TABLE appops_lab.category_tree;. That schema change is not undone by an ordinary ROLLBACK. Do not substitute a business table name into the cleanup command.