1 | #include "catch.hpp" |
2 | #include "test_helpers.hpp" |
3 | |
4 | using namespace duckdb; |
5 | using namespace std; |
6 | |
7 | TEST_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 | |
55 | TEST_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 | |
131 | TEST_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 | |
248 | TEST_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 | |