1. Обзор

with-clause: hide

Common Table Expressions или CTE действуют как временные обзоры, которые существуют только на время единственного SQL-оператора. Есть два вида общих выражений: "обычный" и "рекурсивный". Обычные общие выражения полезны для того, чтобы сделать проще в понимании запросы, учитывая подзапросы из главного SQL-оператора. Рекурсивные общие выражения обеспечивают способность сделать иерархические или рекурсивные запросы деревьев и графов, способность, которая в других отношениях недоступна на языке SQL.

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

2. Обычные общие выражения таблицы

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

WITH может содержать обычные общие выражения таблицы, даже если он включает ключевое слово RECURSIVE. Использование RECURSIVE не вынуждает общие выражения таблицы быть рекурсивными.

3. Рекурсивные общие выражения таблицы

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

1. "select-stmt" должен быть составной select. То есть тело CTE должно быть двумя или больше отдельными операторами SELECT, отделенными составными операторами вроде UNION, UNION ALL, INTERSECT или EXCEPT.
2. Один или больше отдельных операторов SELECT, которые составляют комплекс, должен быть "рекурсивным". Оператор SELECT рекурсивный, если его пункт FROM содержит точно одну ссылку на таблицу CTE (таблицу, названную на левой стороне пункта AS).
3. Один или больше операторов SELECT в комплексе должно быть нерекурсивным.
4. Все нерекурсивные операторы SELECT должны произойти перед любыми рекурсивными операторами SELECT.
5. Рекурсивные операторы SELECT должны быть отделены от нерекурсивных операторов SELECT и друг от друга UNION или UNION ALL. Если есть два или больше рекурсивных оператора SELECT, они все должны быть отделены друг от друга с применением того же самого оператора, который отделяет первый рекурсивный SELECT от последнего нерекурсивного оператора SELECT.
6. Рекурсивные операторы SELECT не могут использовать агрегатные функции или функции окна.

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

recursive-cte: hide

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

Назовем таблицу cte-table-name в рекурсивном общем выражении таблицы "рекурсивная таблица". На диаграмме пузыря recursive-cte выше рекурсивная таблица должна появиться точно однажды в пункте FROM каждого оператора SELECT верхнего уровня в recursive-select и не должна появляться больше нигде в initial-select или recursive-select, включая подзапросы. initial-select может быть составным select, но он не может включать ORDER BY, LIMIT или OFFSET. recursive-select может также быть составным select с ограничением, что все элементы того комплекса должны быть отделены тем же самым UNION или UNION ALL, который отделяет initial-select от recursive-select. recursive-select позволяют включать ORDER BY, LIMIT и/или OFFSET, но он не может использовать агрегатные функции или функции окна.

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

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

1. Выполните initial-select и добавьте результаты к очереди
2. В то время как очередь не пуста:
   1. Извлеките единственную строку из очереди
   2. Вставьте единственную строку в рекурсивную таблицу
   3. Притворитесь, что единственная извлеченная строка является единственной строкой в в рекурсивной таблице и управляйте recursive-select, добавляя все результаты к очереди.

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

• Если UNION соединяет initial-select с recursive-select, то только добавляют строки к очереди, если никакая идентичная строка не была ранее добавлена к очереди. От повторных строк отказываются прежде, чем они добавлены к очереди, даже если повторные строки были уже извлечены из очереди шагом рекурсии. Если оператор UNION ALL, то все строки, произведенные initial-select и recursive-select, всегда добавляются к очереди, даже если они повторения. Определяя, повторяется ли строка, значения NULL равны друг другу и не равны любому другому значению.

• Пункт LIMIT, если есть определяет максимальное количество строк, которые будут когда-либо добавляться к рекурсивной таблице в шаге 2b. Как только предел достигнут, рекурсия прекратится. Предел 0 значит, что никакие строки никогда не добавляются к рекурсивной таблице, отрицательный предел означает, что неограниченное количество строк может быть добавлено к рекурсивной таблице.

• OFFSET, если есть и имеет положительное значение N, блокирует добавление первых N строк в рекурсивную таблицу. Они все еще обрабатываются recursive-select, но не добавляются к рекурсивной таблице. Строки не посчитаны LIMIT, пока все строки OFFSET не были пропущены.

• Если есть ORDER BY, он определяет порядок, в котором строки извлечены из очереди в шаге 2a. Если нет никакого пункта ORDER BY, то порядок, в котором извлечены строки, не определен. В текущем внедрении очередь становится FIFO, если пункт ORDER BY опущен, но приложения не должны зависеть от этого факта, так как это могло бы измениться.

3.1. Примеры рекурсивного запроса

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

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

