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
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
|
# name: test/sql/function/list/lambdas/incorrect.test
# description: Test incorrect usage of the lambda functions
# group: [lambdas]
require no_extension_autoloading "EXPECTED: This tests is not compatible with JSON extension, that will otherwise be autoloaded"
statement ok
CREATE TABLE incorrect_test (i INTEGER);
foreach func_name list_transform list_filter list_reduce
statement error
SELECT ${func_name}();
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}([]);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}(1, 2, 3);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}(NULL, NULL);
----
<REGEX>:Binder Error.*Invalid lambda expression!.*
statement error
SELECT ${func_name}(NULL, x);
----
<REGEX>:Binder Error.*Referenced column.*was not found because the FROM clause is missing.*
statement error
SELECT ${func_name}([1, 2], (SELECT 1) -> x + 1);
----
<REGEX>:Binder Error.*Invalid lambda parameters!.*
statement error
SELECT ${func_name}(NULL, i) FROM incorrect_test;
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}(NULL, x -> y);
----
<REGEX>:Binder Error.*Referenced column.*was not found because the FROM clause is missing.*
statement error
SELECT ${func_name}([1]);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}([1], NULL);
----
<REGEX>:Binder Error.*Invalid lambda expression!.*
statement error
SELECT ${func_name}([[1]], x -> x + 1);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}(1, 1);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}(1, x -> x + 1);
----
Invalid LIST argument during lambda function binding!
endloop
# list_reduce accepts a third parameter, so shows a different error message
foreach func_name list_transform list_filter
statement error
SELECT ${func_name}([1], x -> x, 3);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
statement error
SELECT ${func_name}([True], x -> x, x -> x);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
endloop
statement error
SELECT list_reduce([1], x -> x, 3);
----
<REGEX>:Binder Error.*list_reduce expects a function with 2 or 3 arguments.*
statement error
SELECT list_reduce([True], x -> x, x -> x);
----
<REGEX>:Binder Error.*No function matches the given name and argument types.*You might need to add explicit type casts.*
# list_transform specific tests
statement error
SELECT [split('01:08:22', ':'), x -> CAST (x AS INTEGER)];
----
<REGEX>:Binder Error.*failed to bind function, either.*This scalar function does not support lambdas!.*Referenced column.*was not found because the FROM clause is missing.*
statement error
select list_apply(i, x -> x * 3 + 2 / zz) from (values (list_value(1, 2, 3))) tbl(i);
----
<REGEX>:Binder Error.*Referenced column.*not found in FROM clause!.*
# lambda cannot be the root of a plan
statement error
select x -> x + 1 from (values (list_value(1, 2, 3))) tbl(i);
----
<REGEX>:Binder Error.*Referenced column.*not found in FROM clause!.*
statement error
select list_apply(i, y + 1 -> x + 1) from (values (list_value(1, 2, 3))) tbl(i);
----
<REGEX>:Binder Error.*Invalid lambda parameters!.*
statement error
SELECT list_apply(i, a.x -> x + 1) FROM (VALUES (list_value(1, 2, 3))) tbl(i);
----
<REGEX>:Binder Error.*Invalid lambda parameters!.*
statement error
select list_apply(i, x -> x + 1 AND y + 1) from (values (list_value(1, 2, 3))) tbl(i);
----
<REGEX>:Binder Error.*Referenced column.*not found in FROM clause!.*
statement ok
CREATE TABLE l_filter_test (l integer[]);
statement error
SELECT list_transform([1, 2], (x, y, z) -> x + y + z);
----
<REGEX>:Binder Error.*This lambda function only supports up to two lambda parameters!.*
statement error
SELECT list_filter([1, 2], (x, y, z) -> x >= y AND y >= z);
----
<REGEX>:Binder Error.*This lambda function only supports up to two lambda parameters!.*
# using lambdas in functions that do not support them
statement error
SELECT cos(x -> x + 1);
----
<REGEX>:Binder Error.*failed to bind function, either.*This scalar function does not support lambdas!.*Referenced column.*was not found because the FROM clause is missing.*
statement error
SELECT cos([1], x -> x + 1);
----
<REGEX>:Binder Error.*failed to bind function, either.*This scalar function does not support lambdas!.*Referenced column.*was not found because the FROM clause is missing.*
# FIXME: support lambdas in CHECK constraints
statement error
CREATE TABLE lambda_check (i BIGINT[],
CHECK (list_filter(i, x -> x % 2 = 0) == []));
----
<REGEX>:Not implemented Error.*Lambda functions are currently not supported in CHECK constraints.*
statement error
CREATE TABLE lambda_check (i BIGINT[],
CHECK (list_transform(i, x -> x % 2) == []));
----
<REGEX>:Not implemented Error.*Lambda functions are currently not supported in CHECK constraints.*
statement error
CREATE TABLE lambda_check (i BIGINT[],
CHECK ([x + 1 FOR x IN i IF x > 0] == []));
----
<REGEX>:Not implemented Error.*Lambda functions are currently not supported in CHECK constraints.*
statement error
CREATE TABLE lambda_check (
i BIGINT[],
j BIGINT[],
CHECK ((list_apply(i, x -> list_count(list_filter(j, y -> y%2=0)) + x)) == []));
----
<REGEX>:Not implemented Error.*Lambda functions are currently not supported in CHECK constraints.*
# FIXME: allow lambdas in generated columns
statement error
CREATE TABLE unit2(
price INTEGER[],
total_price INTEGER GENERATED ALWAYS AS (list_transform(price, x -> x + 1)) VIRTUAL
);
----
<REGEX>:Not implemented Error.*Lambda functions are currently not supported in generated columns.*
# Issue 9732
statement ok
CREATE TABLE tbl AS SELECT {'a': 10} AS s;
statement error
SELECT list_transform(UNNEST(s), x -> UNNEST(x)) FROM tbl;
----
<REGEX>:Binder Error.*failed to bind function, either.*struct column can only be applied as the root element of a SELECT expression.*
# UNNEST in lambdas is not supported
statement ok
CREATE TABLE nested_list(i INT[][], other INT[]);
statement ok
INSERT INTO nested_list VALUES ([[1, 2]], [3, 4]);
statement error
SELECT list_transform(i, x -> UNNEST(x)) FROM nested_list;
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
statement error
SELECT list_transform(i, x -> UNNEST(other)) FROM nested_list;
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
statement ok
CREATE TABLE map_tbl(m MAP(INTEGER, INTEGER));
statement error
SELECT [UNNEST([x.key FOR y IN range(x.value)]) FOR x IN map_entries(m)] FROM map_tbl;
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
statement error
SELECT list_transform(map_entries(m), x -> UNNEST(range(x.value))) FROM map_tbl;
----
<REGEX>:Binder Error.*UNNEST in lambda expressions is not supported.*
statement ok
CREATE TABLE dummy_tbl (y INT);
statement error
SELECT list_transform([1], lambda x: lambda y: x + y) FROM dummy_tbl;
----
<REGEX>:Binder Error.*invalid lambda expression.*
statement error
SELECT list_transform([1], lambda x: 1 : hello) FROM dummy_tbl;
----
<REGEX>:Parser Error.*syntax error.*
statement ok
SET lambda_syntax='DISABLE_SINGLE_ARROW'
statement error
CREATE OR REPLACE function transpose(lst) AS (
SELECT list_transform(range(1, 1 + length(lst[1])),
j -> list_transform(range(1, length(lst) + 1),
lambda i: lst[i][j]
)
)
);
----
<REGEX>:Binder Error.*Deprecated lambda arrow.*
statement error
CREATE OR REPLACE FUNCTION transpose(lst) AS (
SELECT list_transform(range(1, 1 + length(lst[1])),
j -> list_transform(range(1, length(lst) + 1),
i -> lst[i][j]
)
)
);
----
<REGEX>:Binder Error.*Deprecated lambda arrow.*
statement ok
SET lambda_syntax='ENABLE_SINGLE_ARROW'
statement ok
CREATE OR REPLACE FUNCTION transpose(lst) AS (
SELECT list_transform(range(1, 1 + length(lst[1])),
j -> list_transform(range(1, length(lst) + 1),
i -> lst[i][j]
)
)
);
|
