CTE (Common Table Expression) / Django CTE / Комментарии / Хабр

CTE (Common Table Expression) они же обобщенные табличные выражения - это временный результат выполнения SQL-выражения, который можно использовать в другом SQL-выражении.

Вы серьёзно? CTE - это набор записей?

CTE- это особая форма написания именованного подзапроса. Которая позволяет использовать результат выполнения этого подзапроса как статическую таблицу. Такой подзапрос по сравнению с обычным имеет как ограничения (например, он не может быть коррелированным), так и расширения (например, он может быть рекурсивным).

То есть CTE - это в первую голову часть SQL-кода, а вовсе даже никакой не результат.

CTE хранится в оперативной памяти сервера баз данных.

Вы явно указали в тегах PostgreSQL. А коли так - то забыли, что результат выполнения CTE может и материализоваться. В том числе и на диск.

Вообще существует две стратегии выполнения нерекурсивного CTE. Это в терминах PostgreSQL материализация (кэширование результата - результат однократного выполнения переиспользуется столько раз, сколько требуется) и встраивание (код подзапроса подставляется вместо его имени в источник данных внешнего запроса или использующего подзапроса/CTE - и в результате код CTE выполняется несколько раз). По счастью, PostgreSQL следит за тем, чтобы результат подзапроса в CTE был детерминированным, и если это не так, то безусловно применяет материализацию. Но не все СУБД так поступают, и уж тут возможно всякое (пример в MariaDB).

Рекурсивные CTE состоят из двух частей: анкерной части и рекурсивной части.

Anchor - это в первую очередь не "анкер", а "якорь". И именно такие термины (якорь и рекурсивная часть) обычно и используют.

А PostgreSQL вообще с такими терминами не связывается, используя "non-recursive term" и "recursive term".

Например, в PostgreSQL можно настроить параметр max_recursion для увеличения глубины рекурсии.

Где вы это прочитали? PostgreSQL ничего не знает о таком параметре, поиск по указанному термину приводит к "Your search for max_recursion returned no hits". ИИ выдумал, не иначе - не верьте ему.

Есть правильно написанный WHERE, есть CYCLE, есть LIMIT - всё, иных способов ограничения глубины рекурсии нет. Причём LIMIT надо применять крайне осторожно, там есть свои ограничения и подводные камни.

MySQL: Не поддерживает материализацию CTE.

Неправда. MySQL не поддерживает управление материализацией. Хотя написать запрос такой, чтобы заставить его встраивать CTE - это надо постараться. А вот MariaDB поступает с точностью до наоборот, и дефолтно инлайнит текст CTE (см. ссылку на пример выше).

-----

Кстати, ещё одна особенность PostgreSQL. В нерекурсивных CTE он допускает использование запросов, изменяющих данные (INSERT, UPDATE, DELETE).

PyLounge 14 янв в 04:39

Вы явно указали в тегах PostgreSQL. А коли так - то забыли, что результат выполнения CTE может и материализоваться. В том числе и на диск.

Дальше по тексту же это есть

Akina 14 янв в 04:55

Да, есть. Но в статье-то написано "хранится в оперативной памяти сервера баз данных" - однозначно и безальтернативно. Ни "как правило", ни "в большинстве случаев", ни даже "если иное не указано явно"... Если читатель не имел дела плотно с CTE и/или PostgreSQL, он ведь может и не связать эту фразу и то, что далее по тексту.

PyLounge 14 янв в 05:01

Справедливо

PyLounge 14 янв в 05:45

поправил

PyLounge 14 янв в 04:54

Неправда. MySQL не поддерживает управление материализацией.

Косяк, базара зеро

PyLounge 14 янв в 05:45

поправил

PyLounge 14 янв в 04:59

это набор записей?

Возможно, неоднозначность трактовки

(CTE) is a named temporary result set that exists within the scope (c) https://dev.mysql.com/doc/refman/8.4/en/with.html

PyLounge 14 янв в 05:45

поправил

Akina 14 янв в 07:37

Возможно, неоднозначность трактовки

Скорее, неаккуратность автора мануала в формулировании определения.

Запрос, подзапрос, CTE и т.п. - всё это SQL-код. А вот возвращаемые этим кодом в процессе его выполнения данные, финальные или промежуточные - это уже наборы записей (для промежуточных - возможно, наборы ссылок на записи).