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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
# name: test/sql/function/list/lambdas/expression_iterator_cases.test
# description: Test the different possible cases of the expression iterator
# group: [lambdas]
# these tests check the different cases of the expression iterator
statement ok
SET lambda_syntax='DISABLE_SINGLE_ARROW'
# this BOUND_AGGREGATE is a constant, so we can allow it inside lambda functions
query I
SELECT list_transform([10], lambda x: sum(1) + x)
----
[11]
query I
SELECT list_filter([10], lambda x: sum(1) > 0)
----
[10]
# BOUND_BETWEEN
query I
SELECT list_transform([NULL, DATE '1992-09-20', DATE '2021-09-20'], lambda elem: extract('year' FROM elem) BETWEEN 2000 AND 2022)
----
[NULL, false, true]
query I
SELECT list_filter([NULL, DATE '1992-09-20', DATE '2021-09-20'], lambda elem: extract('year' FROM elem) BETWEEN 2000 AND 2022)
----
[2021-09-20]
# BOUND_CASE
query I
SELECT list_transform(['hello', 'duck', 'sunshine'], lambda str: CASE WHEN str LIKE '%e%' THEN 'e' ELSE 'other' END)
----
[e, other, e]
query I
SELECT list_filter(['hello', 'duck', 'sunshine'], lambda str: (CASE WHEN str LIKE '%e%' THEN 'e' ELSE 'other' END) LIKE 'e')
----
[hello, sunshine]
# BOUND_CAST
query I
SELECT list_transform([2.0::DOUBLE], lambda x: x::INTEGER)
----
[2]
query I
SELECT list_filter([2], lambda x: x::DOUBLE == 2)
----
[2]
# BOUND_COMPARISON
query I
SELECT list_transform([2.4, NULL, -4.7], lambda x: x != 10.4)
----
[true, NULL, true]
query I
SELECT list_filter([2.4, NULL, -4.7], lambda x: x != -4.7)
----
[2.4]
# BOUND_CONJUNCTION
query I
SELECT list_transform([True, False, NULL], lambda x: x AND true)
----
[true, false, NULL]
query I
SELECT list_filter([True, False, NULL], lambda x: x AND true)
----
[true]
# BOUND_FUNCTION
query I
SELECT list_transform([TIMESTAMP '1992-03-22', TIMESTAMP '209-03-22', TIMESTAMP '1700-03-22'], lambda x: century(x))
----
[20, 3, 17]
query I
SELECT list_filter([TIMESTAMP '1992-03-22', TIMESTAMP '209-03-22', TIMESTAMP '1700-03-22'], lambda x: century(x) > 16)
----
['1992-03-22 00:00:00', '1700-03-22 00:00:00']
# BOUND_OPERATOR
query I
SELECT list_transform([2], lambda x: x + x)
----
[4]
query I
SELECT list_filter([2], lambda x: x + x = 4)
----
[2]
# BOUND_SUBQUERY
statement error
SELECT list_transform([2], lambda x: (SELECT 1 - x) * x)
----
<REGEX>:Binder Error.*subqueries in lambda expressions are not supported.*
statement error
SELECT list_filter([2], lambda x: (SELECT 1 - x) * x > 2)
----
<REGEX>:Binder Error.*subqueries in lambda expressions are not supported.*
statement ok
CREATE MACRO list_contains_macro(x, y) AS (SELECT list_contains(x, y))
statement error
SELECT list_filter([[1, 2, 1], [1, 2, 3], [1, 1, 1]], lambda x: list_contains_macro(x, 3))
----
<REGEX>:Binder Error.*subqueries in lambda expressions are not supported.*
# BOUND_UNNEST
statement error
SELECT list_transform([1], lambda x: x = UNNEST([1]));
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
statement error
SELECT list_filter([1], lambda x: x = UNNEST([1]));
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
# BOUND_WINDOW
statement ok
CREATE TABLE my_window (l integer[], g integer, o integer)
statement ok
INSERT INTO my_window VALUES ([1], 1, 1), ([1, NULL, 2], 1, 2), ([], 2, 3), (NULL, NULL, NULL), ([1, 2], 2, 4)
query I
SELECT list(list_transform(l, lambda e: e + 1)) OVER (PARTITION BY g ORDER BY o)
FROM my_window
ORDER BY ALL
----
[[]]
[[], [2, 3]]
[[2]]
[[2], [2, NULL, 3]]
[NULL]
|