Рассмотрите, как этот запрос работает. initial-select работает сначала и вернет единственную строку с отдельным столбцом "1". Эта строка добавляется к очереди. В шаге 2a одна строка извлечена из очереди и добавлена к "cnt". Тогда recursive-select работает в соответствии с шагом 2c, производящим единственную новую строку со значением "2", чтобы добавить к очереди. Очередь все еще имеет одну строку, таким образом, шаг 2 повторяется. Строка "2" извлечена и добавлена к рекурсивной таблице шагами 2a и 2b. Тогда строка, содержащая 2, используется, как будто это было полное содержание рекурсивной таблицы, и снова выполняется recursive-select, заканчиваясь со значением "3", добавляемым к очереди. Это повторяется 999999 раз, пока наконец на шаге 2a единственное значение очереди это строка, содержащая 1000000. Та строка извлечена и добавлена к рекурсивной таблице. Но на этот раз оператор Where заставляет recursive-select не возвращать строки, таким образом, очередь остается пустой, а рекурсия останавливается.

Оптимизация: В обсуждении выше запросы вроде "вставка строки в рекурсивную таблицу" должны быть поняты концептуально, не буквально. Кажется будто SQLite накапливает огромную таблицу, содержащую один миллион строк, затем возвращаясь и просматривая таблицу сверху донизу, чтобы произвести результат. То, что действительно происходит, это то, что оптимизатор запросов видит, что значения в рекурсивной таблице "cnt" используются только однажды. Таким образом, поскольку каждая строка добавляется к рекурсивной таблице, эта строка немедленно возвращена в результате главного оператора SELECT, а затем пропущена. SQLite не накапливает временную таблицу, содержащую миллион строк. Очень мало памяти необходимо, чтобы управлять вышеупомянутым примером. накапливает временную таблицу, содержащую миллион строк. Очень мало памяти необходимо, чтобы управлять вышеупомянутым примером. Однако, если бы пример использовал UNION вместо UNION ALL, то SQLite должен был бы иметь в наличии все ранее произведенное содержание, чтобы проверить на дубликаты. Поэтому программисты должны стремиться использовать UNION ALL вместо UNION, когда это выполнимо.

Вот изменение на предыдущем примере:

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

Есть два различия в этом изменении. initial-select это "SELECT 1" вместо "VALUES(1)". Но это просто различные синтаксисы для того, чтобы сказать точно то же самое. Другое изменение в том, что рекурсия остановлена LIMIT, а не оператором Where. Использование LIMIT означает, что, когда миллионная строка добавлена к таблице "cnt" (и возвращена главным SELECT благодаря оптимизатору запросов), рекурсия немедленно останавливается независимо от того, сколько строк можно было бы оставить в очереди. На более сложных запросах может иногда быть трудно гарантировать, что оператор Where в конечном счете заставит очередь закончиться. Но пункт LIMIT будет всегда останавливать рекурсию. Таким образом, хорошая практика, чтобы всегда включать пункт LIMIT как безопасность, если верхняя граница размера рекурсии известна.

3.2. Примеры иерархических запросов

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

CREATE TABLE org(name TEXT PRIMARY KEY, boss TEXT REFERENCES org,
                 height INT, -- other content omitted
);

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

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

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. Следующая таблица делает запись родословной:

CREATE TABLE family(name TEXT PRIMARY KEY, mom TEXT REFERENCES family,
                    dad TEXT REFERENCES family, born DATETIME,
                    died DATETIME -- NULL if still alive
                                  -- other content
);

Таблица "family" подобна "org" за исключением того, что теперь есть два родителя каждому участнику. Мы хотим знать всех живущих предков Элис от самого старого до самого молодого. Обычное общее выражение таблицы "parent_of" определяется сначала. Это обычное CTE это представление, которое может использоваться, чтобы найти всех родителей любого человека. Тот обычный CTE затем используется в рекурсивном CTE "ancestor_of_alice". Рекурсивный CTE затем используется в заключительном запросе:

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;

3.3. Запросы графа

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

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;

initial-select в этом случае является простой запрос "SELECT 59". Это устанавливает основной случай. recursive-select состоит из других двух операторов SELECT. Первый рекурсивный SELECT следует за краями в bb-to-aa направлении, и второй рекурсивный SELECT следует за краями в aa-to-bb направлении. UNION используется вместо UNION ALL, чтобы препятствовать тому, чтобы рекурсия вошла в бесконечный цикл, если граф содержит циклы.

Вот реальный пример использования запроса графа для направленного графа: система управления версиями (VCS) будет, как правило, хранить развивающиеся версии проекта как направленный граф без петель (DAG). Назовите каждую версию проекта "checkin". У единственного checkin могут быть ноль или больше родителей. У большинства checkin (кроме первого) есть родитель-одиночка, но в случае слияния, у регистрации могло бы быть два, три или больше родителей. Схема, чтобы отслеживать checkin и порядок, в котором они происходят, могла бы выглядеть примерно так:

