1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4#include <algorithm>
5#include <mutex>
6#include <thread>
7
8using namespace duckdb;
9using namespace std;
10
11TEST_CASE("Test Sequences", "[sequence]") {
12 unique_ptr<QueryResult> result;
13 DuckDB db(nullptr);
14 Connection con(db);
15 // note: query verification is disabled for these queries
16 // because running the same query multiple times with a sequence does not result in the same answer
17
18 // create a sequence
19 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
20 // cannot create duplicate sequence
21 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq;"));
22 // ignore errors if sequence already exists
23 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE IF NOT EXISTS seq;"));
24
25 // generate values from the sequence
26 result = con.Query("SELECT nextval('seq')");
27 REQUIRE(CHECK_COLUMN(result, 0, {1}));
28 result = con.Query("SELECT nextval('seq')");
29 REQUIRE(CHECK_COLUMN(result, 0, {2}));
30 result = con.Query("SELECT nextval('seq'), nextval('seq');");
31 REQUIRE(CHECK_COLUMN(result, 0, {3}));
32 REQUIRE(CHECK_COLUMN(result, 1, {4}));
33
34 // NULL in nextval
35 result = con.Query("SELECT nextval(NULL)");
36 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
37 result = con.Query("SELECT nextval(a) FROM (VALUES ('seq'), (NULL), ('seq')) tbl1(a)");
38 REQUIRE(CHECK_COLUMN(result, 0, {5, Value(), 6}));
39
40 // can't create a sequence that already exists
41 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq;"));
42 // drop the sequence
43 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
44 // can't drop non-existing sequence
45 REQUIRE_FAIL(con.Query("DROP SEQUENCE seq;"));
46 // but doesn't fail with IF EXISTS
47 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE IF EXISTS seq;"));
48
49 // INCREMENT BY
50 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT BY 2;"));
51 result = con.Query("SELECT nextval('seq')");
52 REQUIRE(CHECK_COLUMN(result, 0, {1}));
53 result = con.Query("SELECT nextval('\"seq\"')");
54 REQUIRE(CHECK_COLUMN(result, 0, {3}));
55 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
56
57 // MINVALUE
58 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MINVALUE 3;"));
59 result = con.Query("SELECT nextval('seq')");
60 REQUIRE(CHECK_COLUMN(result, 0, {3}));
61 result = con.Query("SELECT nextval('seq')");
62 REQUIRE(CHECK_COLUMN(result, 0, {4}));
63 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
64
65 // MAXVALUE
66 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MAXVALUE 2;"));
67 result = con.Query("SELECT nextval('seq')");
68 REQUIRE(CHECK_COLUMN(result, 0, {1}));
69 result = con.Query("SELECT nextval('seq')");
70 REQUIRE(CHECK_COLUMN(result, 0, {2}));
71 // max value exceeded
72 REQUIRE_FAIL(con.Query("SELECT nextval('seq')"));
73 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
74
75 // MAXVALUE and CYCLE
76 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MAXVALUE 2 CYCLE;"));
77 result = con.Query("SELECT nextval('seq')");
78 REQUIRE(CHECK_COLUMN(result, 0, {1}));
79 result = con.Query("SELECT nextval('seq')");
80 REQUIRE(CHECK_COLUMN(result, 0, {2}));
81 // max value exceeded: cycle back
82 result = con.Query("SELECT nextval('seq')");
83 REQUIRE(CHECK_COLUMN(result, 0, {1}));
84 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
85
86 // START WITH, MINVALUE, MAXVALUE and CYCLE
87 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MINVALUE 3 MAXVALUE 5 START WITH 4 CYCLE;"));
88 result = con.Query("SELECT nextval('seq')");
89 REQUIRE(CHECK_COLUMN(result, 0, {4}));
90 result = con.Query("SELECT nextval('seq')");
91 REQUIRE(CHECK_COLUMN(result, 0, {5}));
92 result = con.Query("SELECT nextval('seq')");
93 REQUIRE(CHECK_COLUMN(result, 0, {3}));
94 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
95
96 // START WITH defaults to MAXVALUE if increment is negative
97 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT BY -1 MINVALUE 0 MAXVALUE 2;"));
98 result = con.Query("SELECT nextval('seq')");
99 REQUIRE(CHECK_COLUMN(result, 0, {2}));
100 result = con.Query("SELECT nextval('seq')");
101 REQUIRE(CHECK_COLUMN(result, 0, {1}));
102 result = con.Query("SELECT nextval('seq')");
103 REQUIRE(CHECK_COLUMN(result, 0, {0}));
104 REQUIRE_FAIL(con.Query("SELECT nextval('seq')"));
105 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
106
107 // START WITH defaults to MINVALUE if increment is positive
108 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT BY 1 MINVALUE 0 MAXVALUE 2;"));
109 result = con.Query("SELECT nextval('seq')");
110 REQUIRE(CHECK_COLUMN(result, 0, {0}));
111 result = con.Query("SELECT nextval('seq')");
112 REQUIRE(CHECK_COLUMN(result, 0, {1}));
113 result = con.Query("SELECT nextval('seq')");
114 REQUIRE(CHECK_COLUMN(result, 0, {2}));
115 REQUIRE_FAIL(con.Query("SELECT nextval('seq')"));
116 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
117
118 // for positive increment min_value/start defaults to 1 and max_value defaults to 2^63
119 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT 1 MAXVALUE 3 START 2 CYCLE;"));
120 result = con.Query("SELECT nextval('seq')");
121 REQUIRE(CHECK_COLUMN(result, 0, {2}));
122 result = con.Query("SELECT nextval('seq')");
123 REQUIRE(CHECK_COLUMN(result, 0, {3}));
124 result = con.Query("SELECT nextval('seq')");
125 REQUIRE(CHECK_COLUMN(result, 0, {1}));
126 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
127
128 // for negative increment min_value defaults to -2^63 and max_value/start defaults to -1
129 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT -1 CYCLE;"));
130 result = con.Query("SELECT nextval('seq')");
131 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
132 result = con.Query("SELECT nextval('seq')");
133 REQUIRE(CHECK_COLUMN(result, 0, {-2}));
134 result = con.Query("SELECT nextval('seq')");
135 REQUIRE(CHECK_COLUMN(result, 0, {-3}));
136 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
137
138 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT -1 MINVALUE -2 CYCLE;"));
139 result = con.Query("SELECT nextval('seq')");
140 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
141 result = con.Query("SELECT nextval('seq')");
142 REQUIRE(CHECK_COLUMN(result, 0, {-2}));
143 result = con.Query("SELECT nextval('seq')");
144 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
145 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
146
147 // min_value defaults to 1, setting start to -1 gives start < min_value
148 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT 1 START -1 CYCLE;"));
149 // max_value defaults to -1, setting start to 1 gives start > max_value
150 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT -1 START 1 CYCLE;"));
151
152 // sequences in schemas
153 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA a;"));
154 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA b;"));
155 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE a.seq;"));
156 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE b.seq;"));
157 result = con.Query("SELECT nextval('a.seq'), nextval('b.seq');");
158 REQUIRE(CHECK_COLUMN(result, 0, {1}));
159 REQUIRE(CHECK_COLUMN(result, 1, {1}));
160
161 // with quotes
162 result = con.Query("SELECT nextval('\"a\".\"seq\"'), nextval('\"b\".seq');");
163 REQUIRE(CHECK_COLUMN(result, 0, {2}));
164 REQUIRE(CHECK_COLUMN(result, 1, {2}));
165
166 // unterminated quotes
167 REQUIRE_FAIL(con.Query("SELECT nextval('\"a\".\"seq');"));
168 // too many separators
169 REQUIRE_FAIL(con.Query("SELECT nextval('a.b.c.d');"));
170
171 // start exceeds max value
172 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq MAXVALUE 5 START WITH 6;"));
173 // start preceeds min value
174 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq MINVALUE 5 START WITH 4;"));
175 // min value bigger than max
176 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq MINVALUE 7 MAXVALUE 5;"));
177 // increment must not be 0
178 REQUIRE_FAIL(con.Query("CREATE SEQUENCE seq INCREMENT 0;"));
179
180 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
181 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq2;"));
182 // we can use operations in nextval
183 result = con.Query("SELECT nextval('s'||'e'||'q')");
184 REQUIRE(CHECK_COLUMN(result, 0, {1}));
185 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
186
187 // sequences with tables
188 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
189 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR);"));
190 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('seq'), ('seq2')"));
191
192 // nextval is run once per value
193 result = con.Query("SELECT s, nextval('seq') FROM strings");
194 REQUIRE(CHECK_COLUMN(result, 0, {"seq", "seq2"}));
195 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
196 // we can also use the strings from the table as input to the sequence
197 result = con.Query("SELECT s, nextval(s) FROM strings");
198 REQUIRE(CHECK_COLUMN(result, 0, {"seq", "seq2"}));
199 REQUIRE(CHECK_COLUMN(result, 1, {3, 1}));
200
201 // this will also cause an error if the sequence does not exist
202 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('nonexistant_seq')"));
203 REQUIRE_FAIL(con.Query("SELECT s, nextval(s) FROM strings"));
204}
205
206TEST_CASE("Test Sequences Are Transactional", "[sequence]") {
207 unique_ptr<QueryResult> result;
208 DuckDB db(nullptr);
209 Connection con(db);
210 Connection con2(db);
211
212 // start a transaction for both nodes
213 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
214 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
215
216 // create a sequence in node one
217 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
218 // node one can see it
219 REQUIRE_NO_FAIL(con.Query("SELECT nextval('seq');"));
220 // node two can't see it
221 REQUIRE_FAIL(con2.Query("SELECT nextval('seq');"));
222
223 // we commit the sequence
224 REQUIRE_NO_FAIL(con.Query("COMMIT"));
225 // node two still can't see it
226 REQUIRE_FAIL(con2.Query("SELECT nextval('seq');"));
227 // now commit node two
228 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
229
230 // we can now see the sequence in node two
231 REQUIRE_NO_FAIL(con2.Query("SELECT nextval('seq');"));
232
233 // drop sequence seq in a transaction
234 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
235 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
236
237 // node one can't use it anymore
238 REQUIRE_FAIL(con.Query("SELECT nextval('seq');"));
239 // node two can still use it
240 REQUIRE_NO_FAIL(con2.Query("SELECT nextval('seq');"));
241
242 // rollback cancels the drop sequence
243 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
244
245 // we can still use it
246 REQUIRE_NO_FAIL(con2.Query("SELECT nextval('seq');"));
247
248 // now we drop it for real
249 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
250
251 // we can't use it anymore
252 REQUIRE_FAIL(con.Query("SELECT nextval('seq');"));
253 REQUIRE_FAIL(con2.Query("SELECT nextval('seq');"));
254}
255
256struct ConcurrentData {
257 DuckDB &db;
258 mutex lock;
259 vector<int64_t> results;
260
261 ConcurrentData(DuckDB &db) : db(db) {
262 }
263};
264
265#define THREAD_COUNT 20
266#define INSERT_COUNT 100
267
268static void append_values_from_sequence(ConcurrentData *data) {
269 Connection con(data->db);
270 for (size_t i = 0; i < INSERT_COUNT; i++) {
271 auto result = con.Query("SELECT nextval('seq')");
272 int64_t res = result->GetValue(0, 0).GetValue<int64_t>();
273 lock_guard<mutex> lock(data->lock);
274 data->results.push_back(res);
275 }
276}
277
278TEST_CASE("Test Concurrent Usage of Sequences", "[sequence][.]") {
279 unique_ptr<QueryResult> result;
280 DuckDB db(nullptr);
281 Connection con(db);
282 thread threads[THREAD_COUNT];
283 ConcurrentData data(db);
284 ConcurrentData seq_data(db);
285
286 // create a sequence
287 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
288 // fetch a number of values sequentially
289 for (size_t i = 0; i < THREAD_COUNT; i++) {
290 append_values_from_sequence(&seq_data);
291 }
292
293 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
294 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
295 // now launch threads that all use the sequence in parallel
296 // each appends the values to a vector "results"
297 for (size_t i = 0; i < THREAD_COUNT; i++) {
298 threads[i] = thread(append_values_from_sequence, &data);
299 }
300 for (size_t i = 0; i < THREAD_COUNT; i++) {
301 threads[i].join();
302 }
303 // now we sort the output data
304 std::sort(seq_data.results.begin(), seq_data.results.end());
305 std::sort(data.results.begin(), data.results.end());
306 // the sequential and threaded data should be the same
307 REQUIRE(seq_data.results == data.results);
308
309 seq_data.results.clear();
310 data.results.clear();
311 // now do the same but for a cyclic sequence
312 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
313 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MAXVALUE 10 CYCLE;"));
314 for (size_t i = 0; i < THREAD_COUNT; i++) {
315 append_values_from_sequence(&seq_data);
316 }
317
318 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
319 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq MAXVALUE 10 CYCLE;"));
320 for (size_t i = 0; i < THREAD_COUNT; i++) {
321 threads[i] = thread(append_values_from_sequence, &data);
322 }
323 for (size_t i = 0; i < THREAD_COUNT; i++) {
324 threads[i].join();
325 }
326 // now we sort the output data
327 std::sort(seq_data.results.begin(), seq_data.results.end());
328 std::sort(data.results.begin(), data.results.end());
329 // the sequential and threaded data should be the same
330 REQUIRE(seq_data.results == data.results);
331}
332
333TEST_CASE("Test query verification failures", "[sequence]") {
334 unique_ptr<QueryResult> result;
335 DuckDB db(nullptr);
336 Connection con(db);
337 con.EnableQueryVerification();
338
339 // create a sequence
340 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
341
342 // getting the value from a sequence results in a verification failure, but only in debug mode
343 con.Query("SELECT nextval('seq')");
344 // normal queries still work after that
345 REQUIRE_NO_FAIL(con.Query("SELECT 1"));
346}
347