近期参与的一个私有云项目要升级，因为maxcompute要升级到更新的版本，对之前的一些SQL写法有个更高的要求，就引出了这个union隐式转换的问题。运维同学扫描到内部的异常是：union.string.meet.non.string。
在ODPS某些模式中在union两侧对应列如果类型不同时会尝试隐式类型转换，其行为是一边为string，另一边为数字或datetime类型时，转为另一边的类型（string）。然而绝大多数的数据库或者开源生态而言，使用的都不是这种转换规则，比如hive，mysql等会优先转成string。这种不确定的转换规则有时候会很危险，如用户从hive往odps迁移时，可能会导致无声无息的精度损失，语义错误等。 ODPS2.0为了安全禁止此隐式类型转换（这也是目前oracle的默认行为）,如果需要请使用CAST函数。（之前好好的，现在要报错了）所以现在项目组要求脚本作者检查自己脚本，明确要转到的类型，如果需要加入显式转换。
例：
select
*
from
(
--
（错误）
select
a_bigint c1
from
t1
union all
select
a_string c1
from
t2) x;
--
如果希望结果
c1
为
bigint
类型（这是目前
ODPS
的行为），改为
select
*
from
(
--
（正确）
select
a_bigint c1
from
t1
union all
select
cast
(a_string
as
bigint
) c1
from
t2) x;
--
如果希望结果
c1
为
string
类型（这是目前
HIVE
的行为），改为
select
*
from
(
--
（正确）
select
cast
(a_bigint
as
string) c1
from
t1
union all
select
a_string c1
from
t2) x;
因为还未升级，目前脚本也不会报错，maxcompute的异常我们也捕获不到，改造的压力有点纯靠肉眼识别了，着实有点难过。
错误示例：
select
123
as
aa,
0
as
ab
from
xlog
union ALL
select
getdate
()
as
aa,
0
as
ab
from
xlog;
FAILED: ODPS-
0130241
:[4,8] Illegal
union
operation -
type
mismatch for column
0
of
UNION
,
left
is
BIGINT
while
right
is
DATETIME
--
注释：这里的
[4,8]
是指第四行，第八个字符开始也就是
getdate().
那怎么去快速的定位到是哪个字段呢？我翻了一下后台检索出来的上百个脚本，脚本代码在500-1000行之间居多，union 的数量在单个脚本中少则三五个，多的有二十几个。呆了一早上，毫无进展。
一抽莫展之际，突然想到了执行计划。MaxCompute的执行计划，虽然会不会刚好会展示输出的数据类型呢？答案：会的。
explain
select
123
as
aa,
0
as
ab
from
xlog
Job Queueing...
job0
is
root
job
In
Job job0:
root
Tasks: M1
In
Task M1:
Data
source: mujiao.xlog
TS: mujiao.xlog
SEL: 123L aa, 0L ab
FS:
output
: Screen
schema:
aa (
bigint
)
ab (
bigint
)
explain
select
getdate
()
as
aa,
0
as
ab
from
xlog;
Job Queueing...
job0
is
root
job
In
Job job0:
root
Tasks: M1
In
Task M1:
Data
source: mujiao.xlog
TS: mujiao.xlog
SEL:
1655965081824
aa, 0L ab
FS:
output
: Screen
schema:
aa (
datetime
)
ab (
bigint
)
我们看到在FS:output:Screen 下面是schema:aa(bigint),ab(bigint)。这就是我们可以利用的数据类型了。所以，我们可以把长脚本中的union一段一段的explain，然后截取这部分内容，比较多个schema的不同。
schema1: schema2:
aa
(
bigint
)
aa
(
datetime
)
ab (
bigint
) ab (
bigint
)
这样就肉眼可视的发现其实union中两段SQL的字段aa是不同的。
其他相关的一些问题：
1) 执行计划中的max_pt()函数无法在开发环境使用，因为开发环境没有分区，这个函数会直接报错。要么删除、注释这个函数，要么在表前面增加生产环境前缀。
2) 超长的SQL段，执行计划可能有几百行上千行，找不到最终的output。可以在日志中搜索“output: Screen”这段对应的就是最终的输出。
3) 太多的字段，肉眼无法判断哪些类型不一样的时候，建议在excel中来比较，利用excel的筛选能力，逐个数据类型筛选比较。
4) 执行计划在特别的情况下可能出不来，使用create table as创建一个临时表来识别SQL输出的数据类型，然后再desc表结构。不过每个字段都要给一个名称，在create table的时候，还有null这种写法也是需要cast后给一个明确的数据类型。
5) 日期转换，因为string到日期转换的格式化类型不是能猜出来的，建议实际看一下数据格式，不要猜测。否则只能线上运行后，报错才能排查出问题。
6) 对于Null值，可以cast(null as datetime)、cast(null as double)给字段赋值。
即便这些都可以，对于数百个长达几百行的脚本来说，这项工作都足以让你烦躁不安失去耐心。建议研发同学还是劳逸结合，再就是日后把这个工作变成一个习惯。一大段SQL的union，就直接先explain，别等报错一个一个看心烦。
最后，你会发现这一切的缘由还是我们的基础工作没有做好。既然是union一起的数据字段，理论数据类型和值域是一模一样的，怎么会出这种问题。标准化的数据应该是日期就是日期，数值就是数值，字符就是字符，不会数值存储成字符、日期存储成字符。显然，现在的痛苦还是来源于之前的工作缺失，做好每一步，后面会越来越轻松。
2.3 另外一个方法
后来跟研发同学要到了一个可以让warning信息显示出来的提示。
set
odps.compiler.warning.disable=false;
sql
running .....
WARNING:[4,8] implicit conversion
from
bigint
to
datetime
,
use
cast
function
to
suppress
这个warning会让所有的隐式转换都抛出来，在现场环境中，明显比我实际按照explain的方法判断出来的要多很多。这两种方法，在实际使用中该如何使用，大家可以自行判断。
祝大家好运！
