1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test mix of updates inserts and deletes", "[update]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db), con2(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3);"));
14
15 result = con.Query("SELECT SUM(a) FROM test");
16 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
17
18 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
19
20 // append from con2
21 REQUIRE_NO_FAIL(con2.Query("INSERT INTO test VALUES (4), (5), (6);"));
22
23 result = con.Query("SELECT SUM(a) FROM test");
24 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
25 result = con2.Query("SELECT SUM(a) FROM test");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(21)}));
27
28 // delete from con2
29 REQUIRE_NO_FAIL(con2.Query("DELETE FROM test WHERE a < 4"));
30
31 result = con.Query("SELECT SUM(a) FROM test");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
33 result = con2.Query("SELECT SUM(a) FROM test");
34 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(15)}));
35
36 // update from con2
37 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET a=a-3"));
38
39 result = con.Query("SELECT SUM(a) FROM test");
40 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
41 result = con2.Query("SELECT SUM(a) FROM test");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
43
44 // now commit
45 REQUIRE_NO_FAIL(con.Query("COMMIT"));
46
47 result = con.Query("SELECT SUM(a) FROM test");
48 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
49 result = con2.Query("SELECT SUM(a) FROM test");
50 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(6)}));
51}
52
53TEST_CASE("Test update and delete of the same tuple", "[transactions]") {
54 unique_ptr<QueryResult> result;
55 DuckDB db(nullptr);
56 Connection con(db), con2(db);
57
58 // on a normal table, we can update and delete the same tuple concurrently without a conflict
59 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
60 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3);"));
61
62 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
63 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION;"));
64
65 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+1;"));
66 REQUIRE_NO_FAIL(con2.Query("DELETE FROM test"));
67
68 result = con.Query("SELECT * FROM test ORDER BY a");
69 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
70 result = con2.Query("SELECT * FROM test ORDER BY a");
71 REQUIRE(CHECK_COLUMN(result, 0, {}));
72
73 REQUIRE_NO_FAIL(con.Query("COMMIT;"));
74 REQUIRE_NO_FAIL(con2.Query("COMMIT;"));
75
76 REQUIRE_NO_FAIL(con.Query("DROP TABLE test;"));
77}
78