ClickHouse插入报错NO_COMMON_TYPE：Float64与UInt64无公共超类型

问题场景 #

执行以下插入SQL时触发NO_COMMON_TYPE错误：

insert into atlas_aggregator.aggregates_dist
(topic, integration_id, name, window_end_ts, window_size_sec, update_ts, groups, metrics)
    with
        'topic_name' as topic_name,
        'e823d358-a2ae-46b1-8dab-2701c3328a7a' as integration_id_,
        'DiscountItemAggregates' as name,
        3600 as window,
        toDateTime(1698850800) as endTs,
        endTs - window as startTs
    select
        topic_name,
        integration_id_,
        name,
        endTs,
        window,
        now(),
        map (
            'ItemDiscount.status', data['ItemDiscount.status']
             ) as groups,
        map(
                'total_count', count(data['ItemDiscount.item_id']),
                'average_discount', avg(toFloat64(data['ItemDiscount.discount']))

            ) as metrics
    from (
        select
            data
        from
        (
            select
                data,
                row_number() over (partition by integration_id, kafka_partition, `offset` order by kafka_ts) as row_num
            from atlas_aggregator.raw_data_dist
            where
                integration_id = integration_id_
                and kafka_ts between startTs and endTs
                and not(data['ItemDiscount.item_id'] = '' OR
                data['ItemDiscount.discount'] = '' OR
                data['ItemDiscount.status'] = '' )
        )
        where
            row_num = 1
    ) as t
    group by cube(data['ItemDiscount.status'])
    settings insert_distributed_sync=1;

报错信息：

Code: 386. DB::Exception: There is no supertype for types Float64, UInt64  because some of them are integers and some are floating point, but there is no floating point type, that can exactly represent all required integers: While processing 'CPU.ItemDiscount' AS topic_name, 'e823d358-a2ae-46b1-8dab-2701c3328a7a' AS integration_id_, 'DiscountItemAggregates' AS name, toDateTime(1698850800) AS endTs, 3600 AS window, now(), map('ItemDiscount.status', data['ItemDiscount.status']) AS groups, map('total_count', count(data['ItemDiscount.item_id']), 'average_discount', avg(toFloat64(data['ItemDiscount.discount']))) AS metrics. (NO_COMMON_TYPE) (version 23.3.2.37 (official build))
[2023-11-09 18:21:21] , server ClickHouseNode [uri=http://prodclickhouse22985-65576z502.h.o3.ru:8123/default, options={dataTransferTimeout=100000,connection_timeout=100000,custom_http_params=session_id=DataGrip_bcbbe03b-bd32-4cb8-96ec-f15fff3fca83}]@23883692

删除'average_discount',avg(toFloat64(data['ItemDiscount.discount']))后语句可正常执行；单独执行以下查询也无异常：

select data['ItemDiscount.status'], avg(toFloat64(data['ItemDiscount.discount'])) from atlas_aggregator.raw_data_dist
where topic = 'topic_name'
group by data['ItemDiscount.status'];

目标表atlas_aggregator.aggregates结构：

create table if not exists atlas_aggregator.aggregates on cluster nodes (
    topic String,
    integration_id String,
    name String,
    window_end_ts DATETIME64(3),
    window_size_sec Int64,
    update_ts DATETIME64(3),
    groups Map(String, String),
    metrics Map(String, Double)
) engine=ReplicatedMergeTree('/clickhouse/{cluster}/tables/{uuid}/aggregates/{shard}', '{replica}')
order by (topic, window_end_ts, window_size_sec)
partition by (topic, window_end_ts, window_size_sec);

其中data字段的ItemDiscount.discount是int32类型的字符串表示。

问题原因 #

ClickHouse的Map类型要求所有值的类型必须完全一致：

• count(data['ItemDiscount.item_id'])返回UInt64类型（计数结果）
• avg(toFloat64(data['ItemDiscount.discount']))返回Float64类型（平均值结果）

虽然目标表的metrics字段是Map(String, Double)（Double是Float64的别名），但构建Map时，ClickHouse会先尝试推断Map值的统一类型。由于UInt64的取值范围超出了Float64能精确表示的整数范围（Float64仅能精确表示2^53以内的整数），无法自动将UInt64转换为Float64，因此报错“无共同超类型”。

单独执行avg查询时，是两个独立列，不需要统一类型，因此不会触发该错误。

解决办法 #

显式将计数结果转换为Double/Float64类型，确保Map中所有值的类型一致，修改后的metrics部分代码如下：

map(
    'total_count', toDouble(count(data['ItemDiscount.item_id'])),
    'average_discount', avg(toFloat64(data['ItemDiscount.discount']))
) as metrics

也可以用toFloat64替代toDouble，两者在ClickHouse中是等价的。

修改后的完整插入SQL：

insert into atlas_aggregator.aggregates_dist
(topic, integration_id, name, window_end_ts, window_size_sec, update_ts, groups, metrics)
    with
        'topic_name' as topic_name,
        'e823d358-a2ae-46b1-8dab-2701c3328a7a' as integration_id_,
        'DiscountItemAggregates' as name,
        3600 as window,
        toDateTime(1698850800) as endTs,
        endTs - window as startTs
    select
        topic_name,
        integration_id_,
        name,
        endTs,
        window,
        now(),
        map (
            'ItemDiscount.status', data['ItemDiscount.status']
             ) as groups,
        map(
                'total_count', toDouble(count(data['ItemDiscount.item_id'])),
                'average_discount', avg(toFloat64(data['ItemDiscount.discount']))

            ) as metrics
    from (
        select
            data
        from
        (
            select
                data,
                row_number() over (partition by integration_id, kafka_partition, `offset` order by kafka_ts) as row_num
            from atlas_aggregator.raw_data_dist
            where
                integration_id = integration_id_
                and kafka_ts between startTs and endTs
                and not(data['ItemDiscount.item_id'] = '' OR
                data['ItemDiscount.discount'] = '' OR
                data['ItemDiscount.status'] = '' )
        )
        where
            row_num = 1
    ) as t
    group by cube(data['ItemDiscount.status'])
    settings insert_distributed_sync=1;

内容的提问来源于stack exchange，提问作者Gumada Yaroslav