-- name: test_array_test01

select ARRAY<INT>[], [], ARRAY<STRING>['abc'], [123, NULL, 1.0], ['abc', NULL];

-- result:

[] [] ["abc"] [123.0,null,1.0] ["abc",null]

-- !result

-- name: testArrayPredicate

CREATE TABLE array_data_type

(c1 int,

c2 array<bigint>,

c3 array<bigint>,

c4 array<bigint> not null,

c5 array<bigint> not null)

PRIMARY KEY(c1)

DISTRIBUTED BY HASH(c1)

BUCKETS 1

PROPERTIES ("replication_num" = "1");

-- result:

-- !result

insert into array_data_type (c1, c2, c3, c4,c5) values

(1,NULL,NULL,[22, 11, 33],[22, 11, 33]);

-- result:

-- !result

select c2 = c3 from array_data_type;

insert into array_data_type (c1, c2, c3, c4,c5) values

(2,NULL,[22, 11, 33],[22, 11, 33],[22, 11, 33]),

(3,[22, 11, 33],[22, 11, 33],[22, 11, 33],[22, 11, 33]),

(4,[22, 11, 33],NULL,[22, 11, 33],[22, 11, 33]);

-- result:

None

-- !result

select c2 <=> c3 from array_data_type;

-- result:

1

0

1

0

-- !result

select c2 = c3 from array_data_type;

-- result:

None

None

1

None

-- !result

select c3 = c4 from array_data_type;

-- result:

None

1

1

None

-- !result

select c4 = c5 from array_data_type;

-- result:

1

1

1

1

-- !result

insert into array_data_type (c1, c2, c3, c4,c5) values

(5,[22, 11, 33],[22, 11, 33],[22, 11, 44],[22, 11, 33]);

-- result:

-- !result

select c4 = c5 from array_data_type;

-- result:

1

1

1

1

0

-- !result

select c4 > c5 from array_data_type;

-- result:

0

0

0

0

1

-- !result

select * from (select array_map(x -> x*2 + x*2, [1,3]) col1) t1 join (select array_map(x -> x*2 + x*2, c3) col2 from array_data_type) t2;

-- result:

[4,12] None

[4,12] [88,44,132]

[4,12] [88,44,132]

[4,12] None

[4,12] [88,44,132]

-- !result

-- name: testArrayVarchar

CREATE TABLE array_data_type_1

(c1 int,

c2 array<datetime>,

c3 array<float>,

c4 array<varchar(10)>,

c5 array<varchar(20)>,

c6 array<array<varchar(10)>>)

PRIMARY KEY(c1)

DISTRIBUTED BY HASH(c1)

BUCKETS 1

PROPERTIES ("replication_num" = "1");

-- result:

-- !result

insert into array_data_type_1 values

(1, ['2020-11-11', '2021-11-11', '2022-01-01'], [1.23, 1.35, 2.7894], ['a', 'b'], ['sss', 'eee', 'fff'], [['a', 'b']]),

(2, ['2020-01-11', null, '2022-11-01'], [2.23, 2.35, 3.7894], ['aa', null], ['ssss', 'eeee', null], [['a', null], null]),

(3, null, null, null, null, null);

-- result:

-- !result

select * from array_data_type_1 where c4 != ['a'] or c6 = [['a', 'b']];

-- result:

1 ["2020-11-11 00:00:00","2021-11-11 00:00:00","2022-01-01 00:00:00"] [1.23,1.35,2.7894] ["a","b"] ["sss","eee","fff"] [["a","b"]]

2 ["2020-01-11 00:00:00",null,"2022-11-01 00:00:00"] [2.23,2.35,3.7894] ["aa",null] ["ssss","eeee",null] [["a",null],null]

-- !result

select * from array_data_type_1 where c4 = ['a'] or c6 != [['a', 'b']];

-- result:

2 ["2020-01-11 00:00:00",null,"2022-11-01 00:00:00"] [2.23,2.35,3.7894] ["aa",null] ["ssss","eeee",null] [["a",null],null]

-- !result

select * from array_data_type_1 where c4 = cast(c4 as array<char(10)>);

-- result:

1 ["2020-11-11 00:00:00","2021-11-11 00:00:00","2022-01-01 00:00:00"] [1.23,1.35,2.7894] ["a","b"] ["sss","eee","fff"] [["a","b"]]

-- !result

