forked from StarRocks/starrocks
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtest_array_map
More file actions
125 lines (109 loc) · 2.63 KB
/
Copy pathtest_array_map
File metadata and controls
125 lines (109 loc) · 2.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- name: test_array_map_1
-- Prepare Table and Data.
CREATE TABLE t1 (
k1 bigint,
c1 array < varchar(65536) >
) ENGINE = OLAP
DUPLICATE KEY(k1) PROPERTIES (
"replication_num" = "1"
);
CREATE TABLE t2 (
k1 bigint,
c1 bigint
) ENGINE = OLAP
DUPLICATE KEY(k1) PROPERTIES (
"replication_num" = "1"
);
insert into t1
values
(1, ["1","2"] ),
(2, ["0","2","1"] ),
(3, ["0","2","1"] ),
(4, ["1","2"] ),
(5, ["0","2","1"] ),
(6, ["0","2","1","1"]),
(7, ["0","2","1"] ),
(8, ["1","2"] ),
(9, ["L","2","1"] ),
(10, ["1","2"] );
insert into t2
values
(1, 1),
(2, 1),
(3, 3),
(4, 5);
-- Query.
with w1 as (
select
k1, c1, array_map (x -> true, c1) as c2
from
t1
)
select
w1.*
from
w1
join [broadcast] t2 using(k1)
where
array_sum(w1.c1) <= t2.c1
order by
w1.k1;
-- union const with array_map won't crash
INSERT INTO t1 (k1, c1)
VALUES
(1, ARRAY_MAP(
x -> CAST(x AS STRING),
ARRAY_GENERATE(1, 1000)
)),
(2, ARRAY_MAP(
x -> CAST(x AS STRING),
ARRAY_GENERATE(1, 1000)
)),
(3, ARRAY_MAP(
x -> CAST(x AS STRING),
ARRAY_GENERATE(1, 1000)
)),
(4, ARRAY_MAP(
x -> CAST(x AS STRING),
ARRAY_GENERATE(1, 1000)
)),
(5, ARRAY_MAP(
x -> CAST(x AS STRING),
ARRAY_GENERATE(1, 1000)
));
-- test array_map with join
CREATE TABLE table1 (
id INT,
arr_largeint ARRAY<INT> NOT NULL
)PROPERTIES ("replication_num" = "1");
INSERT INTO table1 (id, arr_largeint) VALUES
(1, [1, 2]),
(2, [3, 4, 5]),
(3, [6]);
CREATE TABLE table2 (
id INT,
arr_str ARRAY<INT> NOT NULL
) PROPERTIES ("replication_num" = "1");
INSERT INTO table2 (id, arr_str) VALUES
(1, [1, 2, 3]),
(2, [4, 5]),
(3, [6, 7, 8, 9]);
SELECT t1.id AS t1_id, t2.id AS t2_id, t1.arr_largeint
FROM table1 t1
LEFT JOIN[broadcast] table2 t2
ON t1.id = t2.id
AND array_length(array_map(x -> x + array_length(t2.arr_str), t1.arr_largeint)) >= 2;
WITH `CTE` AS (
SELECT TRUE AS bool_1, TRUE AS bool_2, TRUE AS bool_3, ["a"] AS arr
UNION ALL
SELECT TRUE AS bool_1, TRUE AS bool_2, TRUE AS bool_3, ["a"] AS arr
) SELECT ARRAY_MAP((arg)->`bool_1` AND `bool_2` AND `bool_3`, arr), ARRAY_MAP((arg)->`bool_1` AND `bool_3` AND `bool_2`, arr) FROM `CTE`;
-- test array_map with const child
with t1 as (
select parse_json('[{"open_id": "aaa", "num": 1},{"open_id": "bbb", "num": 2},{"open_id": "ccc", "num": 3}]') as price_list
),t2 as (
select price_list,array_map(x -> get_json_string(x,'$.open_id'), cast(price_list as array<json>) ) as fields
from t1
)
select * from t2
where array_contains(fields,'bbb');