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