-- Migrated from DuckDB test style: test array aggregation
-- Test ARRAY_AGG function
-- Test with integers
CREATE TABLE integers(i INTEGER, g INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO integers VALUES (1, 1, 1000), (2, 1, 2000), (3, 1, 3000), (4, 2, 4000), (5, 2, 5000);

Affected Rows: 5

-- Basic array aggregation
SELECT array_agg(i) FROM integers;

+-----------------------+
| array_agg(integers.i) |
+-----------------------+
| [1, 2, 3, 4, 5]       |
+-----------------------+

-- Array aggregation with GROUP BY
SELECT g, array_agg(i) FROM integers GROUP BY g ORDER BY g;

+---+-----------------------+
| g | array_agg(integers.i) |
+---+-----------------------+
| 1 | [1, 2, 3]             |
| 2 | [4, 5]                |
+---+-----------------------+

-- Test with ORDER BY
SELECT array_agg(i ORDER BY i DESC) FROM integers;

+--------------------------------------------------------------+
| array_agg(integers.i) ORDER BY [integers.i DESC NULLS FIRST] |
+--------------------------------------------------------------+
| [5, 4, 3, 2, 1]                                              |
+--------------------------------------------------------------+

SELECT g, array_agg(i ORDER BY i DESC) FROM integers GROUP BY g ORDER BY g;

+---+--------------------------------------------------------------+
| g | array_agg(integers.i) ORDER BY [integers.i DESC NULLS FIRST] |
+---+--------------------------------------------------------------+
| 1 | [3, 2, 1]                                                    |
| 2 | [5, 4]                                                       |
+---+--------------------------------------------------------------+

-- Test with strings
CREATE TABLE strings(s VARCHAR, g INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO strings VALUES
    ('apple', 1, 1000), ('banana', 1, 2000), ('cherry', 2, 3000),
    ('date', 2, 4000), ('elderberry', 1, 5000);

Affected Rows: 5

SELECT array_agg(s) FROM strings;

+-------------------------------------------+
| array_agg(strings.s)                      |
+-------------------------------------------+
| [apple, banana, cherry, date, elderberry] |
+-------------------------------------------+

SELECT g, array_agg(s ORDER BY s) FROM strings GROUP BY g ORDER BY g;

+---+----------------------------------------------------------+
| g | array_agg(strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+---+----------------------------------------------------------+
| 1 | [apple, banana, elderberry]                              |
| 2 | [cherry, date]                                           |
+---+----------------------------------------------------------+

-- Test with NULL values
INSERT INTO strings VALUES (NULL, 1, 6000), ('fig', NULL, 7000);

Affected Rows: 2

SELECT array_agg(s) FROM strings WHERE s IS NOT NULL;

+------------------------------------------------+
| array_agg(strings.s)                           |
+------------------------------------------------+
| [apple, banana, cherry, date, elderberry, fig] |
+------------------------------------------------+

SELECT g, array_agg(s) FROM strings WHERE g IS NOT NULL GROUP BY g ORDER BY g;

+---+-------------------------------+
| g | array_agg(strings.s)          |
+---+-------------------------------+
| 1 | [apple, banana, elderberry, ] |
| 2 | [cherry, date]                |
+---+-------------------------------+

-- Test with DISTINCT
SELECT array_agg(DISTINCT s ORDER BY s) FROM strings WHERE s IS NOT NULL;

+-------------------------------------------------------------------+
| array_agg(DISTINCT strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+-------------------------------------------------------------------+
| [apple, banana, cherry, date, elderberry, fig]                    |
+-------------------------------------------------------------------+

-- Test empty result
SELECT array_agg(i) FROM integers WHERE i > 100;

+-----------------------+
| array_agg(integers.i) |
+-----------------------+
|                       |
+-----------------------+

-- Test with doubles
CREATE TABLE doubles(d DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO doubles VALUES (1.1, 1000), (2.2, 2000), (3.3, 3000), (4.4, 4000);

Affected Rows: 4

SELECT array_agg(d ORDER BY d) FROM doubles;

+----------------------------------------------------------+
| array_agg(doubles.d) ORDER BY [doubles.d ASC NULLS LAST] |
+----------------------------------------------------------+
| [1.1, 2.2, 3.3, 4.4]                                     |
+----------------------------------------------------------+

-- cleanup
DROP TABLE integers;

Affected Rows: 0

DROP TABLE strings;

Affected Rows: 0

DROP TABLE doubles;

Affected Rows: 0