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("Update big table of even and odd values", "[update][.]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db), con2(db);
12
13 // create a table with the values [0, ..., 3K]
14 int64_t sum = 0;
15 int64_t count = 3000;
16
17 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
18 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b VARCHAR);"));
19 for (int64_t i = 0; i < count; i++) {
20 sum += i;
21 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (?, 'hello')", (int)i));
22 }
23 // insert a bunch more values
24 while (count < Storage::BLOCK_SIZE) {
25 REQUIRE_NO_FAIL(con.Query("INSERT INTO test SELECT * FROM test"));
26 count *= 2;
27 sum *= 2;
28 }
29
30 REQUIRE_NO_FAIL(con.Query("COMMIT"));
31
32 result = con.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
33 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
34 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 5)}));
35 result = con2.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
36 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
37 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 5)}));
38
39 // increment all even values by two
40 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
41 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+2, b='hellohello' WHERE a%2=0"));
42
43 result = con.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum + count)}));
45 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 10 - (count / 2 * 5))}));
46 result = con2.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
47 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
48 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 5)}));
49
50 // now increment all odd values by two
51 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+2, b='hellohello' WHERE a%2=1"));
52
53 result = con.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
54 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum + count * 2)}));
55 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 10)}));
56 result = con2.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
57 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
58 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 5)}));
59
60 // increment all tuples by two now
61 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+2, b='hellohellohellohello'"));
62
63 result = con.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
64 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum + count * 4)}));
65 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 20)}));
66 result = con2.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
67 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum)}));
68 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 5)}));
69
70 REQUIRE_NO_FAIL(con.Query("COMMIT"));
71
72 result = con.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
73 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum + count * 4)}));
74 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 20)}));
75 result = con2.Query("SELECT SUM(a), SUM(LENGTH(b)) FROM test");
76 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(sum + count * 4)}));
77 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(count * 20)}));
78}
79