clickhouse 日期函数无效报错问题处理

clickhouse建表语句：

CREATE TABLE test.user_action_log
(
    `event_time` DateTime,
    `action` String,
    `user_id` String,
    `school_id` String,
    `uuid` String,
    `app_id` String,
    `student_id` String,
    `date_key` String
)
ENGINE = MergeTree()
PARTITION BY date_key
PRIMARY KEY user_id
ORDER BY (user_id, student_id, school_id, app_id)
SETTINGS index_granularity = 8192;

问题：

现象：

当我们使用指定日期查询时发现可以查询出结果：

localhost :) select count(1) from test.user_action_log where date_key = '2020-12-04';

SELECT count(1)
FROM test.user_action_log
WHERE date_key = '2020-12-04'

Query id: cab1314f-25e2-4328-b865-17ede5f0c2fc

┌──count(1)─┐
│ 105338324 │
└───────────┘

1 rows in set. Elapsed: 0.002 sec.

但是我们发现在使用日期加减函数时却发生报错：

localhost :) select count(1) from test.user_action_log where date_key = addsDays(today(),6);

SELECT count(1)
FROM test.user_action_log
WHERE date_key = addsDays(today(), -6)

Query id: 8d757fd0-6cbc-4b95-8b32-f87f78850db6

Received exception from server (version 20.11.3):
Code: 46. DB::Exception: Received from 10.12.80.130:9000. DB::Exception: Unknown function addsDays. Maybe you meant: ['addDays','addYears']: While processing SELECT count(1) FROM test.user_action_log WHERE date_key = addsDays(today(), -6).

0 rows in set. Elapsed: 0.006 sec.

解决思路：

经过排查我们发现建表时字段使用了String类型，而addsDays(today(), 6)生成的数据类型是Date类型。这样我们就有了两种解决的思路。
一种是将WHERE条件中的日期字段强转为Date:

localhost :)  select count(1) from test.user_action_log where cast(date_key AS date) = addDays(today(), -6);

SELECT count(1)
FROM test.user_action_log
WHERE CAST(date_key, 'date') = addDays(today(), -6)

Query id: d0fb99a6-023f-403a-832b-629ce6011b53

┌──count(1)─┐
│ 105338324 │
└───────────┘

1 rows in set. Elapsed: 0.003 sec.

另一种是将WHERE条件中的日期字段强转为Date:

localhost :)  select count(1) from test.user_action_log where date_key = cast(addDays(today(), -6) AS String);

SELECT count(1)
FROM test.user_action_log
WHERE date_key = CAST(addDays(today(), -6), 'String')

Query id: 20cf4d53-07b9-47cd-9b66-51356f6d625a

┌──count(1)─┐
│ 105338324 │
└───────────┘

1 rows in set. Elapsed: 0.003 sec.

两种方式相比差别就是在where条件中在操作符前边添加运算会导致索引失效，也就是WHERE CAST(date_key, 'date') = addDays(today(), -6)会导致date_key索引失效，也就是该任务在执行时不在走索引。

总结：

我们发现引起这种问题的根本原因是建表时日期字段使用错误，使用为String类型而不是Date类型导致，这就要求我们在建表一定要慎重使用字段类型，尤其是日期类型。