ClickHouse报错：仅支持常量和CTE解析父作用域标识符‘locality’

错误详情 #

Resolve identifier 'locality' from parent scope only supported for constants and CTE. Actual default.peoples.locality node type COLUMN.

原查询语句 #

SELECT (
    SELECT arrayDistinct(groupArray(zipcode))
    FROM mapping
    WHERE has(city_list, lower(locality))
) AS zip_list
FROM peoples
LIMIT 10

当前设置 #

SET allow_experimental_analyzer = 1

问题说明 #

该错误属于新分析器的限制，类似已知问题场景。尝试改用常规关联查询时，has函数无法直接适配JOIN的IN条件，因此需要替代方案。

方案1：使用LATERAL JOIN（推荐） #

ClickHouse 21.10及以上版本支持LATERAL JOIN，可实现子查询与外部表的关联，同时保留has函数的逻辑：

SELECT zip_list
FROM peoples
LATERAL JOIN (
    SELECT arrayDistinct(groupArray(zipcode)) AS zip_list
    FROM mapping
    WHERE has(city_list, lower(peoples.locality))
)
LIMIT 10

方案2：数组映射+过滤（兼容低版本） #

若你的ClickHouse版本不支持LATERAL JOIN，可通过数组聚合后匹配的方式实现：

SELECT 
    arrayDistinct(
        arrayMap(
            pair -> pair.2,
            arrayFilter(
                pair -> has(pair.1, lower(locality)),
                (SELECT groupArray((city_list, zipcode)) FROM mapping)
            )
        )
    ) AS zip_list
FROM peoples
LIMIT 10

注意：此方案在mapping表数据量较大时性能会受影响，仅作为临时兼容方案。

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