ClickHouse中如何在子查询内使用WITH语句定义的变量

问题原因 #

ClickHouse 20.11版本默认限制了WITH子句的作用域：仅当前所在的SELECT层级可以访问WITH定义的变量，嵌套子查询无法直接继承上层的WITH变量，因此会触发Missing columns: 'test_val'报错。

解决方案 #

方案1：开启全局WITH作用域参数（推荐） #

执行查询前先开启enable_global_with_statement配置项，开启后顶层WITH定义的变量可以在所有嵌套子查询中直接调用，不需要修改原有业务逻辑：

-- 先执行参数配置，会话级别生效
SET enable_global_with_statement = 1;

WITH
    (SELECT 1) AS test_val
SELECT *
FROM
(
    SELECT
        test_val,
        count()
    FROM system.tables
)

方案2：下沉WITH定义到子查询内部 #

如果不方便修改全局/会话参数，可以直接把WITH变量的定义移动到需要使用它的子查询内部：

SELECT *
FROM
(
    WITH
        (SELECT 1) AS test_val
    SELECT
        test_val,
        count()
    FROM system.tables
)

方案3：通过变量引用符传递上层WITH变量 #

也可以在子查询中用$符号直接引用上层定义的WITH变量，不需要调整参数或移动变量定义：

WITH
    (SELECT 1) AS test_val
SELECT *
FROM
(
    SELECT
        $test_val AS test_val,
        count()
    FROM system.tables
)

内容的提问来源于stack exchange，提问作者Andrei Koch