#奇葩现象 我们知道,常用的语句 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 表为主表, ..

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

本贴最后更新于 198 天前,其中的信息可能已经物是人非

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

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 程序时,对部分字段类型进行转换,但部分未转换,造成了结果的不一致。

  • SQL
    89 引用 • 271 回帖 • 3 关注
  • Spark

    Spark 是 UC Berkeley AMP lab 所开源的类 Hadoop MapReduce 的通用并行框架。Spark 拥有 Hadoop MapReduce 所具有的优点;但不同于 MapReduce 的是 Job 中间输出结果可以保存在内存中,从而不再需要读写 HDFS,因此 Spark 能更好地适用于数据挖掘与机器学习等需要迭代的 MapReduce 的算法。

    73 引用 • 45 回帖 • 560 关注
  • join
    6 引用 • 20 回帖
回帖
请输入回帖内容...