记一次数据类型不同导致的 sql join 异常

#奇葩现象
我们知道，常用的语句

select count(distinct a.id) as id1
,count(distinct b.id) as id2
from table_a a
left  join table_b b
on a.id=b.id
​

结果逻辑上 id1 必定大于等于 id2，因为 a 表为主表，且 b 表为被关联表，包含条件 a.id=b.id，也就是说 b 表有的 id a 表也都存在。

但是今天在 spark-sql 场景下，本人碰到了 id2 的值大于 id1 的值的奇葩现象，一开始，百思不得其解啊，后来经过数据抽样排查，发现是数据类型不一致导致的锅，具体来说，就是 int 的 1 和 001 的字符串类型关联上了

现象复现：

--创建int主键类型的测试表
drop  table  if  exists temp.int_primary;
create  table  if  not  exists temp.int_primary(
id int comment 'int 型id'
)；

--写入int类型的主键值
insert  into  table temp.int_primary
select id
from
  ( select 1  as id ) a ;

--数据查看
select * from temp.int_primary;
--结果
id
---
1

  
--创建字符串类型的结果表
drop  table  if  exists temp.string_primary;
create  table  if  not  exists temp.string_primary(
id string comment 'string 型id'
)
;
 
--写入字符串类型表数据
insert  into  table temp.string_primary
select id
from
  (select '1'  as id
   union
   select '01'  as id
   union
   select '001'  as id
) a ;

--查看数据结果
select * from temp.string_primary;
--结果
id
---
1
01
001  

--见证奇迹的时刻，使得被关联表id更多
select count(distinct a.id) as id1
,count(distinct b.id) as id2
from temp.int_primary a
left join temp.string_primary b
on a.id=b.id

--结果
id1 id2
----
1    3
​

如果按以上操作，必定能复现次奇怪现象。

原因分析

其实，看了现象复现，对于产生原因，笔者相信大部分人都会有响应的猜测了。

经过本人测试，发现 spark-sql 进行 sql 解析时，对于 a left join b 或 a right join b 这种操作时，如果关联的字段类型不一致，会以主表的字段类型为基础，将被关联表的字段类型转为主表的字段类型进行关联，如果被关联表的字段类型无法转换（如字符串‘sdfsd’无法转换为数字），那么会被当成 null 处理。

在以上的例子中,b 表的 01 和 001 字符串，被转成 int 型后为 1，和 a 表关联上了，所以关联后的结果有 3 条。

但是在 count(distinct b.id)这个语句时，获取的又是未经处理的字符串类型，所以统计出来的 3，而非 1.

至此，奇怪的现象已经解释完毕，spark-sql 在将 sql 转换为 spark 程序时，对部分字段类型进行转换，但部分未转换，造成了结果的不一致。