# name: test/sql/function/list/lambdas/reduce_initial.test
# description: Test list_reduce function with an intial value
# group: [lambdas]

statement ok
SET lambda_syntax='DISABLE_SINGLE_ARROW'

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x + y, 100);
----
106

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x * y, -1);
----
-6

query I
SELECT list_reduce([100, 10, 1], lambda x, y, i: x - y - i, 1000);
----
883

query I
SELECT list_reduce([1, 2, 3], lambda x, y: y - x, -1);
----
3

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x - y, 10);
----
4

query I
SELECT list_reduce([1, 2, 3], lambda x, y, i: x + y + i, -1);
----
11

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x + y, NULL);
----
NULL

query I
SELECT list_reduce([NULL], lambda x, y, i: x + y + i, 100);
----
NULL

query I
SELECT list_reduce(NULL, lambda x, y, i: x + y + i, 100);
----
NULL

query I
SELECT list_reduce(['Once', 'upon', 'a', 'time'], lambda x, y: x || ' ' || y, '-->');
----
--> Once upon a time

query I
SELECT list_reduce([], lambda x, y: x + y, 100);
----
100

query I
SELECT list_reduce(['a', 'b', 'c'], lambda x, y: x || y, NULL);
----
NULL

#errors on simple reduce with initial value
statement error
SELECT list_reduce([1, 2, 3], lambda x, y: (x * y), 'i dare you to cast me');
----
Binder Error

statement error
SELECT list_reduce([1, 2], lambda x: x, 100);
----
list_reduce expects a function with 2 or 3 arguments

statement error
SELECT list_reduce([1, 2], NULL, 100);
----
Invalid lambda expression!

statement error
SELECt list_reduce([1, 2], (len('abc') AS x, y) - > x + y, 100)
----
Parser Error: syntax error at or near "AS"

# simple reduce on a table

statement ok
CREATE table t1(l int[], initial int);

statement ok
INSERT INTO t1 VALUES ([1, 2, 3], 100);

statement ok
INSERT INTO t1 VALUES ([666], 1000);

statement ok
INSERT INTO t1 VALUES ([1, 2, 3], NULL);

statement ok
INSERT INTO t1 VALUES (NULL, 2);

statement ok
INSERT INTO t1 VALUES ([44, 55], 3);

statement ok
INSERT INTO t1 VALUES ([-1, NULL, -2], 4);

query I
SELECT list_reduce(l, lambda x, y: x + y, initial) FROM t1;
----
106
1666
NULL
NULL
102
NULL

query I
SELECT list_reduce(l, lambda x, y, i: x + y + i, initial) FROM t1;
----
112
1667
NULL
NULL
105
NULL

statement ok
INSERT INTO t1 VALUES ([], 100);

statement error
SELECT list_reduce(l, lambda x, y: x + y) FROM t1;
----
Cannot perform list_reduce on an empty input list

query I
SELECT list_reduce(l, lambda x, y: x + y, initial) FROM t1;
----
106
1666
NULL
NULL
102
NULL
100

statement ok
DROP TABLE t1;

# reduce on a table with a list of strings

statement ok
CREATE TABLE t1 (l varchar[], initial varchar);

statement ok
INSERT INTO t1 VALUES (['a', 'b'], '1:'), (NULL, '2:'), (['e', 'f'], '3:');

query I
SELECT list_reduce(l, lambda x, y: x || ' ' || y, initial) FROM t1;
----
1: a b
NULL
3: e f

statement ok
INSERT INTO t1 values (['h', NULL], '4:'), (['i', 'j'], '5:');

query I
SELECT list_reduce(l, lambda x, y: x || ' ' || y, initial) FROM t1;
----
1: a b
NULL
3: e f
NULL
5: i j

statement ok
INSERT INTO t1 values (['x', 'y'], NULL);

query I
SELECT list_reduce(l, lambda x, y: x || ' ' || y, initial) FROM t1;
----
1: a b
NULL
3: e f
NULL
5: i j
NULL

statement ok
INSERT INTO t1 VALUES ([], '7:');

statement error
SELECT list_reduce(l, lambda x, y: x || ' ' || y) FROM t1;
----
Cannot perform list_reduce on an empty input list

