1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar NULL handling", "[nullhandling]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // scalar NULL
13 result = con.Query("SELECT NULL");
14 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
15
16 // scalar NULL addition
17 result = con.Query("SELECT 3 + NULL");
18 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
19 result = con.Query("SELECT NULL + 3");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
21
22 // NULL on both sides
23 result = con.Query("SELECT NULL + NULL");
24 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
25
26 // nested NULL addition
27 result = con.Query("SELECT 1 + (NULL + NULL)");
28 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
29
30 // division by zero
31 result = con.Query("SELECT 4 / 0");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
33}
34
35TEST_CASE("Test simple NULL handling", "[nullhandling]") {
36 unique_ptr<QueryResult> result;
37 DuckDB db(nullptr);
38 Connection con(db);
39
40 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
41 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (NULL, 21), (13, 22)"));
42
43 // NULL selection
44 result = con.Query("SELECT a FROM test");
45 REQUIRE(CHECK_COLUMN(result, 0, {11, Value(), 13}));
46
47 // cast NULL
48 result = con.Query("SELECT cast(a AS BIGINT) FROM test;");
49 REQUIRE(CHECK_COLUMN(result, 0, {11, Value(), 13}));
50
51 // division by constant zero is NULL
52 result = con.Query("SELECT a / 0 FROM test;");
53 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
54
55 // division by non-constant zero as well
56 result = con.Query("SELECT a / (a - a) FROM test;");
57 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
58
59 // NULL addition results in NULL
60 result = con.Query("SELECT a + b FROM test");
61 REQUIRE(CHECK_COLUMN(result, 0, {33, Value(), 35}));
62}
63
64TEST_CASE("Test NULL handling in aggregations", "[nullhandling]") {
65 unique_ptr<QueryResult> result;
66 DuckDB db(nullptr);
67 Connection con(db);
68
69 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
70 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (NULL, 21), (13, 22)"));
71
72 // aggregations should ignore NULLs
73 result = con.Query("SELECT SUM(a), MIN(a), MAX(a) FROM test");
74 REQUIRE(CHECK_COLUMN(result, 0, {24}));
75 REQUIRE(CHECK_COLUMN(result, 1, {11}));
76 REQUIRE(CHECK_COLUMN(result, 2, {13}));
77
78 // count should ignore NULL
79 result = con.Query("SELECT COUNT(*), COUNT(a), COUNT(b) FROM test");
80 REQUIRE(CHECK_COLUMN(result, 0, {3})); // * returns full table count
81 REQUIRE(CHECK_COLUMN(result, 1, {2})); // counting "a" ignores null values
82 REQUIRE(CHECK_COLUMN(result, 2, {3}));
83
84 // with GROUP BY as well
85 result = con.Query("SELECT b, COUNT(a), SUM(a), MIN(a), MAX(a) FROM test "
86 "GROUP BY b ORDER BY b");
87 REQUIRE(CHECK_COLUMN(result, 0, {21, 22}));
88 REQUIRE(CHECK_COLUMN(result, 1, {0, 2}));
89 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 24}));
90 REQUIRE(CHECK_COLUMN(result, 3, {Value(), 11}));
91 REQUIRE(CHECK_COLUMN(result, 4, {Value(), 13}));
92
93 // GROUP BY null value
94 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (12, NULL), (16, NULL)"));
95
96 result = con.Query("SELECT b, COUNT(a), SUM(a), MIN(a), MAX(a) FROM test "
97 "GROUP BY b ORDER BY b");
98 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 21, 22}));
99 REQUIRE(CHECK_COLUMN(result, 1, {2, 0, 2}));
100 REQUIRE(CHECK_COLUMN(result, 2, {28, Value(), 24}));
101 REQUIRE(CHECK_COLUMN(result, 3, {12, Value(), 11}));
102 REQUIRE(CHECK_COLUMN(result, 4, {16, Value(), 13}));
103
104 // NULL values should be ignored entirely in the aggregation
105 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (NULL, NULL), (NULL, 22)"));
106
107 result = con.Query("SELECT b, COUNT(a), SUM(a), MIN(a), MAX(a) FROM test "
108 "GROUP BY b ORDER BY b");
109 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 21, 22}));
110 REQUIRE(CHECK_COLUMN(result, 1, {2, 0, 2}));
111 REQUIRE(CHECK_COLUMN(result, 2, {28, Value(), 24}));
112 REQUIRE(CHECK_COLUMN(result, 3, {12, Value(), 11}));
113 REQUIRE(CHECK_COLUMN(result, 4, {16, Value(), 13}));
114}
115
116TEST_CASE("Test IS NULL", "[nullhandling]") {
117 unique_ptr<QueryResult> result;
118 DuckDB db(nullptr);
119 Connection con(db);
120
121 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
122 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (NULL, 2), (13, 3)"));
123
124 // IS NULL/IS NOT NULL
125 result = con.Query("SELECT a IS NULL, a IS NOT NULL, rowid IS NULL, (a = NULL) IS NULL FROM test ORDER BY b");
126 REQUIRE(CHECK_COLUMN(result, 0, {false, true, false}));
127 REQUIRE(CHECK_COLUMN(result, 1, {true, false, true}));
128 REQUIRE(CHECK_COLUMN(result, 2, {false, false, false}));
129 REQUIRE(CHECK_COLUMN(result, 3, {true, true, true}));
130
131 result = con.Query(
132 "SELECT a IS NULL, a IS NOT NULL, rowid IS NULL, (a = NULL) IS NULL FROM test WHERE b != 1 ORDER BY b");
133 REQUIRE(CHECK_COLUMN(result, 0, {true, false}));
134 REQUIRE(CHECK_COLUMN(result, 1, {false, true}));
135 REQUIRE(CHECK_COLUMN(result, 2, {false, false}));
136 REQUIRE(CHECK_COLUMN(result, 3, {true, true}));
137}
138