select * from array_data_type_1 where c5 = c4 or c6 = [['a']];

-- result:

-- !result

select * from array_data_type_1 where array_map((x) -> concat(x, 'a'), c5) = c4;

-- result:

-- !result

select c6[0] = ['a'] from array_data_type_1;

-- result:

None

None

None

-- !result

select c6[0] > array_map((x) -> concat(x, 'a'), c5) from array_data_type_1;

-- result:

None

None

None

-- !result

-- name: testArrayTopN

CREATE TABLE array_top_n

(c1 int,

c2 array<int>)

PRIMARY KEY(c1)

DISTRIBUTED BY HASH(c1)

BUCKETS 1

PROPERTIES ("replication_num" = "1");

-- result:

-- !result

insert into array_top_n values

(1, [1]),

(2, [5, 6]),

(3, [2, 3, 4]),

(4, [12, 13, 14, 15]),

(5, [7, 8, 9, 10, 11]);

-- result:

-- !result

select * from array_top_n order by c2[1];

-- result:

1 [1]

3 [2,3,4]

2 [5,6]

5 [7,8,9,10,11]

4 [12,13,14,15]

-- !result

select * from array_top_n order by c2[1] limit 1,10;

-- result:

3 [2,3,4]

2 [5,6]

5 [7,8,9,10,11]

4 [12,13,14,15]

-- !result

select * from array_top_n order by c2[1] limit 2,10;

-- result:

2 [5,6]

5 [7,8,9,10,11]

4 [12,13,14,15]

-- !result

select * from array_top_n order by c2[1] limit 3,10;

-- result:

5 [7,8,9,10,11]

4 [12,13,14,15]

-- !result

select * from array_top_n order by c2[1] limit 4,10;

-- result:

4 [12,13,14,15]

-- !result

select * from array_top_n order by c2[1] limit 5,10;

-- result:

-- !result

-- name: testArrayExpr

CREATE TABLE array_exprr

(

c1 int not null,

c2 int not null

)

PRIMARY KEY(c1)

DISTRIBUTED BY HASH(c1)

BUCKETS 1

PROPERTIES ("replication_num" = "1");

-- result:

-- !result

insert into array_exprr SELECT generate_series, generate_series FROM TABLE(generate_series(1, 13336));

-- result:

-- !result

select count([CAST(if(c2 is null, c1 + c2, 0) as DECIMAL128(38,0)) + if(c1 is null, c2 ,0)] is null) from array_exprr;

-- result:

13336

-- !result

-- name: testEmptyArray

with t0 as (

select c1 from (values([])) as t(c1)

)

select

array_concat(c1, [1])

from t0;

-- result:

[1]

-- !result

with t0 as (

select c1 from (values([])) as t(c1)

)

select

array_concat([1], c1)

from t0;

-- result:

[1]

-- !result

select array_concat(c1, [[]])

from (select c1 from (values([])) as t(c1)) t;

-- result:

[[]]

-- !result

select array_concat(c1, [[1]])

from (select c1 from (values([])) as t(c1)) t;

-- result:

[[1]]

-- !result

select array_concat(c1, [[1]])

from (select c1 from (values([[]])) as t(c1)) t;

-- result:

[[],[1]]

-- !result

select array_concat(c1, [map{'a':1}])

from (select c1 from (values([map()])) as t(c1)) t;

-- result:

[{},{"a":1}]

-- !result

select array_concat(c1, [map{'a':1}])

from (select c1 from (values([])) as t(c1)) t;

-- result:

[{"a":1}]

-- !result

select array_concat(c1, [named_struct('a', 1, 'b', 2, 'c', 3)])

from (select c1 from (values([])) as t(c1)) t;

-- result:

[{"a":1,"b":2,"c":3}]

-- !result

CREATE TABLE `t2` (

`pk` bigint(20) NOT NULL COMMENT "",

`aas_1` array<array<array<varchar(65533)>>> NULL COMMENT "",

`aad_1` array<array<array<DECIMAL128(26,2)>>> NULL COMMENT ""

) ENGINE=OLAP

DUPLICATE KEY(`pk`)

DISTRIBUTED BY HASH(`pk`) BUCKETS 3

PROPERTIES (

"replication_num" = "1",

"enable_persistent_index" = "true",

"replicated_storage" = "true",

"fast_schema_evolution" = "true",

"compression" = "LZ4"

);

-- result:

-- !result

