Краткий ответ

Запрос не завершится нормальным конечным результатом: после узлов 3 → 2 → 1 рекурсия снова получит 2, затем 1, и так далее. Причина — UNION ALL сохраняет повторяющиеся строки, а рекурсивный CTE не удаляет уже посещённые узлы автоматически; конкретная СУБД обычно прервёт выполнение по лимиту рекурсии, времени или памяти.

Исторический контекст

Рекурсивные CTE появились как декларативный способ обходить иерархии и графы: деревья каталогов, организационные структуры, зависимости и цепочки связанных объектов. Они позволяют описать начальный набор строк и правило получения следующего уровня без ручного цикла в прикладном коде.

При этом SQL сам по себе не предполагает, что граф является деревом. Поэтому обнаружение циклов и контроль посещённых узлов остаются частью логики запроса либо предоставляются специальными возможностями конкретной СУБД.

Постановка проблемы

В примере начальный узел — 3. Из него находится родитель 2, затем родитель 1, после чего ребро (1, 2) возвращает выполнение к узлу 2. Рекурсивная часть снова находит 1, и цикл повторяется бесконечно.

Такой запрос может нагрузить CPU и память, вернуть частичный результат перед отменой или завершиться ошибкой ограничения глубины рекурсии. Рассчитывать на то, что СУБД сама удалит повторные узлы, нельзя.

Подробное решение

Рекурсивный CTE состоит из якорной части и рекурсивной части. Якорь добавляет 3, затем на каждой итерации рекурсивная часть соединяет уже найденные узлы с edges и добавляет их родителей:

3 → 2 → 1 → 2 → 1 → ...

UNION ALL объединяет результаты как мультимножество и не выполняет устранение дубликатов. Поэтому повторное появление 2 считается новой строкой результата и снова используется на следующей итерации.

Один из вариантов защиты — заменить UNION ALL на UNION, если рекурсивный результат содержит только идентификатор узла:

WITH RECURSIVE edges(child, parent) AS (
    VALUES (1, 2), (2, 1), (3, 2)
), ancestors(node) AS (
    SELECT 3
    UNION
    SELECT e.parent
    FROM edges e
    JOIN ancestors a ON e.child = a.node
)
SELECT node
FROM ancestors;

В таком варианте уже получённые значения 2 и 1 не добавляются повторно, поэтому результат конечен. Однако UNION требует устранения дубликатов и может быть дороже UNION ALL на больших объёмах данных.

Есть важное ограничение: если в строке рекурсивного CTE хранится путь или глубина, каждая итерация может отличаться этими полями. Тогда UNION не обязательно остановит цикл, поскольку полные строки будут разными. Надёжная проверка должна хранить множество посещённых узлов или использовать предусмотренный конкретной СУБД механизм обнаружения циклов.

Даже при гарантированно ацикличных данных полезно задать ограничение глубины, если его поддерживает используемая СУБД. Синтаксис ограничения и поведение при превышении лимита различаются между диалектами SQL, поэтому их нужно проверять по документации конкретной системы.

Ситуация из практики

Сервис строит цепочку наследования категорий. Из-за ошибки миграции появились связи 10 → 20 и 20 → 10, а запрос использовал UNION ALL. В рабочей среде запрос не возвращал отчёт и завершался только после срабатывания защитного лимита.

Рассматривались три варианта:

заменить UNION ALL на UNION — просто и эффективно для результата, состоящего только из идентификаторов, но потенциально дороже из-за дедупликации;
хранить путь посещённых категорий и отбрасывать строку, если категория уже встречалась — позволяет явно обнаружить цикл, но требует дополнительной логики и увеличивает размер промежуточных строк;
исправить данные и добавить ограничение целостности — устраняет источник проблемы, но не защищает от аналогичных ошибок в будущих загрузках.

Выбрали комбинацию: исправили существующие связи, добавили проверку циклов при загрузке и оставили защиту глубины в отчётном запросе. Это позволило не полагаться только на корректность данных и предотвратило повторное зависание отчёта.

Что кандидаты часто упускают

Достаточно ли заменить UNION ALL на UNION в любом рекурсивном запросе?

Нет. Дедупликация сравнивает полные строки рекурсивного CTE. Если запрос возвращает (node, depth), один и тот же узел на разных уровнях имеет разные строки, поэтому цикл не будет остановлен. Для дедупликации по узлу нужно отдельно организовать проверку посещённых идентификаторов или использовать механизм циклов конкретной СУБД.
Остановит ли условие depth < 100 обнаружение цикла?

Нет, оно только ограничит число итераций. При цикле запрос будет повторять одни и те же узлы до глубины 100, а затем завершится с неполным и, возможно, логически неверным результатом. Ограничение глубины полезно как предохранитель, но не заменяет проверку циклов.
Почему UNION ALL обычно предпочтительнее при гарантии отсутствия циклов?

UNION ALL не тратит ресурсы на сравнение и удаление дубликатов, поэтому обычно быстрее и сохраняет все пути, если одна вершина достижима несколькими маршрутами. Однако это безопасно только при корректной модели данных или при явной логике контроля повторных посещений; иначе наличие цикла может привести к неограниченной рекурсии.