1#include "catch.hpp"
2#include "duckdb/common/types/date.hpp"
3#include "duckdb/common/types/timestamp.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("IF test", "[function]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 result = con.Query("SELECT IF(true, 1, 10), IF(false, 1, 10), IF(NULL, 1, 10)");
16 REQUIRE(CHECK_COLUMN(result, 0, {1}));
17 REQUIRE(CHECK_COLUMN(result, 1, {10}));
18 REQUIRE(CHECK_COLUMN(result, 2, {10}));
19
20 result = con.Query("SELECT IF(true, 20, 2000), IF(false, 20, 2000), IF(NULL, 20, 2000)");
21 REQUIRE(CHECK_COLUMN(result, 0, {20}));
22 REQUIRE(CHECK_COLUMN(result, 1, {2000}));
23 REQUIRE(CHECK_COLUMN(result, 2, {2000}));
24
25 result = con.Query("SELECT IF(true, 20.5, 2000), IF(false, 20, 2000.5), IF(NULL, 20, 2000.5)");
26 REQUIRE(CHECK_COLUMN(result, 0, {20.5}));
27 REQUIRE(CHECK_COLUMN(result, 1, {2000.5}));
28
29 result = con.Query("SELECT IF(true, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp), "
30 "IF(false, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp), "
31 "IF(NULL, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp)");
32 REQUIRE(CHECK_COLUMN(result, 0, {Timestamp::FromString("2020-05-05 00:00:00")}));
33 REQUIRE(CHECK_COLUMN(result, 1, {Timestamp::FromString("1996-11-05 10:11:56")}));
34 REQUIRE(CHECK_COLUMN(result, 2, {Timestamp::FromString("1996-11-05 10:11:56")}));
35
36 result = con.Query("SELECT IF(true, 'true', 'false'), IF(false, 'true', 'false'), IF(NULL, 'true', 'false')");
37 REQUIRE(CHECK_COLUMN(result, 0, {"true"}));
38 REQUIRE(CHECK_COLUMN(result, 1, {"false"}));
39 REQUIRE(CHECK_COLUMN(result, 2, {"false"}));
40}
41
42TEST_CASE("IFNULL test", "[function]") {
43 unique_ptr<QueryResult> result;
44 DuckDB db(nullptr);
45 Connection con(db);
46 con.EnableQueryVerification();
47
48 result = con.Query("SELECT IFNULL(NULL, NULL), IFNULL(NULL, 10), IFNULL(1, 10)");
49 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
50 REQUIRE(CHECK_COLUMN(result, 1, {10}));
51 REQUIRE(CHECK_COLUMN(result, 2, {1}));
52
53 result = con.Query("SELECT IFNULL(NULL, 2000), IFNULL(20.5, 2000)");
54 REQUIRE(CHECK_COLUMN(result, 0, {2000.0}));
55 REQUIRE(CHECK_COLUMN(result, 1, {20.5}));
56
57 result = con.Query("SELECT IFNULL(NULL, '1996-11-05 10:11:56'::timestamp), "
58 "IFNULL('2020-05-05'::date, '1996-11-05 10:11:56'::timestamp)");
59 REQUIRE(CHECK_COLUMN(result, 0, {Timestamp::FromString("1996-11-05 10:11:56")}));
60 REQUIRE(CHECK_COLUMN(result, 1, {Timestamp::FromString("2020-05-05 00:00:00")}));
61
62 result = con.Query("SELECT IFNULL(NULL, 'not NULL'), IFNULL('NULL', 'not NULL')");
63 REQUIRE(CHECK_COLUMN(result, 0, {"not NULL"}));
64 REQUIRE(CHECK_COLUMN(result, 1, {"NULL"}));
65}
66