insert into t2 values

(1, [[["10"],["20"],["30"]],[["60"],["5"],["4"]],[["-100","-2"],["-20","10"],["100","23"]]], [[[1.00],[2.00],[3.00]],[[6.00],[5.00],[4.00]],[[-1.00,-2.00],[-2.00,10.00],[100.00,23.00]]]);

-- result:

-- !result

select aad_1 != aas_1 from t2;

-- result:

1

-- !result

-- name: test_array_generate

select array_generate(1, array_length([1,2,3]),1);

-- result:

[1,2,3]

-- !result

select array_generate(1, NULL,1);

-- result:

None

-- !result

select array_generate(NULL,1,1);

-- result:

None

-- !result

select array_generate(1,1,NULL);

-- result:

None

-- !result

select array_generate(1,9);

-- result:

[1,2,3,4,5,6,7,8,9]

-- !result

select array_generate(9,1);

-- result:

[9,8,7,6,5,4,3,2,1]

-- !result

select array_generate(9);

-- result:

[1,2,3,4,5,6,7,8,9]

-- !result

select array_generate(3,3);

-- result:

[3]

-- !result

select array_generate(3,2,1);

-- result:

[]

-- !result

select array_generate("2025-10-01", "2025-10-05",1);

-- result:

["2025-10-01 00:00:00","2025-10-02 00:00:00","2025-10-03 00:00:00","2025-10-04 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2025-10-01", "2025-10-05",interval 1 day);

-- result:

["2025-10-01 00:00:00","2025-10-02 00:00:00","2025-10-03 00:00:00","2025-10-04 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2025-10-01", "2025-10-05",interval 0 day);

-- result:

[]

-- !result

select array_generate("2025-10-01", 10000, interval 0 day);

-- result:

None

-- !result

select array_generate("2025-10-01", "abc", 1);

-- result:

None

-- !result

select array_generate("2025-10-01", "2025-10-05",interval -1 day);

-- result:

E: (1064, 'Getting analyzing error from line 1, column 7 to line 1, column 64. Detail message: array_generate requires step parameter must be non-negative.')

-- !result

select array_generate(DATE"2025-10-01", DATE"2025-10-05",interval 1 day);

-- result:

["2025-10-01","2025-10-02","2025-10-03","2025-10-04","2025-10-05"]

-- !result

select array_generate(DATETIME"2025-10-01", DATETIME"2025-10-05",interval 1 day);

-- result:

["2025-10-01 00:00:00","2025-10-02 00:00:00","2025-10-03 00:00:00","2025-10-04 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2025-10-01", "2027-10-05",interval 1 year);

-- result:

["2025-10-01 00:00:00","2026-10-01 00:00:00","2027-10-01 00:00:00"]

-- !result

select array_generate("2025-10-01", "2027-10-05",interval 1 QUARTER);

-- result:

["2025-10-01 00:00:00","2026-01-01 00:00:00","2026-04-01 00:00:00","2026-07-01 00:00:00","2026-10-01 00:00:00","2027-01-01 00:00:00","2027-04-01 00:00:00","2027-07-01 00:00:00","2027-10-01 00:00:00"]

-- !result

select array_generate("2025-10-01", "2026-10-05",interval 2 MONTH);

-- result:

["2025-10-01 00:00:00","2025-12-01 00:00:00","2026-02-01 00:00:00","2026-04-01 00:00:00","2026-06-01 00:00:00","2026-08-01 00:00:00","2026-10-01 00:00:00"]

-- !result

select array_generate("2025-10-01", "2025-12-05",interval 2 WEEK);

-- result:

["2025-10-01 00:00:00","2025-10-15 00:00:00","2025-10-29 00:00:00","2025-11-12 00:00:00","2025-11-26 00:00:00"]

-- !result

select array_generate("2025-10-01", "2025-10-05",interval 2 day);

-- result:

["2025-10-01 00:00:00","2025-10-03 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2025-10-01 14:28:31", "2025-10-01 23:12:36",interval 3 hour);

-- result:

["2025-10-01 14:28:31","2025-10-01 17:28:31","2025-10-01 20:28:31"]

-- !result

select array_generate("2025-10-01 14:28:31", "2025-10-01 15:12:36",interval 8 MINUTE);

-- result:

["2025-10-01 14:28:31","2025-10-01 14:36:31","2025-10-01 14:44:31","2025-10-01 14:52:31","2025-10-01 15:00:31","2025-10-01 15:08:31"]