query I
SELECT list_reduce(l, lambda x, y: x || ' ' || y, initial) FROM t1;
----
1: a b
NULL
3: e f
NULL
5: i j
NULL
7:

# reduce on a string only using the RHS

statement ok
CREATE TABLE right_only (v varchar[], i int);

statement ok
INSERT INTO right_only VALUES (['blue', 'babbling', 'brook'], 1), (['dogs', 'doing', 'dishes'], 2), (['she', 'sells', 'seashells'], 3);

query I
SELECT list_reduce(v, lambda x, y: y[i]) FROM right_only;
----
b
i
a

# nested functions with ints

query I
SELECT list_reduce([1, 2, 3], lambda x, y: list_reduce([4, 5, 6], lambda a, b: x + y + a + b, 100), 1000);
----
28549

statement error
SELECT list_reduce([1, 2, 3], lambda x, y: list_reduce([], lambda a, b: x + y + a + b), 1000);
----
Cannot perform list_reduce on an empty input list

query I
SELECT list_reduce([1, 2, 3], lambda x, y, x_i: list_reduce([4, 5, 6], lambda a, b, a_i: x + y + a + b + x_i + a_i, 100), 1000);
----
28681

statement error
SELECT list_reduce([1, 2, 3], lambda x, y, x_i: list_reduce([], lambda a, b, a_i: x + y + a + b + x_i + a_i), 1000);
----
Cannot perform list_reduce on an empty input list

query I
SELECT list_reduce([[10, 20], [30, 40], [50, 60]], lambda x, y: list_pack(list_reduce(x, lambda l, m: l + m) + list_reduce(y, lambda n, o: n + o)), [100, 200]);
----
[510]

query I
SELECT list_reduce([[1,2,3], [4,5,6], [7,8,9]], lambda x, y: list_pack(list_reduce(x, lambda l, m: l + m) + list_reduce(y, lambda n, o: n + o)), [100]);
----
[145]

query I
SELECT list_reduce([[10, 20], [30, 40], NULL, [NULL, 60], NULL], lambda x, y: list_pack(list_reduce(x, lambda l, m: l + m) + list_reduce(y, lambda n, o: n + o)), [100]);
----
[NULL]

# nested functions with strings

query I
SELECT list_reduce(['a', 'b'], lambda x, y: list_reduce(['1', '2'], lambda a, b: x || y || a || b, 'B'), 'A');
----
AaAaB12bAaAaB12bB12

query I
SELECT list_reduce([['a', 'b'], ['c', 'd'], ['e', 'f']], lambda x, y: list_pack(list_reduce(x, lambda a, b: a || b) || list_reduce(y, lambda c, d: c || d)), ['->']);
----
[->abcdef]

# nested functions in a table with ints

statement ok
CREATE TABLE nested (n integer[], l integer[], initial integer);

statement ok
INSERT INTO nested VALUES ([1, 2, 3], [4, 5, 6], 100), (NULL, NULL, 100), (NULL, [110, 111, 112], 44), ([77, 88, 99], [55, 66, NULL], 1);

query I
SELECT list_reduce(n, lambda x, y: list_reduce(l, lambda a, b: x + y + a + b), initial) FROM nested;
----
927
NULL
NULL
NULL

query I
SELECT list_reduce(n, lambda x, y, x_i: list_reduce(l, lambda a, b, a_i: x + y + a + b + x_i + a_i), initial) FROM nested;
----
984
NULL
NULL
NULL

query I
SELECT list_reduce(n, lambda x, y, x_i: list_reduce(l, lambda a, b, a_i: x + y + x_i < a + b + a_i), initial) FROM nested;
----
1
NULL
NULL
NULL

statement ok
INSERT INTO nested VALUES ([4, 5, 6], [], -1);

statement error
SELECT list_reduce(n, lambda x, y: list_reduce(l, lambda a, b: x + y + a + b), initial) FROM nested;
----
Cannot perform list_reduce on an empty input list

statement ok
DROP TABLE nested;

statement ok
CREATE TABLE nested (n integer[][], initial integer[]);

