-- 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");

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']);

SELECT id, array_intersect(array_boolean, [true, false]) AS intersect_boolean FROM test_array_intersect order by id;

SELECT id, array_intersect(array_tinyint, [2, 8, 10]) AS intersect_tinyint FROM test_array_intersect order by id;

SELECT id, array_intersect(array_smallint, [100, 300, 900]) AS intersect_smallint FROM test_array_intersect order by id;

SELECT id, array_intersect(array_int, [123, 303, 707]) AS intersect_int FROM test_array_intersect order by id;

SELECT id, array_intersect(array_bigint, [1000000000, 6000000000, 10000000000]) AS intersect_bigint FROM test_array_intersect order by id;

SELECT id, array_intersect(array_largeint, [1234, 5678]) AS intersect_largeint FROM test_array_intersect order by id;

SELECT id, array_intersect(array_float, [1.1, 3.3, 9.9]) AS intersect_float FROM test_array_intersect order by id;

SELECT id, array_intersect(array_double, [1.11, 4.44, 10.01]) AS intersect_double FROM test_array_intersect order by id;

SELECT id, array_intersect(array_decimalv2, [123.45, 456.78, 789.01]) AS intersect_decimalv2 FROM test_array_intersect order by id;

SELECT id, array_intersect(array_decimal32, [123.45, 890.12]) AS intersect_decimal32 FROM test_array_intersect order by id;

SELECT id, array_intersect(array_decimal64, [123.4567, 345.6789]) AS intersect_decimal64 FROM test_array_intersect order by id;

SELECT id, array_intersect(array_decimal128, [1234567890.1234567890, 3344556677.8899001122]) AS intersect_decimal128 FROM test_array_intersect order by id;

SELECT id, array_intersect(array_varchar, ['apple', 'banana', 'cherry']) AS intersect_varchar FROM test_array_intersect order by id;

SELECT id, array_intersect(array_date, ['2025-01-01', '2025-01-08']) AS intersect_date FROM test_array_intersect order by id;

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;