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
|
# name: test/sql/parser/test_columns_where.test
# description: Test the columns expression in the WHERE clause
# group: [parser]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE tbl(col1 INTEGER, col2 INTEGER, col3 INTEGER);
statement ok
INSERT INTO tbl VALUES (1, 200, 10), (2, 100, 20), (3, 200, 0)
query III
SELECT * FROM tbl WHERE COLUMNS(*) >= 2 ORDER BY ALL
----
2 100 20
query III
SELECT * FROM tbl WHERE COLUMNS(['col1', 'col2']) >= 2 ORDER BY ALL
----
2 100 20
3 200 0
query III
SELECT * FROM tbl WHERE COLUMNS(['col1', 'col2']) >= 2 AND COLUMNS(*) IS NOT NULL ORDER BY ALL
----
2 100 20
3 200 0
query III
SELECT * FROM tbl WHERE COLUMNS(['col1', 'col2']) >= 2 AND COLUMNS(['col1', 'col3']) < 10 ORDER BY ALL
----
3 200 0
statement error
SELECT * FROM tbl WHERE COLUMNS(['nonexistent']) >= 2 ORDER BY ALL
----
nonexistent
statement error
SELECT * FROM tbl WHERE COLUMNS(* EXCLUDE (col1, col2, col3)) >= 2 ORDER BY ALL
----
empty set of columns
statement error
SELECT * FROM tbl WHERE * ORDER BY ALL
----
STAR expression is not allowed in the WHERE clause
statement error
SELECT * FROM tbl WHERE * >= 2 ORDER BY ALL
----
Use COLUMNS(*) instead
|
