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

Перенос фильтра в рекурсивную часть может не только скрыть неподходящие узлы, но и прекратить обход через них. Поэтому потомки подходящих узлов могут исчезнуть из результата, хотя сами они удовлетворяют фильтру.

Фильтр в итоговом запросе применяется после построения всего множества достижимых узлов. Фильтр в рекурсивной части ограничивает множество узлов, из которых разрешено продолжать следующий шаг обхода.

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

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

Ключевая идея рекурсивного CTE — разделить начальные строки и правило получения следующего поколения строк. Это позволяет описать обход дерева или графа как итеративное построение набора данных внутри одного SQL-оператора.

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

Пусть из узла 1 можно перейти в узел 2, а из узла 2 — в узел 3. Узел 2 может не соответствовать условию отчёта, тогда как узел 3 ему соответствует.

Если отфильтровать результат только в конце, узел 2 не попадёт в выдачу, но узел 3 будет найден. Если же запретить рекурсию через узел 2, узел 3 вообще не будет достигнут.

Ошибка особенно опасна в запросах для организационных структур, маршрутов, зависимостей и графов доступа: итог может выглядеть правдоподобно, но содержать неполный набор потомков.

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

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

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

Условие в рекурсивной части влияет на границу обхода. Если оно проверяет текущий узел перед переходом к потомкам, то после неподходящего узла дальнейшие переходы прекращаются. Важно различать фильтрацию добавляемого узла и фильтрацию узла-родителя: эти варианты могут давать разные результаты.

Минимальный пример:

WITH RECURSIVE
nodes(id, active) AS (
  VALUES (1, true), (2, false), (3, true)
),
edges(parent, child) AS (
  VALUES (1, 2), (2, 3)
),
walk(id) AS (
  SELECT 1
  UNION ALL
  SELECT e.child
  FROM edges e JOIN walk w ON e.parent = w.id
)
SELECT w.id
FROM walk w JOIN nodes n ON n.id = w.id
WHERE n.active;

В этом варианте обход сначала находит узлы 1, 2 и 3, а затем оставляет 1 и 3. Если добавить ограничение, запрещающее рекурсию из неактивных узлов, переход из 2 в 3 не выполнится.

Фильтр в рекурсивной части следует использовать, когда он описывает допустимую область поиска: например, запрещённые ветви действительно нельзя исследовать. Если требуется найти всех достижимых потомков, а затем показать только подходящие узлы, фильтр должен применяться после обхода.

В графах с циклами дополнительно требуется стратегия предотвращения повторного посещения узлов: например, хранение пути, проверка уже посещённых идентификаторов или применение возможностей конкретной СУБД. Фильтр по свойству узла сам по себе не гарантирует завершение рекурсии.

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

В системе зависимостей нужно показать все активные сервисы, достижимые от выбранного сервиса. Неактивные сервисы должны быть скрыты, но через них зависимости всё равно могут вести к активным сервисам.

Первый вариант — фильтровать активность в рекурсивном члене. Его плюс — меньший объём обхода и потенциально лучшая производительность. Минус — активные потомки после неактивного промежуточного узла будут потеряны.

Второй вариант — строить полный обход, а фильтровать активность после него. Он сохраняет корректную достижимость, но может обработать много ненужных узлов и потребовать защиты от циклов.

Выбран второй вариант, потому что бизнес-смысл задачи требует не удалять связи через неактивные узлы. Для ускорения отдельно ограничили область графа по периоду действия зависимостей и добавили контроль посещённых узлов. В результате отчёт сохранил все требуемые активные сервисы, а объём обхода остался ограниченным.

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

Всегда ли фильтр в рекурсивной части фильтрует именно добавляемый узел?

Нет. Это зависит от того, какие поля и строки участвуют в условии. Условие может проверять текущую строку рекурсии, родителя, ребёнка или их комбинацию. Фильтр по родителю ограничивает продолжение обхода, а фильтр по ребёнку может не добавлять конкретный узел, но логика должна быть явно сформулирована.
Можно ли получить одинаковый результат фильтром в конце и фильтром внутри рекурсии?

Иногда да — например, если доказано, что любой путь к подходящему узлу состоит только из подходящих промежуточных узлов. Без такого свойства эквивалентность гарантировать нельзя: промежуточный узел может не соответствовать фильтру, но вести к подходящему потомку.
Что произойдёт, если фильтр зависит от уровня вложенности?

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