CREATE TABLE checkin(id INTEGER PRIMARY KEY,
                     mtime INTEGER -- timestamp when this checkin occurred
);
CREATE TABLE derivedfrom(
  xfrom INTEGER NOT NULL REFERENCES checkin, -- parent checkin
  xto INTEGER NOT NULL REFERENCES checkin,   -- derived checkin
  PRIMARY KEY(xfrom,xto));
CREATE INDEX derivedfrom_back ON derivedfrom(xto,xfrom);

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

Мы хотим знать двадцать новых предков вовремя (из тысяч и тысяч предков в целом DAG) для регистрации "@BASELINE". Запрос, подобный этому, используется в Fossil VCS, чтобы показать новых предков регистрации N. Например: https://www.sqlite.org/src/timeline?p=trunk&n=30.)

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" в recursive-select делает запрос управляемым намного быстрее, препятствуя тому, чтобы он следовал за отделениями, которые сливают checkin по возрасту. ORDER BY вынуждает recursive-select сосредоточиться на новом checkin. Без ORDER BY recursive-select был бы вынужден вычислить полный набор из тысяч предков, сортировать их всех mtime, затем взяло бы лучшие двадцать. ORDER BY по существу создает приоритетную очередь, которая вынуждает рекурсивный запрос посмотреть на новых предков сначала, позволяя использованию пункта LIMIT ограничить объем запроса.

3.4. Управление глубиной сначала против поиска в ширину дерева, используя ORDER BY

ORDER BY на recursive-select может использоваться, чтобы управлять, является ли поиск дерева сначала в глубину или в ширину. Чтобы иллюстрировать, мы будем использовать изменение на таблице "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", который заставит более низкие уровни (с большими значениями "level") быть обработанными сначала recursive-select, приводя к поиску в глубину:

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 опущен в recursive-select, очередь ведет себя как FIFO, который приводит к поиску в ширину.

3.5. Диковинные примеры рекурсивного запроса

Следующий запрос вычисляет приближение 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;

В этом запросе "xaxis" м "yaxis" CTE определяют сетку пунктов, для которых будет приближено Mandelbrot Set. Каждая строка в "m(iter,cx,cy,x,y)" CTE означает, что после повторений "iter", повторение Мандельброта, начинающееся в cx,cy достигло пункта x, y. Количество повторений в этом примере ограничивается 28 (что сильно ограничивает разрешение вычисления, но достаточно для вывода ASCII-творчества с низкой разрешающей способностью). "m2(iter,cx,cy)" CTE считает максимальное количество повторений достигнутым, начинаясь в пункте cx,cy. Наконец, каждая строка в "a(t)" CTE держит последовательность, которая является единственной линией вывода ASCII-творчества. Оператор SELECT в конце просто запрашивает "a" CTE, чтобы получить все линии 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;

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

Решающее устройство работает, добавляя новые записи в рекурсивную таблицу "x". Учитывая предшествующие записи, recursive-select пытается заполнить единственное новое положение со всеми значениями между 1 и 9, которые на самом деле работают в том положении. Сложный подзапрос "NOT EXISTS" выясняет, является ли каждый кандидат "s" действительной судоку или нет.

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

534678912672195348198342567859761423426853791713924856961537284287419635345286179

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

4. Намеки материализации

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

Если использовано MATERIALIZED, select-stmt будет осуществлен в эфемерной таблице, которая проводится в памяти или во временном дисковом файле. Эфемерная таблица будет использоваться вместо имени таблицы CTE каждый раз, когда имя таблицы CTE появляется в последующем SQL. Поскольку select-stmt немедленно оценено, возможность применить оптимизацию, такую как query flattening или push-down optimization, отсутствует. Эта потеря оптимизации особенность, не ошибка. Разработчики в состоянии использовать ключевое слово MATERIALIZED, как "optimization fence", чтобы более строго контролировать поведение планировщика запросов SQLite. SQLite скопировал идею использовать MATERIALIZED в качестве забора оптимизации от PostgreSQL.

Если использовано NOT MATERIALIZED, select-stmt заменен как подзапрос вместо каждого возникновения имени таблицы CTE. Оптимизация, такая как flattening и push-down, применяется к подзапросу, как будто подзапрос был использован непосредственно. Несмотря на имя, фраза NOT MATERIALIZED не запрещает использование материализации. Планировщик запросов все еще свободен осуществить подзапрос, используя материализацию, если это чувствует, что это лучшее решение. Истинное значение NOT MATERIALIZED ближе к "TREAT LIKE ANY ORDINARY VIEW OR SUBQUERY".

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

MATERIALIZED и NOT MATERIALIZED доступны в SQLite version 3.35.0 (2021-03-12) и позже.

5. Ограничения

• WITH не может использоваться в CREATE TRIGGER.

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

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