记录ClickHouse的一次采坑经历

最近在做Metabase和ClickHouse集成，使用社区开发的数据驱动把ClickHouse集成到了Metabase。大部分查询正常，只有时间维度的聚合查询会报以下异常：

DB::Exception: Column fdate is not under aggregate function and not in GROUP BY.

详细使用场景如下：

一、ClickHouse的版本

Connected to ClickHouse server version 20.1.4 revision 54431.

二、查询表结构

2.1 本地表创建sql

create table if not exists dw_live.dw_player_dim_hour_local on cluster test_cluster_two_replicas (
fdate Datetime,
num Int64 default 0
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{cluster}-{shard}/dw_live-dw_player_dim_hour_local', '{replica}')
PARTITION BY (fdate)
PRIMARY KEY (fdate)
ORDER BY (fdate)
TTL fdate + toIntervalDay(120)
SETTINGS index_granularity = 8192, storage_policy = 'round', merge_with_ttl_timeout = 3600

2.2 分布式表创建sql

create table if not exists dw_live.dw_player_dim_hour(
fdate Datetime,
num Int64 default 0
)
ENGINE = Distributed(test_cluster_two_replicas, dw_live, dw_player_dim_hour_local, rand())

三、发生异常的使用场景

在Metabase页面点击dwlive.dwplayerdimhour表的时间时间维度聚合查询，监控后台异常日志，页面向ClickHouse请求的查询SQL是(查询的是分布式表)：

SELECT
    toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour`.`fdate`)) AS `fdate`,
    count() AS `count`
FROM `dw_live`.`dw_player_dim_hour`
GROUP BY toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour`.`fdate`))
ORDER BY toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour`.`fdate`)) ASC

将该SQL粘贴到ClickHouse的命令行窗口执行，同样报以下异常：

DB::Exception: Column fdate is not under aggregate function and not in GROUP BY.

同样的查询语句，查询本地表返回结果正常。查询本地表的SQL是：

SELECT
    toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour_local`.`fdate`)) AS `fdate`,
    count() AS `count`
FROM `dw_live`.`dw_player_dim_hour`
GROUP BY toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour_local`.`fdate`))
ORDER BY toStartOfHour(toDateTime(`dw_live`.`dw_player_dim_hour_local`.`fdate`)) ASC

四、结论和解决方法

到此判定是ClickHouse的Bug，升级到20.3.18，没有再复现该查询异常。