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
|
# name: test/sql/parser/test_columns_lists.test
# description: Test the columns expression with list comprehension
# group: [parser]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers AS SELECT 42 i, 84 j UNION ALL SELECT 13, 14
query II
SELECT COLUMNS([x for x in *]) FROM integers
----
42 84
13 14
query I
SELECT COLUMNS([x for x in (*) if x <> 'i']) FROM integers
----
84
14
# short-hand, allow directly specifying the lambda
query I
SELECT COLUMNS(lambda x: x <> 'i') FROM integers
----
84
14
query I
SELECT COLUMNS([x for x in (*) if x SIMILAR TO 'i']) FROM integers
----
42
13
query I
SELECT COLUMNS(['i', 'i']) FROM integers
----
42
13
query I
SELECT COLUMNS(list_concat(['i'], ['i'])) FROM integers
----
42
13
# star with exclude
query I
SELECT COLUMNS([x for x in (* EXCLUDE (i))]) FROM integers
----
84
14
# multiple columns that are the same works
query I
SELECT COLUMNS(['i']) + COLUMNS(['i']) FROM integers
----
84
26
# columns must be returned as strings
statement error
SELECT COLUMNS([i, j]) FROM integers
----
COLUMNS expression can only contain lambda parameters
# nested columns
statement error
SELECT COLUMNS([x for x in COLUMNS(*)]) FROM integers
----
COLUMNS expression is not allowed inside another COLUMNS expression
statement error
SELECT COLUMNS(COLUMNS(*)) FROM integers
----
COLUMNS expression is not allowed inside another COLUMNS expression
statement error
SELECT * + 42 FROM integers
----
cannot be applied to a star expression
# empty lambda
statement error
SELECT COLUMNS([x for x in (*) if x = 'k']) FROM integers
----
resulted in an empty set of columns
# columns that are not found
statement error
SELECT COLUMNS(['k']) FROM integers
----
not found in the FROM clause
# COLUMNS and joins
query I rowsort
SELECT COLUMNS([x for x in (*) if x LIKE 'i']) FROM integers i1 JOIN integers i2 USING (i)
----
13
42
query II rowsort
SELECT COLUMNS([x for x in (*) if x LIKE 'i']) FROM integers i1 JOIN integers i2 ON (i1.i=i2.i)
----
13 13
42 42
# wrong parameter type
statement error
SELECT COLUMNS([43]) FROM integers
----
VARCHAR argument
statement error
SELECT COLUMNS([NULL]) FROM integers
----
VARCHAR argument
statement error
SELECT COLUMNS([]::VARCHAR[]) FROM integers
----
empty set of columns
statement error
SELECT COLUMNS(NULL::VARCHAR[]) FROM integers
----
empty set of columns
statement error
SELECT COLUMNS(NULL::VARCHAR) FROM integers
----
does not support NULL
# different columns
statement error
SELECT COLUMNS(['i']) + COLUMNS(['j']) FROM integers
----
Multiple different STAR/COLUMNS in the same expression are not supported
# star with replace
statement error
SELECT COLUMNS([x for x in (* REPLACE (i AS i))]) FROM integers
----
only allowed as the root element
|