-- !result

select array_generate("2025-10-01 14:28:31", "2025-10-01 14:29:36",interval 10 SECOND);

-- result:

["2025-10-01 14:28:31","2025-10-01 14:28:41","2025-10-01 14:28:51","2025-10-01 14:29:01","2025-10-01 14:29:11","2025-10-01 14:29:21","2025-10-01 14:29:31"]

-- !result

select array_generate("2025-10-01 14:28:31", "2025-10-01 14:28:34",interval 500 MILLISECOND);

-- result:

["2025-10-01 14:28:31","2025-10-01 14:28:31.500000","2025-10-01 14:28:32","2025-10-01 14:28:32.500000","2025-10-01 14:28:33","2025-10-01 14:28:33.500000","2025-10-01 14:28:34"]

-- !result

select array_generate("2025-10-01 14:28:31", "2025-10-01 14:28:32.800000",interval 500000 MICROSECOND);

-- result:

["2025-10-01 14:28:31","2025-10-01 14:28:31.500000","2025-10-01 14:28:32","2025-10-01 14:28:32.500000"]

-- !result

select array_generate("2027-10-05", "2025-10-01",interval 1 year);

-- result:

["2027-10-05 00:00:00","2026-10-05 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2027-10-05", "2025-10-01",interval 1 QUARTER);

-- result:

["2027-10-05 00:00:00","2027-07-05 00:00:00","2027-04-05 00:00:00","2027-01-05 00:00:00","2026-10-05 00:00:00","2026-07-05 00:00:00","2026-04-05 00:00:00","2026-01-05 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2026-10-05", "2025-10-01",interval 2 MONTH);

-- result:

["2026-10-05 00:00:00","2026-08-05 00:00:00","2026-06-05 00:00:00","2026-04-05 00:00:00","2026-02-05 00:00:00","2025-12-05 00:00:00","2025-10-05 00:00:00"]

-- !result

select array_generate("2025-12-05", "2025-10-01",interval 2 WEEK);

-- result:

["2025-12-05 00:00:00","2025-11-21 00:00:00","2025-11-07 00:00:00","2025-10-24 00:00:00","2025-10-10 00:00:00"]

-- !result

select array_generate("2025-10-05", "2025-10-01",interval 2 day);

-- result:

["2025-10-05 00:00:00","2025-10-03 00:00:00","2025-10-01 00:00:00"]

-- !result

select array_generate("2025-10-01 23:12:36", "2025-10-01 14:28:31",interval 3 hour);

-- result:

["2025-10-01 23:12:36","2025-10-01 20:12:36","2025-10-01 17:12:36"]

-- !result

select array_generate("2025-10-01 15:12:36", "2025-10-01 14:28:31",interval 8 MINUTE);

-- result:

["2025-10-01 15:12:36","2025-10-01 15:04:36","2025-10-01 14:56:36","2025-10-01 14:48:36","2025-10-01 14:40:36","2025-10-01 14:32:36"]

-- !result

select array_generate("2025-10-01 14:29:36", "2025-10-01 14:28:31",interval 10 SECOND);

-- result:

["2025-10-01 14:29:36","2025-10-01 14:29:26","2025-10-01 14:29:16","2025-10-01 14:29:06","2025-10-01 14:28:56","2025-10-01 14:28:46","2025-10-01 14:28:36"]

-- !result

select array_generate("2025-10-01 14:28:34", "2025-10-01 14:28:31",interval 500 MILLISECOND);

-- result:

["2025-10-01 14:28:34","2025-10-01 14:28:33.500000","2025-10-01 14:28:33","2025-10-01 14:28:32.500000","2025-10-01 14:28:32","2025-10-01 14:28:31.500000","2025-10-01 14:28:31"]

-- !result

select array_generate("2025-10-01 14:28:32.800000", "2025-10-01 14:28:31",interval 500000 MICROSECOND);

-- result:

["2025-10-01 14:28:32.800000","2025-10-01 14:28:32.300000","2025-10-01 14:28:31.800000","2025-10-01 14:28:31.300000"]

-- !result

select array_generate("2025-10-05", "2025-10-01",interval -1 day);

-- result:

E: (1064, 'Getting analyzing error from line 1, column 7 to line 1, column 64. Detail message: array_generate requires step parameter must be non-negative.')

-- !result

