ClickHouse groupArrayInsertAt传0作为位置参数报错问题

在使用ClickHouse的groupArrayInsertAt聚合函数时，为了避免数组开头出现多余的0，将row_number()的结果减1作为位置参数传入，却收到报错：

SQL Error [43] [07000]: Code: 43. DB::Exception: Second argument of aggregate function groupArrayInsertAt must be unsigned integer. (ILLEGAL_TYPE_OF_ARGUMENT) (version 23.8.9.54 (official build))

原查询使用row_number()作为位置参数时，结果数组开头会出现0（因为ClickHouse数组索引从0开始，而row_number()从1开始计数），示例查询及结果如下：

with dat_ as 
(select 'K' as f, 'P' as g, 2 as h
union all
select 'K' as f, 'P' as g, 7 as h
union all
select 'K' as f, 'P' as g, 5 as h
union all
select 'G' as f, 'P' as g, 1 as h
union all
select 'G' as f, 'K' as g, 3 as h
union all
select 'G' as f, 'K' as g, 8 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'J' as g, 5 as h)
,recs_ as (
select f, g, h, row_number() over(partition by f, g order by h desc) as rnk
from dat_
)
select f, g, groupArrayInsertAt(h, rnk) as arr_
from recs_
group by f, g

查询结果：

g   f   arr_
------------------
P   G   [0,1]
J   P   [0,5]
Y   P   [0,2,2,2]
K   G   [0,8,3]
P   K   [0,7,5,2]

报错原因 #

row_number()函数返回的是无符号整数类型（UInt64），当对其执行减1操作后，ClickHouse会自动将结果转换为有符号整数类型（Int64）（这是为了避免无符号数减1导致的溢出问题）。而groupArrayInsertAt的第二个参数要求必须是无符号整数类型，即使值为0，只要类型不匹配就会触发报错。

解决方案 #

将减1后的结果显式转换为无符号整数类型，比如使用toUInt32()或toUInt64()函数包裹计算结果。

修改后的完整查询：

with dat_ as 
(select 'K' as f, 'P' as g, 2 as h
union all
select 'K' as f, 'P' as g, 7 as h
union all
select 'K' as f, 'P' as g, 5 as h
union all
select 'G' as f, 'P' as g, 1 as h
union all
select 'G' as f, 'K' as g, 3 as h
union all
select 'G' as f, 'K' as g, 8 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'Y' as g, 2 as h
union all
select 'P' as f, 'J' as g, 5 as h)
,recs_ as (
select f, g, h, toUInt32(row_number() over(partition by f, g order by h desc) - 1) as rnk
from dat_
)
select f, g, groupArrayInsertAt(h, rnk) as arr_
from recs_
group by f, g

执行后得到预期结果：

g   f   arr_
------------------
P   G   [1]
J   P   [5]
Y   P   [2,2,2]
K   G   [8,3]
P   K   [7,5,2]

内容的提问来源于stack exchange，提问作者Ebube Okoli