1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test case statement", "[case]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12 con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
13 con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)");
14
15 result = con.Query("SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test");
16 REQUIRE(CHECK_COLUMN(result, 0, {22, Value(), Value()}));
17 // constant case statements
18 // all constant
19 result = con.Query("SELECT CASE WHEN 1=1 THEN 1 ELSE NULL END FROM test ORDER BY b");
20 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1}));
21 // check + res_if_false constant
22 result = con.Query("SELECT CASE WHEN 1=1 THEN b ELSE NULL END FROM test ORDER BY b");
23 REQUIRE(CHECK_COLUMN(result, 0, {21, 22, 22}));
24 // check + res_if_true constant
25 result = con.Query("SELECT CASE WHEN 3>2 THEN NULL ELSE b+1 END FROM test ORDER BY b");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
27 // check constant
28 result = con.Query("SELECT CASE WHEN 1=0 THEN b ELSE b+1 END FROM test ORDER BY b");
29 REQUIRE(CHECK_COLUMN(result, 0, {22, 23, 23}));
30 // res_if_true and res_if_false constant
31 result = con.Query("SELECT CASE WHEN b=22 THEN NULL ELSE 1 END FROM test ORDER BY b");
32 REQUIRE(CHECK_COLUMN(result, 0, {1, Value(), Value()}));
33 // res_if_false constant
34 result = con.Query("SELECT CASE WHEN b=22 THEN b+1 ELSE 1 END FROM test ORDER BY b");
35 REQUIRE(CHECK_COLUMN(result, 0, {1, 23, 23}));
36 // res_if_true constant
37 result = con.Query("SELECT CASE WHEN b=22 THEN NULL ELSE b+1 END FROM test ORDER BY b");
38 REQUIRE(CHECK_COLUMN(result, 0, {22, Value(), Value()}));
39
40 // fail case on types that can't be cast to boolean
41 REQUIRE_FAIL(con.Query("SELECT CASE WHEN 'hello' THEN b ELSE a END FROM test"));
42 // but only when cast cannot be performed
43 result = con.Query("SELECT CASE WHEN 'true' THEN NULL ELSE b+1 END FROM test ORDER BY b");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
45 result = con.Query("SELECT CASE WHEN 'false' THEN NULL ELSE b+1 END FROM test ORDER BY b");
46 REQUIRE(CHECK_COLUMN(result, 0, {22, 23, 23}));
47}
48
49TEST_CASE("Test NULL IF statement", "[case]") {
50 unique_ptr<QueryResult> result;
51 DuckDB db(nullptr);
52 Connection con(db);
53 con.EnableQueryVerification();
54 // NULL IF
55 result = con.Query("SELECT NULLIF(NULLIF ('hello', 'world'), 'blabla');");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value("hello")}));
57
58 // NULL IF with subquery
59 con.Query("CREATE TABLE test (a STRING);");
60 con.Query("INSERT INTO test VALUES ('hello'), ('world'), ('test')");
61
62 con.Query("CREATE TABLE test2 (a STRING, b STRING);");
63 con.Query("INSERT INTO test2 VALUES ('blabla', 'b'), ('blabla2', 'c'), "
64 "('blabla3', 'd')");
65
66 result = con.Query("SELECT NULLIF(NULLIF ((SELECT a FROM test "
67 "LIMIT 1 offset 1), a), b) FROM test2");
68 REQUIRE(CHECK_COLUMN(result, 0, {Value("world"), Value("world"), Value("world")}));
69
70 REQUIRE_NO_FAIL(con.Query("DROP TABLE test;"));
71}
72
73TEST_CASE("NULL IF with strings", "[case]") {
74 unique_ptr<QueryResult> result;
75 DuckDB db(nullptr);
76 Connection con(db);
77 con.EnableQueryVerification();
78
79 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
80 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
81
82 result = con.Query("SELECT NULLIF(CAST(a AS VARCHAR), 11) FROM test;");
83 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value("13"), Value("12")}));
84
85 result =
86 con.Query("SELECT a, CASE WHEN a>11 THEN CAST(a AS VARCHAR) ELSE CAST(b AS VARCHAR) END FROM test ORDER BY 1;");
87 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
88 REQUIRE(CHECK_COLUMN(result, 1, {Value("22"), Value("12"), Value("13")}));
89}
90