1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test deletions", "[delete]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i INTEGER);"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (42);"));
14
15 result = con.Query("SELECT COUNT(*) FROM a;");
16 REQUIRE(CHECK_COLUMN(result, 0, {1}));
17
18 // delete everything
19 result = con.Query("DELETE FROM a;");
20 REQUIRE(CHECK_COLUMN(result, 0, {1}));
21
22 // nothing left
23 result = con.Query("SELECT COUNT(*) FROM a;");
24 REQUIRE(CHECK_COLUMN(result, 0, {0}));
25
26 //////////////
27 // ROLLBACK //
28 //////////////
29 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (42);"));
30 result = con.Query("SELECT COUNT(*) FROM a;");
31 REQUIRE(CHECK_COLUMN(result, 0, {1}));
32
33 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
34 // delete everything
35 result = con.Query("DELETE FROM a;");
36 REQUIRE(CHECK_COLUMN(result, 0, {1}));
37 result = con.Query("SELECT COUNT(*) FROM a;");
38 REQUIRE(CHECK_COLUMN(result, 0, {0}));
39 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
40
41 // after rollback, the data is back
42 result = con.Query("SELECT COUNT(*) FROM a;");
43 REQUIRE(CHECK_COLUMN(result, 0, {1}));
44}
45
46TEST_CASE("Test scan with large deletions", "[delete][.]") {
47 unique_ptr<QueryResult> result;
48 DuckDB db(nullptr);
49 Connection con(db);
50
51 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
52 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i INTEGER);"));
53 for (idx_t i = 0; i < 10000; i++) {
54 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (" + to_string(i) + ")"));
55 }
56 REQUIRE_NO_FAIL(con.Query("COMMIT;"));
57
58 // delete a segment of the table
59 REQUIRE_NO_FAIL(con.Query("DELETE FROM a WHERE i >= 2000 AND i < 5000;"));
60
61 result = con.Query("SELECT COUNT(*) FROM a;");
62 REQUIRE(CHECK_COLUMN(result, 0, {7000}));
63}
64
65TEST_CASE("Test scan with many segmented deletions", "[delete][.]") {
66 unique_ptr<QueryResult> result;
67 DuckDB db(nullptr);
68 Connection con(db);
69
70 idx_t n = 20;
71 idx_t val_count = 1024;
72 vector<idx_t> tested_values = {0, 1, val_count - 2, val_count - 1};
73
74 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
75 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i INTEGER);"));
76 for (idx_t k = 0; k < n; k++) {
77 for (idx_t i = 0; i < val_count; i++) {
78 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (" + to_string(i) + ")"));
79 }
80 }
81 REQUIRE_NO_FAIL(con.Query("COMMIT;"));
82
83 // verify the initial count
84 for (idx_t j = 0; j < 2; j++) {
85 // verify the initial count again twice
86 result = con.Query("SELECT COUNT(*) FROM a");
87 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(n * val_count)}));
88 }
89
90 // for every value, delete it, verify the count and then roll back
91 for (auto &i : tested_values) {
92 // begin a transaction and delete tuples of a specific value
93 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
94 REQUIRE_NO_FAIL(con.Query("DELETE FROM a WHERE i = " + to_string(i)));
95 // verify the count
96 result = con.Query("SELECT COUNT(*) FROM a");
97 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(n * (val_count - 1))}));
98 // rollback
99 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
100
101 for (idx_t j = 0; j < 2; j++) {
102 // verify the initial count again twice
103 result = con.Query("SELECT COUNT(*) FROM a");
104 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(n * val_count)}));
105 }
106 }
107
108 // for every value, delete it in a separate connection and verify the count
109 vector<unique_ptr<Connection>> cons;
110 for (auto &i : tested_values) {
111 auto new_connection = make_unique<Connection>(db);
112 // begin a transaction and delete tuples of a specific value
113 REQUIRE_NO_FAIL(new_connection->Query("BEGIN TRANSACTION"));
114 REQUIRE_NO_FAIL(new_connection->Query("DELETE FROM a WHERE i = " + to_string(i)));
115 // verify the count
116 result = new_connection->Query("SELECT COUNT(*) FROM a");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(n * (val_count - 1))}));
118 // store the connection for now
119 cons.push_back(move(new_connection));
120 }
121 result = con.Query("SELECT COUNT(*) FROM a");
122 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(n * val_count)}));
123}
124