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

Question

I am trying to make the following request to the clickhouse:

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;

But I get the following error:

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

It works if I delete a line 'average_discount',avg(toFloat64(data['ItemDiscount.discount'])).

At first I thought that I was doing an invalid cast to Float64, but the next request worked without any queries:

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

The table into which I insert looks like this:

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

the data field['Item Discount.discount']) contains int32 string representations.

Maybe someone has encountered this, how to fix it and what is the reason for this behavior?

Answer 1

It is not avg(toFloat64(data['ItemDiscount.discount'])) causing the error. The reason is the map of string keys and different type values (Float64 produced by avg and UInt64 - by count).

Replace

'total_count', count(data['ItemDiscount.item_id'])

with

'total_count', toFloat64(count(data['ItemDiscount.item_id']))