Конструкция WITH в SQLite: обобщённые табличные выражения

---

Обзор синтаксиса

Обобщённые табличные выражения (Common Table Expressions, CTE) представляют собой временные конструкции, доступные только во время выполнения одного SQL-оператора. Существует два типа CTE: «обычные» и «рекурсивные». Обычные CTE упрощают запросы, позволяя вынести подзапросы за пределы основного оператора, а рекурсивные CTE служат для выполнения иерархических или рекурсивных запросов к деревьям и графам.

Все CTE (обычные и рекурсивные) создаются путём добавления конструкции WITH перед оператором SELECT, INSERT, DELETE или UPDATE. Одна конструкция WITH может задавать одно или несколько обобщённых табличных выражений, часть из которых может быть обычными, а часть — рекурсивными.

Формальный синтаксис конструкции выглядит следующим образом:

WITH RECURSIVE cte-table-name AS [NOT] MATERIALIZED ( select-stmt ) , ...

Где cte-table-name задаётся как table-name [ ( column-name, ... ) ], а select-stmt — полноценный оператор выборки, включая составные операторы (UNION, UNION ALL, INTERSECT, EXCEPT), ORDER BY, LIMIT и OFFSET.

Обычные CTE: синтаксис и примеры использования

Обычное CTE функционирует как временное представление (view) на время выполнения одного SQL-оператора. Эти конструкции особенно полезны для вынесения подзапросов, что упрощает чтение и понимание общего запроса.

Конструкция WITH позволяет включать обычные CTE даже при использовании RECURSIVE. Это ключевое слово не требует, чтобы все CTE в блоке были рекурсивными; оно просто разрешает использование рекурсии там, где это необходимо.

Рекурсивные CTE в SQLite: синтаксис и алгоритм работы

Рекурсивное CTE можно использовать для написания запроса, обходящего дерево или граф. Оно имеет тот же базовый синтаксис, что и обычное, но должно соответствовать нескольким требованиям: селект должен быть составным (состоящим из двух и более операторов SELECT), один из операторов должен быть рекурсивным — то есть ссылаться на таблицу CTE ровно один раз в предложении FROM, а кроме того, все нерекурсивные операторы SELECT должны предшествовать рекурсивным.

• select-stmt должен быть составным выбором (compound select), то есть тело CTE должно состоять из двух или более отдельных операторов SELECT, разделённых составными операторами: UNION, UNION ALL, INTERSECT или EXCEPT
• Один или несколько отдельных операторов SELECT должны быть «рекурсивными» — то есть их предложение FROM содержит ровно одну ссылку на таблицу CTE
• Один или несколько операторов SELECT должны быть нерекурсивными
• Все нерекурсивные операторы SELECT должны располагаться перед любыми рекурсивными
• Рекурсивные операторы SELECT должны быть отделены от нерекурсивных операторами UNION или UNION ALL
• Рекурсивные операторы SELECT не могут использовать агрегатные функции или оконные функции

Иными словами, рекурсивное CTE должно выглядеть примерно так:

cte-table-name AS ( initial-select UNION [ALL] recursive-select
)

Здесь initial-select — один или несколько нерекурсивных операторов SELECT, а recursive-select — один или несколько рекурсивных. Наиболее распространённый случай — ровно один initial-select и ровно один recursive-select, однако допускается и большее количество каждого из них.

Рекурсивное CTE должно содержать рекурсивную таблицу, которая фигурирует только один раз в предложении FROM каждого верхнего уровня в recursive-select. Эта таблица не должна повторяться ни в initial-select, ни в recursive-select, включая подзапросы. initial-select может быть составным запросом, но не должен включать ORDER BY, LIMIT или OFFSET.

Рекурсивное тело тоже может быть составным, но его элементы должны быть разделены тем же оператором, который соединяет initial-select и recursive-select.

Возможность для recursive-select быть составным запросом была добавлена в версии SQLite 3.34.0 (2020-12-01). В более ранних версиях recursive-select мог быть только одним простым оператором SELECT.

Базовый алгоритм вычисления содержимого рекурсивной таблицы выглядит следующим образом:

1. Выполнить initial-select и добавить результаты в очередь
2. Пока очередь не пуста:

