-- name: test_cast_array

CREATE TABLE `tbl` (k1 string,k2 string,k3 int) DUPLICATE KEY(`k1`) DISTRIBUTED BY HASH(`k1`) BUCKETS 3 PROPERTIES ("replication_num" = "1");

-- result:

-- !result

insert into tbl values

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1),

('abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq', 'ab', 1);

-- result:

-- !result

set @arr_str = (select array_agg(k1) from (select t1.k1 from tbl t1 join tbl t2 join tbl t3 join tbl t4) t);

-- result:

-- !result

select @arr_str[1];

-- result:

abcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopqabcdefghijklmnopq

-- !result

select element_at(array_agg(array_length(@arr_str)), 5) from (select t1.k3 from tbl t1 join tbl t2 join tbl t3 join tbl t4) t;

-- result:

10000

-- !result

select * from (select array_length(@arr_str) array_len from (select t1.k3 from tbl t1 join tbl t2 join tbl t3 join tbl t4) t) t where array_len = 1;

-- result:

-- !result

select count(*) from (select @arr_str arr from (select t1.k3 from tbl t1 join tbl t2) t) t group by arr[1];

-- result:

100

-- !result

select /*+SET_VAR(pipeline_dop=100)*/ count(*) from (select @arr_str arr from (select t1.k3 from tbl t1 join tbl t2) t) t group by arr[1];

-- result:

100

-- !result

select count(*), array_length(array_agg(arr)) from (select @arr_str arr from (select t1.k3 from tbl t1) t) t group by arr[1];

-- result:

10 10

-- !result

select array_length(array_agg(arr)) from (select cast("[[1, 2, 3], [1, 2, 3]]" as array<array<int>>) arr from (select t1.k3 from tbl t1 join tbl t2) t) t;

-- result:

100

-- !result

select array_agg(arr)[1] from (select cast("[[1, 2, 3], [1, 2, 3]]" as array<array<int>>) arr from (select t1.k3 from tbl t1 join tbl t2) t) t;

-- result:

[[1,2,3],[1,2,3]]

-- !result