1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test DEFAULT in tables", "[default]") {
8 unique_ptr<MaterializedQueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // no default specified: write NULL value
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (3);"));
16 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (DEFAULT, DEFAULT);"));
17 result = con.Query("SELECT * FROM test");
18 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value()}));
19 REQUIRE(CHECK_COLUMN(result, 1, {3, Value()}));
20 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
21
22 // no default specified: default is NULL value
23 // but we set column to NOT NULL
24 // now insert should fail
25 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER NOT NULL, b INTEGER);"));
26 REQUIRE_FAIL(con.Query("INSERT INTO test (b) VALUES (3);"));
27 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
28
29 // simple default: constant value
30 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT 1, b INTEGER);"));
31 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (3);"));
32 result = con.Query("SELECT * FROM test");
33 REQUIRE(CHECK_COLUMN(result, 0, {1}));
34 REQUIRE(CHECK_COLUMN(result, 1, {3}));
35 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
36
37 // default as expression
38 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT 1+1, b INTEGER);"));
39 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (3);"));
40 result = con.Query("SELECT * FROM test");
41 REQUIRE(CHECK_COLUMN(result, 0, {2}));
42 REQUIRE(CHECK_COLUMN(result, 1, {3}));
43 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
44
45 // default with insert from query
46 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT 1+1, b INTEGER);"));
47 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) SELECT 3"));
48 result = con.Query("SELECT * FROM test");
49 REQUIRE(CHECK_COLUMN(result, 0, {2}));
50 REQUIRE(CHECK_COLUMN(result, 1, {3}));
51 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
52
53 // default from sequence
54 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
55 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT nextval('seq'), b INTEGER);"));
56 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (2), (4), (6), (2), (4);"));
57 result = con.Query("SELECT * FROM test ORDER BY 1");
58 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
59 REQUIRE(CHECK_COLUMN(result, 1, {2, 4, 6, 2, 4}));
60 // // cannot drop sequence now
61 // REQUIRE_FAIL(con.Query("DROP SEQUENCE seq"));
62 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
63 // after dropping table we can drop seq
64 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq"));
65
66 // test default with update
67 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
68 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT nextval('seq'), b INTEGER);"));
69 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (1);"));
70 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=DEFAULT"));
71 result = con.Query("SELECT * FROM test ORDER BY 1");
72 REQUIRE(CHECK_COLUMN(result, 0, {2}));
73 REQUIRE(CHECK_COLUMN(result, 1, {1}));
74
75 // cannot use subquery in DEFAULT expression
76 REQUIRE_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT (SELECT 42), b INTEGER);"));
77 // aggregate functions are not allowed in DEFAULT expressions
78 REQUIRE_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT SUM(42), b INTEGER);"));
79 // window functions are not allowed in DEFAULT expressions
80 REQUIRE_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT row_number() OVER (), b INTEGER);"));
81 // default value must be scalar expression
82 REQUIRE_FAIL(con.Query("CREATE TABLE test (a INTEGER DEFAULT b+1, b INTEGER);"));
83
84 // test default with random
85 unique_ptr<MaterializedQueryResult> result_tmp;
86 REQUIRE_NO_FAIL(con.Query("DROP TABLE test"));
87 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a DOUBLE DEFAULT random(), b INTEGER);"));
88 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (1);"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (b) VALUES (2);"));
90 result = con.Query("SELECT a FROM test WHERE b = 1;");
91 result_tmp = move(result);
92 result = con.Query("SELECT a FROM test WHERE b = 2;");
93 REQUIRE(!result->Equals(*result_tmp));
94}
95