1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test standard update behavior", "[update]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db), con2(db);
11
12 // create a table
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3)"));
15
16 result = con.Query("SELECT * FROM test");
17 REQUIRE(CHECK_COLUMN(result, 0, {3}));
18
19 // test simple update
20 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
21 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=1"));
22
23 // not seen yet by con2, only by con1
24 result = con.Query("SELECT * FROM test");
25 REQUIRE(CHECK_COLUMN(result, 0, {1}));
26 result = con2.Query("SELECT * FROM test");
27 REQUIRE(CHECK_COLUMN(result, 0, {3}));
28
29 // commit
30 REQUIRE_NO_FAIL(con.Query("COMMIT"));
31
32 result = con.Query("SELECT * FROM test");
33 REQUIRE(CHECK_COLUMN(result, 0, {1}));
34 result = con2.Query("SELECT * FROM test");
35 REQUIRE(CHECK_COLUMN(result, 0, {1}));
36
37 // now test a rollback
38 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
39 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=4"));
40
41 // not seen yet by con2, only by con1
42 result = con.Query("SELECT * FROM test");
43 REQUIRE(CHECK_COLUMN(result, 0, {4}));
44 result = con2.Query("SELECT * FROM test");
45 REQUIRE(CHECK_COLUMN(result, 0, {1}));
46
47 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
48
49 result = con.Query("SELECT * FROM test");
50 REQUIRE(CHECK_COLUMN(result, 0, {1}));
51 result = con2.Query("SELECT * FROM test");
52 REQUIRE(CHECK_COLUMN(result, 0, {1}));
53}
54
55TEST_CASE("Update the same value multiple times in one transaction", "[update]") {
56 unique_ptr<QueryResult> result;
57 DuckDB db(nullptr);
58 Connection con(db), con2(db);
59
60 // create a table
61 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
62 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3)"));
63
64 result = con.Query("SELECT * FROM test ORDER BY a");
65 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
66
67 // update entire table
68 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
69 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+1"));
70
71 // not seen yet by con2, only by con1
72 result = con.Query("SELECT * FROM test");
73 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
74 result = con2.Query("SELECT * FROM test");
75 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
76
77 // update the entire table again
78 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+1"));
79
80 result = con.Query("SELECT * FROM test ORDER BY a");
81 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
82 result = con2.Query("SELECT * FROM test ORDER BY a");
83 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
84
85 // now commit
86 REQUIRE_NO_FAIL(con.Query("COMMIT"));
87
88 result = con.Query("SELECT * FROM test ORDER BY a");
89 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
90 result = con2.Query("SELECT * FROM test ORDER BY a");
91 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
92
93 // now perform updates one by one
94 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
95 // 5 => 9
96 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=9 WHERE a=5"));
97 result = con.Query("SELECT * FROM test ORDER BY a");
98 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 9}));
99 // test concurrent update in con2, it should fail now
100 REQUIRE_FAIL(con2.Query("UPDATE test SET a=a+1"));
101 result = con2.Query("SELECT * FROM test ORDER BY a");
102 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
103
104 // 3 => 7
105 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=7 WHERE a=3"));
106 result = con.Query("SELECT * FROM test ORDER BY a");
107 REQUIRE(CHECK_COLUMN(result, 0, {4, 7, 9}));
108 // test concurrent update in con2, it should fail now
109 REQUIRE_FAIL(con2.Query("UPDATE test SET a=a+1"));
110 result = con2.Query("SELECT * FROM test ORDER BY a");
111 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
112
113 // 4 => 8
114 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=8 WHERE a=4"));
115 result = con.Query("SELECT * FROM test ORDER BY a");
116 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
117 // test concurrent update in con2, it should fail now
118 REQUIRE_FAIL(con2.Query("UPDATE test SET a=a+1"));
119 result = con2.Query("SELECT * FROM test ORDER BY a");
120 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
121
122 // commit
123 REQUIRE_NO_FAIL(con.Query("COMMIT"));
124
125 result = con.Query("SELECT * FROM test ORDER BY a");
126 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
127 result = con2.Query("SELECT * FROM test ORDER BY a");
128 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
129}
130
131TEST_CASE("Test update behavior with multiple updaters", "[update]") {
132 unique_ptr<QueryResult> result;
133 DuckDB db(nullptr);
134 Connection con(db), con2(db), con3(db), con4(db);
135 Connection u1(db), u2(db), u3(db);
136
137 // create a table, filled with 3 values (1), (2), (3)
138 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
139 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3)"));
140
141 // now we start updating specific values and reading different versions
142 for (idx_t i = 0; i < 2; i++) {
143 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
144 REQUIRE_NO_FAIL(u1.Query("UPDATE test SET a=4 WHERE a=1"));
145 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
146 REQUIRE_NO_FAIL(u2.Query("UPDATE test SET a=5 WHERE a=2"));
147 REQUIRE_NO_FAIL(con3.Query("BEGIN TRANSACTION"));
148 REQUIRE_NO_FAIL(u3.Query("UPDATE test SET a=6 WHERE a=3"));
149 REQUIRE_NO_FAIL(con4.Query("BEGIN TRANSACTION"));
150
151 // now read the different states
152 // con sees {1, 2, 3}
153 result = con.Query("SELECT * FROM test ORDER BY a");
154 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
155 // con2 sees {2, 3, 4}
156 result = con2.Query("SELECT * FROM test ORDER BY a");
157 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
158 // con3 sees {3, 4, 5}
159 result = con3.Query("SELECT * FROM test ORDER BY a");
160 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
161 // con4 sees {4, 5, 6}
162 result = con4.Query("SELECT * FROM test ORDER BY a");
163 REQUIRE(CHECK_COLUMN(result, 0, {4, 5, 6}));
164
165 if (i == 0) {
166 // now verify that we get conflicts when we update values that have been updated AFTER we started
167 REQUIRE_FAIL(con.Query("UPDATE test SET a=99 WHERE a=1"));
168 REQUIRE_FAIL(con2.Query("UPDATE test SET a=99 WHERE a=2"));
169 REQUIRE_FAIL(con3.Query("UPDATE test SET a=99 WHERE a=3"));
170 REQUIRE_NO_FAIL(u1.Query("UPDATE test SET a=a-3"));
171 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
172 REQUIRE_NO_FAIL(con2.Query("ROLLBACK"));
173 REQUIRE_NO_FAIL(con3.Query("ROLLBACK"));
174 REQUIRE_NO_FAIL(con4.Query("ROLLBACK"));
175 } else {
176 // however we CAN update values that were committed BEFORE we started
177 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET a=7 WHERE a=4"));
178 REQUIRE_NO_FAIL(con3.Query("UPDATE test SET a=8 WHERE a=5"));
179 REQUIRE_NO_FAIL(con4.Query("UPDATE test SET a=9 WHERE a=6"));
180 }
181 }
182
183 // now read the different states again
184 // con sees {1, 2, 3} still
185 result = con.Query("SELECT * FROM test ORDER BY a");
186 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
187 // con2 sees {2, 3, 7}
188 result = con2.Query("SELECT * FROM test ORDER BY a");
189 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 7}));
190 // con3 sees {3, 4, 8}
191 result = con3.Query("SELECT * FROM test ORDER BY a");
192 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 8}));
193 // con4 sees {4, 5, 9}
194 result = con4.Query("SELECT * FROM test ORDER BY a");
195 REQUIRE(CHECK_COLUMN(result, 0, {4, 5, 9}));
196 // u1 still sees {4, 5, 6}
197 result = u1.Query("SELECT * FROM test ORDER BY a");
198 REQUIRE(CHECK_COLUMN(result, 0, {4, 5, 6}));
199
200 // now we commit in phases
201 // first we commit con4
202 REQUIRE_NO_FAIL(con4.Query("COMMIT"));
203
204 // con, con2, con3 still see the same data, con4 sees the currently committed data
205 result = con.Query("SELECT * FROM test ORDER BY a");
206 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
207 result = con2.Query("SELECT * FROM test ORDER BY a");
208 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 7}));
209 result = con3.Query("SELECT * FROM test ORDER BY a");
210 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 8}));
211 result = con4.Query("SELECT * FROM test ORDER BY a");
212 REQUIRE(CHECK_COLUMN(result, 0, {4, 5, 9}));
213
214 // then we commit con2
215 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
216
217 // con, con3 still see the same data, con2 and con4 see the committed data
218 result = con.Query("SELECT * FROM test ORDER BY a");
219 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
220 result = con2.Query("SELECT * FROM test ORDER BY a");
221 REQUIRE(CHECK_COLUMN(result, 0, {5, 7, 9}));
222 result = con3.Query("SELECT * FROM test ORDER BY a");
223 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 8}));
224 result = con4.Query("SELECT * FROM test ORDER BY a");
225 REQUIRE(CHECK_COLUMN(result, 0, {5, 7, 9}));
226
227 // then we commit con3
228 REQUIRE_NO_FAIL(con3.Query("COMMIT"));
229
230 // con still sees the same data, but the rest all see the committed data
231 result = con.Query("SELECT * FROM test ORDER BY a");
232 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
233 result = con2.Query("SELECT * FROM test ORDER BY a");
234 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
235 result = con3.Query("SELECT * FROM test ORDER BY a");
236 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
237 result = con4.Query("SELECT * FROM test ORDER BY a");
238 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
239
240 // now we commit con1, this should trigger a cleanup
241 REQUIRE_NO_FAIL(con.Query("COMMIT"));
242
243 // now con also sees the committed data
244 result = con.Query("SELECT * FROM test ORDER BY a");
245 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9}));
246}
247
248TEST_CASE("Test update behavior with multiple updaters and NULL values", "[update]") {
249 unique_ptr<QueryResult> result;
250 DuckDB db(nullptr);
251 Connection con(db), con2(db), con3(db), con4(db), con5(db);
252 Connection u(db);
253
254 // create a table, filled with 3 values (1), (2), (3)
255 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
256 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3)"));
257
258 // now we start updating specific values and reading different versions
259 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
260 REQUIRE_NO_FAIL(u.Query("UPDATE test SET a=NULL WHERE a=1"));
261 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
262 REQUIRE_NO_FAIL(u.Query("UPDATE test SET a=NULL WHERE a=2"));
263 REQUIRE_NO_FAIL(con3.Query("BEGIN TRANSACTION"));
264 REQUIRE_NO_FAIL(u.Query("UPDATE test SET a=NULL WHERE a=3"));
265 REQUIRE_NO_FAIL(con4.Query("BEGIN TRANSACTION"));
266 REQUIRE_NO_FAIL(u.Query("UPDATE test SET a=99 WHERE a IS NULL"));
267 REQUIRE_NO_FAIL(con5.Query("BEGIN TRANSACTION"));
268
269 // now read the different states
270 // con sees {1, 2, 3}
271 result = con.Query("SELECT * FROM test ORDER BY a");
272 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
273 // con2 sees {NULL, 2, 3}
274 result = con2.Query("SELECT * FROM test ORDER BY a");
275 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3}));
276 // con3 sees {NULL, NULL, 3}
277 result = con3.Query("SELECT * FROM test ORDER BY a");
278 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 3}));
279 // con4 sees {NULL, NULL, NULL}
280 result = con4.Query("SELECT * FROM test ORDER BY a");
281 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
282 // con5 sees {99, 99, 99}
283 result = con5.Query("SELECT * FROM test ORDER BY a");
284 REQUIRE(CHECK_COLUMN(result, 0, {99, 99, 99}));
285
286 // now verify that we get conflicts when we update values that have been updated AFTER we started
287 REQUIRE_FAIL(con.Query("UPDATE test SET a=99 WHERE a=1"));
288 REQUIRE_FAIL(con2.Query("UPDATE test SET a=99 WHERE a=2"));
289 REQUIRE_FAIL(con3.Query("UPDATE test SET a=99 WHERE a=3"));
290 REQUIRE_FAIL(con4.Query("UPDATE test SET a=99 WHERE a IS NULL"));
291}
292