ClickHouse带WHERE子句的LEFT JOIN不符合预期问题求助

1. 核心原因分析 #

原查询中LEFT JOIN后通过WHERE ssp_reporting__account=21过滤左表字段时，右表字段返回NULL，大概率是以下两种情况：

• 左表account_id=21的行在右表中无匹配数据：显式转换后的toInt64(s_s_p_accounts.id)与toInt64(s_s_p_reporting_stats.account_id)实际不相等；
• ClickHouse优化器谓词下推导致JOIN逻辑异常：优化器将左表过滤条件下推到JOIN之前，若右表数据因分片、索引等问题未被匹配，会出现NULL结果。

2. 分步排查验证 #

步骤1：确认左表目标数据的类型与值 #

执行以下查询，查看左表account_id的实际类型和具体值：

SELECT 
  account_id, 
  toTypeName(account_id) AS account_id_type
FROM adplatform_dev.ssp_dist
WHERE time >= parseDateTimeBestEffort('2025-01-01T00:00:00.000Z') 
  AND time <= parseDateTimeBestEffort('2025-01-13T23:59:59.000Z') 
  AND account_id = 21
LIMIT 50

如果account_id是字符串类型，需确认值是否为纯"21"（无空格、特殊字符）。

步骤2：验证右表是否存在匹配数据 #

根据左表account_id的类型，执行对应查询：

• 若左表account_id是数字类型：

SELECT id, name, toTypeName(id) AS id_type
FROM adplatform_dev_ssp_facts.accounts
WHERE id = 21

• 若左表account_id是字符串类型：

SELECT id, name, toTypeName(id) AS id_type
FROM adplatform_dev_ssp_facts.accounts
WHERE toInt64(id) = 21

若此查询无结果，说明右表确实没有匹配数据，原查询返回NULL是正常逻辑；若有结果，继续下一步排查。

步骤3：检查优化器对JOIN的影响 #

禁用谓词下推和指定JOIN算法，执行原查询对比结果：

SELECT
  `s_s_p_reporting_stats`.account_id `ssp_reporting__account`,
  `s_s_p_accounts`.id,
  `s_s_p_accounts`.name
FROM adplatform_dev.ssp_dist AS `s_s_p_reporting_stats`
LEFT JOIN adplatform_dev_ssp_facts.accounts AS `s_s_p_accounts` 
ON toInt64(`s_s_p_accounts`.id) = toInt64(`s_s_p_reporting_stats`.account_id)
WHERE `s_s_p_reporting_stats`.time >= parseDateTimeBestEffort('2025-01-01T00:00:00.000Z') 
  AND `s_s_p_reporting_stats`.time <= parseDateTimeBestEffort('2025-01-13T23:59:59.000Z') 
  AND ssp_reporting__account=21 
SETTINGS optimize_predicate_expression = 0, join_algorithm = 'hash'
LIMIT 50

若此时右表字段正常返回，说明是优化器的谓词下推导致了逻辑异常。

3. 解决方案 #

方案1：确保JOIN条件类型严格匹配 #

避免不必要的显式类型转换，将JOIN条件改为同类型比较：

• 若左右表对应字段均为字符串：

ON `s_s_p_accounts`.id = `s_s_p_reporting_stats`.account_id

• 若需转换，统一处理字符串格式（比如去除空格）：

ON toInt64(trim(`s_s_p_accounts`.id)) = toInt64(trim(`s_s_p_reporting_stats`.account_id))

方案2：调整查询顺序，先JOIN再过滤 #

用子查询先完成JOIN操作，再过滤结果，避免优化器下推逻辑：

SELECT
  `ssp_reporting__account`,
  id,
  name
FROM (
  SELECT
    `s_s_p_reporting_stats`.account_id `ssp_reporting__account`,
    `s_s_p_accounts`.id,
    `s_s_p_accounts`.name
  FROM adplatform_dev.ssp_dist AS `s_s_p_reporting_stats`
  LEFT JOIN adplatform_dev_ssp_facts.accounts AS `s_s_p_accounts` 
  ON toInt64(`s_s_p_accounts`.id) = toInt64(`s_s_p_reporting_stats`.account_id)
  WHERE `s_s_p_reporting_stats`.time >= parseDateTimeBestEffort('2025-01-01T00:00:00.000Z') 
    AND `s_s_p_reporting_stats`.time <= parseDateTimeBestEffort('2025-01-13T23:59:59.000Z') 
) t
WHERE t.ssp_reporting__account = 21
LIMIT 50

方案3：改用INNER JOIN（业务允许时） #

如果业务只需要左右表都匹配的行，直接使用INNER JOIN，避免LEFT JOIN带来的NULL结果：

SELECT
  `s_s_p_reporting_stats`.account_id `ssp_reporting__account`,
  `s_s_p_accounts`.id,
  `s_s_p_accounts`.name
FROM adplatform_dev.ssp_dist AS `s_s_p_reporting_stats`
INNER JOIN adplatform_dev_ssp_facts.accounts AS `s_s_p_accounts` 
ON toInt64(`s_s_p_accounts`.id) = toInt64(`s_s_p_reporting_stats`.account_id)
WHERE `s_s_p_reporting_stats`.time >= parseDateTimeBestEffort('2025-01-01T00:00:00.000Z') 
  AND `s_s_p_reporting_stats`.time <= parseDateTimeBestEffort('2025-01-13T23:59:59.000Z') 
  AND ssp_reporting__account=21 
LIMIT 50

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