1#include "catch.hpp"
2#include "test_helpers.hpp"
3#include "duckdb/storage/storage_info.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test update of string columns", "[update]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db), con2(db);
12
13 // create a table
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('hello'), ('world')"));
16
17 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION;"));
18
19 // scan the table
20 result = con.Query("SELECT * FROM test ORDER BY a");
21 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
22 result = con2.Query("SELECT * FROM test ORDER BY a");
23 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
24
25 // test a delete from the table
26 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a='hello';"));
27
28 result = con.Query("SELECT * FROM test ORDER BY a");
29 REQUIRE(CHECK_COLUMN(result, 0, {"world"}));
30 result = con2.Query("SELECT * FROM test ORDER BY a");
31 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
32
33 // now test an update of the table
34 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='hello';"));
35
36 result = con.Query("SELECT * FROM test ORDER BY a");
37 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
38 result = con2.Query("SELECT * FROM test ORDER BY a");
39 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
40
41 REQUIRE_NO_FAIL(con2.Query("COMMIT;"));
42
43 result = con2.Query("SELECT * FROM test ORDER BY a");
44 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
45}
46
47TEST_CASE("Test update of string columns with NULLs", "[update]") {
48 unique_ptr<QueryResult> result;
49 DuckDB db(nullptr);
50 Connection con(db), con2(db);
51
52 // create a table
53 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
54 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('hello'), ('world')"));
55
56 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION;"));
57
58 // update a string to NULL
59 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=NULL where a='world';"));
60
61 result = con.Query("SELECT * FROM test ORDER BY a");
62 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "hello"}));
63 result = con2.Query("SELECT * FROM test ORDER BY a");
64 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
65
66 REQUIRE_NO_FAIL(con2.Query("COMMIT;"));
67
68 result = con2.Query("SELECT * FROM test ORDER BY a");
69 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "hello"}));
70}
71
72TEST_CASE("Test repeated update of string in same segment", "[update]") {
73 unique_ptr<QueryResult> result;
74 DuckDB db(nullptr);
75 Connection con(db), con2(db);
76
77 // create a table
78 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
79 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('hello'), ('world')"));
80
81 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION;"));
82
83 // scan the table
84 result = con.Query("SELECT * FROM test ORDER BY a");
85 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
86 result = con2.Query("SELECT * FROM test ORDER BY a");
87 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
88
89 // test a number of repeated updates
90 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test' WHERE a='hello';"));
91 result = con.Query("SELECT * FROM test ORDER BY a");
92 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
93 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test2' WHERE a='world';"));
94 result = con.Query("SELECT * FROM test ORDER BY a");
95 REQUIRE(CHECK_COLUMN(result, 0, {"test", "test2"}));
96
97 result = con2.Query("SELECT * FROM test ORDER BY a");
98 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
99
100 REQUIRE_NO_FAIL(con2.Query("COMMIT;"));
101
102 result = con.Query("SELECT * FROM test ORDER BY a");
103 REQUIRE(CHECK_COLUMN(result, 0, {"test", "test2"}));
104}
105
106TEST_CASE("Test repeated update of string in same transaction", "[update]") {
107 unique_ptr<QueryResult> result;
108 DuckDB db(nullptr);
109 Connection con(db);
110
111 // create a table
112 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
113 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('hello'), ('world')"));
114
115 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
116
117 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test' WHERE a='hello';"));
118 result = con.Query("SELECT * FROM test ORDER BY a");
119 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
120 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test2' WHERE a='world';"));
121 result = con.Query("SELECT * FROM test ORDER BY a");
122 REQUIRE(CHECK_COLUMN(result, 0, {"test", "test2"}));
123
124 REQUIRE_NO_FAIL(con.Query("COMMIT;"));
125
126 result = con.Query("SELECT * FROM test ORDER BY a");
127 REQUIRE(CHECK_COLUMN(result, 0, {"test", "test2"}));
128}
129
130TEST_CASE("Test rollback of string update", "[update]") {
131 unique_ptr<QueryResult> result;
132 DuckDB db(nullptr);
133 Connection con(db), con2(db);
134
135 // create a table
136 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
137 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('hello'), ('world')"));
138
139 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
140
141 // perform an update within the transaction
142 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test' WHERE a='hello';"));
143
144 result = con.Query("SELECT * FROM test ORDER BY a");
145 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
146 result = con2.Query("SELECT * FROM test ORDER BY a");
147 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
148
149 // now rollback the update
150 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
151
152 result = con.Query("SELECT * FROM test ORDER BY a");
153 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
154 result = con2.Query("SELECT * FROM test ORDER BY a");
155 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
156
157 // rollback of a value that is updated twice
158 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test' WHERE a='hello';"));
159
160 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
161 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test2' WHERE a='test';"));
162
163 result = con.Query("SELECT * FROM test ORDER BY a");
164 REQUIRE(CHECK_COLUMN(result, 0, {"test2", "world"}));
165 result = con2.Query("SELECT * FROM test ORDER BY a");
166 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
167
168 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
169
170 result = con.Query("SELECT * FROM test ORDER BY a");
171 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
172 result = con2.Query("SELECT * FROM test ORDER BY a");
173 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
174
175 // test rollback of string update in different part
176 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
177 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='test2' WHERE a='world';"));
178
179 result = con.Query("SELECT * FROM test ORDER BY a");
180 REQUIRE(CHECK_COLUMN(result, 0, {"test", "test2"}));
181 result = con2.Query("SELECT * FROM test ORDER BY a");
182 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
183
184 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
185
186 result = con.Query("SELECT * FROM test ORDER BY a");
187 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
188 result = con2.Query("SELECT * FROM test ORDER BY a");
189 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
190}
191
192TEST_CASE("Test rollback of string update with NULL", "[update]") {
193 unique_ptr<QueryResult> result;
194 DuckDB db(nullptr);
195 Connection con(db), con2(db);
196
197 // create a table
198 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
199 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('test'), ('world')"));
200
201 // test rollback of value -> NULL update
202 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
203 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=NULL WHERE a='world';"));
204
205 result = con.Query("SELECT * FROM test ORDER BY a");
206 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "test"}));
207 result = con2.Query("SELECT * FROM test ORDER BY a");
208 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
209
210 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
211
212 result = con.Query("SELECT * FROM test ORDER BY a");
213 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
214 result = con2.Query("SELECT * FROM test ORDER BY a");
215 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
216
217 // test rollback of NULL -> value update
218 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=NULL WHERE a='world';"));
219 result = con.Query("SELECT * FROM test ORDER BY a");
220 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "test"}));
221
222 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
223 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='world' WHERE a IS NULL;"));
224
225 result = con.Query("SELECT * FROM test ORDER BY a");
226 REQUIRE(CHECK_COLUMN(result, 0, {"test", "world"}));
227 result = con2.Query("SELECT * FROM test ORDER BY a");
228 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "test"}));
229
230 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
231
232 result = con.Query("SELECT * FROM test ORDER BY a");
233 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "test"}));
234 result = con2.Query("SELECT * FROM test ORDER BY a");
235 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "test"}));
236}
237
238TEST_CASE("Test string updates with many strings", "[update][.]") {
239 unique_ptr<QueryResult> result;
240 DuckDB db(nullptr);
241 Connection con(db), con2(db);
242
243 // create a table
244 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
245 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('a'), ('b'), ('c'), (NULL)"));
246
247 // insert the same strings many times
248 idx_t size = 4 * sizeof(int32_t);
249 while (size < Storage::BLOCK_SIZE * 2) {
250 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT * FROM test"));
251 size *= 4;
252 }
253
254 // verify that the distinct values are correct
255 result = con.Query("SELECT DISTINCT a FROM test ORDER BY a");
256 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "a", "b", "c"}));
257 result = con2.Query("SELECT DISTINCT a FROM test ORDER BY a");
258 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "a", "b", "c"}));
259
260 // test update of string column in another transaction
261 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
262 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='aa' WHERE a='a';"));
263
264 // verify that the values were updated
265 result = con.Query("SELECT DISTINCT a FROM test ORDER BY a");
266 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "aa", "b", "c"}));
267 result = con2.Query("SELECT DISTINCT a FROM test ORDER BY a");
268 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "a", "b", "c"}));
269
270 // now roll it back
271 REQUIRE_NO_FAIL(con.Query("ROLLBACK;"));
272
273 // the values should be back to normal
274 result = con.Query("SELECT DISTINCT a FROM test ORDER BY a");
275 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "a", "b", "c"}));
276 result = con2.Query("SELECT DISTINCT a FROM test ORDER BY a");
277 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "a", "b", "c"}));
278
279 // this time do the same but commit it
280 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='aa' WHERE a='a';"));
281
282 // now both connections have the updated value
283 result = con.Query("SELECT DISTINCT a FROM test ORDER BY a");
284 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "aa", "b", "c"}));
285 result = con2.Query("SELECT DISTINCT a FROM test ORDER BY a");
286 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "aa", "b", "c"}));
287}
288
289TEST_CASE("Test update of big string", "[update][.]") {
290 unique_ptr<QueryResult> result;
291 DuckDB db(nullptr);
292 Connection con(db), con2(db);
293
294 // create a table
295 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a VARCHAR);"));
296 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES ('abcdefghijklmnopqrstuvwxyz')"));
297
298 // increase the size of the string until it is bigger than a block
299 idx_t size = 26;
300 while (size < Storage::BLOCK_SIZE * 2) {
301 // concat the string 10x and insert it
302 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT a||a||a||a||a||a||a||a||a||a FROM test"));
303 // delete the old value
304 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE length(a) = (SELECT MIN(length(a)) FROM test)"));
305 size *= 10;
306 }
307
308 // verify that the string length is correct
309 result = con.Query("SELECT LENGTH(a) FROM test");
310 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(size)}));
311
312 // now update the big string in a separate transaction
313 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
314 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a='a'"));
315
316 // verify the lengths
317 result = con.Query("SELECT LENGTH(a) FROM test");
318 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(1)}));
319 result = con2.Query("SELECT LENGTH(a) FROM test");
320 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(size)}));
321
322 // now commit
323 REQUIRE_NO_FAIL(con.Query("COMMIT"));
324
325 // the big string is gone now
326 result = con.Query("SELECT LENGTH(a) FROM test");
327 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(1)}));
328 result = con2.Query("SELECT LENGTH(a) FROM test");
329 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(1)}));
330}
331