forked from StarRocks/starrocks
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtest_array_remove
More file actions
125 lines (110 loc) · 9.01 KB
/
Copy pathtest_array_remove
File metadata and controls
125 lines (110 loc) · 9.01 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_remove_all_type @mac @no_arrow_flight_sql
CREATE TABLE test_array_remove (
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_decimalv2 ARRAY<DECIMALV2(10, 2)>,
array_decimal32 ARRAY<DECIMAL32(9, 2)>,
array_decimal64 ARRAY<DECIMAL64(18, 2)>,
array_decimal128 ARRAY<DECIMAL128(38, 10)>,
array_float ARRAY<FLOAT>,
array_double ARRAY<DOUBLE>,
array_varchar ARRAY<VARCHAR(100)>,
array_date ARRAY<DATE>,
array_datetime ARRAY<DATETIME>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES (
"replication_num" = "1"
);
INSERT INTO test_array_remove VALUES
(1, [true, false, NULL], [1, 2, NULL], [100, 200, NULL], [10, 20, NULL], [1000, 2000, NULL], [1234567890123456789, NULL, NULL],
[12345.67, 89012.34, NULL], [123.45, 678.90, NULL], [12345678.90, 9876543.21, NULL], [1234567890.1234567890, NULL, NULL],
[1.23, 4.56, NULL], [123.456, 789.012, NULL], ['hello', NULL, 'starrocks'],
['2025-01-01', '2025-01-02', NULL], ['2025-01-01 12:00:00', NULL, '2025-01-02 14:00:00']),
(2, NULL, [5, NULL, 3], [500, 400, NULL], [50, 40, NULL], [5000, NULL, 3000], [987654321987654321, NULL, NULL],
[56789.01, NULL, 45678.12], [345.67, NULL, 234.56], [56789012.34, NULL, 34567890.12], [2345678901.2345678901, NULL, NULL],
[2.34, NULL, 1.23], [234.567, NULL, 123.456], [NULL, 'array', 'test'],
[NULL, '2024-12-31', '2024-12-30'], ['2024-12-31 23:59:59', '2024-12-30 12:00:00', NULL]),
(3, [true, NULL, false], [1, 6, NULL], [150, NULL, 250], [15, NULL, 35], [1100, NULL, 2200], [2345678901234567890, NULL, NULL],
[67890.12, NULL, 56789.01], [456.78, NULL, 345.67], [67890123.45, NULL, 56789012.34], [3456789012.3456789012, NULL, NULL],
[1.11, NULL, 3.33], [222.333, NULL, 333.444], ['foo', 'bar', NULL],
['2025-01-03', NULL, '2025-01-04'], ['2025-01-03 16:00:00', '2025-01-04 18:00:00', NULL]),
(4, [NULL, true, false], [NULL, 20, 10], [110, 220, NULL], [NULL, 12, 24], [1200, NULL, 2400], [3456789012345678901, NULL, NULL],
[78901.23, 67890.12, NULL], [567.89, 456.78, NULL], [78901234.56, 67890123.45, NULL], [4567890123.4567890123, NULL, NULL],
[NULL, 5.55, 4.44], [NULL, 777.888, 666.777], ['NULL', 'banana', 'apple'],
[NULL, '2025-01-05', '2025-01-06'], [NULL, '2025-01-06 20:00:00', '2025-01-05 18:00:00']),
(5, [false, NULL, true], [10, NULL, 30], [300, 400, NULL], [70, NULL, 90], [4000, NULL, 6000], [987654321234567890, NULL, NULL],
[123456.78, NULL, 876543.21], [678.90, NULL, 789.01], [9876543.21, NULL, 1234567.89], [5678901234.5678901234, NULL, NULL],
[3.21, 4.32, NULL], [111.222, NULL, 333.444], ['dog', 'cat', NULL],
['2025-01-07', '2025-01-08', NULL], ['2025-01-07 10:00:00', NULL, '2025-01-08 15:00:00']),
(6, [NULL, true, true], [NULL, 40, 50], [450, 500, NULL], [80, 100, NULL], [1500, NULL, 2500], [765432198765432109, NULL, NULL],
[34567.89, NULL, 12345.67], [123.45, NULL, 678.90], [54321.12, NULL, 12345.67], [7654321098.7654321098, NULL, NULL],
[NULL, 6.54, 7.65], [555.666, NULL, 444.333], [NULL, 'bird', 'fish'],
['2025-01-09', '2025-01-10', NULL], ['2025-01-09 12:00:00', NULL, '2025-01-10 18:00:00']),
(7, [false, false, NULL], [70, NULL, 90], [650, NULL, 750], [120, 140, NULL], [8000, NULL, 9000], [543210987654321098, NULL, NULL],
[45678.12, NULL, 23456.78], [234.56, NULL, 456.78], [67890123.45, NULL, 34567890.12], [4321098765.4321098765, NULL, NULL],
[7.89, 8.90, NULL], [333.222, NULL, 111.000], ['lion', NULL, 'tiger'],
['2025-01-11', '2025-01-12', NULL], ['2025-01-11 20:00:00', NULL, '2025-01-12 22:00:00']),
(8, [true, NULL, false], [5, 15, NULL], [50, NULL, 150], [25, NULL, 75], [4500, NULL, 5500], [321098765432109876, NULL, NULL],
[23456.78, NULL, 12345.67], [345.67, NULL, 456.78], [8901234.56, NULL, 7890123.45], [2109876543.2109876543, NULL, NULL],
[5.67, NULL, 4.56], [666.555, NULL, 222.111], [NULL, 'grape', 'pear'],
['2025-01-13', NULL, '2025-01-14'], ['2025-01-13 23:59:59', '2025-01-14 12:00:00', NULL]),
(9, [false, true, NULL], [25, 35, NULL], [350, NULL, 450], [100, 200, NULL], [6000, NULL, 7000], [654321098765432109, NULL, NULL],
[67890.12, NULL, 34567.89], [456.78, NULL, 234.56], [34567890.12, NULL, 23456789.01], [8765432109.8765432109, NULL, NULL],
[9.87, NULL, 8.76], [444.333, NULL, 555.222], ['watermelon', NULL, 'kiwi'],
[NULL, '2025-01-15', '2025-01-16'], ['2025-01-15 12:00:00', NULL, '2025-01-16 18:00:00']),
(10, [true, true, NULL], [50, 70, NULL], [750, 850, NULL], [300, 400, NULL], [10000, NULL, 12000], [789012345678901234, NULL, NULL],
[78901.23, NULL, 67890.12], [567.89, NULL, 456.78], [12345678.90, NULL, 9876543.21], [1234567890.1234567890, NULL, NULL],
[NULL, 1.11, 2.22], [777.888, 999.000, NULL], ['blueberry', 'cherry', NULL],
['2025-01-17', '2025-01-18', NULL], [NULL, '2025-01-17 10:00:00', '2025-01-18 20:00:00']);
SELECT id, array_remove(array_boolean, true) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_boolean, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_boolean, false) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_tinyint, 5) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_tinyint, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_tinyint, 20) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_smallint, 100) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_smallint, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_smallint, 300) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_int, 50) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_int, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_int, 90) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_bigint, 4000) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_bigint, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_bigint, 8000) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_largeint, 1234567890123456789) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_largeint, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_largeint, 765432198765432109) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimalv2, 12345.67) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimalv2, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimalv2, 56789.01) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal32, 123.45) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal32, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal32, 567.89) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal64, 12345678.90) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal64, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal64, 7890123.45) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal128, 1234567890.1234567890) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal128, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_decimal128, 8765432109.8765432109) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_float, 1.23) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_float, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_float, 7.89) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_double, 123.456) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_double, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_double, 444.333) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_varchar, 'hello') AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_varchar, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_varchar, 'starrocks') AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_date, '2025-01-01') AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_date, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_date, '2025-01-13') AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_datetime, '2025-01-01 12:00:00') AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_datetime, NULL) AS result FROM test_array_remove ORDER BY id;
SELECT id, array_remove(array_datetime, '2025-01-17 10:00:00') AS result FROM test_array_remove ORDER BY id;