【解决】ClickHouse union all 错误386 There is no supertype for types UInt64(类型A), Int64(类型B)

适用范围：

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());