ClickHouse не тормозит, но заставляет глаз дергаться. CTE

[Akina]

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

Собственно 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;

[seriych]

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

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

Если хочется 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 из запроса выпиливаю, потому что в разных местах запроса оптимальнее использовать разные подзапросы, чем переиспользовать что-то универсальное.