1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "test_helpers.hpp"
4#include "duckdb/main/appender.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test empty startup", "[storage]") {
10 auto config = GetTestConfig();
11 unique_ptr<DuckDB> db;
12 unique_ptr<QueryResult> result;
13 auto storage_database = TestCreatePath("storage_test");
14
15 // make sure the database does not exist
16 DeleteDatabase(storage_database);
17 // create a database and close it
18 REQUIRE_NOTHROW(db = make_unique<DuckDB>(storage_database, config.get()));
19 db.reset();
20 // reload the database
21 REQUIRE_NOTHROW(db = make_unique<DuckDB>(storage_database, config.get()));
22 db.reset();
23 DeleteDatabase(storage_database);
24}
25
26TEST_CASE("Test empty table", "[storage]") {
27 auto config = GetTestConfig();
28 unique_ptr<QueryResult> result;
29
30 auto storage_database = TestCreatePath("storage_test");
31
32 // make sure the database does not exist
33 // make sure the database does not exist
34 DeleteDatabase(storage_database);
35 {
36 // create a database and insert values
37 DuckDB db(storage_database, config.get());
38 Connection con(db);
39 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b VARCHAR);"));
40
41 result = con.Query("SELECT COUNT(*) FROM test");
42 REQUIRE(CHECK_COLUMN(result, 0, {0}));
43 }
44 {
45 DuckDB db(storage_database, config.get());
46 Connection con(db);
47 result = con.Query("SELECT COUNT(*) FROM test");
48 REQUIRE(CHECK_COLUMN(result, 0, {0}));
49 }
50 {
51 DuckDB db(storage_database, config.get());
52 Connection con(db);
53 result = con.Query("SELECT COUNT(*) FROM test");
54 REQUIRE(CHECK_COLUMN(result, 0, {0}));
55 }
56}
57
58TEST_CASE("Test simple storage", "[storage]") {
59 auto config = GetTestConfig();
60 unique_ptr<QueryResult> result;
61 auto storage_database = TestCreatePath("storage_test");
62
63 // make sure the database does not exist
64 DeleteDatabase(storage_database);
65 {
66 // create a database and insert values
67 DuckDB db(storage_database, config.get());
68 Connection con(db);
69 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
70 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21), (NULL, NULL)"));
71 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER);"));
72 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (13), (12), (11)"));
73 }
74 // reload the database from disk a few times
75 for (idx_t i = 0; i < 2; i++) {
76 DuckDB db(storage_database, config.get());
77 Connection con(db);
78 result = con.Query("SELECT * FROM test ORDER BY a");
79 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 11, 12, 13}));
80 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 22, 21, 22}));
81 result = con.Query("SELECT * FROM test2 ORDER BY a");
82 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
83 }
84 DeleteDatabase(storage_database);
85}
86
87TEST_CASE("Test storing NULLs and strings", "[storage]") {
88 auto config = GetTestConfig();
89 unique_ptr<QueryResult> result;
90 auto storage_database = TestCreatePath("storage_test");
91
92 // make sure the database does not exist
93 DeleteDatabase(storage_database);
94 {
95 // create a database and insert values
96 DuckDB db(storage_database, config.get());
97 Connection con(db);
98 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b STRING);"));
99 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (NULL, 'hello'), "
100 "(13, 'abcdefgh'), (12, NULL)"));
101 }
102 // reload the database from disk a few times
103 for (idx_t i = 0; i < 2; i++) {
104 DuckDB db(storage_database, config.get());
105 Connection con(db);
106 result = con.Query("SELECT a, b FROM test ORDER BY a");
107 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 12, 13}));
108 REQUIRE(CHECK_COLUMN(result, 1, {"hello", Value(), "abcdefgh"}));
109 }
110 DeleteDatabase(storage_database);
111}
112
113TEST_CASE("Test updates/deletes and strings", "[storage]") {
114 auto config = GetTestConfig();
115 unique_ptr<QueryResult> result;
116 auto storage_database = TestCreatePath("storage_test");
117
118 // make sure the database does not exist
119 DeleteDatabase(storage_database);
120 {
121 // create a database and insert values
122 DuckDB db(storage_database, config.get());
123 Connection con(db);
124 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b STRING);"));
125 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (NULL, 'hello'), "
126 "(13, 'abcdefgh'), (12, NULL)"));
127 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=NULL WHERE a IS NULL"));
128 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=12"));
129 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (12, NULL)"));
130 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b='test123' WHERE a=12"));
131 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+1"));
132 result = con.Query("SELECT a, b FROM test ORDER BY a");
133 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 13, 14}));
134 REQUIRE(CHECK_COLUMN(result, 1, {Value(), "test123", "abcdefgh"}));
135 }
136 // reload the database from disk a few times
137 for (idx_t i = 0; i < 2; i++) {
138 DuckDB db(storage_database, config.get());
139 Connection con(db);
140 result = con.Query("SELECT a, b FROM test ORDER BY a");
141 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 13, 14}));
142 REQUIRE(CHECK_COLUMN(result, 1, {Value(), "test123", "abcdefgh"}));
143 }
144 DeleteDatabase(storage_database);
145}
146
147TEST_CASE("Test deletes with storage", "[storage]") {
148 auto config = GetTestConfig();
149 unique_ptr<QueryResult> result;
150 auto storage_database = TestCreatePath("storage_test");
151
152 // make sure the database does not exist
153 DeleteDatabase(storage_database);
154 {
155 // create a database and insert values
156 DuckDB db(storage_database, config.get());
157 Connection con(db);
158 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
159 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
160 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (12, 21), (13, 22), (12, 21)"));
161 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=12"));
162 result = con.Query("SELECT a, b FROM test ORDER BY a");
163 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
164 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
165 REQUIRE_NO_FAIL(con.Query("COMMIT"));
166 }
167 // reload the database from disk
168 for (idx_t i = 0; i < 2; i++) {
169 DuckDB db(storage_database, config.get());
170 Connection con(db);
171 result = con.Query("SELECT a, b FROM test ORDER BY a");
172 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
173 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
174 }
175 DeleteDatabase(storage_database);
176}
177
178TEST_CASE("Test updates with storage", "[storage]") {
179 auto config = GetTestConfig();
180 unique_ptr<QueryResult> result;
181 auto storage_database = TestCreatePath("storage_test");
182
183 // make sure the database does not exist
184 DeleteDatabase(storage_database);
185 {
186 // create a database and insert values
187 DuckDB db(storage_database, config.get());
188 Connection con(db);
189 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
190 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
191 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
192 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=b+1 WHERE a=11"));
193 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=b+1 WHERE a=11"));
194 REQUIRE_NO_FAIL(con.Query("COMMIT"));
195 }
196 // reload the database from disk
197 for (idx_t i = 0; i < 2; i++) {
198 DuckDB db(storage_database, config.get());
199 Connection con(db);
200 result = con.Query("SELECT a, b FROM test ORDER BY a");
201 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
202 REQUIRE(CHECK_COLUMN(result, 1, {24, 21, 22}));
203 }
204 DeleteDatabase(storage_database);
205}
206
207TEST_CASE("Test mix of updates and deletes with storage", "[storage]") {
208 auto config = GetTestConfig();
209 unique_ptr<QueryResult> result;
210 auto storage_database = TestCreatePath("storage_test");
211
212 // make sure the database does not exist
213 DeleteDatabase(storage_database);
214 {
215 // create a database and insert values
216 DuckDB db(storage_database, config.get());
217 Connection con(db);
218 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
219 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
220 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
221 for (size_t i = 0; i < 1000; i++) {
222 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=b+1 WHERE a=11"));
223 }
224 result = con.Query("DELETE FROM test WHERE a=12");
225 REQUIRE(CHECK_COLUMN(result, 0, {1}));
226 REQUIRE_NO_FAIL(con.Query("COMMIT"));
227 }
228 // reload the database from disk
229 for (idx_t i = 0; i < 2; i++) {
230 DuckDB db(storage_database, config.get());
231 Connection con(db);
232 result = con.Query("SELECT a, b FROM test ORDER BY a");
233 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
234 REQUIRE(CHECK_COLUMN(result, 1, {1022, 22}));
235 }
236 DeleteDatabase(storage_database);
237}
238
239TEST_CASE("Test large inserts in a single transaction", "[storage]") {
240 auto config = GetTestConfig();
241 unique_ptr<QueryResult> result;
242 auto storage_database = TestCreatePath("storage_test");
243
244 // make sure the database does not exist
245 int64_t expected_sum_a = 0, expected_sum_b = 0, expected_count = 0;
246 DeleteDatabase(storage_database);
247 {
248 // create a database and insert values
249 DuckDB db(storage_database, config.get());
250 Connection con(db);
251 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
252 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
253 for (idx_t i = 0; i < 1000; i++) {
254 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
255 expected_sum_a += 11 + 13;
256 expected_sum_b += 22 + 22;
257 expected_count += 2;
258 }
259 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=12"));
260 REQUIRE_NO_FAIL(con.Query("COMMIT"));
261
262 result = con.Query("SELECT SUM(a), SUM(b), COUNT(*) FROM test");
263 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_a)}));
264 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(expected_sum_b)}));
265 REQUIRE(CHECK_COLUMN(result, 2, {Value::BIGINT(expected_count)}));
266 }
267 // reload the database from disk
268 for (idx_t i = 0; i < 2; i++) {
269 DuckDB db(storage_database, config.get());
270 Connection con(db);
271 result = con.Query("SELECT SUM(a), SUM(b), COUNT(*) FROM test");
272 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_a)}));
273 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(expected_sum_b)}));
274 REQUIRE(CHECK_COLUMN(result, 2, {Value::BIGINT(expected_count)}));
275 }
276 DeleteDatabase(storage_database);
277}
278
279TEST_CASE("Test interleaving of insertions/updates/deletes on multiple tables", "[storage][.]") {
280 auto config = GetTestConfig();
281 unique_ptr<QueryResult> result;
282 auto storage_database = TestCreatePath("storage_test");
283
284 // make sure the database does not exist
285 DeleteDatabase(storage_database);
286 {
287 // create a database and insert values
288 DuckDB db(storage_database, config.get());
289 Connection con(db);
290 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
291 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
292 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER, b INTEGER);"));
293 idx_t test_insert = 0, test_insert2 = 0;
294 for (idx_t i = 0; i < 1000; i++) {
295 idx_t stage = i % 7;
296 switch (stage) {
297 case 0:
298 for (; test_insert < i; test_insert++) {
299 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(test_insert) + ")"));
300 }
301 break;
302 case 1:
303 for (; test_insert2 < i; test_insert2++) {
304 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (" + to_string(test_insert) + ", " +
305 to_string(test_insert) + " + 2)"));
306 }
307 break;
308 case 2:
309 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a = a + 1 WHERE a % 2 = 0"));
310 break;
311 case 3:
312 REQUIRE_NO_FAIL(con.Query("UPDATE test2 SET a = a + 1 WHERE a % 2 = 0"));
313 break;
314 case 4:
315 REQUIRE_NO_FAIL(con.Query("UPDATE test2 SET b = b + 1 WHERE b % 2 = 0"));
316 break;
317 case 5:
318 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a % 5 = 0"));
319 break;
320 default:
321 REQUIRE_NO_FAIL(con.Query("DELETE FROM test2 WHERE a % 5 = 0"));
322 break;
323 }
324 }
325 REQUIRE_NO_FAIL(con.Query("COMMIT"));
326
327 result = con.Query("SELECT SUM(a) FROM test ORDER BY 1");
328 REQUIRE(CHECK_COLUMN(result, 0, {396008}));
329
330 result = con.Query("SELECT SUM(a), SUM(b) FROM test2 ORDER BY 1");
331 REQUIRE(CHECK_COLUMN(result, 0, {403915}));
332 REQUIRE(CHECK_COLUMN(result, 1, {405513}));
333 }
334 // reload the database from disk
335 for (idx_t i = 0; i < 2; i++) {
336 DuckDB db(storage_database, config.get());
337 Connection con(db);
338 result = con.Query("SELECT SUM(a) FROM test ORDER BY 1");
339 REQUIRE(CHECK_COLUMN(result, 0, {396008}));
340
341 result = con.Query("SELECT SUM(a), SUM(b) FROM test2 ORDER BY 1");
342 REQUIRE(CHECK_COLUMN(result, 0, {403915}));
343 REQUIRE(CHECK_COLUMN(result, 1, {405513}));
344 }
345 DeleteDatabase(storage_database);
346}
347
348TEST_CASE("Test update/deletes on big table", "[storage][.]") {
349 auto config = GetTestConfig();
350 unique_ptr<QueryResult> result;
351 auto storage_database = TestCreatePath("storage_test");
352
353 // make sure the database does not exist
354 DeleteDatabase(storage_database);
355 {
356 // create a big table
357 DuckDB db(storage_database, config.get());
358 Connection con(db);
359 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
360 Appender appender(con, "test");
361 for (int32_t i = 0; i < 100000; i++) {
362 appender.BeginRow();
363 appender.Append<int32_t>(i % 1000);
364 appender.EndRow();
365 }
366 appender.Close();
367 // now perform some updates
368 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=2000 WHERE a=1"));
369 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=2 OR a=17"));
370
371 result = con.Query("SELECT SUM(a), COUNT(a) FROM test");
372 REQUIRE(CHECK_COLUMN(result, 0, {50148000}));
373 REQUIRE(CHECK_COLUMN(result, 1, {99800}));
374 result = con.Query("SELECT COUNT(a) FROM test WHERE a=0");
375 REQUIRE(CHECK_COLUMN(result, 0, {100}));
376 result = con.Query("SELECT COUNT(a) FROM test WHERE a=1");
377 REQUIRE(CHECK_COLUMN(result, 0, {0}));
378 result = con.Query("SELECT COUNT(a) FROM test WHERE a=2");
379 REQUIRE(CHECK_COLUMN(result, 0, {0}));
380 result = con.Query("SELECT COUNT(a) FROM test WHERE a=17");
381 REQUIRE(CHECK_COLUMN(result, 0, {0}));
382 }
383 // reload the database from disk
384 for (idx_t i = 0; i < 2; i++) {
385 DuckDB db(storage_database, config.get());
386 Connection con(db);
387 result = con.Query("SELECT SUM(a), COUNT(a) FROM test");
388 REQUIRE(CHECK_COLUMN(result, 0, {50148000}));
389 REQUIRE(CHECK_COLUMN(result, 1, {99800}));
390 result = con.Query("SELECT COUNT(a) FROM test WHERE a=0");
391 REQUIRE(CHECK_COLUMN(result, 0, {100}));
392 result = con.Query("SELECT COUNT(a) FROM test WHERE a=1");
393 REQUIRE(CHECK_COLUMN(result, 0, {0}));
394 result = con.Query("SELECT COUNT(a) FROM test WHERE a=2");
395 REQUIRE(CHECK_COLUMN(result, 0, {0}));
396 result = con.Query("SELECT COUNT(a) FROM test WHERE a=17");
397 REQUIRE(CHECK_COLUMN(result, 0, {0}));
398 }
399 DeleteDatabase(storage_database);
400}
401
402TEST_CASE("Test updates/deletes/insertions on persistent segments", "[storage]") {
403 auto config = GetTestConfig();
404 unique_ptr<QueryResult> result;
405 auto storage_database = TestCreatePath("storage_test");
406
407 // make sure the database does not exist
408 DeleteDatabase(storage_database);
409 {
410 // create a database and insert values
411 DuckDB db(storage_database, config.get());
412 Connection con(db);
413 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(a INTEGER, b INTEGER);"));
414 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 3), (NULL, NULL)"));
415 }
416 // reload the database from disk
417 {
418 DuckDB db(storage_database, config.get());
419 Connection con(db);
420 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 2)"));
421 result = con.Query("SELECT * FROM test ORDER BY a");
422 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2}));
423 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 2}));
424 }
425 // reload the database from disk, we do this again because checkpointing at startup causes this to follow a
426 // different code path
427 {
428 DuckDB db(storage_database, config.get());
429 Connection con(db);
430 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
431
432 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=4 WHERE a=1"));
433
434 result = con.Query("SELECT * FROM test ORDER BY a");
435 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
436 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 4, 2, 3}));
437 }
438 {
439 DuckDB db(storage_database, config.get());
440 Connection con(db);
441 result = con.Query("SELECT * FROM test ORDER BY a");
442 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
443 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 4, 2, 3}));
444
445 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=4, b=4 WHERE a=1"));
446
447 result = con.Query("SELECT * FROM test ORDER BY a");
448 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 4}));
449 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
450 }
451 {
452 DuckDB db(storage_database, config.get());
453 Connection con(db);
454 result = con.Query("SELECT * FROM test ORDER BY a");
455 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 4}));
456 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
457
458 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=5, a=6 WHERE a=4"));
459
460 result = con.Query("SELECT * FROM test ORDER BY a");
461 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 6}));
462 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 5}));
463 }
464 {
465 DuckDB db(storage_database, config.get());
466 Connection con(db);
467 result = con.Query("SELECT * FROM test ORDER BY a");
468 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 6}));
469 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 5}));
470
471 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=7 WHERE a=3"));
472
473 result = con.Query("SELECT * FROM test ORDER BY a");
474 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 6}));
475 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 7, 5}));
476 }
477 DeleteDatabase(storage_database);
478}
479