Каждый, кто приходит в ClickHouse из мира классических OLTP-баз, несет с собой багаж священных знаний. Один из таких «священных граалей» — Common Table Expressions (CTE).

Казалось бы, что в ClickHouse может пойти не так? Ведь там тоже есть WITH! Любой нормальный человек просто возьмет и начнет использовать, казалось бы, привычный функционал. Но в итоге останется у разбитого корыта.

В этой статье мы разберем главные грабли: почему WITH в ClickHouse — это не оптимизация, а макрос для парсера и выстрел себе в ногу, если этого не знать.

Ну я же не буду читать документацию каждой функции!

Это очевидный и максимально правильный паттерн поведения, ведь если садиться и читать документацию по каждой функции, прежде чем писать код — то можно никогда и не начать писать код. Да и к тому же — мы ведь говорим про базы данных и SQL. Так зачем садиться и заново учить SQL при переходе с одной СУБД на другую? Логичные умозаключения.

Поэтому каждый из нас просто возьмет и будет использовать СТЕ, подразумевая, что раз они в ClickHouse называются СТЕ, значит и работают как СТЕ в классических СУБД (ведь именно они являются родителями СТЕ).

Давайте приведем пример, на котором станет понятно, что из себя представляют СТЕ в ClickHouse:

Оставляю запрос текстом для тех, кто хочет поэкспериментировать, обернуть его в explain (там четко видны две операции чтения, хотя казалось бы) и т.д.

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

А мы же дадим ответ на вопрос — почему результат выполнения запроса равен 607 546 (более того, он каждый раз разный)? Ведь мы запрашивали 1 000 000 записей в СТЕ, а значит и результат выполнения кода всегда должен быть равен 1 000 000!

Ответ прост: СТЕ в клике — макрос, который подставляется и исполняется в запросе столько раз, сколько раз вы упомянули СТЕ. В итоге на сервере в действительности выполнится вот такой запрос:

select
	count()
from
	(
	select
		num
	from
		generateRandom('num UInt64', null)
	limit 1000000
)
where
	num in (
	select
		num
	from
		generateRandom('num UInt64', null)
	limit 1000000)

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

Что делать?

Наиболее правильный способ: create temporary table — создание таблицы в оперативной памяти.

В таком случае видим ожидаемый 1 000 000. Запрос текстом.

CREATE TEMPORARY TABLE cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
;

SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
;

Также стоит сказать, что в новых версиях дорабатывают эту "особенность", пытаясь добавить материализацию у СТЕ (на момент написания статьи функционал в стадии experimental). Подробнее можно почитать в документации — https://clickhouse.com/docs/sql-reference/statements/select/with

Итого:

Как вы уже поняли, ClickHouse — штука мощная, но со своим характером. Подводных камней здесь хватает: даже банальные CTE живут по своим правилам, а уж если копнуть глубже...

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

Выбирайте путь умного. Вот он:
https://stepik.org/course/277938/promo