06.04.2026 08:43
select_zvezdo4ka_from 3 5800 Источник

Каждый, кто приходит в 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

Комментарии (3)


  1. Akina
    06.04.2026 10:44
    #29784404

    О минусах такой реализации, думаю, и говорить не стоит.

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

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

    А вот инлайнинг - это весьма спорная стратегия. Особенно если внутри есть нечто недетерминированное. Каждая заинлайненная копия вернёт уникальный набор записей, не совпадающий с таковым, возвращённым другой копией. И неудивительно, что в конечном итоге результат может сильно отличаться не то что от ожидаемого, но и просто от вменяемого. Что вы как раз и наблюдаете.

    Но при этом и инлайнинг имеет право на существование - просто потому, что кратное выполнение CTE может оказаться выгоднее по ресурсам, чем кэширование. Но для корректного использования инлайнинга СУБД должна грамотно исследовать код CTE и убедиться, что он производит гарантированно детерминированный результат, и что при этом план с кратным исполнением более выгоден, чем план с кэшированием. Некоторые СУБД это всё же умеют. Вроде бы...

    Если ClickHouse использует инлайнинг и описывает это в документации, то он (или она? или вообще оно?) просто сваливает такой анализ на программиста. Не прочитал, не проанализировал, не учёл - ну хлебай полной ложкой. А если не описывает - то это несомненный косяк. Который, как вы говорите, уже обнаружили и даже начали исправлять.

    С другой стороны, и программист тоже имеет право поучаствовать... наверное. Хоть язык изначально и декларативный. А потому возможность указать, какую стратегию использовать - ну если и не "должна быть", то во всяком случае невредно. И все шишки за свой счёт.

    PS. К слову, было вовсе необязательно генерить миллион значений и обнаруживать косяк по косвенным признакам. Простой запрос должен по идее сразу и явно продемонстрировать эту проблему просто на выводимом результате выполнения:

    WITH cte_numbers AS (
    SELECT num FROM generateRandom(‘num UInt64’, NULL) LIMIT 2 
    )
SELECT t1.num, t2.num 
FROM cte_numbers t1, cte_numbers t2;


  1. seriych
    06.04.2026 10:44
    #29785650

    Наиболее правильный способ

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

    Если хочется cte, то это можно обойти с помощью костыля:

    with
    (
        select groupArray(num)
        from ( /* тут исходный запрос из cte */
            SELECT
                num
            FROM generateRandom('num UInt64', NULL)
            LIMIT 1000000
        )
    ) as cte_constant,
    cte_numbers as (
        select arrayJoin(cte_constant) as num
    )
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
┌─count()─┐
│ 1000000 │
└─────────┘

    через groupArray() возвращаем одно значение, которое через синтаксис with (...) as материализуется в константу, а потом обратно разворачиваем где нужно через arrayJoin().

    В более общем случае надо в tuple завернуть значения, если их несколько:

    with
    (
        select groupArray((c1, c2, ...))
        from (
            select c1, c2, ...
        )
    ) as cte_constant,
    cte_values as (
        select (arrayJoin(cte_constant) as tpl).1 as c1, tpl.2 as c2...
    )
select ...

    Но у меня работа во многом про оптимизацию, и как правило я сталкиваюсь с тем, что cte из запроса выпиливаю, потому что в разных местах запроса оптимальнее использовать разные подзапросы, чем переиспользовать что-то универсальное.


  1. speshuric
    06.04.2026 10:44
    #29786456

    значит и работают как СТЕ в классических СУБД

    Так они работают везде по-разному с разными особенностями. В SQL Server CTE в целом тоже ближе к "макросам". В PG CTE исходно умели быть только метериализованными (и этим немало крови выпили программистам), сейчас и так и так могут. Про Oracle я не скажу - не сталкивался плотно.
    Но это лишь часть вопроса. Потому что второй вопрос - выполнение функций в выражениях выборки. Да еще и нестандартных функций. Да еще и с вопросами по детерминированности (должна ли недетерминированная функция возвращать одно и то же значение во всё запросе? ). Каждая СУБД тут вправе применять подход, который показался логичным разработчикам (у функций текущего времени похожие вопросики).