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
|
# name: test/sql/parser/test_columns.test
# description: Test the columns expression
# 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(*) FROM integers
----
42 84
13 14
query IIII
SELECT MIN(COLUMNS(*)), MAX(COLUMNS(*)) FROM integers
----
13 14 42 84
query II
SELECT MIN(COLUMNS(* EXCLUDE (j))), MAX(COLUMNS(* EXCLUDE (i))) FROM integers
----
13 84
query II
SELECT MIN(COLUMNS(* REPLACE (i+j AS i))) FROM integers
----
27 14
query II
SELECT COLUMNS(*) + 1 FROM integers
----
43 85
14 15
# multiple COLUMNS work, as long as the star inside the columns is the same
query II
SELECT COLUMNS(*) + COLUMNS(*) FROM integers
----
84 168
26 28
# regex
statement ok
CREATE TABLE grouped_table AS SELECT 1 id, 42 index1, 84 index2 UNION ALL SELECT 2, 13, 14
statement error
SELECT COLUMNS('indxe.*') FROM grouped_table
----
"index1"
query III
SELECT id, MIN(COLUMNS('index[0-9]')) FROM grouped_table GROUP BY all ORDER BY ALL
----
1 42 84
2 13 14
# partial match
query III
SELECT id, MIN(COLUMNS('[0-9]')) FROM grouped_table GROUP BY all ORDER BY ALL
----
1 42 84
2 13 14
# regex without matches
statement error
SELECT id, MIN(COLUMNS('xxx')) FROM grouped_table GROUP BY all
----
# empty regex
statement error
SELECT MIN(COLUMNS('xxx')) FROM grouped_table
----
# invalid regex
statement error
SELECT MIN(COLUMNS('[asdadd')) FROM grouped_table
----
# multiple different COLUMNS statements in the same expression are not supported
statement error
SELECT COLUMNS(*) + COLUMNS(* EXCLUDE(j)) FROM integers
----
# COLUMNS in subquery without FROM clause
statement error
SELECT (SELECT COLUMNS(*)) FROM integers
----
statement error
SELECT columns(['a', null]) FROM values (42) t(a);
----
does not support NULL input parameters
# COLUMNS in order by clause
query III
SELECT * FROM grouped_table ORDER BY COLUMNS('index[0-9]');
----
2 13 14
1 42 84
query III
SELECT * FROM grouped_table ORDER BY COLUMNS(*);
----
1 42 84
2 13 14
|