• a. Извлечь одну строку из очереди
• b. Вставить эту одну строку в рекурсивную таблицу
• c. Считая, что только что извлечённая строка является единственной строкой в рекурсивной таблице, выполнить recursive-select, добавив все результаты в очередь

Описанная выше базовая процедура может быть изменена следующими дополнительными правилами:

• Если оператор UNION соединяет initial-select с recursive-select, строки добавляются в очередь только в том случае, если идентичная строка ранее не была добавлена. Повторяющиеся строки отбрасываются до добавления в очередь, даже если они уже были извлечены из очереди на шаге рекурсии. Если используется UNION ALL, все строки всегда добавляются в очередь, даже если они являются повторами. При определении того, является ли строка повторяющейся, значения NULL считаются равными друг другу и не равными любому другому значению
• Предложение LIMIT, если оно присутствует, определяет максимальное количество строк, которые когда-либо будут добавлены в рекурсивную таблицу на шаге 2b. Как только лимит достигнут, рекурсия останавливается. Лимит, равный нулю, означает, что в рекурсивную таблицу не добавляется ни одной строки, а отрицательный лимит означает, что может быть добавлено неограниченное количество строк
• Предложение OFFSET, если оно присутствует и имеет положительное значение N, предотвращает добавление первых N строк в рекурсивную таблицу. Первые N строк всё равно обрабатываются recursive-select — они просто не добавляются в рекурсивную таблицу. Строки не засчитываются в счёт выполнения LIMIT до тех пор, пока все строки OFFSET не будут пропущены
• Если присутствует предложение ORDER BY, оно определяет порядок, в котором строки извлекаются из очереди на шаге 2a. Если предложение ORDER BY отсутствует, порядок извлечения строк не определён. В текущей реализации очередь становится очередью FIFO (первым пришёл — первым вышел) при отсутствии ORDER BY, однако приложения не должны полагаться на этот факт, поскольку он может измениться

Примеры рекурсивных запросов в SQLite

Счётчик от 1 до 1 000 000

Следующий запрос возвращает все целые числа от 1 до 1 000 000:

WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;

Рассмотрим, как работает этот запрос. Сначала выполняется начальный select, который возвращает одну строку с единственным столбцом «1». Эта одна строка добавляется в очередь. На шаге 2a эта одна строка извлекается из очереди и добавляется в «cnt». Затем выполняется рекурсивный select в соответствии с шагом 2c, генерируя одну новую строку со значением «2» для добавления в очередь.

В очереди по-прежнему одна строка, поэтому шаг 2 повторяется. Строка «2» извлекается и добавляется в рекурсивную таблицу на шагах 2a и 2b. Затем строка, содержащая 2, используется так, как если бы она была полным содержимым рекурсивной таблицы, и рекурсивный select выполняется снова, в результате чего в очередь добавляется строка со значением «3».

Это повторяется 999 999 раз, пока наконец на шаге 2a единственным значением в очереди не окажется строка, содержащая 1 000 000. Эта строка извлекается и добавляется в рекурсивную таблицу. Но на этот раз условие WHERE заставляет рекурсивный select не возвращать ни одной строки, поэтому очередь остаётся пустой и рекурсия останавливается.

Примечание об оптимизации: при описании вышеизложенного множество утверждений, включая «вставить строку в рекурсивную таблицу», следует понимать концептуально. Оптимизатор запросов осознает, что значения в рекурсивной таблице «cnt» используются лишь единожды и добавляются, а затем немедленно возвращаются как результат основного оператора SELECT.

Вот вариация на предыдущий пример:

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 1000000
)
SELECT x FROM cnt;

В этой вариации есть два отличия. Начальный select — это SELECT 1 вместо VALUES(1). Но это просто разные синтаксисы для выражения одного и того же. Другое изменение состоит в том, что рекурсия останавливается с помощью LIMIT, а не условия WHERE.

Использование LIMIT означает, что когда миллионная строка добавляется в таблицу «cnt» (и возвращается основным SELECT благодаря оптимизатору запросов), рекурсия немедленно останавливается независимо от того, сколько строк может оставаться в очереди. В более сложных запросах иногда бывает трудно гарантировать, что условие WHERE в конечном счёте приведёт к опустошению очереди и завершению рекурсии.

