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
|
# name: test/sql/function/list/lambdas/table_functions.test
# description: Test list lambdas in table functions
# group: [lambdas]
require parquet
statement ok
SET lambda_syntax='DISABLE_SINGLE_ARROW'
statement ok
CREATE TABLE tmp AS SELECT range AS id FROM range(10);
# create a parquet file
query I
COPY tmp TO '__TEST_DIR__/my_file_cba.parquet' (FORMAT PARQUET);
----
10
# now test different table functions
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_transform(['C', 'B', 'A'], lambda s: lower(s)), 'string_agg', '') || '.parquet');
----
10
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_transform(list_sort(['a', 'b', 'c'], 'DESC'), lambda s: lower(s)), 'string_agg', '') || '.parquet');
----
10
# nested lambdas
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_transform(list_filter(['s', 'c', 'b', NULL, 'a'], lambda x: x IS NOT NULL AND x != 's'),
lambda s: lower(s)), 'string_agg', '') || '.parquet');
----
10
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_transform(list_filter(
list_filter(['s', 'c', 'b', NULL, 'a'], lambda y: y != 's'), lambda x: x IS NOT NULL),
lambda s: lower(s)), 'string_agg', '') || '.parquet');
----
10
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_reduce([['c', 'b', NULL, 'a']], lambda x, y: coalesce(x,y)), 'string_agg', '') || '.parquet');
----
10
# lambda parameters in rhs
query I
COPY tmp TO '__TEST_DIR__/my_file_ac.parquet' (FORMAT PARQUET);
----
10
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_aggr(list_transform(['a'], lambda x: list_transform(['a', 'c'], lambda y: x || y)[2]), 'string_agg', '') || '.parquet');
----
10
# issue 5821 (without httpfs)
query I
COPY tmp TO '__TEST_DIR__/my_file_a=1,b=2,c=3.parquet' (FORMAT PARQUET);
----
10
query I
SELECT COUNT(*) FROM read_parquet('__TEST_DIR__/my_file_' ||
list_string_agg([lower(s) for s in ['a=1', 'b=2', 'c=3']]) || '.parquet');
----
10
# lambdas in ALTER TABLE statements
statement ok
CREATE TABLE cities AS
SELECT * FROM (VALUES ('Amsterdam', [90, 10]), ('London', [89, 102])) cities (name, prices);
statement ok
ALTER TABLE cities
ALTER COLUMN prices SET DATA TYPE INTEGER[] USING
list_filter(cities.prices, lambda price: price < 100);
query II
SELECT name, prices AS cheap_options FROM cities;
----
Amsterdam [90, 10]
London [89]
|
