1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test standard update behavior with NULLs", "[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 (1), (2), (3), (NULL)"));
15
16 result = con.Query("SELECT * FROM test ORDER BY a");
17 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
18
19 ///////////////
20 // test updating from a non-null value to a null value
21 ///////////////
22 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
23 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=NULL WHERE a=2"));
24
25 // not seen yet by con2, only by con1
26 result = con.Query("SELECT * FROM test ORDER BY a");
27 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
28 result = con2.Query("SELECT * FROM test ORDER BY a");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
30
31 // commit
32 REQUIRE_NO_FAIL(con.Query("COMMIT"));
33
34 result = con.Query("SELECT * FROM test ORDER BY a");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
36 result = con2.Query("SELECT * FROM test ORDER BY a");
37 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
38
39 // now test a rollback
40 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
41 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=NULL WHERE a=3"));
42
43 // not seen yet by con2, only by con1
44 result = con.Query("SELECT * FROM test ORDER BY a");
45 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), 1}));
46 result = con2.Query("SELECT * FROM test ORDER BY a");
47 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
48
49 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
50
51 result = con.Query("SELECT * FROM test ORDER BY a");
52 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
53 result = con2.Query("SELECT * FROM test ORDER BY a");
54 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
55
56 ///////////////
57 // test updating from a null value to a non-null value
58 ///////////////
59 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
60 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=10 WHERE a IS NULL"));
61
62 // not seen yet by con2, only by con1
63 result = con.Query("SELECT * FROM test ORDER BY a");
64 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 10, 10}));
65 result = con2.Query("SELECT * FROM test ORDER BY a");
66 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
67
68 // now rollback
69 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
70
71 // values are back to original values
72 result = con.Query("SELECT * FROM test ORDER BY a");
73 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
74 result = con2.Query("SELECT * FROM test ORDER BY a");
75 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
76
77 // perform the same update, but this time commit
78 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
79 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=10 WHERE a IS NULL"));
80
81 result = con.Query("SELECT * FROM test ORDER BY a");
82 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 10, 10}));
83 result = con2.Query("SELECT * FROM test ORDER BY a");
84 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 1, 3}));
85
86 REQUIRE_NO_FAIL(con.Query("COMMIT"));
87
88 result = con.Query("SELECT * FROM test ORDER BY a");
89 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 10, 10}));
90 result = con2.Query("SELECT * FROM test ORDER BY a");
91 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 10, 10}));
92}
93