1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Complex Expressions", "[sql]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE exprtest (a INTEGER, b INTEGER)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO exprtest VALUES (42, 10), (43, 100), (NULL, 1), (45, -1)"));
15
16 result = con.Query("SELECT * FROM exprtest");
17 REQUIRE(CHECK_COLUMN(result, 0, {42, 43, Value(), 45}));
18
19 // BETWEEN
20 result = con.Query("SELECT a FROM exprtest WHERE a BETWEEN 43 AND 44");
21 REQUIRE(CHECK_COLUMN(result, 0, {43}));
22
23 result = con.Query("SELECT a FROM exprtest WHERE a NOT BETWEEN 43 AND 44");
24 REQUIRE(CHECK_COLUMN(result, 0, {42, 45}));
25
26 result = con.Query("SELECT a FROM exprtest WHERE a BETWEEN b AND 44");
27 REQUIRE(CHECK_COLUMN(result, 0, {42}));
28
29 // CASE
30 result = con.Query("SELECT CASE a WHEN 42 THEN 100 WHEN 43 THEN 200 ELSE "
31 "300 END FROM exprtest");
32 REQUIRE(CHECK_COLUMN(result, 0, {100, 200, 300, 300}));
33
34 result = con.Query("SELECT CASE WHEN a = 42 THEN 100 WHEN a = 43 THEN 200 "
35 "ELSE 300 END FROM exprtest");
36 REQUIRE(CHECK_COLUMN(result, 0, {100, 200, 300, 300}));
37
38 result = con.Query("SELECT CASE WHEN a = 42 THEN 100 WHEN a = 43 THEN 200 "
39 "END FROM exprtest");
40 REQUIRE(CHECK_COLUMN(result, 0, {100, 200, Value(), Value()}));
41
42 // COALESCE
43 result = con.Query("SELECT COALESCE(NULL, NULL, 42, 43)");
44 REQUIRE(CHECK_COLUMN(result, 0, {42}));
45
46 result = con.Query("SELECT COALESCE(NULL, NULL, 42)");
47 REQUIRE(CHECK_COLUMN(result, 0, {42}));
48
49 result = con.Query("SELECT COALESCE(42, NULL, 43)");
50 REQUIRE(CHECK_COLUMN(result, 0, {42}));
51
52 result = con.Query("SELECT COALESCE(NULL, NULL, NULL)");
53 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
54
55 result = con.Query("SELECT COALESCE(a, b) FROM exprtest");
56 REQUIRE(CHECK_COLUMN(result, 0, {42, 43, 1, 45}));
57
58 // ABS
59 result = con.Query("SELECT ABS(1), ABS(-1), ABS(NULL)");
60 REQUIRE(CHECK_COLUMN(result, 0, {1}));
61 REQUIRE(CHECK_COLUMN(result, 1, {1}));
62 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
63
64 result = con.Query("SELECT ABS(b) FROM exprtest");
65 REQUIRE(CHECK_COLUMN(result, 0, {10, 100, 1, 1}));
66
67 // IN
68
69 con.Query("CREATE TABLE intest (a INTEGER, b INTEGER, c INTEGER)");
70
71 con.Query("INSERT INTO intest VALUES (42, 42, 42), (43, 42, 42), (44, 41, 44);");
72
73 result = con.Query("SELECT * FROM intest WHERE a IN (42, 43)");
74 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
75 REQUIRE(CHECK_COLUMN(result, 1, {42, 42}));
76 REQUIRE(CHECK_COLUMN(result, 2, {42, 42}));
77
78 result = con.Query("SELECT a IN (42, 43) FROM intest ");
79 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 0}));
80
81 result = con.Query("SELECT * FROM intest WHERE a IN (86, 103, 162)");
82 REQUIRE(CHECK_COLUMN(result, 0, {}));
83 REQUIRE(CHECK_COLUMN(result, 1, {}));
84 REQUIRE(CHECK_COLUMN(result, 2, {}));
85
86 result = con.Query("SELECT * FROM intest WHERE a IN (NULL, NULL, NULL, NULL)");
87 REQUIRE(CHECK_COLUMN(result, 0, {}));
88 REQUIRE(CHECK_COLUMN(result, 1, {}));
89 REQUIRE(CHECK_COLUMN(result, 2, {}));
90
91 result = con.Query("SELECT * FROM intest WHERE a IN (b)");
92 REQUIRE(CHECK_COLUMN(result, 0, {42}));
93 REQUIRE(CHECK_COLUMN(result, 1, {42}));
94 REQUIRE(CHECK_COLUMN(result, 2, {42}));
95
96 result = con.Query("SELECT * FROM intest WHERE a IN (b, c)");
97 REQUIRE(CHECK_COLUMN(result, 0, {42, 44}));
98 REQUIRE(CHECK_COLUMN(result, 1, {42, 41}));
99 REQUIRE(CHECK_COLUMN(result, 2, {42, 44}));
100
101 result = con.Query("SELECT * FROM intest WHERE a IN (43, b) ORDER BY 1");
102 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
103 REQUIRE(CHECK_COLUMN(result, 1, {42, 42}));
104 REQUIRE(CHECK_COLUMN(result, 2, {42, 42}));
105
106 result = con.Query("SELECT * FROM intest WHERE a NOT IN (42, 43)");
107 REQUIRE(CHECK_COLUMN(result, 0, {44}));
108 REQUIRE(CHECK_COLUMN(result, 1, {41}));
109 REQUIRE(CHECK_COLUMN(result, 2, {44}));
110
111 result = con.Query("SELECT * FROM intest WHERE a NOT IN (86, 103, 162) ORDER BY 1");
112 REQUIRE(CHECK_COLUMN(result, 0, {42, 43, 44}));
113 REQUIRE(CHECK_COLUMN(result, 1, {42, 42, 41}));
114 REQUIRE(CHECK_COLUMN(result, 2, {42, 42, 44}));
115
116 result = con.Query("SELECT * FROM intest WHERE a NOT IN (NULL, NULL)");
117 REQUIRE(CHECK_COLUMN(result, 0, {}));
118 REQUIRE(CHECK_COLUMN(result, 1, {}));
119 REQUIRE(CHECK_COLUMN(result, 2, {}));
120
121 result = con.Query("SELECT * FROM intest WHERE a NOT IN (b) ORDER BY 1");
122 REQUIRE(CHECK_COLUMN(result, 0, {43, 44}));
123 REQUIRE(CHECK_COLUMN(result, 1, {42, 41}));
124 REQUIRE(CHECK_COLUMN(result, 2, {42, 44}));
125
126 result = con.Query("SELECT * FROM intest WHERE a NOT IN (b, c)");
127 REQUIRE(CHECK_COLUMN(result, 0, {43}));
128 REQUIRE(CHECK_COLUMN(result, 1, {42}));
129 REQUIRE(CHECK_COLUMN(result, 2, {42}));
130
131 result = con.Query("SELECT * FROM intest WHERE a NOT IN (43, b)");
132 REQUIRE(CHECK_COLUMN(result, 0, {44}));
133 REQUIRE(CHECK_COLUMN(result, 1, {41}));
134 REQUIRE(CHECK_COLUMN(result, 2, {44}));
135
136 result = con.Query("SELECT * FROM intest WHERE NULL IN ('a', 'b')");
137 REQUIRE(CHECK_COLUMN(result, 0, {}));
138 REQUIRE(CHECK_COLUMN(result, 1, {}));
139 REQUIRE(CHECK_COLUMN(result, 2, {}));
140
141 result = con.Query("SELECT * FROM intest WHERE NULL NOT IN ('a', 'b')");
142 REQUIRE(CHECK_COLUMN(result, 0, {}));
143 REQUIRE(CHECK_COLUMN(result, 1, {}));
144 REQUIRE(CHECK_COLUMN(result, 2, {}));
145
146 con.Query("CREATE TABLE strtest (a INTEGER, b VARCHAR)");
147 con.Query("INSERT INTO strtest VALUES (1, 'a'), (2, 'h'), (3, 'd')");
148
149 con.Query("INSERT INTO strtest VALUES (4, NULL)");
150
151 result = con.Query("SELECT a FROM strtest WHERE b = 'a'");
152 REQUIRE(CHECK_COLUMN(result, 0, {1}));
153
154 result = con.Query("SELECT a FROM strtest WHERE b <> 'a'");
155 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
156
157 result = con.Query("SELECT a FROM strtest WHERE b < 'h'");
158 REQUIRE(CHECK_COLUMN(result, 0, {1, 3}));
159
160 result = con.Query("SELECT a FROM strtest WHERE b <= 'h'");
161 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
162
163 result = con.Query("SELECT a FROM strtest WHERE b > 'h'");
164 REQUIRE(CHECK_COLUMN(result, 0, {}));
165
166 result = con.Query("SELECT a FROM strtest WHERE b >= 'h'");
167 REQUIRE(CHECK_COLUMN(result, 0, {2}));
168}
169