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

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

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

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
    129 引用 • 396 回帖 • 3 关注
  • Spark

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

    74 引用 • 46 回帖 • 564 关注
  • join
    6 引用 • 21 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • Eclipse

    Eclipse 是一个开放源代码的、基于 Java 的可扩展开发平台。就其本身而言,它只是一个框架和一组服务,用于通过插件组件构建开发环境。

    76 引用 • 258 回帖 • 623 关注
  • 以太坊

    以太坊(Ethereum)并不是一个机构,而是一款能够在区块链上实现智能合约、开源的底层系统。以太坊是一个平台和一种编程语言 Solidity,使开发人员能够建立和发布下一代去中心化应用。 以太坊可以用来编程、分散、担保和交易任何事物:投票、域名、金融交易所、众筹、公司管理、合同和知识产权等等。

    34 引用 • 367 回帖
  • Firefox

    Mozilla Firefox 中文俗称“火狐”(正式缩写为 Fx 或 fx,非正式缩写为 FF),是一个开源的网页浏览器,使用 Gecko 排版引擎,支持多种操作系统,如 Windows、OSX 及 Linux 等。

    7 引用 • 30 回帖 • 373 关注
  • 支付宝

    支付宝是全球领先的独立第三方支付平台,致力于为广大用户提供安全快速的电子支付/网上支付/安全支付/手机支付体验,及转账收款/水电煤缴费/信用卡还款/AA 收款等生活服务应用。

    29 引用 • 347 回帖
  • Follow
    4 引用 • 12 回帖 • 8 关注
  • ActiveMQ

    ActiveMQ 是 Apache 旗下的一款开源消息总线系统,它完整实现了 JMS 规范,是一个企业级的消息中间件。

    19 引用 • 13 回帖 • 685 关注
  • App

    App(应用程序,Application 的缩写)一般指手机软件。

    91 引用 • 384 回帖 • 1 关注
  • 酷鸟浏览器

    安全 · 稳定 · 快速
    为跨境从业人员提供专业的跨境浏览器

    3 引用 • 59 回帖 • 53 关注
  • Markdown

    Markdown 是一种轻量级标记语言,用户可使用纯文本编辑器来排版文档,最终通过 Markdown 引擎将文档转换为所需格式(比如 HTML、PDF 等)。

    172 引用 • 1541 回帖
  • 电影

    这是一个不能说的秘密。

    123 引用 • 608 回帖
  • LaTeX

    LaTeX(音译“拉泰赫”)是一种基于 ΤΕΧ 的排版系统,由美国计算机学家莱斯利·兰伯特(Leslie Lamport)在 20 世纪 80 年代初期开发,利用这种格式,即使使用者没有排版和程序设计的知识也可以充分发挥由 TeX 所提供的强大功能,能在几天,甚至几小时内生成很多具有书籍质量的印刷品。对于生成复杂表格和数学公式,这一点表现得尤为突出。因此它非常适用于生成高印刷质量的科技和数学类文档。

    12 引用 • 59 回帖
  • Hadoop

    Hadoop 是由 Apache 基金会所开发的一个分布式系统基础架构。用户可以在不了解分布式底层细节的情况下,开发分布式程序。充分利用集群的威力进行高速运算和存储。

    93 引用 • 122 回帖 • 616 关注
  • API

    应用程序编程接口(Application Programming Interface)是一些预先定义的函数,目的是提供应用程序与开发人员基于某软件或硬件得以访问一组例程的能力,而又无需访问源码,或理解内部工作机制的细节。

    79 引用 • 431 回帖 • 3 关注
  • 友情链接

    确认过眼神后的灵魂连接,站在链在!

    24 引用 • 373 回帖
  • Thymeleaf

    Thymeleaf 是一款用于渲染 XML/XHTML/HTML5 内容的模板引擎。类似 Velocity、 FreeMarker 等,它也可以轻易的与 Spring 等 Web 框架进行集成作为 Web 应用的模板引擎。与其它模板引擎相比,Thymeleaf 最大的特点是能够直接在浏览器中打开并正确显示模板页面,而不需要启动整个 Web 应用。

    11 引用 • 19 回帖 • 395 关注
  • 锤子科技

    锤子科技(Smartisan)成立于 2012 年 5 月,是一家制造移动互联网终端设备的公司,公司的使命是用完美主义的工匠精神,打造用户体验一流的数码消费类产品(智能手机为主),改善人们的生活质量。

    4 引用 • 31 回帖
  • Android

    Android 是一种以 Linux 为基础的开放源码操作系统,主要使用于便携设备。2005 年由 Google 收购注资,并拉拢多家制造商组成开放手机联盟开发改良,逐渐扩展到到平板电脑及其他领域上。

    336 引用 • 324 回帖 • 1 关注
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    346 引用 • 760 回帖 • 1 关注
  • 反馈

    Communication channel for makers and users.

    120 引用 • 906 回帖 • 281 关注
  • 深度学习

    深度学习(Deep Learning)是机器学习的分支,是一种试图使用包含复杂结构或由多重非线性变换构成的多个处理层对数据进行高层抽象的算法。

    43 引用 • 44 回帖
  • MyBatis

    MyBatis 本是 Apache 软件基金会 的一个开源项目 iBatis,2010 年这个项目由 Apache 软件基金会迁移到了 google code,并且改名为 MyBatis ,2013 年 11 月再次迁移到了 GitHub。

    173 引用 • 414 回帖 • 364 关注
  • OnlyOffice
    4 引用 • 20 关注
  • 安装

    你若安好,便是晴天。

    132 引用 • 1184 回帖 • 1 关注
  • 强迫症

    强迫症(OCD)属于焦虑障碍的一种类型,是一组以强迫思维和强迫行为为主要临床表现的神经精神疾病,其特点为有意识的强迫和反强迫并存,一些毫无意义、甚至违背自己意愿的想法或冲动反反复复侵入患者的日常生活。

    15 引用 • 161 回帖
  • RYMCU

    RYMCU 致力于打造一个即严谨又活泼、专业又不失有趣,为数百万人服务的开源嵌入式知识学习交流平台。

    4 引用 • 6 回帖 • 62 关注
  • RabbitMQ

    RabbitMQ 是一个开源的 AMQP 实现,服务器端用 Erlang 语言编写,支持多种语言客户端,如:Python、Ruby、.NET、Java、C、PHP、ActionScript 等。用于在分布式系统中存储转发消息,在易用性、扩展性、高可用性等方面表现不俗。

    49 引用 • 60 回帖 • 352 关注
  • 996
    13 引用 • 200 回帖 • 1 关注