select array_generate("2025-10-01 14:28:32.800000", "2025-10-01 14:28:31",NULL);

-- result:

E: (1064, 'Getting analyzing error from line 1, column 7 to line 1, column 78. Detail message: array_generate requires step parameter must be a constant integer.')

-- !result

-- name: test_array_repeat

select array_repeat(1,5);

-- result:

[1,1,1,1,1]

-- !result

select array_repeat([1,2],3);

-- result:

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

-- !result

select array_repeat(1,-1);

-- result:

[]

-- !result

CREATE TABLE IF NOT EXISTS repeat_test (COLA INT, COLB INT) PROPERTIES ("replication_num"="1");

-- result:

-- !result

INSERT INTO repeat_test (COLA, COLB) VALUES (1, 3), (NULL, 3), (2, NULL);

-- result:

-- !result

SELECT array_repeat(COLA, COLB) FROM repeat_test ORDER BY COLA;

-- result:

[None,None,None]

[1,1,1]

None

-- !result

-- name: test_array_flatten

select array_flatten([[1, 2], [1, 4]]);

-- result:

[1,2,1,4]

-- !result

select array_flatten([[[1],[2]],[[3],[4]]]);

-- result:

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

-- !result

CREATE TABLE IF NOT EXISTS flatten_test (COLA INT, COLB ARRAY<ARRAY<INT>>) PROPERTIES ("replication_num"="1");

-- result:

-- !result

INSERT INTO flatten_test (COLA, COLB) VALUES (1, [[1, 2], [1, 4]]), (2, NULL), (3, [[5], [6, 7, 8], [9]]), (4, [[2, 3], [4, 5, 6], NULL]);

-- result:

-- !result

SELECT array_flatten(COLB) FROM flatten_test ORDER BY COLA;

-- result:

[1,2,1,4]

None

[5,6,7,8,9]

[2,3,4,5,6]

-- !result

SELECT array_slice(array_flatten(COLB), 1, 2) FROM flatten_test ORDER BY COLA;

-- result:

[1,2]

None

[5,6]

[2,3]

-- !result

SELECT array_flatten(array_slice(COLB, 1, 2)) FROM flatten_test ORDER BY COLA;

-- result:

[1,2,1,4]

None

[5,6,7,8]

[2,3,4,5,6]

-- !result

CREATE TABLE IF NOT EXISTS flatten_one_layer_arr_test (COLA INT, COLB ARRAY<INT>) PROPERTIES ("replication_num"="1");

-- result:

-- !result

INSERT INTO flatten_one_layer_arr_test (COLA, COLB) VALUES (1, [1, 2, 3]);

-- result:

-- !result

SELECT array_flatten(COLB) FROM flatten_one_layer_arr_test ORDER BY COLA;

-- result:

E: (1064, 'Getting analyzing error from line 1, column 7 to line 1, column 25. Detail message: The only one input of array_flatten should be an array of arrays, rather than array<int(11)>.')

-- !result

SELECT array_slice(array_flatten(COLB), 1, 2) FROM flatten_one_layer_arr_test ORDER BY COLA;

-- result:

E: (1064, 'Getting analyzing error from line 1, column 19 to line 1, column 37. Detail message: The only one input of array_flatten should be an array of arrays, rather than array<int(11)>.')

-- !result

SELECT array_flatten(array_slice(COLB, 1, 2)) FROM flatten_one_layer_arr_test ORDER BY COLA;

-- result:

E: (1064, 'Getting analyzing error from line 1, column 7 to line 1, column 44. Detail message: The only one input of array_flatten should be an array of arrays, rather than array<int(11)>.')

-- !result

-- name: null_or_empty

select null_or_empty([1, 2]);

-- result:

0

-- !result

select null_or_empty(null);

-- result:

1

-- !result

select null_or_empty([]);

-- result:

0

-- !result

select null_or_empty([[1,2],[1]]);

-- result:

1

-- !result

CREATE TABLE IF NOT EXISTS null_or_empty_test (COLA INT, COLB ARRAY<INT>) PROPERTIES ("replication_num"="1");

-- result:

-- !result

INSERT INTO null_or_empty_test (COLA, COLB) VALUES (1, []), (2, NULL), (3, [1,2]);

-- result:

-- !result

SELECT null_or_empty(COLB) FROM flatten_test ORDER BY COLA;

-- result:

1

1

0

-- !result