forked from StarRocks/starrocks
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtest_array_intersect
More file actions
131 lines (131 loc) · 4.46 KB
/
Copy pathtest_array_intersect
File metadata and controls
131 lines (131 loc) · 4.46 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
126
127
128
129
130
131
-- name: test_array_intersect @mac
CREATE TABLE test_array_intersect (
id INT,
array_boolean ARRAY<BOOLEAN>,
array_tinyint ARRAY<TINYINT>,
array_smallint ARRAY<SMALLINT>,
array_int ARRAY<INT>,
array_bigint ARRAY<BIGINT>,
array_largeint ARRAY<LARGEINT>,
array_float ARRAY<FLOAT>,
array_double ARRAY<DOUBLE>,
array_decimalv2 ARRAY<DECIMALV2>,
array_decimal32 ARRAY<DECIMAL(10, 2)>,
array_decimal64 ARRAY<DECIMAL(18, 4)>,
array_decimal128 ARRAY<DECIMAL(27, 9)>,
array_varchar ARRAY<VARCHAR(50)>,
array_date ARRAY<DATE>,
array_datetime ARRAY<DATETIME>
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES("replication_num" = "1");
-- result:
-- !result
INSERT INTO test_array_intersect VALUES
(1, [true, false], [1, 2, 3], [100, 200, 300], [1000, 2000, 3000], [10000, 20000, 30000],
[123456789012345678901234567890, 123456789012345678901234567891], [1.1, 2.2, 3.3],
[10.11, 20.22, 30.33], [123.45, 456.78, 789.01], [123.45, 890.12], [1234.5678, 2345.6789],
[1234567890.123456789, 3344556677.889900112], ['apple', 'banana', 'cherry'],
['2025-01-01', '2025-01-08'], ['2025-01-01 12:00:00', '2025-01-08 19:00:00']),
(2, [false], [5, 6, 7], [400, 500, 600], [4000, 5000, 6000], [40000, 50000, 60000],
[123456789012345678901234567892, 123456789012345678901234567893], [4.4, 5.5, 6.6],
[40.44, 50.55, 60.66], [987.65, 543.21, 123.45], [567.89, 234.56], [9876.5432, 1234.5678],
[9876543210.987654321, 1122334455.667788990], ['grape', 'melon', 'pear'],
['2025-01-02', '2025-01-09'], ['2025-01-02 13:00:00', '2025-01-09 20:00:00']),
(3, [true, true], [8, 9], [700, 800], [7000, 8000], [70000, 80000],
[123456789012345678901234567894], [7.7, 8.8], [70.77, 80.88], [246.80, 369.12],
[678.90, 987.65], [3456.7890, 4567.8901], [4567890123.456789012],
['peach', 'plum'], ['2025-01-03'], ['2025-01-03 14:00:00']);
-- result:
-- !result
SELECT id, array_intersect(array_boolean, [true, false]) AS intersect_boolean FROM test_array_intersect order by id;
-- result:
1 [0,1]
2 [0]
3 [1]
-- !result
SELECT id, array_intersect(array_tinyint, [2, 8, 10]) AS intersect_tinyint FROM test_array_intersect order by id;
-- result:
1 [2]
2 []
3 [8]
-- !result
SELECT id, array_intersect(array_smallint, [100, 300, 900]) AS intersect_smallint FROM test_array_intersect order by id;
-- result:
1 [300,100]
2 []
3 []
-- !result
SELECT id, array_intersect(array_int, [123, 303, 707]) AS intersect_int FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_bigint, [1000000000, 6000000000, 10000000000]) AS intersect_bigint FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_largeint, [1234, 5678]) AS intersect_largeint FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_float, [1.1, 3.3, 9.9]) AS intersect_float FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_double, [1.11, 4.44, 10.01]) AS intersect_double FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_decimalv2, [123.45, 456.78, 789.01]) AS intersect_decimalv2 FROM test_array_intersect order by id;
-- result:
1 [123.450000000,789.010000000,456.780000000]
2 [123.450000000]
3 []
-- !result
SELECT id, array_intersect(array_decimal32, [123.45, 890.12]) AS intersect_decimal32 FROM test_array_intersect order by id;
-- result:
1 [123.45,890.12]
2 []
3 []
-- !result
SELECT id, array_intersect(array_decimal64, [123.4567, 345.6789]) AS intersect_decimal64 FROM test_array_intersect order by id;
-- result:
1 []
2 []
3 []
-- !result
SELECT id, array_intersect(array_decimal128, [1234567890.1234567890, 3344556677.8899001122]) AS intersect_decimal128 FROM test_array_intersect order by id;
-- result:
1 [1234567890.1234567890]
2 []
3 []
-- !result
SELECT id, array_intersect(array_varchar, ['apple', 'banana', 'cherry']) AS intersect_varchar FROM test_array_intersect order by id;
-- result:
1 ["cherry","apple","banana"]
2 []
3 []
-- !result
SELECT id, array_intersect(array_date, ['2025-01-01', '2025-01-08']) AS intersect_date FROM test_array_intersect order by id;
-- result:
1 ["2025-01-01","2025-01-08"]
2 []
3 []
-- !result
SELECT id, array_intersect(array_datetime, ['2025-01-01 12:00:00', '2025-01-08 19:00:00']) AS intersect_datetime FROM test_array_intersect order by id;
-- result:
1 ["2025-01-01 12:00:00","2025-01-08 19:00:00"]
2 []
3 []
-- !result