function any_value doesn't support array<map>

[wangsimo0]

StarRocks > select id,any_value(value) from anyvalue;
ERROR 1064 (HY000): Getting analyzing error from line 1, column 10 to line 1, column 25. Detail message: No matching function with signature: any_value(array<map<varchar(65533),int(11)>>).

StarRocks > desc anyvalue;
+-------+--------------------------------+------+-------+---------+-------+
| Field | Type                           | Null | Key   | Default | Extra |
+-------+--------------------------------+------+-------+---------+-------+
| id    | int                            | YES  | true  | NULL    |       |
| value | array<map<varchar(65533),int>> | YES  | false | NULL    |       |
+-------+--------------------------------+------+-------+---------+-------+
2 rows in set (0.00 sec)

StarRocks > show frontends;
+---------------------------------+--------------+-------------+----------+-----------+---------+--------+-----------+------+-------+-------------------+---------------------+----------+--------+---------------------+-----------------------+
| Name                            | IP           | EditLogPort | HttpPort | QueryPort | RpcPort | Role   | ClusterId | Join | Alive | ReplayedJournalId | LastHeartbeat       | IsHelper | ErrMsg | StartTime           | Version               |
+---------------------------------+--------------+-------------+----------+-----------+---------+--------+-----------+------+-------+-------------------+---------------------+----------+--------+---------------------+-----------------------+
| ddf52d2f144f_9010_1690797268042 | ddf52d2f144f | 9010        | 8030     | 9030      | 9020    | LEADER | 666604267 | true | true  | 94895             | 2023-08-04 01:44:48 | true     |        | 2023-07-31 09:54:36 | 3.1.0-rc01-64ca37e863 |
+---------------------------------+--------------+-------------+----------+-----------+---------+--------+-----------+------+-------+-------------------+---------------------+----------+--------+---------------------+-----------------------+
1 row in set (0.01 sec)
``

[dustinlineweber]

Just in case it wasn't clear, this problem is not just for arrays of maps, but for any array. And not just this function, but many other aggregate functions (like max_by, that could be used in place of any_value if it was implemented).

Use-cases include rollup tables or materialized views where columns are arrays, but the contents don't change from row to row, and I can't figure out another way to solve this.

Easy steps to recreate this:

create table test_table (
 `id` int NOT NULL,
 `batch` int NOT NULL,
 `vals` ARRAY<int> NOT NULL
 ) ENGINE=OLAP 
PRIMARY KEY(`id`)
DISTRIBUTED BY HASH(`id`);

insert into test_table values (1,1,[2,3]);
insert into test_table values (2,1,[2,3]);

Test Case 1 (any_value)

select batch, any_value(vals) from test_table group by batch;

Expected:

# batch, any_value(vals)
'1', '[2,3]'

Actual:

Error Code: 1064. No matching function with signature: any_value(ARRAY<int(11)>).

Test Case 2 (max_by)

select batch, max_by(vals,id) from test_table group by batch;

Expected:

# batch, max_by(vals,id)
'1', '[2,3]'

Actual:

Error Code: 1064. No matching function with signature: max_by(ARRAY<int(11)>, int(11)).

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!

[dustinlineweber]

An update for anyone finding this: as of at least 3.2.3 (and perhaps sooner) ANY_VALUE does support arrays now (my Test Case 1 above). MAX_BY and MIN_BY unfortunately do not yet.