SQL中COALESCE函数报操作数需为同类型varchar错误排查

这个类型错误和两个字段的底层存储类型无关，是SQL写法触发了查询引擎的别名解析歧义：

• 你在SELECT子句开头写了b.*，会先读取b表所有原生字段，其中就包含sf_account_id、date_invoice两个字段
• 紧接着你写coalesce(a.accountid, b.sf_account_id) sf_account_id，给coalesce返回结果起的别名，和b表原生字段名完全重名
• 从你用的from_iso8601_timestamp函数、双引号包裹标识符的写法判断，你用的是Presto/Trino引擎，这类引擎解析同层级SELECT表达式的入参时，会优先匹配当前SELECT块内的别名，而不是表的原生字段。也就是说coalesce的第二个参数根本没有读取到b表原生的varchar类型字段，而是匹配到了你正在定义、还没完成类型推导的sf_account_id别名列，自然会报操作数类型不匹配的错误。

你之前尝试给字段加cast(... as varchar)没生效也是这个原因：你以为转换的是b表的原生字段，实际上引擎解析到的是未完成定义的同名列，类型转换根本没有作用到实际的字符串字段上。

两种方案选一个就能解决问题：

1. 避免别名重名
   不要给coalesce的计算结果起和b表原生字段一样的名字，同时去掉函数外面多余的双引号，所有字段加明确的表别名避免歧义：

   SELECT 
       b.*,
       coalesce(cast(a.accountid as varchar), cast(b.sf_account_id as varchar)) as calc_sf_account_id,
       coalesce(cast(a.createddate as timestamp), cast(b.date_invoice as date)) as calc_date_invoice
   from salesforce.sf_account_history a
   FULL OUTER JOIN add_next_month b 
       on substr(a.accountid, 1, length(a.accountid) - 3) = b.sf_account_id 
           and date_trunc('month',cast(from_iso8601_timestamp(a.createddate) as date)) = date_trunc('month', CAST(b.date_invoice as date))
   

2. 调整SELECT字段顺序
   如果你一定要保留sf_account_id、date_invoice的别名，就把计算字段写在b.*前面，避免b.*带出的同名字段干扰别名解析，同时记得补全最外层子查询的闭合和别名：

   SELECT *
   FROM(
       SELECT 
           coalesce(cast(a.accountid as varchar), cast(b.sf_account_id as varchar)) sf_account_id,
           coalesce(cast(a.createddate as timestamp), cast(b.date_invoice as date)) date_invoice,
           b.*
       from salesforce.sf_account_history a
       FULL OUTER JOIN add_next_month b 
           on substr(a.accountid, 1, length(a.accountid) - 3) = b.sf_account_id 
               and date_trunc('month',cast(from_iso8601_timestamp(a.createddate) as date)) = date_trunc('month', CAST(b.date_invoice as date))
   ) t
   

注意：你原来的SQL还存在子查询未闭合、JOIN条件里的date_invoice未加表别名的问题，修改时需要一并补上，否则还会触发其他解析错误。

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