适用范围:
There is no supertype for types A, B
问题复现:
在用union all,将两个相同字段名称的查询结果相连时报错。
SQL语句:
select count(distinct user) as "value", name from table1
union all
select sum(userTotal) as "value" , name from table2
报错日志:
SQL 错误 [386]: ClickHouse exception, code: 386, host: ...., port: ....; Code: 386, e.displayText() = DB::Exception: There is no supertype for types UInt64, Int64 because some of them are signed integers and some are unsigned integers, but there is no signed integer type, that can exactly represent all required unsigned integer values (version 19.15.2.2)
分析原因:
虽然结果字段名一样,但是类型不同,所以无法union all到一起,一个count的结果是Int64 ,一个sum的结果是UInt64
解决办法:
将两个查询结果对应的字段统一即可。
本查询是数值类型的不一致,可用toInt64(xxx)进行类型转换
如下,将语句修改为:
select count(distinct user) as "value", name from table1
union all
select toUInt64(sum(userTotal)) as "value" , name from table2
再执行就没问题了。
附 Clickhouse常见数据类型
整数:UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64
浮点数:Float32, Float64
字符串:String
日期类型:Date、DateTime
其他:枚举、数组、元组 等
附 类型转换函数 部分例子:
SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);
SELECT toString(now());
SELECT now() AS now_local, toString(now(), ‘Asia/Yekaterinburg‘) AS now_yekat;
SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());