Но условие LIMIT всегда остановит рекурсию. Поэтому хорошей практикой является всегда включать условие LIMIT в качестве защитной меры, если известна верхняя граница размера рекурсии.

Иерархические запросы

Рассмотрим таблицу, описывающую членов организации, а также цепочку командования внутри этой организации:

CREATE TABLE org( name TEXT PRIMARY KEY, boss TEXT REFERENCES org, height INT, -- другое содержимое опущено
);

Каждый член организации имеет имя, и у большинства членов есть один непосредственный руководитель. У главы всей организации поле «boss» равно NULL. Строки таблицы «org» образуют дерево.

Вот запрос, который вычисляет средний рост всех сотрудников в организации Алисы, включая саму Алису:

WITH RECURSIVE works_for_alice(n) AS ( VALUES('Alice') UNION SELECT name FROM org, works_for_alice WHERE org.boss=works_for_alice.n
)
SELECT avg(height) FROM org WHERE org.name IN works_for_alice;

В следующем примере в одном предложении WITH используются два CTE. Следующая таблица записывает генеалогическое дерево:

CREATE TABLE family( name TEXT PRIMARY KEY, mom TEXT REFERENCES family, dad TEXT REFERENCES family, born DATETIME, died DATETIME -- NULL если ещё жив -- другое содержимое
);

Таблица «family» похожа на более раннюю таблицу «org», за исключением того, что теперь у каждого члена двое родителей. Мы хотим узнать всех живых предков Алисы, от старших к младшим. Сначала определяется обычное CTE «parent_of» — представление, которое можно использовать для поиска всех родителей любого человека. Затем это обычное CTE используется в рекурсивном CTE «ancestor_of_alice», которое затем используется в финальном запросе:

WITH RECURSIVE parent_of(name, parent) AS ( SELECT name, mom FROM family UNION SELECT name, dad FROM family ), ancestor_of_alice(name) AS ( SELECT parent FROM parent_of WHERE name='Alice' UNION ALL SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name) )
SELECT family.name FROM ancestor_of_alice, family
WHERE ancestor_of_alice.name=family.name AND died IS NULL
ORDER BY born;

Запросы к графу

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

CREATE TABLE edge(aa INT, bb INT);
CREATE INDEX edge_aa ON edge(aa);
CREATE INDEX edge_bb ON edge(bb);

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

WITH RECURSIVE nodes(x) AS ( SELECT 59 UNION SELECT aa FROM edge JOIN nodes ON bb=x UNION SELECT bb FROM edge JOIN nodes ON aa=x
)
SELECT x FROM nodes;

Начальный SELECT в данном случае — это простой запрос SELECT 59. Он устанавливает базовый случай. Рекурсивный SELECT состоит из двух других операторов SELECT. Первый рекурсивный SELECT следует рёбрам в направлении от bb к aa, а второй — в направлении от aa к bb. Вместо UNION ALL используется UNION, чтобы предотвратить уход рекурсии в бесконечный цикл, если граф содержит циклы.

Вот реальный пример использования запроса к графу применительно к ориентированному графу: система контроля версий (VCS) обычно хранит развивающиеся версии проекта в виде направленного ациклического графа (DAG). Назовём каждую версию проекта «чекином» (checkin). Один чекин может иметь ноль или более родителей.

Большинство чекинов (кроме первого) имеют одного родителя, но в случае слияния чекин может иметь двух, трёх или более родителей. Схема для отслеживания чекинов и порядка их появления может выглядеть примерно так:

CREATE TABLE checkin( id INTEGER PRIMARY KEY, mtime INTEGER -- временная метка создания чекина
);
CREATE TABLE derivedfrom( xfrom INTEGER NOT NULL REFERENCES checkin, -- родительский чекин xto INTEGER NOT NULL REFERENCES checkin, -- производный чекин PRIMARY KEY(xfrom,xto)
);
CREATE INDEX derivedfrom_back ON derivedfrom(xto,xfrom);

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

Мы хотим узнать двадцать наиболее поздних по времени предков (из тысяч и тысяч предков во всём DAG) для чекина @BASELINE. Похожий запрос используется в Fossil VCS для отображения N наиболее поздних предков чекина:

