--hive字段关联类型不一致时，两边都会转换成double类型
explain
select a.*,b.*
from (
select cast(186994576061124660 as bigint) col ) a
left join (select '186994576061124672' as col ) b on a.col=b.col ;
186994576061124660 186994576061124672
Reduce Operator Tree:
Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 UDFToDouble(186994576061124660) (type: double)
1 UDFToDouble('186994576061124672') (type: double)
--不一致时人工转成string
explain
select a.*,b.*
from (
select cast(186994576061124660 as bigint) col ) a
left join (select '186994576061124672' as col ) b on cast(a.col as string)=b.col;
186994576061124660 NULL
Reduce Operator Tree:
Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 UDFToString(186994576061124660) (type: string)
1 '186994576061124672' (type: string)
select '186994576061124660' , cast(186994576061124660 as double) as dl,cast(186994576061124660 as bigint) as bt,cast(186994576061124660 as decimal) as dm
union all
select '186994576061124559' , cast(186994576061124559 as double) as dl,cast(186994576061124559 as bigint) as bt,cast(186994576061124559 as decimal) as dm
186994576061124660 1.86994576061124672E17 186994576061124660 NULL
186994576061124559 1.86994576061124544E17 186994576061124559 NULL
select if(cast(186994576061124660 as double)='186994576061124660',1,0)
1
select if(cast(186994576061124660 as double)='186994576061124672',1,0)
1
select if(cast(186994576061124660 as bigint)='186994576061124672',1,0);
WARNING: Comparing a bigint and a string may result in a loss of precision.
1