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
|
# name: test/sql/projection/test_value_list.test
# description: Test value list in selection
# group: [projection]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
# value list can be a top-level statement
query II
(VALUES (1, 3), (2, 4));
----
1 3
2 4
# nulls first and then integers
query II
SELECT * FROM (VALUES (NULL, NULL), (3, 4), (3, 7)) v1;
----
NULL NULL
3 4
3 7
# standard value list
query III
SELECT * FROM (VALUES (1, 2, 3), (1, 2, 3)) v1;
----
1 2 3
1 2 3
# value list with expressions
query III
SELECT * FROM (VALUES (1 + 1, 2, 3), (1 + 3, 2, 3)) v1;
----
2 2 3
4 2 3
# value list with subqueries
query III
SELECT * FROM (VALUES ((SELECT 42), 2, 3), (1 + 3,2,3)) v1;
----
42 2 3
4 2 3
# value list in insert
statement ok
CREATE TABLE test (a INTEGER, b INTEGER);
statement ok
INSERT INTO test VALUES (1, 2), (3, 4);
# value list with more complicated subqueries
query III
SELECT * FROM (VALUES ((SELECT MIN(a) FROM test), 2, 3), ((SELECT MAX(b) FROM test), 2, 3)) v1;
----
1 2 3
4 2 3
# value list with different types
statement error
SELECT * FROM (VALUES ('hello', 2), (1 + 3, '5'), (DATE '1992-09-20', 3)) v1;
----
<REGEX>:.*Not implemented Error.*an explicit cast is required.*
# value list with NULLs
query TI
SELECT * FROM (VALUES (DATE '1992-09-20', 3), (NULL, NULL)) v1;
----
1992-09-20 3
NULL NULL
# only NULLs
query II
SELECT * FROM (VALUES (NULL, NULL)) v1;
----
NULL NULL
# nulls first and then integers
query II
SELECT * FROM (VALUES (NULL, NULL), (3, 4)) v1;
----
NULL NULL
3 4
query I
SELECT * FROM (VALUES (3), ('42')) v1;
----
3
42
statement error
SELECT * FROM (VALUES (3), ('hello')) v1;
----
<REGEX>:.*Conversion Error.*hello.*
query I
SELECT typeof(x) FROM (VALUES (DATE '1992-01-01'), ('1992-01-01')) v1(x) LIMIT 1;
----
DATE
query I
SELECT * FROM (VALUES (NULL), ('hello')) v1;
----
NULL
hello
# unbalanced value list is not allowed
statement error
SELECT * FROM (VALUES (1, 2, 3), (1,2)) v1;
----
<REGEX>:.*Parser Error.*lists must all be the same length.*
# default in value list is not allowed
statement error
SELECT * FROM (VALUES (DEFAULT, 2, 3), (1,2)) v1;
----
<REGEX>:.*Parser Error.*lists must all be the same length.*
# VALUES list for INSERT
statement ok
CREATE TABLE varchars(v VARCHAR);
statement ok
INSERT INTO varchars VALUES (1), ('hello'), (DEFAULT);
query T
SELECT * FROM varchars ORDER BY 1
----
NULL
1
hello
# too many columns provided
statement error
INSERT INTO varchars VALUES (1, 2), ('hello', 3), (DEFAULT, DEFAULT);
----
<REGEX>:.*Binder Error.*table varchars has 1 columns.*
statement error
INSERT INTO varchars (v) VALUES (1, 2), ('hello', 3), (DEFAULT, DEFAULT);
----
statement error
INSERT INTO varchars (v) VALUES (1, 2), ('hello'), (DEFAULT, DEFAULT);
----
<REGEX>:.*Parser Error.*lists must all be the same length.*
# operation on default not allowed
statement error
INSERT INTO varchars (v) VALUES (DEFAULT IS NULL);
----
<REGEX>:.*Binder Error.*not allowed.*
|