WITH RECURSIVE ancestor(id,mtime) AS ( SELECT id, mtime FROM checkin WHERE id=@BASELINE UNION SELECT derivedfrom.xfrom, checkin.mtime FROM ancestor, derivedfrom, checkin WHERE ancestor.id=derivedfrom.xto AND checkin.id=derivedfrom.xfrom ORDER BY checkin.mtime DESC LIMIT 20
)
SELECT * FROM checkin JOIN ancestor USING(id);

Условие ORDER BY checkin.mtime DESC в рекурсивном SELECT значительно ускоряет выполнение запроса, не давая ему следовать ветвям, которые сливают чекины из далёкого прошлого. ORDER BY заставляет рекурсивный SELECT сосредоточиться на самых последних чекинах — тех, которые нам нужны. Без ORDER BY в рекурсивном SELECT пришлось бы вычислять полный набор из тысяч предков, сортировать их все по mtime, а затем брать первые двадцать. ORDER BY фактически создаёт очередь с приоритетом, которая заставляет рекурсивный запрос рассматривать наиболее поздних предков первыми, что позволяет использовать предложение LIMIT для ограничения области запроса только интересующими нас чекинами.

Управление поиском в глубину и поиском в ширину по дереву с помощью ORDER BY

Предложение ORDER BY в рекурсивном SELECT можно использовать для управления тем, является ли обход дерева поиском в глубину (depth-first search) или поиском в ширину (breadth-first search). Для иллюстрации воспользуемся вариантом таблицы «org» из приведённого выше примера, без столбца «height», но с реальными вставленными данными:

CREATE TABLE org( name TEXT PRIMARY KEY, boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');

Вот запрос для отображения структуры дерева в режиме поиска в ширину:

WITH RECURSIVE under_alice(name,level) AS ( VALUES('Alice',0) UNION ALL SELECT org.name, under_alice.level+1 FROM org JOIN under_alice ON org.boss=under_alice.name ORDER BY 2
)
SELECT substr('..........',1,level*3) || name FROM under_alice;

ORDER BY 2 (что означает то же самое, что ORDER BY under_alice.level+1) заставляет более высокие уровни в организационной схеме (с меньшими значениями «level») обрабатываться первыми, что приводит к поиску в ширину. Результат:

Alice
...Bob
...Cindy
......Dave
......Emma
......Fred
......Gail

Но если изменить предложение ORDER BY, добавив модификатор DESC, это заставит рекурсивный SELECT обрабатывать первыми более низкие уровни организации (с большими значениями «level»), что приведёт к поиску в глубину:

WITH RECURSIVE under_alice(name,level) AS ( VALUES('Alice',0) UNION ALL SELECT org.name, under_alice.level+1 FROM org JOIN under_alice ON org.boss=under_alice.name ORDER BY 2 DESC
)
SELECT substr('..........',1,level*3) || name FROM under_alice;

Результат этого изменённого запроса:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail

Когда предложение ORDER BY в рекурсивном SELECT опущено, очередь ведёт себя как FIFO, что приводит к поиску в ширину.

Экзотические примеры: множество Мандельброта и решатель судоку

Следующий запрос вычисляет приближение множества Мандельброта (Mandelbrot set) и выводит результат в виде ASCII-арта:

WITH RECURSIVE xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2), yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0), m(iter, cx, cy, x, y) AS ( SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis UNION ALL SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m WHERE (x*x + y*y) < 4.0 AND iter<28 ), m2(iter, cx, cy) AS ( SELECT max(iter), cx, cy FROM m GROUP BY cx, cy ), a(t) AS ( SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') FROM m2 GROUP BY cy )
SELECT group_concat(rtrim(t),x'0a') FROM a;

В этом запросе CTE «xaxis» и «yaxis» определяют сетку точек, для которых будет вычислено приближение множества Мандельброта. Каждая строка в CTE m(iter,cx,cy,x,y) означает, что после «iter» итераций итерация Мандельброта, начавшаяся в точке cx,cy, достигла точки x,y.

Количество итераций в данном примере ограничено 28 (что существенно снижает разрешение вычисления, но достаточно для вывода ASCII-арта с низким разрешением). CTE m2(iter,cx,cy) хранит максимальное количество итераций, достигнутое при старте из точки cx,cy. Наконец, каждая строка в CTE a(t) содержит строку, представляющую собой одну строку выходного ASCII-арта.

Оператор SELECT в конце просто обращается к CTE «a» для последовательного получения всех строк ASCII-арта.

Выполнение приведённого выше запроса в командной строке SQLite даёт следующий результат:

....# ..#*.. ..+####+. .......+####.... + ..##+*##########+.++++ .+.##################+. .............+###################+.+ ..++..#.....*#####################+. ...+#######++#######################. ....+*################################.
#############################################... ....+*################################. ...+#######++#######################. ..++..#.....*#####################+. .............+###################+.+ .+.##################+. ..##+*##########+.++++ .......+####.... + ..+####+. ..#*.. ....# +.

Следующий запрос решает головоломку судоку. Состояние головоломки задаётся строкой из 81 символа, сформированной путём считывания ячеек поля построчно слева направо и сверху вниз. Пустые клетки обозначаются символом «.». Таким образом, входная строка:

53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79

Соответствует головоломке следующего вида:

5 3 . . 7 . . . .
6 . . 1 9 5 . . .
. 9 8 . . . . 6 .
8 . . . 6 . . . 3
4 . . 8 . 3 . . 1
7 . . . 2 . . . 6
. 6 . . . . 2 8 .
. . . 4 1 9 . . 5
. . . . 8 . . 7 9

Вот запрос, решающий эту головоломку:

WITH RECURSIVE input(sud) AS ( VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79') ), digits(z, lp) AS ( VALUES('1', 1) UNION ALL SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9 ), x(s, ind) AS ( SELECT sud, instr(sud, '.') FROM input UNION ALL SELECT substr(s, 1, ind-1) || z || substr(s, ind+1), instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' ) FROM x, digits AS z WHERE ind>0 AND NOT EXISTS ( SELECT 1 FROM digits AS lp WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1) OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1) OR z.z = substr(s, (((ind-1)/3) % 3) * 3 + ((ind-1)/27) * 27 + lp + ((lp-1) / 3) * 6, 1) ) )
SELECT s FROM x WHERE ind=0;

