-- Missing recursion limit

WITH RECURSIVE org AS (

SELECT id, name, manager_id, 1 as level

FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, o.level+1

FROM employees e JOIN org o ON e.manager_id = o.id

-- No WHERE clause to stop recursion!

)

SELECT * FROM org;

-- Always limit recursion depth

WITH RECURSIVE org AS (

SELECT id, name, manager_id, 1 as level

FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, o.level+1

FROM employees e JOIN org o ON e.manager_id = o.id

WHERE o.level < 10 -- Prevents infinite recursion

)

SELECT * FROM org;

Without a termination condition, the query keeps recursing even after finding all employees. If there's a circular reference (A reports to B, B reports to A), it will loop infinitely. Always add WHERE level < max_depth in the recursive part.

Add WHERE level < 10 (or appropriate max depth) in recursive JOIN

Critical

Query runs forever or times out. Database may become unresponsive.

-- Using UNION instead of UNION ALL

WITH RECURSIVE tree AS (

SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL

UNION -- Wrong! Removes duplicates

SELECT c.id, c.parent_id, c.name

FROM categories c JOIN tree t ON c.parent_id = t.id

)

SELECT * FROM tree;

-- Always use UNION ALL in recursive CTEs

WITH RECURSIVE tree AS (

SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL

UNION ALL -- Correct! Keeps all rows

SELECT c.id, c.parent_id, c.name

FROM categories c JOIN tree t ON c.parent_id = t.id

WHERE t.level < 10

)