1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Booleans and NULLs", "[sql]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // AND
14 result = con.Query("SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1, NULL AND "
15 "0, NULL AND 1, 0 AND NULL, 1 AND NULL, NULL AND NULL");
16 REQUIRE(CHECK_COLUMN(result, 0, {0}));
17 REQUIRE(CHECK_COLUMN(result, 1, {0}));
18 REQUIRE(CHECK_COLUMN(result, 2, {0}));
19 REQUIRE(CHECK_COLUMN(result, 3, {1}));
20 REQUIRE(CHECK_COLUMN(result, 4, {0}));
21 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
22 REQUIRE(CHECK_COLUMN(result, 6, {0}));
23 REQUIRE(CHECK_COLUMN(result, 7, {Value()}));
24 REQUIRE(CHECK_COLUMN(result, 8, {Value()}));
25
26 // OR
27 result = con.Query("SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1, NULL OR "
28 "0, NULL OR 1, 0 OR NULL, 1 OR NULL, NULL OR NULL");
29
30 REQUIRE(CHECK_COLUMN(result, 0, {0}));
31 REQUIRE(CHECK_COLUMN(result, 1, {1}));
32 REQUIRE(CHECK_COLUMN(result, 2, {1}));
33 REQUIRE(CHECK_COLUMN(result, 3, {1}));
34 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
35 REQUIRE(CHECK_COLUMN(result, 5, {1}));
36 REQUIRE(CHECK_COLUMN(result, 6, {Value()}));
37 REQUIRE(CHECK_COLUMN(result, 7, {1}));
38 REQUIRE(CHECK_COLUMN(result, 8, {Value()}));
39
40 // NOT
41 result = con.Query("SELECT NOT(0), NOT(1), NOT(NULL)");
42 REQUIRE(CHECK_COLUMN(result, 0, {1}));
43 REQUIRE(CHECK_COLUMN(result, 1, {0}));
44 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
45
46 // IS NULL
47 result = con.Query("SELECT NULL IS NULL, NULL IS NOT NULL, 42 IS NULL, 42 IS NOT NULL");
48 REQUIRE(CHECK_COLUMN(result, 0, {1}));
49 REQUIRE(CHECK_COLUMN(result, 1, {0}));
50 REQUIRE(CHECK_COLUMN(result, 2, {0}));
51 REQUIRE(CHECK_COLUMN(result, 3, {1}));
52
53 // Comparisions
54 result = con.Query("SELECT NULL = NULL, NULL <> NULL, 42 = NULL, 42 <> NULL");
55 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
56 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
57 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
58 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
59
60 con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
61 con.Query("INSERT INTO test VALUES (11, 22), (NULL, 21), (13, 22), (12, "
62 "NULL), (16, NULL)");
63
64 result = con.Query("SELECT b, COUNT(a), SUM(a), MIN(a), MAX(a) FROM test "
65 "GROUP BY b ORDER BY b");
66 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 21, 22}));
67 REQUIRE(CHECK_COLUMN(result, 1, {2, 0, 2}));
68 REQUIRE(CHECK_COLUMN(result, 2, {28, Value(), 24}));
69 REQUIRE(CHECK_COLUMN(result, 3, {12, Value(), 11}));
70 REQUIRE(CHECK_COLUMN(result, 4, {16, Value(), 13}));
71
72 // REQUIRE(duckdb_query(connection,
73 // "SELECT b, COUNT(a), SUM(a), MIN(a), MAX(a) FROM
74 // test " "GROUP BY b ORDER BY b", &result) ==
75 // DuckDBSuccess);
76 // REQUIRE(CHECK_NUMERIC_COLUMN(result, 0, {NULL_NUMERIC, 21, 22}));
77 // REQUIRE(CHECK_NUMERIC_COLUMN(result, 1, {2, 0, 2}));
78 // REQUIRE(CHECK_NUMERIC_COLUMN(result, 2, {28, NULL_NUMERIC, 24}));
79 // REQUIRE(CHECK_NUMERIC_COLUMN(result, 3, {12, NULL_NUMERIC, 11}));
80 // REQUIRE(CHECK_NUMERIC_COLUMN(result, 4, {16, NULL_NUMERIC, 13}));
81 // duckdb_destroy_result(result);
82}
83
84// NOT
85