PostgreSQL SQL迁移ClickHouse遇Unsupported JOIN ON错误，求修复方案

将PostgreSQL中可正常运行的SQL迁移至ClickHouse时触发错误，原SQL语句如下：

select 
  u.counter_id, 
  r.date_of_visit, 
  sum(r.sessions) as sessions, 
  sum(r.pageviews) as pageviews, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term 
from 
  metrika.utm_for_collect u 
  inner join metrika.utm_sessions_report r on u.counter_id = r.counter_id 
  and (
    u.utm_campaign = r.utm_campaign 
    or u.utm_campaign is null
  ) 
  and (
    u.utm_source = r.utm_source 
    or u.utm_source is null
  ) 
  and (
    u.utm_medium = r.utm_medium 
    or u.utm_medium is null
  ) 
  and (
    u.utm_content = r.utm_content 
    or u.utm_content is null
  ) 
  and (
    u.utm_term = r.utm_term 
    or u.utm_term is null
  ) 
group by 
  u.counter_id, 
  r.date_of_visit, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term

报错信息：

Unsupported JOIN ON conditions.
Unexpected '(utm_campaign = r.utm_campaign) OR (utm_campaign IS NULL)': While processing (utm_campaign = r.utm_campaign) OR (utm_campaign IS NULL). (INVALID_JOIN_ON_EXPRESSION) (version 22.7.2.15 (official build))

ClickHouse对JOIN的ON条件有严格限制，仅支持等值连接，不允许在ON子句中使用OR逻辑。针对该场景，提供两种可行修复方案：

方案1：将OR条件迁移至WHERE子句 #

把原JOIN ON中的OR判断移到WHERE子句中，仅在ON中保留等值的counter_id关联，完全保留原业务逻辑：

select 
  u.counter_id, 
  r.date_of_visit, 
  sum(r.sessions) as sessions, 
  sum(r.pageviews) as pageviews, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term 
from 
  metrika.utm_for_collect u 
  inner join metrika.utm_sessions_report r on u.counter_id = r.counter_id 
where
  (u.utm_campaign is null or u.utm_campaign = r.utm_campaign)
  and (u.utm_source is null or u.utm_source = r.utm_source)
  and (u.utm_medium is null or u.utm_medium = r.utm_medium)
  and (u.utm_content is null or u.utm_content = r.utm_content)
  and (u.utm_term is null or u.utm_term = r.utm_term)
group by 
  u.counter_id, 
  r.date_of_visit, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term

此方案无需修改业务逻辑，符合ClickHouse语法要求，是优先推荐的解决方式。

方案2：用COALESCE转换NULL为通配标识（需数据无冲突） #

如果业务数据中不存在某个特定的占位值（例如'__ALL__'），可以通过COALESCE函数将NULL替换为该占位值，把OR逻辑转换为等值匹配：

select 
  u.counter_id, 
  r.date_of_visit, 
  sum(r.sessions) as sessions, 
  sum(r.pageviews) as pageviews, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term 
from 
  metrika.utm_for_collect u 
  inner join metrika.utm_sessions_report r 
    on u.counter_id = r.counter_id 
    and coalesce(u.utm_campaign, '__ALL__') = coalesce(r.utm_campaign, '__ALL__')
    and coalesce(u.utm_source, '__ALL__') = coalesce(r.utm_source, '__ALL__')
    and coalesce(u.utm_medium, '__ALL__') = coalesce(r.utm_medium, '__ALL__')
    and coalesce(u.utm_content, '__ALL__') = coalesce(r.utm_content, '__ALL__')
    and coalesce(u.utm_term, '__ALL__') = coalesce(r.utm_term, '__ALL__')
group by 
  u.counter_id, 
  r.date_of_visit, 
  u.utm_campaign, 
  u.utm_source, 
  u.utm_medium, 
  u.utm_content, 
  u.utm_term

注意：需确保所选占位值（如'__ALL__'）不会出现在实际的utm字段数据中，否则会引发错误匹配。

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