1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5#include "duckdb/execution/index/art/art_key.hpp"
6
7#include <cfloat>
8#include <iostream>
9
10using namespace duckdb;
11using namespace std;
12
13TEST_CASE("Test index creation statements with multiple connections", "[art][.]") {
14 unique_ptr<QueryResult> result;
15 DuckDB db(nullptr);
16 Connection con(db), con2(db);
17
18 // create a table
19 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
20 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
21 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 3)"));
22 for (int i = 0; i < 3000; i++) {
23 int key = i + 10;
24 REQUIRE_NO_FAIL(
25 con.Query("INSERT INTO integers VALUES (" + to_string(i + 10) + ", " + to_string(i + 12) + ")"));
26 result = con.Query("SELECT i FROM integers WHERE i=" + to_string(i + 10));
27 REQUIRE(CHECK_COLUMN(result, 0, {Value(key)}));
28 }
29
30 // both con and con2 start a transaction
31 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
32 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
33
34 // con2 updates the integers array before index creation
35 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=4 WHERE i=1"));
36
37 // con should see the old state
38 result = con.Query("SELECT j FROM integers WHERE i=1");
39 REQUIRE(CHECK_COLUMN(result, 0, {3}));
40
41 // con2 should see the updated state
42 result = con2.Query("SELECT j FROM integers WHERE i=4");
43 REQUIRE(CHECK_COLUMN(result, 0, {3}));
44
45 // now we commit con
46 REQUIRE_NO_FAIL(con.Query("COMMIT"));
47
48 // con should still see the old state
49 result = con.Query("SELECT j FROM integers WHERE i=1");
50 REQUIRE(CHECK_COLUMN(result, 0, {3}));
51
52 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
53
54 // after commit of con2 - con should see the old state
55 result = con.Query("SELECT j FROM integers WHERE i=4");
56 REQUIRE(CHECK_COLUMN(result, 0, {3}));
57
58 // now we update the index again, this time after index creation
59 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=7 WHERE i=4"));
60 // the new state should be visible
61 result = con.Query("SELECT j FROM integers WHERE i=7");
62 REQUIRE(CHECK_COLUMN(result, 0, {3}));
63}
64
65TEST_CASE("Test ART index on table with multiple columns", "[art]") {
66 unique_ptr<QueryResult> result;
67 DuckDB db(nullptr);
68 Connection con(db);
69
70 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i BIGINT, j INTEGER, k VARCHAR)"));
71 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(j)"));
72
73 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (10, 1, 'hello'), (11, 2, 'world')"));
74
75 // condition on "i"
76 result = con.Query("SELECT i FROM integers WHERE i=10");
77 REQUIRE(CHECK_COLUMN(result, 0, {10}));
78 result = con.Query("SELECT * FROM integers WHERE i=10");
79 REQUIRE(CHECK_COLUMN(result, 0, {10}));
80 REQUIRE(CHECK_COLUMN(result, 1, {1}));
81 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
82
83 // condition on "j"
84 result = con.Query("SELECT j FROM integers WHERE j=1");
85 REQUIRE(CHECK_COLUMN(result, 0, {1}));
86 result = con.Query("SELECT * FROM integers WHERE j=1");
87 REQUIRE(CHECK_COLUMN(result, 0, {10}));
88 REQUIRE(CHECK_COLUMN(result, 1, {1}));
89 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
90
91 // condition on "k"
92 result = con.Query("SELECT k FROM integers WHERE k='hello'");
93 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
94 result = con.Query("SELECT i, k FROM integers WHERE k='hello'");
95 REQUIRE(CHECK_COLUMN(result, 0, {10}));
96 REQUIRE(CHECK_COLUMN(result, 1, {"hello"}));
97 result = con.Query("SELECT * FROM integers WHERE k='hello'");
98 REQUIRE(CHECK_COLUMN(result, 0, {10}));
99 REQUIRE(CHECK_COLUMN(result, 1, {1}));
100 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
101}
102
103TEST_CASE("Test ART index on table with updates to other columns", "[art]") {
104 unique_ptr<QueryResult> result;
105 DuckDB db(nullptr);
106 Connection con(db), con2(db);
107
108 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i BIGINT, j INTEGER, k VARCHAR)"));
109 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(j)"));
110
111 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (10, 1, 'hello'), (11, 2, 'world')"));
112
113 // condition on "j"
114 result = con.Query("SELECT * FROM integers WHERE j=1");
115 REQUIRE(CHECK_COLUMN(result, 0, {10}));
116 REQUIRE(CHECK_COLUMN(result, 1, {1}));
117 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
118
119 // now update the column
120 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
121 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=100, k='update' WHERE j=1"));
122
123 // con sees the old state, con2 sees the new state
124 result = con.Query("SELECT * FROM integers WHERE j=1");
125 REQUIRE(CHECK_COLUMN(result, 0, {10}));
126 REQUIRE(CHECK_COLUMN(result, 1, {1}));
127 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
128 result = con2.Query("SELECT * FROM integers WHERE j=1");
129 REQUIRE(CHECK_COLUMN(result, 0, {100}));
130 REQUIRE(CHECK_COLUMN(result, 1, {1}));
131 REQUIRE(CHECK_COLUMN(result, 2, {"update"}));
132
133 // after a commit, both see the new state
134 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
135
136 result = con.Query("SELECT * FROM integers WHERE j=1");
137 REQUIRE(CHECK_COLUMN(result, 0, {100}));
138 REQUIRE(CHECK_COLUMN(result, 1, {1}));
139 REQUIRE(CHECK_COLUMN(result, 2, {"update"}));
140 result = con2.Query("SELECT * FROM integers WHERE j=1");
141 REQUIRE(CHECK_COLUMN(result, 0, {100}));
142 REQUIRE(CHECK_COLUMN(result, 1, {1}));
143 REQUIRE(CHECK_COLUMN(result, 2, {"update"}));
144
145 // now do the same but with two outstanding updates
146 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
147 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
148 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=20, k='t1' WHERE j=1"));
149 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=21, k='t2' WHERE j=2"));
150
151 // con1 sees the updated state for the first tuple, but the old state for the new tuple
152 result = con.Query("SELECT * FROM integers WHERE j=1");
153 REQUIRE(CHECK_COLUMN(result, 0, {20}));
154 REQUIRE(CHECK_COLUMN(result, 1, {1}));
155 REQUIRE(CHECK_COLUMN(result, 2, {"t1"}));
156 result = con.Query("SELECT * FROM integers WHERE j=2");
157 REQUIRE(CHECK_COLUMN(result, 0, {11}));
158 REQUIRE(CHECK_COLUMN(result, 1, {2}));
159 REQUIRE(CHECK_COLUMN(result, 2, {"world"}));
160 result = con.Query("SELECT * FROM integers ORDER BY j");
161 REQUIRE(CHECK_COLUMN(result, 0, {20, 11}));
162 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
163 REQUIRE(CHECK_COLUMN(result, 2, {"t1", "world"}));
164 // con2 sees the updated state for the second tuple, but the old state for the new tuple
165 result = con2.Query("SELECT * FROM integers WHERE j=1");
166 REQUIRE(CHECK_COLUMN(result, 0, {100}));
167 REQUIRE(CHECK_COLUMN(result, 1, {1}));
168 REQUIRE(CHECK_COLUMN(result, 2, {"update"}));
169 result = con2.Query("SELECT * FROM integers WHERE j=2");
170 REQUIRE(CHECK_COLUMN(result, 0, {21}));
171 REQUIRE(CHECK_COLUMN(result, 1, {2}));
172 REQUIRE(CHECK_COLUMN(result, 2, {"t2"}));
173 result = con2.Query("SELECT * FROM integers ORDER BY j");
174 REQUIRE(CHECK_COLUMN(result, 0, {100, 21}));
175 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
176 REQUIRE(CHECK_COLUMN(result, 2, {"update", "t2"}));
177
178 // after commit, both see the updated state
179 REQUIRE_NO_FAIL(con.Query("COMMIT"));
180 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
181
182 result = con.Query("SELECT * FROM integers WHERE j=1");
183 REQUIRE(CHECK_COLUMN(result, 0, {20}));
184 REQUIRE(CHECK_COLUMN(result, 1, {1}));
185 REQUIRE(CHECK_COLUMN(result, 2, {"t1"}));
186 result = con.Query("SELECT * FROM integers WHERE j=2");
187 REQUIRE(CHECK_COLUMN(result, 0, {21}));
188 REQUIRE(CHECK_COLUMN(result, 1, {2}));
189 REQUIRE(CHECK_COLUMN(result, 2, {"t2"}));
190 result = con.Query("SELECT * FROM integers ORDER BY j");
191 REQUIRE(CHECK_COLUMN(result, 0, {20, 21}));
192 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
193 REQUIRE(CHECK_COLUMN(result, 2, {"t1", "t2"}));
194 result = con2.Query("SELECT * FROM integers ORDER BY j");
195 REQUIRE(CHECK_COLUMN(result, 0, {20, 21}));
196 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
197 REQUIRE(CHECK_COLUMN(result, 2, {"t1", "t2"}));
198}
199
200TEST_CASE("Test ART index that requires multiple columns for expression", "[art]") {
201 unique_ptr<QueryResult> result;
202 DuckDB db(nullptr);
203 Connection con(db);
204
205 // FIXME: this should work, not a multidimensional index
206 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i BIGINT, j INTEGER, k VARCHAR, l BIGINT)"));
207 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art((j+l))"));
208
209 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (10, 1, 'hello', 4), (11, 2, 'world', 6)"));
210 result = con.Query("SELECT * FROM integers WHERE j+l=5");
211 REQUIRE(CHECK_COLUMN(result, 0, {10}));
212 REQUIRE(CHECK_COLUMN(result, 1, {1}));
213 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
214 REQUIRE(CHECK_COLUMN(result, 3, {4}));
215
216 result = con.Query("SELECT * FROM integers WHERE k='hello'");
217 REQUIRE(CHECK_COLUMN(result, 0, {10}));
218 REQUIRE(CHECK_COLUMN(result, 1, {1}));
219 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
220 REQUIRE(CHECK_COLUMN(result, 3, {4}));
221
222 // update that uses both columns in the index
223 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=5, l=l WHERE j=1"));
224 // update that only uses one of the columns
225 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=5 WHERE j=5"));
226
227 result = con.Query("SELECT * FROM integers WHERE j+l=9");
228 REQUIRE(CHECK_COLUMN(result, 0, {10}));
229 REQUIRE(CHECK_COLUMN(result, 1, {5}));
230 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
231 REQUIRE(CHECK_COLUMN(result, 3, {4}));
232
233 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE j+l=8"));
234 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE j+l=9"));
235
236 result = con.Query("SELECT COUNT(*) FROM integers");
237 REQUIRE(CHECK_COLUMN(result, 0, {0}));
238 result = con.Query("SELECT COUNT(*) FROM integers WHERE j+l>0");
239 REQUIRE(CHECK_COLUMN(result, 0, {0}));
240}
241
242TEST_CASE("Test updates on ART index", "[art]") {
243 unique_ptr<QueryResult> result;
244 DuckDB db(nullptr);
245 Connection con(db);
246
247 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
248 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(j)"));
249
250 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2), (2, 2)"));
251 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=10 WHERE i=1"));
252 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=10 WHERE rowid=0"));
253 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE rowid=1"));
254
255 result = con.Query("SELECT * FROM integers WHERE j>5");
256 REQUIRE(CHECK_COLUMN(result, 0, {1}));
257 REQUIRE(CHECK_COLUMN(result, 1, {10}));
258}
259
260TEST_CASE("Test ART index with single value", "[art-s]") {
261 unique_ptr<QueryResult> result;
262 DuckDB db(nullptr);
263 Connection con(db);
264
265 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
266 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
267
268 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
269
270 result = con.Query("SELECT * FROM integers WHERE i < 3");
271 REQUIRE(CHECK_COLUMN(result, 0, {1}));
272 result = con.Query("SELECT * FROM integers WHERE i <= 1");
273 REQUIRE(CHECK_COLUMN(result, 0, {1}));
274 result = con.Query("SELECT * FROM integers WHERE i > 0");
275 REQUIRE(CHECK_COLUMN(result, 0, {1}));
276 result = con.Query("SELECT * FROM integers WHERE i >= 1");
277 REQUIRE(CHECK_COLUMN(result, 0, {1}));
278 result = con.Query("SELECT * FROM integers WHERE i = 1");
279 REQUIRE(CHECK_COLUMN(result, 0, {1}));
280
281 result = con.Query("SELECT * FROM integers WHERE i < 1");
282 REQUIRE(CHECK_COLUMN(result, 0, {}));
283 result = con.Query("SELECT * FROM integers WHERE i <= 0");
284 REQUIRE(CHECK_COLUMN(result, 0, {}));
285 result = con.Query("SELECT * FROM integers WHERE i > 1");
286 REQUIRE(CHECK_COLUMN(result, 0, {}));
287 result = con.Query("SELECT * FROM integers WHERE i >= 2");
288 REQUIRE(CHECK_COLUMN(result, 0, {}));
289 result = con.Query("SELECT * FROM integers WHERE i = 2");
290 REQUIRE(CHECK_COLUMN(result, 0, {}));
291}
292
293TEST_CASE("Test ART index with selection vector", "[art]") {
294 unique_ptr<QueryResult> result;
295 DuckDB db(nullptr);
296 Connection con(db);
297
298 REQUIRE_NO_FAIL(con.Query("CREATE TABLE source(i INTEGER)"));
299 REQUIRE_NO_FAIL(con.Query("INSERT INTO source VALUES (1), (2), (3), (4), (5), (6)"));
300
301 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
302 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
303
304 // insert with selection vector
305 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers SELECT * FROM source WHERE i % 2 = 0"));
306
307 result = con.Query("SELECT * FROM integers WHERE i<3 ORDER BY 1");
308 REQUIRE(CHECK_COLUMN(result, 0, {2}));
309 result = con.Query("SELECT * FROM integers ORDER BY 1");
310 REQUIRE(CHECK_COLUMN(result, 0, {2, 4, 6}));
311 result = con.Query("SELECT * FROM integers WHERE i>3 ORDER BY 1");
312 REQUIRE(CHECK_COLUMN(result, 0, {4, 6}));
313 result = con.Query("SELECT * FROM integers WHERE i<=3 ORDER BY 1");
314 REQUIRE(CHECK_COLUMN(result, 0, {2}));
315 result = con.Query("SELECT * FROM integers WHERE i>=3 ORDER BY 1");
316 REQUIRE(CHECK_COLUMN(result, 0, {4, 6}));
317
318 // update with selection vector
319 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=3 WHERE i=4"));
320
321 result = con.Query("SELECT * FROM integers WHERE i<3 ORDER BY 1");
322 REQUIRE(CHECK_COLUMN(result, 0, {2}));
323 result = con.Query("SELECT * FROM integers WHERE i<=3 ORDER BY 1");
324 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
325 result = con.Query("SELECT * FROM integers WHERE i>3 ORDER BY 1");
326 REQUIRE(CHECK_COLUMN(result, 0, {6}));
327 result = con.Query("SELECT * FROM integers WHERE i>=3 ORDER BY 1");
328 REQUIRE(CHECK_COLUMN(result, 0, {3, 6}));
329
330 // delete with selection vector
331 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i>3"));
332
333 result = con.Query("SELECT * FROM integers WHERE i > 0 ORDER BY 1");
334 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
335 result = con.Query("SELECT * FROM integers WHERE i < 3 ORDER BY 1");
336 REQUIRE(CHECK_COLUMN(result, 0, {2}));
337}
338
339TEST_CASE("Test ART index with multiple predicates", "[art]") {
340 unique_ptr<QueryResult> result;
341 DuckDB db(nullptr);
342 Connection con(db);
343
344 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
345 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
346
347 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2), (1, 3)"));
348
349 result = con.Query("SELECT * FROM integers WHERE i = 1 AND j = 2");
350 REQUIRE(CHECK_COLUMN(result, 0, {1}));
351 REQUIRE(CHECK_COLUMN(result, 1, {2}));
352}
353
354TEST_CASE("Test ART index with simple updates", "[art]") {
355 unique_ptr<QueryResult> result;
356 DuckDB db(nullptr);
357 Connection con(db), con2(db);
358
359 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
360 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
361
362 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
363
364 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
365 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=10 WHERE i=1"));
366 // con sees the new state
367 result = con.Query("SELECT * FROM integers WHERE i < 5");
368 REQUIRE(CHECK_COLUMN(result, 0, {}));
369 result = con.Query("SELECT * FROM integers WHERE i > 0");
370 REQUIRE(CHECK_COLUMN(result, 0, {10}));
371 // con2 sees the old state
372 result = con2.Query("SELECT * FROM integers WHERE i < 5");
373 REQUIRE(CHECK_COLUMN(result, 0, {1}));
374 result = con2.Query("SELECT * FROM integers WHERE i > 0");
375 REQUIRE(CHECK_COLUMN(result, 0, {1}));
376 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
377}
378
379TEST_CASE("Test ART index with multiple updates on the same value", "[art]") {
380 unique_ptr<QueryResult> result;
381 DuckDB db(nullptr);
382 Connection con(db);
383
384 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
385 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
386
387 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
388
389 result = con.Query("SELECT * FROM integers WHERE i > 0");
390 REQUIRE(CHECK_COLUMN(result, 0, {1}));
391
392 // update the same tuple a bunch of times in the same transaction and then rollback
393 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
394 for (int32_t i = 0; i < 10; i++) {
395 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=" + to_string(i + 2) + " WHERE i=" + to_string(i + 1)));
396
397 result = con.Query("SELECT * FROM integers WHERE i > 0");
398 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(i + 2)}));
399 }
400 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
401
402 result = con.Query("SELECT * FROM integers WHERE i > 0");
403 REQUIRE(CHECK_COLUMN(result, 0, {1}));
404
405 // now update the same tuple a bunch of times in the same transaction and then commit
406 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
407 for (int32_t i = 0; i < 10; i++) {
408 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=$1 WHERE i=$2", i + 2, i + 1));
409
410 result = con.Query("SELECT * FROM integers WHERE i > 0");
411 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(i + 2)}));
412 }
413 REQUIRE_NO_FAIL(con.Query("COMMIT"));
414
415 result = con.Query("SELECT * FROM integers WHERE i > 0");
416 REQUIRE(CHECK_COLUMN(result, 0, {11}));
417}
418
419TEST_CASE("Test ART index with prefixes", "[art]") {
420 unique_ptr<QueryResult> result;
421 DuckDB db(nullptr);
422 Connection con(db);
423
424 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i BIGINT)"));
425 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
426 // insert a bunch of values with different prefixes
427 vector<int64_t> values = {9312908412824241,
428 -2092042498432234,
429 1,
430 -100,
431 0,
432 -598538523852390852,
433 4298421,
434 -498249,
435 9312908412824240,
436 -2092042498432235,
437 2,
438 -101,
439 -598538523852390853,
440 4298422,
441 -498261};
442 idx_t gt_count = 0, lt_count = 0;
443 idx_t count = 0;
444 for (idx_t val_index = 0; val_index < values.size(); val_index++) {
445 auto &value = values[val_index];
446 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", value));
447 if (value >= 0) {
448 gt_count++;
449 } else {
450 lt_count++;
451 }
452 count++;
453 for (idx_t i = 0; i <= val_index; i++) {
454 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(values[i]));
455 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(1)}));
456 }
457 result = con.Query("SELECT COUNT(*) FROM integers");
458 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
459 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 9223372036854775808");
460 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
461 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
462 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(gt_count)}));
463 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 0");
464 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(lt_count)}));
465 }
466}
467
468TEST_CASE("Test ART index with linear insertions and deletes", "[art][.]") {
469 unique_ptr<QueryResult> result;
470 DuckDB db(nullptr);
471 Connection con(db);
472
473 vector<idx_t> insertion_count = {4, 16, 48, 256, 1024};
474 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
475 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
476 for (auto &insert_count : insertion_count) {
477 // insert the data
478 vector<idx_t> elements;
479 idx_t table_count = 0;
480 for (idx_t i = 0; i < insert_count; i++) {
481 idx_t element = i;
482 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", (int32_t)element));
483 elements.push_back(element);
484 table_count++;
485 // test that the insert worked
486 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 100000000");
487 bool checked = CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)});
488 REQUIRE(checked);
489 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
490 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
491 }
492 // test that it worked
493 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 100000000");
494 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
495 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
496 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
497
498 // delete random data
499 for (auto &element : elements) {
500 // delete the element
501 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=$1", (int32_t)element));
502 table_count--;
503 // verify that the deletion worked
504 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
505 bool check = CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)});
506 REQUIRE(check);
507 }
508 }
509}
510
511TEST_CASE("Test ART index with random insertions and deletes", "[art][.]") {
512 unique_ptr<QueryResult> result;
513 DuckDB db(nullptr);
514 Connection con(db);
515
516 vector<idx_t> insertion_count = {1024, 2048};
517 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
518 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
519 for (auto &insert_count : insertion_count) {
520 // insert the data
521 vector<idx_t> elements;
522 idx_t table_count = 0;
523 for (idx_t i = 0; i < insert_count; i++) {
524 idx_t element = i * i;
525 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", (int32_t)element));
526 elements.push_back(element);
527 table_count++;
528 }
529 // test that it worked
530 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
531 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
532
533 // delete random data
534 std::random_shuffle(elements.begin(), elements.end());
535 for (auto &element : elements) {
536 // delete the element
537 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=$1", (int32_t)element));
538 table_count--;
539 // verify that the deletion worked
540 result = con.Query("SELECT COUNT(*) FROM integers");
541 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
542 result = con.Query("SELECT COUNT(*) FROM integers WHERE i >= 0");
543 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(table_count)}));
544 }
545 }
546}
547
548TEST_CASE("Test ART index creation with many versions", "[art][.]") {
549 unique_ptr<QueryResult> result;
550 DuckDB db(nullptr);
551 Connection con(db);
552 Connection r1(db), r2(db), r3(db);
553 int64_t expected_sum_r1 = 0, expected_sum_r2 = 0, expected_sum_r3 = 0, total_sum = 0;
554
555 // insert the values [0...20000]
556 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
557 for (idx_t i = 0; i < 20000; i++) {
558 int32_t val = i + 1;
559 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
560 expected_sum_r1 += val;
561 expected_sum_r2 += val + 1;
562 expected_sum_r3 += val + 2;
563 total_sum += val + 3;
564 }
565 // now start a transaction in r1
566 REQUIRE_NO_FAIL(r1.Query("BEGIN TRANSACTION"));
567 // increment values by 1
568 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i+1"));
569 // now start a transaction in r2
570 REQUIRE_NO_FAIL(r2.Query("BEGIN TRANSACTION"));
571 // increment values by 1 again
572 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i+1"));
573 // now start a transaction in r3
574 REQUIRE_NO_FAIL(r3.Query("BEGIN TRANSACTION"));
575 // increment values by 1 again
576 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i+1"));
577 // create an index, this fails because we have outstanding updates
578 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
579
580 // r1
581 result = r1.Query("SELECT SUM(i) FROM integers");
582 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r1)}));
583 result = r1.Query("SELECT SUM(i) FROM integers WHERE i > 0");
584 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r1)}));
585 // r2
586 result = r2.Query("SELECT SUM(i) FROM integers");
587 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r2)}));
588 result = r2.Query("SELECT SUM(i) FROM integers WHERE i > 0");
589 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r2)}));
590 // r3
591 result = r3.Query("SELECT SUM(i) FROM integers");
592 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r3)}));
593 result = r3.Query("SELECT SUM(i) FROM integers WHERE i > 0");
594 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(expected_sum_r3)}));
595 // total sum
596 result = con.Query("SELECT SUM(i) FROM integers");
597 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(total_sum)}));
598 result = con.Query("SELECT SUM(i) FROM integers WHERE i > 0");
599 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(total_sum)}));
600}
601
602TEST_CASE("Test ART index with many matches", "[art][.]") {
603 unique_ptr<QueryResult> result;
604 DuckDB db(nullptr);
605 Connection con(db);
606
607 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
608 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
609 for (idx_t i = 0; i < 1024; i++) {
610 for (idx_t val = 0; val < 2; val++) {
611 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", (int32_t)val));
612 }
613 }
614 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
615
616 result = con.Query("SELECT COUNT(*) FROM integers WHERE i<1");
617 REQUIRE(CHECK_COLUMN(result, 0, {1024}));
618 result = con.Query("SELECT COUNT(*) FROM integers WHERE i<=1");
619 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
620
621 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=0");
622 REQUIRE(CHECK_COLUMN(result, 0, {1024}));
623 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
624 REQUIRE(CHECK_COLUMN(result, 0, {1024}));
625
626 result = con.Query("SELECT COUNT(*) FROM integers WHERE i>0");
627 REQUIRE(CHECK_COLUMN(result, 0, {1024}));
628 result = con.Query("SELECT COUNT(*) FROM integers WHERE i>=0");
629 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
630
631 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
632
633 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
634 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
635 for (idx_t i = 0; i < 2048; i++) {
636 for (idx_t val = 0; val < 2; val++) {
637 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", (int32_t)val));
638 }
639 }
640
641 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
642
643 result = con.Query("SELECT COUNT(*) FROM integers WHERE i<1");
644 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
645 result = con.Query("SELECT COUNT(*) FROM integers WHERE i<=1");
646 REQUIRE(CHECK_COLUMN(result, 0, {4096}));
647
648 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=0");
649 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
650 result = con.Query("SELECT COUNT(*) FROM integers WHERE i=1");
651 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
652
653 result = con.Query("SELECT COUNT(*) FROM integers WHERE i>0");
654 REQUIRE(CHECK_COLUMN(result, 0, {2048}));
655 result = con.Query("SELECT COUNT(*) FROM integers WHERE i>=0");
656 REQUIRE(CHECK_COLUMN(result, 0, {4096}));
657
658 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
659}
660
661TEST_CASE("Test ART index with non-linear insertion", "[art][.]") {
662 unique_ptr<QueryResult> result;
663 DuckDB db(nullptr);
664 Connection con(db);
665
666 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
667 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
668 idx_t count = 0;
669 for (int32_t it = 0; it < 10; it++) {
670 for (int32_t val = 0; val < 1000; val++) {
671 if (it + val % 2) {
672 count++;
673 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
674 }
675 }
676 }
677 result = con.Query("SELECT COUNT(*) FROM integers");
678 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
679 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 1000000");
680 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
681}
682
683TEST_CASE("Test ART index with rollbacks", "[art][.]") {
684 unique_ptr<QueryResult> result;
685 DuckDB db(nullptr);
686 Connection con(db);
687
688 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
689 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
690 idx_t count = 0;
691 for (int32_t it = 0; it < 10; it++) {
692 for (int32_t val = 0; val < 1000; val++) {
693 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
694 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
695 if (it + val % 2) {
696 count++;
697 REQUIRE_NO_FAIL(con.Query("COMMIT"));
698 } else {
699 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
700 }
701 }
702 }
703 result = con.Query("SELECT COUNT(*) FROM integers");
704 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
705 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < 1000000");
706 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(count)}));
707}
708
709TEST_CASE("Test ART index with the same value multiple times", "[art][.]") {
710 unique_ptr<QueryResult> result;
711 DuckDB db(nullptr);
712 Connection con(db);
713
714 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
715 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
716 for (int32_t val = 0; val < 100; val++) {
717 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
718 }
719 for (int32_t val = 0; val < 100; val++) {
720 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
721 REQUIRE(CHECK_COLUMN(result, 0, {1}));
722 }
723 for (int32_t it = 0; it < 10; it++) {
724 for (int32_t val = 0; val < 100; val++) {
725 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
726 REQUIRE(CHECK_COLUMN(result, 0, {it + 1}));
727 }
728 for (int32_t val = 0; val < 100; val++) {
729 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
730 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
731 REQUIRE(CHECK_COLUMN(result, 0, {it + 2}));
732 }
733 for (int32_t val = 0; val < 100; val++) {
734 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
735 REQUIRE(CHECK_COLUMN(result, 0, {it + 2}));
736 }
737 }
738}
739
740TEST_CASE("Test ART index with negative values and big values", "[art]") {
741 unique_ptr<QueryResult> result;
742 DuckDB db(nullptr);
743 Connection con(db);
744
745 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i BIGINT)"));
746 vector<int64_t> values = {-4611686018427387906, -4611686018427387904, -2305843009213693952, 0,
747 2305843009213693952, 4611686018427387904, 4611686018427387906};
748 for (auto val : values) {
749 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", val));
750 }
751
752 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
753
754 result = con.Query("SELECT COUNT(*) FROM integers WHERE i > $1", 0);
755 REQUIRE(CHECK_COLUMN(result, 0, {3}));
756 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < $1", 0);
757 REQUIRE(CHECK_COLUMN(result, 0, {3}));
758 result = con.Query("SELECT COUNT(*) FROM integers WHERE i < $1", (int64_t)4611686018427387906);
759 REQUIRE(CHECK_COLUMN(result, 0, {6}));
760 result = con.Query("SELECT COUNT(*) FROM integers WHERE i <= $1", (int64_t)4611686018427387906);
761 REQUIRE(CHECK_COLUMN(result, 0, {7}));
762}
763
764TEST_CASE("Test Drop Index", "[drop-index]") {
765 unique_ptr<QueryResult> result;
766 DuckDB db(nullptr);
767 Connection con(db);
768
769 REQUIRE_NO_FAIL(con.Query("CREATE TABLE A (A1 INTEGER,A2 VARCHAR, A3 INTEGER)"));
770 REQUIRE_NO_FAIL(con.Query("INSERT INTO A VALUES (1, 1, 1)"));
771 REQUIRE_NO_FAIL(con.Query("INSERT INTO A VALUES (2, 2, 2)"));
772
773 REQUIRE_NO_FAIL(con.Query("CREATE TABLE B (B1 INTEGER,B2 INTEGER, B3 INTEGER)"));
774 REQUIRE_NO_FAIL(con.Query("INSERT INTO B VALUES (1, 1, 1)"));
775 REQUIRE_NO_FAIL(con.Query("INSERT INTO B VALUES (2, 2, 2)"));
776
777 REQUIRE_NO_FAIL(con.Query("CREATE TABLE C (C1 VARCHAR, C2 INTEGER, C3 INTEGER)"));
778 REQUIRE_NO_FAIL(con.Query("INSERT INTO C VALUES ('t1', 1, 1)"));
779 REQUIRE_NO_FAIL(con.Query("INSERT INTO C VALUES ('t2', 2, 2)"));
780
781 REQUIRE_NO_FAIL(con.Query("SELECT A2 FROM A WHERE A1=1"));
782
783 REQUIRE_NO_FAIL(con.Query("CREATE INDEX A_index ON A (A1)"));
784 REQUIRE_NO_FAIL(con.Query("SELECT A2 FROM A WHERE A1=1"));
785
786 REQUIRE_NO_FAIL(con.Query("CREATE INDEX B_index ON B (B1)"));
787 REQUIRE_NO_FAIL(con.Query("SELECT A2 FROM A WHERE A1=1"));
788
789 REQUIRE_NO_FAIL(con.Query("CREATE INDEX C_index ON C (C2)"));
790 REQUIRE_NO_FAIL(con.Query("SELECT A2 FROM A WHERE A1=1"));
791
792 REQUIRE_NO_FAIL(con.Query("DROP INDEX IF EXISTS A_index"));
793 REQUIRE_NO_FAIL(con.Query("SELECT A2 FROM A WHERE A1=1"));
794}
795
796TEST_CASE("Test ART with different Integer Types", "[art]") {
797 unique_ptr<QueryResult> result;
798 DuckDB db(nullptr);
799
800 Connection con(db);
801 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i TINYINT, j SMALLINT, k INTEGER, l BIGINT)"));
802 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index1 ON integers(i)"));
803 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index2 ON integers(j)"));
804 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index3 ON integers(k)"));
805 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index4 ON integers(l)"));
806
807 // query the empty indices first
808 result = con.Query("SELECT i FROM integers WHERE i > 0");
809 REQUIRE(CHECK_COLUMN(result, 0, {}));
810 result = con.Query("SELECT j FROM integers WHERE j < 0");
811 REQUIRE(CHECK_COLUMN(result, 0, {}));
812 result = con.Query("SELECT k FROM integers WHERE k >= 0");
813 REQUIRE(CHECK_COLUMN(result, 0, {}));
814 result = con.Query("SELECT l FROM integers WHERE l <= 0");
815 REQUIRE(CHECK_COLUMN(result, 0, {}));
816
817 // now insert the values [1..5] in all columns
818 auto prepare = con.Prepare("INSERT INTO integers VALUES ($1, $2, $3, $4)");
819 for (int32_t i = 1; i <= 5; i++) {
820 REQUIRE_NO_FAIL(prepare->Execute(i, i, i, i));
821 }
822 prepare.reset();
823
824 result = con.Query("SELECT * FROM integers ORDER BY i");
825 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
826 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5}));
827 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5}));
828 REQUIRE(CHECK_COLUMN(result, 3, {1, 2, 3, 4, 5}));
829
830 result = con.Query("SELECT i FROM integers WHERE i > 0::TINYINT ORDER BY i");
831 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
832 result = con.Query("SELECT j FROM integers WHERE j <= 2::SMALLINT ORDER BY j");
833 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
834 result = con.Query("SELECT k FROM integers WHERE k >= -100000::INTEGER ORDER BY k");
835 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
836 result = con.Query("SELECT k FROM integers WHERE k >= 100000::INTEGER ORDER BY k");
837 REQUIRE(CHECK_COLUMN(result, 0, {}));
838 result = con.Query("SELECT k FROM integers WHERE k >= 100000::INTEGER AND k <= 100001::INTEGER ORDER BY k");
839 REQUIRE(CHECK_COLUMN(result, 0, {}));
840 result = con.Query("SELECT l FROM integers WHERE l <= 1000000000::BIGINT ORDER BY i");
841 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
842 result = con.Query("SELECT l FROM integers WHERE l <= -1000000000::BIGINT ORDER BY i");
843 REQUIRE(CHECK_COLUMN(result, 0, {}));
844}
845
846TEST_CASE("ART Integer Types", "[art][.]") {
847 unique_ptr<QueryResult> result;
848 DuckDB db(nullptr);
849
850 Connection con(db);
851
852 string int_types[4] = {"tinyint", "smallint", "integer", "bigint"};
853 idx_t n_sizes[4] = {100, 1000, 1000, 1000};
854 for (idx_t idx = 0; idx < 4; idx++) {
855 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i " + int_types[idx] + ")"));
856 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
857
858 idx_t n = n_sizes[idx];
859 auto keys = unique_ptr<int32_t[]>(new int32_t[n]);
860 auto key_pointer = keys.get();
861 for (idx_t i = 0; i < n; i++) {
862 keys[i] = i + 1;
863 }
864 std::random_shuffle(key_pointer, key_pointer + n);
865
866 for (idx_t i = 0; i < n; i++) {
867 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
868 result =
869 con.Query("SELECT i FROM integers WHERE i=CAST(" + to_string(keys[i]) + " AS " + int_types[idx] + ")");
870 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
871 }
872 //! Checking non-existing values
873 result = con.Query("SELECT i FROM integers WHERE i=CAST(" + to_string(-1) + " AS " + int_types[idx] + ")");
874 REQUIRE(CHECK_COLUMN(result, 0, {}));
875 result = con.Query("SELECT i FROM integers WHERE i=CAST(" + to_string(n_sizes[idx] + 1) + " AS " +
876 int_types[idx] + ")");
877 REQUIRE(CHECK_COLUMN(result, 0, {}));
878
879 //! Checking if all elements are still there
880 for (idx_t i = 0; i < n; i++) {
881 result =
882 con.Query("SELECT i FROM integers WHERE i=CAST(" + to_string(keys[i]) + " AS " + int_types[idx] + ")");
883 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
884 }
885
886 //! Checking Multiple Range Queries
887 int32_t up_range_result = n_sizes[idx] * 2 - 1;
888 result = con.Query("SELECT sum(i) FROM integers WHERE i >= " + to_string(n_sizes[idx] - 1));
889 REQUIRE(CHECK_COLUMN(result, 0, {Value(up_range_result)}));
890
891 result = con.Query("SELECT sum(i) FROM integers WHERE i > " + to_string(n_sizes[idx] - 2));
892 REQUIRE(CHECK_COLUMN(result, 0, {Value(up_range_result)}));
893
894 result = con.Query("SELECT sum(i) FROM integers WHERE i > 2 AND i < 5");
895 REQUIRE(CHECK_COLUMN(result, 0, {Value(7)}));
896
897 result = con.Query("SELECT sum(i) FROM integers WHERE i >=2 AND i <5");
898 REQUIRE(CHECK_COLUMN(result, 0, {Value(9)}));
899
900 result = con.Query("SELECT sum(i) FROM integers WHERE i >2 AND i <=5");
901 REQUIRE(CHECK_COLUMN(result, 0, {Value(12)}));
902
903 result = con.Query("SELECT sum(i) FROM integers WHERE i >=2 AND i <=5");
904 REQUIRE(CHECK_COLUMN(result, 0, {Value(14)}));
905 result = con.Query("SELECT sum(i) FROM integers WHERE i <=2");
906 REQUIRE(CHECK_COLUMN(result, 0, {Value(3)}));
907
908 result = con.Query("SELECT sum(i) FROM integers WHERE i <0");
909 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
910
911 result = con.Query("SELECT sum(i) FROM integers WHERE i >10000000");
912 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
913
914 //! Checking Duplicates
915 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1)"));
916 result = con.Query("SELECT SUM(i) FROM integers WHERE i=1");
917 REQUIRE(CHECK_COLUMN(result, 0, {Value(2)}));
918
919 //! Successful update
920 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=14 WHERE i=13"));
921 result = con.Query("SELECT * FROM integers WHERE i=14");
922 REQUIRE(CHECK_COLUMN(result, 0, {14, 14}));
923
924 // Testing rollbacks and commits
925 // rolled back update
926 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
927 // update the value
928 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=14 WHERE i=12"));
929 // now there are three values with 14
930 result = con.Query("SELECT * FROM integers WHERE i=14");
931 REQUIRE(CHECK_COLUMN(result, 0, {14, 14, 14}));
932 // rollback the value
933 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
934 // after the rollback
935 result = con.Query("SELECT * FROM integers WHERE i=14");
936 REQUIRE(CHECK_COLUMN(result, 0, {14, 14}));
937 // roll back insert
938 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
939 // update the value
940 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (14)"));
941 // now there are three values with 14
942 result = con.Query("SELECT * FROM integers WHERE i=14");
943 REQUIRE(CHECK_COLUMN(result, 0, {14, 14, 14}));
944 // rollback the value
945 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
946 // after the rol
947 result = con.Query("SELECT * FROM integers WHERE i=14");
948 REQUIRE(CHECK_COLUMN(result, 0, {14, 14}));
949
950 //! Testing deletes
951 // Delete non-existing element
952 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=0"));
953 // Now Deleting all elements
954 for (idx_t i = 0; i < n; i++) {
955 REQUIRE_NO_FAIL(
956 con.Query("DELETE FROM integers WHERE i=CAST(" + to_string(keys[i]) + " AS " + int_types[idx] + ")"));
957 // check the value does not exist
958 result =
959 con.Query("SELECT * FROM integers WHERE i=CAST(" + to_string(keys[i]) + " AS " + int_types[idx] + ")");
960 REQUIRE(CHECK_COLUMN(result, 0, {}));
961 }
962 // Delete from empty tree
963 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=0"));
964
965 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
966 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
967 }
968}
969
970TEST_CASE("ART Simple Big Range", "[art][.]") {
971 unique_ptr<QueryResult> result;
972 DuckDB db(nullptr);
973 Connection con(db);
974
975 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
976 idx_t n = 4;
977 auto keys = unique_ptr<int32_t[]>(new int32_t[n + 1]);
978 for (idx_t i = 0; i < n + 1; i++) {
979 keys[i] = i + 1;
980 }
981
982 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
983 for (idx_t i = 0; i < n; i++) {
984 for (idx_t j = 0; j < 1500; j++) {
985 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
986 }
987 }
988 REQUIRE_NO_FAIL(con.Query("COMMIT"));
989 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
990
991 result = con.Query("SELECT count(i) FROM integers WHERE i > 1 AND i < 3");
992 REQUIRE(CHECK_COLUMN(result, 0, {Value(1500)}));
993 result = con.Query("SELECT count(i) FROM integers WHERE i >= 1 AND i < 3");
994 REQUIRE(CHECK_COLUMN(result, 0, {Value(3000)}));
995 result = con.Query("SELECT count(i) FROM integers WHERE i > 1");
996 REQUIRE(CHECK_COLUMN(result, 0, {Value(4500)}));
997 result = con.Query("SELECT count(i) FROM integers WHERE i < 4");
998 REQUIRE(CHECK_COLUMN(result, 0, {Value(4500)}));
999 result = con.Query("SELECT count(i) FROM integers WHERE i < 5");
1000 REQUIRE(CHECK_COLUMN(result, 0, {Value(6000)}));
1001 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1002 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
1003}
1004
1005TEST_CASE("ART Big Range with deletions", "[art][.]") {
1006 unique_ptr<QueryResult> result;
1007 DuckDB db(nullptr);
1008 Connection con(db);
1009
1010 idx_t n = 4;
1011 auto keys = unique_ptr<int32_t[]>(new int32_t[n + 1]);
1012 for (idx_t i = 0; i < n + 1; i++) {
1013 keys[i] = i + 1;
1014 }
1015
1016 // now perform a an index creation and scan with deletions with a second transaction
1017 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1018 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1019 for (idx_t j = 0; j < 1500; j++) {
1020 for (idx_t i = 0; i < n + 1; i++) {
1021 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
1022 }
1023 }
1024 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1025
1026 // second transaction: begin and verify counts
1027 Connection con2(db);
1028 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
1029 for (idx_t i = 0; i < n + 1; i++) {
1030 result = con2.Query("SELECT FIRST(i), COUNT(i) FROM integers WHERE i=" + to_string(keys[i]));
1031 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1032 REQUIRE(CHECK_COLUMN(result, 1, {Value(1500)}));
1033 }
1034 result = con2.Query("SELECT COUNT(i) FROM integers WHERE i < 10");
1035 REQUIRE(CHECK_COLUMN(result, 0, {Value(7500)}));
1036
1037 // now delete entries in the first transaction
1038 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i = 5"));
1039 // verify that the counts are still correct in the second transaction
1040 for (idx_t i = 0; i < n + 1; i++) {
1041 result = con2.Query("SELECT FIRST(i), COUNT(i) FROM integers WHERE i=" + to_string(keys[i]));
1042 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1043 REQUIRE(CHECK_COLUMN(result, 1, {Value(1500)}));
1044 }
1045 result = con2.Query("SELECT COUNT(i) FROM integers WHERE i < 10");
1046 REQUIRE(CHECK_COLUMN(result, 0, {Value(7500)}));
1047
1048 // create an index in the first transaction now
1049 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1050 // verify that the counts are still correct for con2
1051 for (idx_t i = 0; i < n + 1; i++) {
1052 result = con2.Query("SELECT FIRST(i), COUNT(i) FROM integers WHERE i=" + to_string(keys[i]));
1053 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1054 REQUIRE(CHECK_COLUMN(result, 1, {Value(1500)}));
1055 }
1056 result = con2.Query("SELECT COUNT(i) FROM integers WHERE i<10");
1057 REQUIRE(CHECK_COLUMN(result, 0, {Value(7500)}));
1058
1059 // do a bunch of queries in the first transaction
1060 result = con.Query("SELECT count(i) FROM integers WHERE i > 1 AND i < 3");
1061 REQUIRE(CHECK_COLUMN(result, 0, {Value(1500)}));
1062 result = con.Query("SELECT count(i) FROM integers WHERE i >= 1 AND i < 3");
1063 REQUIRE(CHECK_COLUMN(result, 0, {Value(3000)}));
1064 result = con.Query("SELECT count(i) FROM integers WHERE i > 1");
1065 REQUIRE(CHECK_COLUMN(result, 0, {Value(4500)}));
1066 result = con.Query("SELECT count(i) FROM integers WHERE i < 4");
1067 REQUIRE(CHECK_COLUMN(result, 0, {Value(4500)}));
1068 result = con.Query("SELECT count(i) FROM integers WHERE i < 5");
1069 REQUIRE(CHECK_COLUMN(result, 0, {Value(6000)}));
1070
1071 // verify that the counts are still correct in the second transaction
1072 result = con2.Query("SELECT COUNT(i) FROM integers WHERE i<10");
1073 REQUIRE(CHECK_COLUMN(result, 0, {Value(7500)}));
1074 result = con2.Query("SELECT COUNT(i) FROM integers WHERE i=5");
1075 REQUIRE(CHECK_COLUMN(result, 0, {Value(1500)}));
1076}
1077
1078TEST_CASE("ART Negative Range", "[art-neg]") {
1079 unique_ptr<QueryResult> result;
1080 DuckDB db(nullptr);
1081
1082 Connection con(db);
1083
1084 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1085 idx_t n = 1000;
1086 auto keys = unique_ptr<int32_t[]>(new int32_t[n]);
1087 for (idx_t i = 0; i < n; i++) {
1088 keys[i] = i - 500;
1089 }
1090
1091 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1092 for (idx_t i = 0; i < n; i++) {
1093 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
1094 }
1095 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1096 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1097
1098 result = con.Query("SELECT sum(i) FROM integers WHERE i >= -500 AND i <= -498");
1099 REQUIRE(CHECK_COLUMN(result, 0, {Value(-1497)}));
1100 result = con.Query("SELECT sum(i) FROM integers WHERE i >= -10 AND i <= 5");
1101 REQUIRE(CHECK_COLUMN(result, 0, {Value(-40)}));
1102 result = con.Query("SELECT sum(i) FROM integers WHERE i >= 10 AND i <= 15");
1103 REQUIRE(CHECK_COLUMN(result, 0, {Value(75)}));
1104 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1105 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
1106}
1107
1108float generate_small_float() {
1109 return static_cast<float>(rand()) / static_cast<float>(RAND_MAX);
1110}
1111
1112float generate_float(float min_float, float max_float) {
1113 return min_float + static_cast<float>(rand()) / (static_cast<float>(RAND_MAX / (max_float - min_float)));
1114}
1115
1116double generate_small_double() {
1117 return static_cast<double>(rand()) / static_cast<double>(RAND_MAX);
1118}
1119
1120double generate_double(double min_double, double max_double) {
1121 return min_double + static_cast<double>(rand()) / (static_cast<double>(RAND_MAX / (max_double - min_double)));
1122}
1123
1124template <class T> int full_scan(T *keys, idx_t size, T low, T high) {
1125 int sum = 0;
1126 for (idx_t i = 0; i < size; i++) {
1127 if (keys[i] >= low && keys[i] <= high) {
1128 sum += 1;
1129 }
1130 }
1131 return sum;
1132}
1133
1134TEST_CASE("ART Floating Point Small", "[art-float-small]") {
1135 unique_ptr<QueryResult> result;
1136 DuckDB db(nullptr);
1137 int64_t a, b;
1138 vector<int64_t> min_values, max_values;
1139 Connection con(db);
1140 //! Will use 100 keys
1141 idx_t n = 100;
1142 auto keys = unique_ptr<int64_t[]>(new int64_t[n]);
1143 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i BIGINT)"));
1144 //! Generate 10 small floats (0.0 - 1.0)
1145 for (idx_t i = 0; i < n / 10; i++) {
1146 keys[i] = Key::EncodeFloat(generate_small_float());
1147 }
1148
1149 //! Generate 40 floats (-50/50)
1150 for (idx_t i = n / 10; i < n / 2; i++) {
1151 keys[i] = Key::EncodeFloat(generate_float(-50, 50));
1152 }
1153 //! Generate 50 floats (min/max)
1154 for (idx_t i = n / 2; i < n; i++) {
1155 keys[i] = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1156 }
1157 //! Insert values and create index
1158 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1159 for (idx_t i = 0; i < n; i++) {
1160 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (" + to_string(keys[i]) + ")"));
1161 }
1162 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1163 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1164 //! Generate 500 small-small range queries
1165 for (idx_t i = 0; i < 5; i++) {
1166 a = Key::EncodeFloat(generate_small_float());
1167 b = Key::EncodeFloat(generate_small_float());
1168 min_values.push_back(min(a, b));
1169 max_values.push_back(max(a, b));
1170 }
1171 //! Generate 500 normal-normal range queries
1172 for (idx_t i = 0; i < 5; i++) {
1173 a = Key::EncodeFloat(generate_float(-50, 50));
1174 b = Key::EncodeFloat(generate_float(-50, 50));
1175 min_values.push_back(min(a, b));
1176 max_values.push_back(max(a, b));
1177 }
1178 //! Generate 500 big-big range queries
1179 for (idx_t i = 0; i < 5; i++) {
1180 a = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1181 b = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1182 min_values.push_back(min(a, b));
1183 max_values.push_back(max(a, b));
1184 }
1185 for (idx_t i = 0; i < min_values.size(); i++) {
1186 int64_t low = Key::EncodeFloat(min_values[i]);
1187 int64_t high = Key::EncodeFloat(max_values[i]);
1188 int answer = full_scan<int64_t>(keys.get(), n, low, high);
1189 string query =
1190 "SELECT COUNT(i) FROM numbers WHERE i >= " + to_string(low) + " and i <= " + to_string(high) + ";";
1191 result = con.Query(query);
1192 if (!CHECK_COLUMN(result, 0, {answer})) {
1193 cout << "Wrong answer on floating point real-small!" << std::endl << "Queries to reproduce:" << std::endl;
1194 cout << "CREATE TABLE numbers(i BIGINT);" << std::endl;
1195 for (idx_t k = 0; k < n; k++) {
1196 cout << "INSERT INTO numbers VALUES (" << keys[k] << ");" << std::endl;
1197 }
1198 cout << query << std::endl;
1199 REQUIRE(false);
1200 }
1201 }
1202 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1203 REQUIRE_NO_FAIL(con.Query("DROP TABLE numbers"));
1204}
1205
1206TEST_CASE("ART Floating Point Double Small", "[art-double-small]") {
1207 unique_ptr<QueryResult> result;
1208 DuckDB db(nullptr);
1209 int64_t a, b;
1210 vector<int64_t> min_values, max_values;
1211 Connection con(db);
1212 //! Will use 100 keys
1213 idx_t n = 100;
1214 auto keys = unique_ptr<int64_t[]>(new int64_t[n]);
1215 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i BIGINT)"));
1216 //! Generate 10 small floats (0.0 - 1.0)
1217 for (idx_t i = 0; i < n / 10; i++) {
1218 keys[i] = Key::EncodeFloat(generate_small_float());
1219 }
1220
1221 //! Generate 40 floats (-50/50)
1222 for (idx_t i = n / 10; i < n / 2; i++) {
1223 keys[i] = Key::EncodeFloat(generate_float(-50, 50));
1224 }
1225 //! Generate 50 floats (min/max)
1226 for (idx_t i = n / 2; i < n; i++) {
1227 keys[i] = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1228 }
1229 //! Insert values and create index
1230 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1231 for (idx_t i = 0; i < n; i++) {
1232 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (" + to_string(keys[i]) + ")"));
1233 }
1234 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1235 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1236 //! Generate 500 small-small range queries
1237 for (idx_t i = 0; i < 5; i++) {
1238 a = Key::EncodeDouble(generate_small_double());
1239 b = Key::EncodeDouble(generate_small_double());
1240 min_values.push_back(min(a, b));
1241 max_values.push_back(max(a, b));
1242 }
1243 //! Generate 500 normal-normal range queries
1244 for (idx_t i = 0; i < 5; i++) {
1245 a = Key::EncodeDouble(generate_double(-50, 50));
1246 b = Key::EncodeDouble(generate_double(-50, 50));
1247 min_values.push_back(min(a, b));
1248 max_values.push_back(max(a, b));
1249 }
1250 //! Generate 500 big-big range queries
1251 for (idx_t i = 0; i < 5; i++) {
1252 a = Key::EncodeDouble(generate_double(FLT_MIN, FLT_MAX));
1253 b = Key::EncodeDouble(generate_double(FLT_MIN, FLT_MAX));
1254 min_values.push_back(min(a, b));
1255 max_values.push_back(max(a, b));
1256 }
1257 for (idx_t i = 0; i < min_values.size(); i++) {
1258 int64_t low = Key::EncodeDouble(min_values[i]);
1259 int64_t high = Key::EncodeDouble(max_values[i]);
1260 int answer = full_scan<int64_t>(keys.get(), n, low, high);
1261 string query =
1262 "SELECT COUNT(i) FROM numbers WHERE i >= " + to_string(low) + " and i <= " + to_string(high) + ";";
1263 result = con.Query(query);
1264 if (!CHECK_COLUMN(result, 0, {answer})) {
1265 cout << "Wrong answer on double!" << std::endl << "Queries to reproduce:" << std::endl;
1266 cout << "CREATE TABLE numbers(i BIGINT);" << std::endl;
1267 for (idx_t k = 0; k < n; k++) {
1268 cout << "INSERT INTO numbers VALUES (" << keys[k] << ");" << std::endl;
1269 }
1270 cout << query << std::endl;
1271 REQUIRE(false);
1272 }
1273 }
1274 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1275 REQUIRE_NO_FAIL(con.Query("DROP TABLE numbers"));
1276}
1277
1278TEST_CASE("ART Strings", "[art-string]") {
1279 unique_ptr<QueryResult> result;
1280 DuckDB db(nullptr);
1281
1282 Connection con(db);
1283 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(i varchar)"));
1284 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON strings(i)"));
1285
1286 //! Insert values and create index
1287 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('test')"));
1288 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('test1')"));
1289 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('vest1')"));
1290 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('somesuperbigstring')"));
1291 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('somesuperbigstring1')"));
1292 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('somesuperbigstring2')"));
1293 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('somesuperbigstring')"));
1294 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('maybesomesuperbigstring')"));
1295 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES "
1296 "('"
1297 "maybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigst"
1298 "ringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstring')"));
1299 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES "
1300 "('"
1301 "maybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigst"
1302 "ringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstring2')"));
1303
1304 result = con.Query("SELECT COUNT(i) FROM strings WHERE i = 'test'");
1305 REQUIRE(CHECK_COLUMN(result, 0, {1}));
1306 result = con.Query("SELECT COUNT(i) FROM strings WHERE i = 'somesuperbigstring'");
1307 REQUIRE(CHECK_COLUMN(result, 0, {2}));
1308 result = con.Query("SELECT COUNT(i) FROM strings WHERE i = "
1309 "'maybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringma"
1310 "ybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstring'");
1311 REQUIRE(CHECK_COLUMN(result, 0, {1}));
1312 result = con.Query("SELECT COUNT(i) FROM strings WHERE i = "
1313 "'maybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstringma"
1314 "ybesomesuperbigstringmaybesomesuperbigstringmaybesomesuperbigstring2'");
1315 REQUIRE(CHECK_COLUMN(result, 0, {1}));
1316
1317 result = con.Query("SELECT COUNT(i) FROM strings WHERE i >= 'somesuperbigstring' and i <='somesuperbigstringz'");
1318 REQUIRE(CHECK_COLUMN(result, 0, {4}));
1319 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1320 REQUIRE_NO_FAIL(con.Query("DROP TABLE strings"));
1321}
1322
1323TEST_CASE("ART Floating Point", "[art-float][.]") {
1324 unique_ptr<QueryResult> result;
1325 DuckDB db(nullptr);
1326 int64_t a, b;
1327 vector<int64_t> min_values, max_values;
1328 Connection con(db);
1329 //! Will use 10k keys
1330 idx_t n = 10000;
1331 auto keys = unique_ptr<int64_t[]>(new int64_t[n]);
1332 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i BIGINT)"));
1333 //! Generate 1000 small floats (0.0 - 1.0)
1334 for (idx_t i = 0; i < n / 10; i++) {
1335 keys[i] = Key::EncodeFloat(generate_small_float());
1336 }
1337
1338 //! Generate 4000 floats (-50/50)
1339 for (idx_t i = n / 10; i < n / 2; i++) {
1340 keys[i] = Key::EncodeFloat(generate_float(-50, 50));
1341 }
1342 //! Generate 5000 floats (min/max)
1343 for (idx_t i = n / 2; i < n; i++) {
1344 keys[i] = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1345 }
1346 //! Insert values and create index
1347 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1348 for (idx_t i = 0; i < n; i++) {
1349 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (" + to_string(keys[i]) + ")"));
1350 }
1351 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1352 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1353 //! Generate 500 small-small range queries
1354 for (idx_t i = 0; i < 500; i++) {
1355 a = Key::EncodeFloat(generate_small_float());
1356 b = Key::EncodeFloat(generate_small_float());
1357 min_values.push_back(min(a, b));
1358 max_values.push_back(max(a, b));
1359 }
1360 //! Generate 500 normal-normal range queries
1361 for (idx_t i = 0; i < 500; i++) {
1362 a = Key::EncodeFloat(generate_float(-50, 50));
1363 b = Key::EncodeFloat(generate_float(-50, 50));
1364 min_values.push_back(min(a, b));
1365 max_values.push_back(max(a, b));
1366 }
1367 //! Generate 500 big-big range queries
1368 for (idx_t i = 0; i < 500; i++) {
1369 a = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1370 b = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1371 min_values.push_back(min(a, b));
1372 max_values.push_back(max(a, b));
1373 }
1374 for (idx_t i = 0; i < min_values.size(); i++) {
1375 int64_t low = Key::EncodeFloat(min_values[i]);
1376 int64_t high = Key::EncodeFloat(max_values[i]);
1377 int answer = full_scan<int64_t>(keys.get(), n, low, high);
1378 string query =
1379 "SELECT COUNT(i) FROM numbers WHERE i >= " + to_string(low) + " and i <= " + to_string(high) + ";";
1380 result = con.Query(query);
1381 if (!CHECK_COLUMN(result, 0, {answer})) {
1382 cout << "Wrong answer on floating point real-small!" << std::endl << "Queries to reproduce:" << std::endl;
1383 cout << "CREATE TABLE numbers(i BIGINT);" << std::endl;
1384 for (idx_t k = 0; k < n; k++) {
1385 cout << "INSERT INTO numbers VALUES (" << keys[k] << ");" << std::endl;
1386 }
1387 cout << query << std::endl;
1388 REQUIRE(false);
1389 }
1390 }
1391 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1392 REQUIRE_NO_FAIL(con.Query("DROP TABLE numbers"));
1393}
1394
1395TEST_CASE("ART Floating Point Double", "[art-double][.]") {
1396 unique_ptr<QueryResult> result;
1397 DuckDB db(nullptr);
1398 int64_t a, b;
1399 vector<int64_t> min_values, max_values;
1400 Connection con(db);
1401 //! Will use 10000 keys
1402 idx_t n = 10000;
1403 auto keys = unique_ptr<int64_t[]>(new int64_t[n]);
1404 con.Query("CREATE TABLE numbers(i BIGINT)");
1405 //! Generate 1000 small floats (0.0 - 1.0)
1406 for (idx_t i = 0; i < n / 10; i++) {
1407 keys[i] = Key::EncodeFloat(generate_small_float());
1408 }
1409
1410 //! Generate 4000 floats (-50/50)
1411 for (idx_t i = n / 10; i < n / 2; i++) {
1412 keys[i] = Key::EncodeFloat(generate_float(-50, 50));
1413 }
1414 //! Generate 5000 floats (min/max)
1415 for (idx_t i = n / 2; i < n; i++) {
1416 keys[i] = Key::EncodeFloat(generate_float(FLT_MIN, FLT_MAX));
1417 }
1418 //! Insert values and create index
1419 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1420 for (idx_t i = 0; i < n; i++) {
1421 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (" + to_string(keys[i]) + ")"));
1422 }
1423 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1424 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1425 //! Generate 500 small-small range queries
1426 for (idx_t i = 0; i < 500; i++) {
1427 a = Key::EncodeDouble(generate_small_double());
1428 b = Key::EncodeDouble(generate_small_double());
1429 min_values.push_back(min(a, b));
1430 max_values.push_back(max(a, b));
1431 }
1432 //! Generate 500 normal-normal range queries
1433 for (idx_t i = 0; i < 500; i++) {
1434 a = Key::EncodeDouble(generate_double(-50, 50));
1435 b = Key::EncodeDouble(generate_double(-50, 50));
1436 min_values.push_back(min(a, b));
1437 max_values.push_back(max(a, b));
1438 }
1439 //! Generate 500 big-big range queries
1440 for (idx_t i = 0; i < 500; i++) {
1441 a = Key::EncodeDouble(generate_double(FLT_MIN, FLT_MAX));
1442 b = Key::EncodeDouble(generate_double(FLT_MIN, FLT_MAX));
1443 min_values.push_back(min(a, b));
1444 max_values.push_back(max(a, b));
1445 }
1446 for (idx_t i = 0; i < min_values.size(); i++) {
1447 int64_t low = Key::EncodeDouble(min_values[i]);
1448 int64_t high = Key::EncodeDouble(max_values[i]);
1449 int answer = full_scan<int64_t>(keys.get(), n, low, high);
1450 string query =
1451 "SELECT COUNT(i) FROM numbers WHERE i >= " + to_string(low) + " and i <= " + to_string(high) + ";";
1452 result = con.Query(query);
1453 if (!CHECK_COLUMN(result, 0, {answer})) {
1454 cout << "Wrong answer on floating point real-small!" << std::endl << "Queries to reproduce:" << std::endl;
1455 cout << "CREATE TABLE numbers(i BIGINT);" << std::endl;
1456 for (idx_t k = 0; k < n; k++) {
1457 cout << "INSERT INTO numbers VALUES (" << keys[k] << ");" << std::endl;
1458 }
1459 cout << query << std::endl;
1460 REQUIRE(false);
1461 }
1462 }
1463 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1464 REQUIRE_NO_FAIL(con.Query("DROP TABLE numbers"));
1465}
1466
1467TEST_CASE("ART FP Unique Constraint", "[art-float-unique]") {
1468 unique_ptr<QueryResult> result;
1469 DuckDB db(nullptr);
1470 Connection con(db);
1471
1472 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i REAL PRIMARY KEY, j INTEGER)"));
1473
1474 //! insert two conflicting pairs at the same time
1475 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (3.45, 4), (3.45, 5)"));
1476
1477 //! insert unique values
1478 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (3.45, 4), (2.2, 5)"));
1479
1480 result = con.Query("SELECT * FROM numbers");
1481 REQUIRE(CHECK_COLUMN(result, 0, {Value::FLOAT(3.45f), Value::FLOAT(2.2f)}));
1482 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
1483
1484 //! insert a duplicate value as part of a chain of values
1485 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (6, 6), (3.45, 4);"));
1486
1487 //! now insert just the first value
1488 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (6, 6);"));
1489
1490 result = con.Query("SELECT * FROM numbers");
1491 REQUIRE(CHECK_COLUMN(result, 0, {Value::FLOAT(3.45f), Value::FLOAT(2.2f), Value::FLOAT(6.0f)}));
1492 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6}));
1493
1494 //! insert NULL value in PRIMARY KEY is not allowed
1495 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (NULL, 4);"));
1496
1497 //! update NULL is also not allowed
1498 REQUIRE_FAIL(con.Query("UPDATE numbers SET i=NULL;"));
1499}
1500
1501TEST_CASE("ART FP Special Cases", "[art-fp-special]") {
1502 unique_ptr<QueryResult> result;
1503 DuckDB db(nullptr);
1504 Connection con(db);
1505
1506 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i REAL)"));
1507
1508 // //! INF
1509 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(POWER(1000,10000) AS REAL))"));
1510 // //! -INF
1511 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(-POWER(1000,10000) AS REAL))"));
1512 // //! NaN
1513 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(POWER(1000,10000)*0 AS REAL))"));
1514 //! +0
1515 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(0 AS REAL))"));
1516 //! -0
1517 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(-0 AS REAL))"));
1518
1519 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1520 // //! INF
1521 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(POWER(1000,10000) AS REAL)");
1522 // REQUIRE(CHECK_COLUMN(result, 0, {1}));
1523 // //! -INF
1524 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(-POWER(1000,10000) AS REAL)");
1525 // REQUIRE(CHECK_COLUMN(result, 0, {1}));
1526 // //! NaN
1527 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(POWER(1000,10000)*0 AS REAL)");
1528 // REQUIRE(CHECK_COLUMN(result, 0, {0}));
1529 //! +0
1530 result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(0 AS REAL)");
1531 REQUIRE(CHECK_COLUMN(result, 0, {2}));
1532 //! -0
1533 result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(-0 AS REAL)");
1534 REQUIRE(CHECK_COLUMN(result, 0, {2}));
1535 //! -INF till INF
1536 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i >= CAST(-POWER(1000,10000) AS REAL) and i <= "
1537 // "CAST(POWER(1000,10000) AS REAL)");
1538 // REQUIRE(CHECK_COLUMN(result, 0, {4}));
1539}
1540
1541TEST_CASE("ART Double Special Cases", "[art-double-special]") {
1542 unique_ptr<QueryResult> result;
1543 DuckDB db(nullptr);
1544 Connection con(db);
1545
1546 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i DOUBLE)"));
1547 // //! INF
1548 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(POWER(1000,10000) AS DOUBLE))"));
1549 // //! -INF
1550 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(-POWER(1000,10000) AS DOUBLE))"));
1551 // //! NaN
1552 // REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(POWER(1000,10000)*0 AS DOUBLE))"));
1553 //! +0
1554 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(0 AS DOUBLE))"));
1555 //! -0
1556 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (CAST(-0 AS DOUBLE))"));
1557
1558 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON numbers(i)"));
1559 // //! INF
1560 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(POWER(1000,10000) AS DOUBLE)");
1561 // REQUIRE(CHECK_COLUMN(result, 0, {1}));
1562 // //! -INF
1563 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(-POWER(1000,10000) AS DOUBLE)");
1564 // REQUIRE(CHECK_COLUMN(result, 0, {1}));
1565 // //! NaN
1566 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(POWER(1000,10000)*0 AS DOUBLE)");
1567 // REQUIRE(CHECK_COLUMN(result, 0, {0}));
1568 //! +0
1569 result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(0 AS DOUBLE)");
1570 REQUIRE(CHECK_COLUMN(result, 0, {2}));
1571 //! -0
1572 result = con.Query("SELECT COUNT(i) FROM numbers WHERE i = CAST(-0 AS DOUBLE)");
1573 REQUIRE(CHECK_COLUMN(result, 0, {2}));
1574 // //! -INF till INF
1575 // result = con.Query("SELECT COUNT(i) FROM numbers WHERE i >= CAST(-POWER(1000,10000) AS DOUBLE) and i <= "
1576 // "CAST(POWER(1000,10000) AS DOUBLE)");
1577 // REQUIRE(CHECK_COLUMN(result, 0, {4}));
1578}
1579TEST_CASE("Test updates resulting from big index scans", "[art][.]") {
1580 unique_ptr<QueryResult> result;
1581 DuckDB db(nullptr);
1582 Connection con(db);
1583
1584 int64_t sum = 0;
1585 int64_t count = 0;
1586
1587 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
1588
1589 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1590 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1591 for (idx_t i = 0; i < 25000; i++) {
1592 int32_t value = i + 1;
1593
1594 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", value));
1595
1596 sum += value;
1597 count++;
1598 }
1599 REQUIRE_NO_FAIL(con.Query("COMMIT"));
1600
1601 // check the sum and the count
1602 result = con.Query("SELECT SUM(i), COUNT(i) FROM integers WHERE i>0");
1603 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
1604 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count)}));
1605
1606 // update the data with an index scan
1607 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i+1 WHERE i>0"));
1608 sum += count;
1609
1610 // now check the sum and the count again
1611 result = con.Query("SELECT SUM(i), COUNT(i) FROM integers WHERE i>0");
1612 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
1613 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count)}));
1614
1615 // now delete from the table with an index scan
1616 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i>0"));
1617
1618 result = con.Query("SELECT SUM(i), COUNT(i) FROM integers WHERE i>0");
1619 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
1620 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(0)}));
1621}
1622
1623TEST_CASE("ART Node 4", "[art]") {
1624 unique_ptr<QueryResult> result;
1625 DuckDB db(nullptr);
1626
1627 Connection con(db);
1628
1629 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1630 idx_t n = 4;
1631 auto keys = unique_ptr<int32_t[]>(new int32_t[n]);
1632 for (idx_t i = 0; i < n; i++) {
1633 keys[i] = i + 1;
1634 }
1635
1636 for (idx_t i = 0; i < n; i++) {
1637 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
1638 }
1639
1640 for (idx_t i = 0; i < n; i++) {
1641 result = con.Query("SELECT i FROM integers WHERE i=$1", keys[i]);
1642 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1643 }
1644 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1645 result = con.Query("SELECT sum(i) FROM integers WHERE i <= 2");
1646 REQUIRE(CHECK_COLUMN(result, 0, {Value(3)}));
1647 result = con.Query("SELECT sum(i) FROM integers WHERE i > 1");
1648 REQUIRE(CHECK_COLUMN(result, 0, {Value(2 + 3 + 4)}));
1649 // Now Deleting all elements
1650 for (idx_t i = 0; i < n; i++) {
1651 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=$1", keys[i]));
1652 }
1653 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i = 0"));
1654 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1655 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
1656}
1657
1658TEST_CASE("ART Node 16", "[art]") {
1659 unique_ptr<QueryResult> result;
1660 DuckDB db(nullptr);
1661
1662 Connection con(db);
1663
1664 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1665 idx_t n = 6;
1666 auto keys = unique_ptr<int32_t[]>(new int32_t[n]);
1667 for (idx_t i = 0; i < n; i++) {
1668 keys[i] = i + 1;
1669 }
1670
1671 for (idx_t i = 0; i < n; i++) {
1672 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
1673 }
1674 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1675 for (idx_t i = 0; i < n; i++) {
1676 result = con.Query("SELECT i FROM integers WHERE i=$1", keys[i]);
1677 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1678 }
1679 result = con.Query("SELECT sum(i) FROM integers WHERE i <=2");
1680 REQUIRE(CHECK_COLUMN(result, 0, {Value(3)}));
1681 result = con.Query("SELECT sum(i) FROM integers WHERE i > 4");
1682 REQUIRE(CHECK_COLUMN(result, 0, {Value(5 + 6)}));
1683 // Now Deleting all elements
1684 for (idx_t i = 0; i < n; i++) {
1685 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=$1", keys[i]));
1686 }
1687 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1688 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
1689}
1690
1691TEST_CASE("ART Node 48", "[art]") {
1692 unique_ptr<QueryResult> result;
1693 DuckDB db(nullptr);
1694
1695 Connection con(db);
1696
1697 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer)"));
1698 idx_t n = 20;
1699 auto keys = unique_ptr<int32_t[]>(new int32_t[n]);
1700 for (idx_t i = 0; i < n; i++) {
1701 keys[i] = i + 1;
1702 }
1703 int64_t expected_sum = 0;
1704 for (idx_t i = 0; i < n; i++) {
1705 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ($1)", keys[i]));
1706 expected_sum += keys[i];
1707 }
1708 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers(i)"));
1709 for (idx_t i = 0; i < n; i++) {
1710 result = con.Query("SELECT i FROM integers WHERE i=$1", keys[i]);
1711 REQUIRE(CHECK_COLUMN(result, 0, {Value(keys[i])}));
1712 }
1713 result = con.Query("SELECT sum(i) FROM integers WHERE i <=2");
1714 REQUIRE(CHECK_COLUMN(result, 0, {Value(3)}));
1715 result = con.Query("SELECT sum(i) FROM integers WHERE i > 15");
1716 REQUIRE(CHECK_COLUMN(result, 0, {Value(16 + 17 + 18 + 19 + 20)}));
1717
1718 // delete an element and reinsert it
1719 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=16"));
1720 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (16)"));
1721
1722 // query again
1723 result = con.Query("SELECT sum(i) FROM integers WHERE i <=2");
1724 REQUIRE(CHECK_COLUMN(result, 0, {Value(3)}));
1725 result = con.Query("SELECT sum(i) FROM integers WHERE i > 15");
1726 REQUIRE(CHECK_COLUMN(result, 0, {Value(16 + 17 + 18 + 19 + 20)}));
1727
1728 // Now delete all elements
1729 for (idx_t i = 0; i < n; i++) {
1730 REQUIRE_NO_FAIL(con.Query("DELETE FROM integers WHERE i=$1", keys[i]));
1731 expected_sum -= keys[i];
1732 // verify the sum
1733 result = con.Query("SELECT sum(i) FROM integers WHERE i > 0");
1734 REQUIRE(CHECK_COLUMN(result, 0, {expected_sum == 0 ? Value() : Value::BIGINT(expected_sum)}));
1735 }
1736 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
1737 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
1738}
1739
1740TEST_CASE("Index Exceptions", "[art]") {
1741 unique_ptr<QueryResult> result;
1742 DuckDB db(nullptr);
1743
1744 Connection con(db);
1745
1746 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer, j integer, k BOOLEAN)"));
1747
1748 REQUIRE_FAIL(con.Query("CREATE INDEX ON integers(i)"));
1749 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON integers(i COLLATE \"de_DE\")"));
1750 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON integers using blabla(i)"));
1751 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON integers(f)"));
1752}
1753
1754TEST_CASE("More Index Exceptions (#496)", "[art]") {
1755 unique_ptr<QueryResult> result;
1756 DuckDB db(nullptr);
1757
1758 Connection con(db);
1759
1760 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOLEAN, c1 INT)"));
1761 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i0 ON t0(c1, c0)"));
1762 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES (0)"));
1763 auto res = con.Query("SELECT * FROM t0");
1764 REQUIRE(res->success);
1765
1766 REQUIRE(CHECK_COLUMN(res, 0, {Value()}));
1767 REQUIRE(CHECK_COLUMN(res, 1, {0}));
1768}
1769