statement ok
INSERT INTO nested VALUES ([[10, 20], [30, 40], [50, 60]], [100]), ([[1,2,3], [4,5,6], [7,8,9]], [1000]), (NULL, [-1]), ([[NULL, 60], [70, NULL], [NULL, NULL]], [NULL]);

query I
SELECT list_reduce(n, lambda x, y: list_pack(list_reduce(x, lambda l, m: l + m) + list_reduce(y, lambda j, k: j + k)), initial) from nested;
----
[310]
[1045]
NULL
[NULL]

statement ok
INSERT INTO nested VALUES ([[4, 5, 6], []], [9]);

statement error
SELECT list_reduce(n, lambda x, y: list_pack(list_reduce(x, lambda l, m: l + m) + list_reduce(y, lambda j, k: j + k)), initial) from nested;
----
Cannot perform list_reduce on an empty input list

statement ok
DROP TABLE nested;

# nested functions in a table with strings

statement ok
CREATE TABLE nested (n varchar[], l varchar[], initialN varchar, initialL varchar);

statement ok
INSERT INTO nested VALUES (['a', 'b'], ['1', '2'], 'A', 'B'), (NULL, NULL, 'C', 'D'), (NULL, ['110', '111', '112'], 'X', 'Y'), (['77', '88', '99'], ['55', '66', NULL], 'E', 'F');

query I
SELECT list_reduce(n, lambda x, y: list_reduce(l, lambda a, b: x || y || a || b, initialL), initialN) FROM nested;
----
AaAaB12bAaAaB12bB12
NULL
NULL
NULL

statement ok
DROP TABLE nested;

# three level nested functions with ints

query I
SELECT list_reduce([1, 2, 3], lambda x, y: list_reduce([4, 5, 6], lambda a, b: list_reduce([7, 8, 9], lambda c, d: x + y + a + b + c + d, 1000), 100), 10);
----
25917331

query I
SELECT list_reduce([1, 2, 3], lambda x, y, x_i: list_reduce([4, 5, 6], lambda a, b, a_i: list_reduce([7, 8, 9], lambda c, d, c_i: x + y + a + b + c + d + x_i + a_i + c_i, 1000), 100), 10);
----
26185861

# three level nested in a table with ints

statement ok
CREATE TABLE nested (n integer[], l integer[], m integer[], initial integer);

statement ok
INSERT INTO nested VALUES ([1, 2, 3], [4, 5, 6], [7, 8, 9], 100), (NULL, NULL, NULL, NULL), (NULL, [110, 111, 112], [113, 114, 115], NULL), ([77, 88, 99], [55, 66, NULL], [44, 33, 22], 1);

query I
SELECT list_reduce(n, lambda x, y: list_reduce(l, lambda a, b: list_reduce(m, lambda c, d: x + y + a + b + c + d, initial), initial), initial) FROM nested;
----
12992341
NULL
NULL
NULL

statement ok
DROP TABLE nested;

statement ok
CREATE TABLE nested (n integer[][][], initial integer[][]);

statement ok
INSERT INTO nested VALUES ([[[10, 20], [100, 200]], [[30, 40], [300, 400]], [[50, 60], [500, 600]]], [[1]]), ([[[1,2,3], [4,5,6], [7,8,9]]], [[2]]), (NULL, [[3]]), ([[[NULL, 60], [70, NULL], [NULL, NULL]]], [[4]]);

query I
SELECT
	list_reduce(n, lambda x, y: list_pack(
		list_reduce(x, lambda l, m: list_pack(
			list_reduce(l, lambda a, b: a + b) +
			list_reduce(m, lambda c, d: c + d))) +
		list_reduce(y, lambda e, f: list_pack(
			list_reduce(e, lambda a, b: a + b) +
			list_reduce(f, lambda c, d: c + d)))))
FROM nested;
----
[[330, 770, 1210]]
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]
NULL
[[NULL, 60], [70, NULL], [NULL, NULL]]

# tests on structs

statement ok
CREATE TABLE t_struct (s STRUCT(v VARCHAR, i INTEGER)[], initial STRUCT(v VARCHAR, i INTEGER));