CTE «input» определяет входную головоломку. CTE «digits» определяет таблицу, содержащую все цифры от 1 до 9. Основная работа по решению головоломки выполняется CTE «x». Запись в x(s,ind) означает, что 81-символьная строка «s» является корректной головоломкой судоку (не содержит конфликтов) и что первый неизвестный символ находится на позиции «ind», либо ind==0, если все позиции заполнены.

Цель, таким образом, состоит в вычислении записей для «x» со значением «ind», равным 0.

Решатель работает путём добавления новых записей в рекурсивную таблицу «x». Для каждой предыдущей записи рекурсивный SELECT пытается заполнить одну новую позицию всеми значениями от 1 до 9, которые действительно допустимы на этой позиции. Сложный подзапрос NOT EXISTS — это то самое «волшебство», которое определяет, является ли строка-кандидат «s» корректной головоломкой судоку или нет.

Окончательный ответ находится путём поиска строки с ind==0. Если исходная головоломка судоку не имеет единственного решения, запрос вернёт все возможные решения. Если исходная задача не имеет решения, строки возвращены не будут. В данном случае единственный ответ таков:

534678912672195348198342567859761423426853791713924856961537284287419635345286179

Решение было вычислено менее чем за 300 миллисекунд на современной рабочей станции.

Подсказки материализации

Формы AS MATERIALIZED и AS NOT MATERIALIZED для CTE представляют собой нестандартный синтаксис SQL, заимствованный из PostgreSQL. Использование MATERIALIZED или NOT MATERIALIZED после ключевого слова AS предоставляет планировщику запросов необязательные подсказки о том, каким образом следует реализовать CTE.

Если используется фраза MATERIALIZED, то select-stmt материализуется во временную таблицу, хранящуюся в памяти или во временном файле на диске. Эта временная таблица затем используется вместо имени таблицы CTE везде, где это имя встречается в последующем SQL.

Поскольку select-stmt вычисляется немедленно, возможность применения оптимизаций, таких как выравнивание запроса (query flattening) или оптимизация проталкивания (push-down optimization), утрачивается. Эта потеря оптимизации является особенностью, а не ошибкой.

