Как ClickHouse заставил меня усомниться в собственной адекватности (и при чем тут CTE). Собственно продолжается рабочее погружение в одну из самых популярных OLAP DB. Когда я перестал кипеть и выдохнул решил поделиться пока, так сказать, "свежо преданье".

Я несколько лет работал с разными базами, думал, что общие табличные выражения - это такая удобная штука, чтобы не плодить подзапросы… а потом, не ожидая подвоха, вставил в запрос для ClickHouse CTE - и началось…

Внимание! Спойлер В PostgreSQL ты пишешь `WITH cte AS (SELECT ...)` - и всё, подзапрос выполнился один раз, результат запомнился, живи спокойно. А в ClickHouse… CTE по умолчанию - это не данные, а фрагмент кода. Он подставляет подзапрос в каждое место, где на него есть ссылка, и каждый раз выполняет его заново. Причем независимо друг от друга. И результаты в разных местах одного запроса могут быть разными, потому что запрос выполняется каждый раз заново. Пример из документации: `generateRandom` в CTE, вызванный дважды, вернет разные наборы чисел.

Итак, вернемся к моим злоключениям. Я просидел полдня, глядя на тайминг запроса, который почему-то падал в 4 раза, когда я добавлял второй джойн к тому же CTE. Или когда мой красивый пайплайн с `IN (SELECT FROM cte)` вдруг начинал читать на 100 миллионов строк вместо 8 тысяч. Я думал, я сошел с ума. Проверял планы запросов, крутил настройки, постоянно смотрел на статистику. А все было просто: CTE выполнялся каждый раз заново (как вызов функции в Python), давай новый результат.

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

Однако ClickHouse даёт джедайский трюк: MATERIALIZED CTE. Если написать `WITH cte AS MATERIALIZED (SELECT ...)`, ClickHouse выполнит подзапрос ровно один раз, сохранит результат во временной таблице, и все ссылки будут читать из неё. Это особенно важно для тяжёлых агрегаций, джойнов и когда CTE используется чаще одного раза. Правда, есть нюанс: материализованные CTE — экспериментальная функция. Нужно включить `analyzer` и `enable_materialized_cte`. И их нельзя смешивать с рекурсивными. Но когда это работает — это магия. Сейчас я знаю и усвоил на всю жизнь: в ClickHouse таблица становится активным компонентом пайплайна, а CTE может быть как хрупким макросом, так и эффективным временным хранилищем, если попросить его об этом.