statement ok
INSERT INTO t_struct VALUES ([row('a', 1), row('b', 2)], row('->', 1)), ([row('c', 3), row('d', 4)], row('-->', 2)), ([row('e', 5), row('f', 6)], row('--->', 3)), ([row('g', 7), row('h', 8)], row('---->', 4)), ([row('i', 9), row('j', 10)], row('----->', 5));

query I
SELECT list_reduce(s, lambda a, b: row(a.v || b.v, a.i + b.i), initial) FROM t_struct;
----
{'v': ->ab, 'i': 4}
{'v': -->cd, 'i': 9}
{'v': --->ef, 'i': 14}
{'v': ---->gh, 'i': 19}
{'v': ----->ij, 'i': 24}

# issue #11142
statement ok
CREATE OR REPLACE TABLE df(s STRUCT(a INT, b INT)[], initial STRUCT(a INT, b INT));

statement ok
INSERT INTO df VALUES ([row(0, 0), row(0, 1), row(0, 2)], row(-2, -3));

query I
SELECT list_reduce(s, lambda curr, next: struct_pack(a:=curr.a + (next.b - curr.b), b:=next.b), initial)
FROM df
----
{'a': 3, 'b': 2}

query I
SELECT 
list_reduce(
    [struct_pack(a := 0, b := 0), struct_pack(a := 0, b := 1), struct_pack(a := 0, b := 2)],
    lambda curr, next: struct_pack(a := curr.a + (next.b - curr.b), b := next.b), struct_pack(a := -2, b := -3)
)
----
{'a': 3, 'b': 2}

# list_reduce in where clause

statement ok
CREATE table where_clause (a int[], initial integer);

statement ok
INSERT INTO where_clause VALUES ([10, 2, 1], 10), ([1, 2, 3], 20), ([15, 4, 3], 30), ([3, 4, 5], 40), ([11, 2, 3, 4, 5], 50), ([5, 4, 3, 2, 1], 60), ([100, 2, 3, 4, 5, 6, 7, 8, 9, 10], 70);

query I
SELECT a FROM where_clause WHERE list_reduce(a, lambda x, y: x - y, initial) > 0;
----
[1, 2, 3]
[15, 4, 3]
[3, 4, 5]
[11, 2, 3, 4, 5]
[5, 4, 3, 2, 1]

# where the initial value has a different type of the list
query I
SELECT list_reduce([0], lambda x, y: x > 3, 3.1);
----
1.0

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x + y, 10.5);
----
16.5

query I
SELECT list_reduce([1, 2, 3], lambda x, y: x * y, 2.5);
----
15.0

statement ok
CREATE TABLE cast_test (l int[], initial float);

statement ok
INSERT INTO cast_test VALUES ([1, 2, 3], 100.0), ([0], 3.1), ([3, 2, 1], 10.5);

query I 
SELECT list_reduce(l, lambda x, y: x > 3, initial) FROM cast_test;
----
0.0
1.0
0.0

query I
SELECT list_reduce(l, lambda x, y: x + y, initial) FROM cast_test;
----
106.0
3.1
16.5

query I
SELECT list_reduce(l, lambda x, y: x * y, initial) FROM cast_test;
----
600.0
0.0
63.0


# Test errors with incompatible types
statement error
SELECT list_reduce([1, 2, 3], lambda x, y: x + y, [1, 2]);
----
The initial value type must be the same as the list child type or a common super type

statement error
SELECT list_reduce([BLOB 'hello'], lambda x, y: x, 'text');
----
The initial value type must be the same as the list child type or a common super type

query I
SELECT list_reduce([NULL, NULL]::INTEGER[], lambda x, y: x + y, 3.14);
----
NULL

# Test using ENUM types
statement ok
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

query I
SELECT list_reduce(['sad', 'happy']::mood[], lambda x, y: y::text, 'Mood:');
----
happy

query I
SELECT list_reduce([[1, 2], [3, 4]], lambda x, y: array_append(x, list_aggregate(y, 'sum')), [0]);
----
[0, 3, 7]

query I
SELECT list_reduce([5, 10, 15], lambda x, y: x * y, 1 + 1);
----
1500