Разработчики могут использовать ключевое слово MATERIALIZED в качестве «барьера оптимизации» для более точного управления поведением планировщика запросов SQLite. SQLite позаимствовал идею использования MATERIALIZED в качестве барьера оптимизации из PostgreSQL.

Если используется фраза NOT MATERIALIZED, то select-stmt подставляется как подзапрос на место каждого вхождения имени таблицы CTE. Оптимизации, такие как выравнивание и проталкивание, затем применяются к подзапросу так, как если бы подзапрос использовался напрямую. Вопреки своему названию, фраза NOT MATERIALIZED не запрещает использование материализации.

Планировщик запросов по-прежнему вправе реализовать подзапрос с использованием материализации, если сочтёт это наилучшим решением. Истинный смысл NOT MATERIALIZED ближе к «ОБРАЩАТЬСЯ КАК С ОБЫЧНЫМ ПРЕДСТАВЛЕНИЕМ ИЛИ ПОДЗАПРОСОМ».

Если ни одна из подсказок не указана, SQLite вправе выбрать любую стратегию реализации, которую сочтёт наиболее подходящей. Это рекомендуемый подход. Не используйте ключевые слова MATERIALIZED или NOT MATERIALIZED в CTE без веской причины.

Подсказки MATERIALIZED и NOT MATERIALIZED доступны только в SQLite версии 3.35.0 (2021-03-12) и более поздних.

Типичные ошибки при работе с конструкцией WITH

На практике при работе с CTE в SQLite чаще всего встречаются следующие проблемы:

Бесконечная рекурсия. Если рекурсивный SELECT не имеет условия завершения в WHERE или LIMIT, запрос будет выполняться бесконечно. Рекомендуется всегда добавлять LIMIT как защитную меру, даже если условие WHERE кажется достаточным.

Использование UNION вместо UNION ALL без необходимости. При использовании UNION SQLite вынужден хранить всё ранее сгенерированное содержимое для проверки дубликатов. Это существенно увеличивает потребление памяти. Если дубликаты не являются проблемой, всегда предпочтительнее UNION ALL.

Ссылка на рекурсивный член CTE в подзапросе. Рекурсивная ссылка на CTE должна появляться ровно один раз в предложении FROM каждого оператора SELECT верхнего уровня в recursive-select. Ссылки на неё в подзапросах внутри recursive-select недопустимы.

Использование агрегатных или оконных функций в рекурсивном SELECT. Это прямо запрещено синтаксисом. Агрегирование следует выносить во внешний запрос или в отдельное CTE.

Использование WITH внутри CREATE TRIGGER. Предложение WITH не может использоваться внутри триггеров — это ограничение SQLite.

Неправильный порядок нерекурсивных и рекурсивных операторов. Все нерекурсивные операторы SELECT должны располагаться перед любыми рекурсивными. Нарушение этого порядка приведёт к ошибке синтаксиса.

Ограничения и оговорки

Предложение WITH не может использоваться внутри CREATE TRIGGER.

Предложение WITH должно располагаться в начале оператора SELECT верхнего уровня или в начале подзапроса. Предложение WITH не может предшествовать второму или последующим операторам SELECT в составном запросе.

Спецификация SQL:1999 требует, чтобы ключевое слово RECURSIVE следовало за WITH в любом предложении WITH, содержащем рекурсивное CTE. Однако для совместимости с SQL Server и Oracle SQLite не применяет это правило принудительно.

Часто задаваемые вопросы о CTE в SQLite

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

Обязательно ли писать WITH RECURSIVE, если CTE не является рекурсивным? Нет. Ключевое слово RECURSIVE необязательно для обычных CTE. Однако его наличие не запрещает использование обычных CTE — оно лишь разрешает рекурсию там, где она нужна.

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

Когда стоит использовать подсказку MATERIALIZED? Когда нужно предотвратить встраивание CTE в основной запрос и гарантировать, что select-stmt вычисляется ровно один раз. Это полезно как «барьер оптимизации», когда поведение планировщика запросов по умолчанию нежелательно.

Начиная с какой версии SQLite доступны подсказки MATERIALIZED и NOT MATERIALIZED? Начиная с версии SQLite 3.35.0 (2021-03-12). В более ранних версиях эти ключевые слова не поддерживаются.