Clickhouse splitByString报错

Question

执行的查询语句：

SELECT
    COUNT( DISTINCT table_22.customer_id ) 
FROM
    buffet.`dws_cha_second_chance_sd_ck_1604262339` AS table_22
    LEFT JOIN buffet.`dws_cha_chance_sd_ck_1604269193` AS table_23 ON table_22.customer_id = table_23.customer_id 
WHERE
    (hasAny ( splitByString (',',COALESCE ( toString(table_23.project), 'null' ) ), splitByString (',','100001,9,16,17,18,100175,100177,100905,100931,100943,100945,101141' ) ) = 1) 
    AND
    (hasAny ( splitByString (',',COALESCE ( toString(table_22.category_child_id), 'null' ) ), splitByString (',','0,1' ) ) = 1);

报错信息：

Code: 44, e.displayText() = DB::Exception: Illegal column String of first argument of function splitByString. Must be constant string. (version 20.7.2.30 (official build))

两个条件单独执行是没问题的，，，，但是组合到一起执行就会报这个错误。

Answer 1

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

这个报错是因为splitByString()函数的第一个参数必须是常量字符串，而不是字段或表达式。在你的查询中，你使用了COALESCE()和toString()函数来处理table_23.project和table_22.category_child_id字段，导致splitByString()函数无法识别这些字段。 为了解决这个问题，我们可以先将这两个字段处理成常量字符串，然后再传递给splitByString()函数。下面是一个修改后的查询语句：

WITH temp_table AS (
    SELECT 
        table_22.customer_id,
        COALESCE(toString(table_23.project), 'null') AS project,
        COALESCE(toString(table_22.category_child_id), 'null') AS category_child_id
    FROM buffet.`dws_cha_second_chance_sd_ck_1604262339` AS table_22
    LEFT JOIN buffet.`dws_cha_chance_sd_ck_1604269193` AS table_23 ON table_22.customer_id = table_23.customer_id
)
SELECT COUNT(DISTINCT customer_id) 
FROM temp_table
WHERE 
    hasAny(splitByString(',', project), splitByString(',', '100001,9,16,17,18,100175,100177,100905,100931,100943,100945,101141')) = 1
    AND 
    hasAny(splitByString(',', category_child_id), splitByString(',', '0,1')) = 1;

通过将字段处理成常量字符串并存储在一个临时表中，然后在查询中使用这些处理后的字段，可以避免报错并正确执行查询。