1问题概述1.1UNION中的隐式类型转换问题最近参与的一个私有云项目需要升级，因为maxcompute需要升级到较新的版本，有一些变化之前的一些SQL写法要求高，导致这个union隐式转换的问题。运维同学扫描到的内部异常是：union.string.meet.non.string。在ODPS的某些模式下，如果并集两侧对应列的类型不同，会尝试进行隐式类型转换。行为是，当一侧是字符串，另一侧是数字或日期时间类型时，将转换为另一侧的类型（字符串）。但是绝大多数的数据库或者开源生态都不使用这个转换规则，比如hive，mysql等，会先转换成string。这种不确定的转换规则有时是非常危险的。例如，当用户从hive迁移到odps时，可能会导致silentprecisionloss和semanticerrors。ODPS2.0为了安全禁止这种隐式类型转换（这也是目前oracle的默认行为），如果需要请使用CAST函数。（之前还好，现在要报错了）所以现在项目组要求脚本作者检查自己的脚本，明确要传输的类型，必要时加上显式转换。例子：select*from(--(error)selecta_bigintc1fromt1unionallselecta_stringc1fromt2)x;--如果希望结果c1为bigint类型（这是ODPS目前的行为），改为select*from(--(Correct)selecta_bigintc1fromt1unionallselectcast(a_stringasbigint)c1fromt2）X;--如果希望结果c1是string类型（这是HIVE当前的行为），改为select*from(--(正确)selectcast(a_bigintasstring)c1fromt1unionallselecta_stringc1fromt2)x;1.2问题分析因为脚本没有升级，所以当前脚本不会报错，无法捕获到maxcompute的异常。转型的压力单纯用肉眼去辨认有点难过。错误示例：select123asaa,0asabfromxlogunionALLselectgetdate()asaa,0asabfromxlog;FAILED:ODPS-0130241:[4,8]Illegalunionoperation-UNION的第0列类型不匹配，左边是BIGINT而右边是DATETIME--注：这里的[4,8]指的是第四行，第八个字符是getdate()。那如何快速定位到是哪个字段呢？我查看了从后台检索到的数百个脚本。大多数脚本代码在500-1000行之间，单个脚本中的联合数量从三个到五个到二十多个不等。呆了一上午，毫无进展。2解决问题简单思考一下，如果想知道Union的两个表的数据类型是否对齐，就得看原表结构中的数据类型，目标表结构中的数据类型，并查看代码找到SQL逻辑执行后的数据类型，从而找出哪些字段数据类型不一致。于是我就开始按照这个思路去看。第一个脚本的代码1000多行，union的表字段数也有100多个，union有6个。我惊呆了，肉眼完全无法辨认。一上午就这样过去了，不但没有搞定，还把自己气死了。2.1在无法使用执行计划的时候，突然想到了执行计划。MaxCompute的执行计划会不会只显示输出的数据类型？答：是的。解释select123asaa,0asabfromxlog;JobQueueing...job0isrootjobInJobjob0:rootTasks:M1InTaskM1:Datasource:mujiao.xlogTS:mujiao.xlogSEL:123Laa,0LabFS:输出：屏幕模式：aa(bigint)ab(bigint)OKexplainselectgetdate()asaa,0asabfromxlog;;JobQueueing...job0isrootjobInJobjob0:rootTasks:M1InTaskM1:Datasource:mujiao.xlogTS:mujiao.xlogSEL:1655965081824aa,0LabFS:output:Screenschema:aa(datetime)ab(bigint)OK我们在FS:output:Screen下看到schema:aa(bigint),ab(bigint)。这是我们可以利用的数据类型。因此，我们可以在长脚本中逐段解释union，然后截取这部分内容来比较多个schema之间的差异。schema1:schema2:aa(bigint)aa(datetime)ab(bigint)ab(bigint)这样，肉眼可以看出union中两个sql的字段aa其实是不一样的。2.2其他问题其他一些相关问题：1）执行计划中的max_pt()函数不能在开发环境中使用，因为开发环境没有分区，这个函数会直接报错。要么把这个函数删掉注释掉，要么在表格前面加个生产环境前缀。2）对于一个特别长的SQL段，执行计划可能有几百几千行，找不到最终的输出。可以在日志中搜索“output:Screen”，对应最终的输出。3）当字段过多，肉眼无法判断哪些类型不同时，建议在excel中进行比较，利用excel的过滤能力，对数据类型进行一一过滤比较。4)特殊情况下执行计划可能不会出来。使用createtableas创建一个临时表来标识SQL输出的数据类型，然后desc表结构。但是，必须为每个字段指定一个名称。在建表的时候，还有一种写null的方式，需要在cast后给一个明确的数据类型。5）日期转换，因为字符串到日期转换的格式化类型无法猜测，建议实际看数据格式，不要猜测。否则只能在线运行报错后才能排查问题。6）对于Null值，可以使用cast(nullasdatetime)或cast(nullasdouble)给字段赋值。即使这一切都是可能的，对于数百个长达几百行的脚本来说，这项工作也足以让你坐立不安、不耐烦了。建议R&D同学要劳逸结合，以后把这项工作变成一种习惯。大段的SQLunion，先解释一下就好了，不要等着报错一个一个。最后你会发现，造成这一切的原因是我们的基础工作没有做好。既然是带联合的数据域，理论上的数据类型和取值范围是完全一样的，怎么会出现这样的问题。标准化的数据应该是日期是日期，值是值，字符是字符。值不存储为字符，日期存储为字符。显然，现在的痛苦还是来自于之前工作的不足。如果每一步都做好，以后会越来越容易。2.3另一种方法后来向研发同学要了一个可以显示警告信息的提示符。setodps.compiler.warning.disable=false;sqlrunning.....WARNING:[4,8]从bigint到datetime的隐式转换，使用cast函数来抑制此警告将导致所有隐式转换被抛出。在现场环境下，明显比我实际根据explain方法判断的要多很多。这两种方法在实际使用中如何使用，大家可以自行判断。祝大家好运！
