ClickHouse分组查询结果存在但精准查询无数据的问题咨询

环境信息 #

• ClickHouse版本：ClickHouse server version 21.3.11 revision 54447
• 表结构设置：

ENGINE = MergeTree
PARTITION BY datadate
PRIMARY KEY datatime
ORDER BY datatime
SETTINGS index_granularity = 8192

问题重现 #

分组查询及结果 #

执行分组查询语句：

SELECT
    datadate,
    datatime,
    hostname,
    count(*)
FROM data_1xxxxxx_0_xx_all
WHERE (datadate = '2024-01-02') AND ((datatime >= 1704195210) AND (datatime <= 1704195218)) AND (hostname = 'NSAE_01')
GROUP BY
    datadate,
    datatime,
    hostname

返回结果显示存在对应数据：

┌───datadate─┬───datatime─┬─hostname─┬─count()─┐
│ 2024-01-02 │ 1704195218 │ NSAE_01  │      52 │
└────────────┴────────────┴──────────┴─────────┘

精准查询及结果 #

执行精准匹配查询：

SELECT *
FROM data_1xxxxxx_0_xx_all
WHERE (datadate = '2024-01-02') AND (datatime = 1704195218) AND (hostname = 'NSAE_01')

返回结果为0行：

Ok.
0 rows in set. Elapsed: 0.044 sec. Processed 335.87 thousand rows, 8.73 MB (7.67 million rows/s., 199.38 MB/s.)

补充验证查询 #

查询该datatime下所有hostname：

SELECT hostname FROM data_1xxxxxx_0_xx_all WHERE (datadate = '2024-01-02') AND (datatime = 1704195218) group by hostname

结果中无NSAE_01：

┌─hostname────────────────────┐
│ xxxx.xx.com                 │
│ xxxip-15.x.x.5.com          │
│ xxxip_16.x.x.2_60.xx.n5.com │
└─────────────────────────────┘

诉求 #

解决分组查询显示有数据，但精准查询无匹配结果的矛盾问题，确保分组结果对应实际存在的数据。

排查与解决方法 #

1. 检查字段类型与精度匹配 #

• 先确认datatime字段类型：执行SELECT toTypeName(datatime) FROM data_1xxxxxx_0_xx_all LIMIT 1;
  • 如果是DateTime类型，直接用数值比较可能存在隐式转换误差，建议改用toDateTime(1704195218)进行匹配
  • 如果是数值类型，检查是否存在毫秒级精度被截断的情况，比如存储的是带小数的时间戳，但查询用了整数
• 验证hostname的实际存储值：执行SELECT DISTINCT hostname FROM data_1xxxxxx_0_xx_all WHERE hostname LIKE '%NSAE%';，确认是否存在大小写、空格或字符编码差异

2. 修复MergeTree分区与索引异常 #

21.3版本属于较旧的ClickHouse版本，可能存在分区合并或索引损坏问题：

• 强制合并目标分区并重建索引：OPTIMIZE TABLE data_1xxxxxx_0_xx_all PARTITION '2024-01-02' FINAL;
• 检查表完整性：CHECK TABLE data_1xxxxxx_0_xx_all;，若提示损坏则需修复或重新导入数据

3. 清除查询缓存 #

ClickHouse可能缓存了旧的分组查询结果，执行SYSTEM DROP CACHE;清除缓存后，重新执行分组查询验证结果

4. 排查数据变更时序 #

确认在分组查询和精准查询之间，是否有数据被删除、更新或分区合并操作，导致数据状态发生变化

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