1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test ceil(ing)/floor function", "[function]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(n DOUBLE)"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (NULL),(-42.8),(-42.2),(0), (42.2), (42.8)"));
14
15 result = con.Query("SELECT cast(CEIL(n::tinyint) as bigint) FROM numbers ORDER BY n");
16 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
17 result = con.Query("SELECT cast(CEIL(n::smallint) as bigint) FROM numbers ORDER BY n");
18 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
19 result = con.Query("SELECT cast(CEIL(n::integer) as bigint) FROM numbers ORDER BY n");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
21 result = con.Query("SELECT cast(CEIL(n::bigint) as bigint) FROM numbers ORDER BY n");
22 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
23 result = con.Query("SELECT cast(CEIL(n::float) as bigint) FROM numbers ORDER BY n");
24 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 43, 43}));
25 result = con.Query("SELECT cast(CEIL(n::double) as bigint) FROM numbers ORDER BY n");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 43, 43}));
27
28 result = con.Query("SELECT cast(CEILING(n::double) as bigint) FROM numbers ORDER BY n");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 43, 43}));
30
31 result = con.Query("SELECT cast(FLOOR(n::tinyint) as bigint) FROM numbers ORDER BY n");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
33 result = con.Query("SELECT cast(FLOOR(n::smallint) as bigint) FROM numbers ORDER BY n");
34 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
35 result = con.Query("SELECT cast(FLOOR(n::integer) as bigint) FROM numbers ORDER BY n");
36 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
37 result = con.Query("SELECT cast(FLOOR(n::bigint) as bigint) FROM numbers ORDER BY n");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -42, -42, 0, 42, 42}));
39 result = con.Query("SELECT cast(FLOOR(n::float) as bigint) FROM numbers ORDER BY n");
40 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -43, -43, 0, 42, 42}));
41 result = con.Query("SELECT cast(FLOOR(n::double) as bigint) FROM numbers ORDER BY n");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value(), -43, -43, 0, 42, 42}));
43}
44
45TEST_CASE("Rounding test", "[function]") {
46 unique_ptr<QueryResult> result;
47 DuckDB db(nullptr);
48 Connection con(db);
49 con.EnableQueryVerification();
50
51 REQUIRE_NO_FAIL(con.Query("CREATE TABLE roundme(a DOUBLE, b INTEGER)"));
52 REQUIRE_NO_FAIL(con.Query("INSERT INTO roundme VALUES (42.123456, 3)"));
53
54 result = con.Query("select round(42.12345, 0)");
55 REQUIRE(CHECK_COLUMN(result, 0, {42.0}));
56
57 result = con.Query("select round(42.12345, 2)");
58 REQUIRE(CHECK_COLUMN(result, 0, {42.12}));
59
60 result = con.Query("select round(42, 0)");
61 REQUIRE(CHECK_COLUMN(result, 0, {42}));
62
63 result = con.Query("select round(a, 1) from roundme");
64 REQUIRE(CHECK_COLUMN(result, 0, {42.1}));
65
66 result = con.Query("select round(b, 1) from roundme");
67 REQUIRE(CHECK_COLUMN(result, 0, {3}));
68
69 result = con.Query("select round(a, b) from roundme");
70 REQUIRE(CHECK_COLUMN(result, 0, {42.123}));
71}
72
73TEST_CASE("Test random & setseed functions", "[function]") {
74 unique_ptr<QueryResult> result, result1, result2;
75 DuckDB db(nullptr);
76 Connection con(db);
77
78 // random() is evaluated twice here
79 result = con.Query("select case when random() between 0 and 0.99999 then 1 else 0 end");
80 REQUIRE(CHECK_COLUMN(result, 0, {1}));
81
82 result1 = con.Query("select random()");
83 result2 = con.Query("select random()");
84 REQUIRE(!result1->Equals(*result2));
85
86 REQUIRE_NO_FAIL(con.Query("select setseed(0.1)"));
87 result1 = con.Query("select random(), random(), random()");
88 REQUIRE(CHECK_COLUMN(result1, 0, {0.612055}));
89 REQUIRE(CHECK_COLUMN(result1, 1, {0.384141}));
90 REQUIRE(CHECK_COLUMN(result1, 2, {0.288025}));
91 REQUIRE_NO_FAIL(con.Query("select setseed(0.1)"));
92 result2 = con.Query("select random(), random(), random()");
93 REQUIRE(result1->Equals(*result2));
94
95 REQUIRE_FAIL(con.Query("select setseed(1.1)"));
96 REQUIRE_FAIL(con.Query("select setseed(-1.1)"));
97
98 REQUIRE_NO_FAIL(con.Query("CREATE TABLE seeds(a DOUBLE)"));
99 REQUIRE_NO_FAIL(con.Query("INSERT INTO seeds VALUES (-0.1), (0.0), (0.1)"));
100 result2 = con.Query("select setseed(a), a from seeds;");
101 REQUIRE(CHECK_COLUMN(result2, 0, {Value(), Value(), Value()}));
102 REQUIRE(CHECK_COLUMN(result2, 1, {-0.1, 0.0, 0.1}));
103 // Make sure last seed (0.1) is in effect
104 result1 = con.Query("select random(), random(), random()");
105 REQUIRE_NO_FAIL(con.Query("select setseed(0.1)"));
106 result2 = con.Query("select random(), random(), random()");
107 REQUIRE(result1->Equals(*result2));
108
109 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(a INTEGER)"));
110 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)"));
111
112 result = con.Query("select case when min(random()) >= 0 then 1 else 0 end from numbers;");
113 REQUIRE(CHECK_COLUMN(result, 0, {1}));
114 result = con.Query("select case when max(random()) < 1 then 1 else 0 end from numbers;");
115 REQUIRE(CHECK_COLUMN(result, 0, {1}));
116
117 REQUIRE_NO_FAIL(con.Query("select * from numbers order by random()"));
118 REQUIRE_NO_FAIL(con.Query("select random() from numbers"));
119}
120
121// see https://www.postgresql.org/docs/10/functions-math.html
122
123TEST_CASE("Function test cases from PG docs", "[function]") {
124 unique_ptr<QueryResult> result;
125 DuckDB db(nullptr);
126 Connection con(db);
127 con.EnableQueryVerification();
128
129 result = con.Query("select abs(-17.4)");
130 REQUIRE(CHECK_COLUMN(result, 0, {17.4}));
131
132 result = con.Query("select cbrt(27.0)");
133 REQUIRE(CHECK_COLUMN(result, 0, {3.0}));
134
135 result = con.Query("select ceil(-42.8)");
136 REQUIRE(CHECK_COLUMN(result, 0, {-42.0}));
137
138 result = con.Query("select ceiling(-95.3)");
139 REQUIRE(CHECK_COLUMN(result, 0, {-95.0}));
140
141 result = con.Query("select exp(1.0)");
142 REQUIRE(CHECK_COLUMN(result, 0, {2.71828182845905}));
143
144 result = con.Query("select floor(-42.8)");
145 REQUIRE(CHECK_COLUMN(result, 0, {-43.0}));
146
147 result = con.Query("select ln(2.0)");
148 REQUIRE(CHECK_COLUMN(result, 0, {0.693147180559945}));
149
150 result = con.Query("select log(100.0)");
151 REQUIRE(CHECK_COLUMN(result, 0, {2.0}));
152
153 result = con.Query("select log10(100.0)");
154 REQUIRE(CHECK_COLUMN(result, 0, {2.0}));
155
156 result = con.Query("select log2(4.0)");
157 REQUIRE(CHECK_COLUMN(result, 0, {2.0}));
158
159 result = con.Query("select pi()");
160 REQUIRE(CHECK_COLUMN(result, 0, {3.14159265358979}));
161
162 result = con.Query("select sqrt(2.0)");
163 REQUIRE(CHECK_COLUMN(result, 0, {1.4142135623731}));
164
165 result = con.Query("select radians(45.0)");
166 REQUIRE(CHECK_COLUMN(result, 0, {0.785398163397448}));
167
168 result = con.Query("select degrees(0.5)");
169 REQUIRE(CHECK_COLUMN(result, 0, {28.6478897565412}));
170
171 result = con.Query("select sign(4.1)");
172 REQUIRE(CHECK_COLUMN(result, 0, {1}));
173
174 result = con.Query("select sign(-4.1)");
175 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
176
177 result = con.Query("select sign(0)");
178 REQUIRE(CHECK_COLUMN(result, 0, {0}));
179
180 result = con.Query("select sign(3)");
181 REQUIRE(CHECK_COLUMN(result, 0, {1}));
182}
183
184TEST_CASE("Mod test", "[function]") {
185 unique_ptr<QueryResult> result;
186 DuckDB db(nullptr);
187 Connection con(db);
188 con.EnableQueryVerification();
189
190 REQUIRE_NO_FAIL(con.Query("CREATE TABLE modme(a DOUBLE, b INTEGER)"));
191 REQUIRE_NO_FAIL(con.Query("INSERT INTO modme VALUES (42.123456, 3)"));
192
193 // input is real, divisor is an integer
194 result = con.Query("select mod(a, 40) from modme");
195 REQUIRE(CHECK_COLUMN(result, 0, {2.123456}));
196
197 // Mod with 0 should be null
198 result = con.Query("select mod(42, 0)");
199 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
200
201 // input is real, divisor is a real
202 result = con.Query("select mod(a, 2) from modme");
203 REQUIRE(CHECK_COLUMN(result, 0, {.123456}));
204
205 // input is an integer, divisor is a real
206 result = con.Query("select mod(b, 2.1) from modme");
207 REQUIRE(CHECK_COLUMN(result, 0, {0.9}));
208}
209TEST_CASE("Power test", "[function]") {
210 unique_ptr<QueryResult> result;
211 DuckDB db(nullptr);
212 Connection con(db);
213 con.EnableQueryVerification();
214
215 REQUIRE_NO_FAIL(con.Query("CREATE TABLE powerme(a DOUBLE, b INTEGER)"));
216 REQUIRE_NO_FAIL(con.Query("INSERT INTO powerme VALUES (2.1, 3)"));
217
218 result = con.Query("select pow(a, 0) from powerme");
219 REQUIRE(CHECK_COLUMN(result, 0, {1.0}));
220
221 result = con.Query("select pow(b, -2) from powerme");
222 REQUIRE(CHECK_COLUMN(result, 0, {.1111}));
223
224 result = con.Query("select pow(a, b) from powerme");
225 REQUIRE(CHECK_COLUMN(result, 0, {9.261}));
226
227 result = con.Query("select pow(b, a) from powerme");
228 REQUIRE(CHECK_COLUMN(result, 0, {10.045}));
229
230 result = con.Query("select power(b, a) from powerme");
231 REQUIRE(CHECK_COLUMN(result, 0, {10.045}));
232}
233
234TEST_CASE("BIT_COUNT test", "[function]") {
235 unique_ptr<QueryResult> result;
236 DuckDB db(nullptr);
237 Connection con(db);
238 con.EnableQueryVerification();
239
240 REQUIRE_NO_FAIL(con.Query("CREATE TABLE bits(t tinyint, s smallint, i integer, b bigint)"));
241 REQUIRE_NO_FAIL(con.Query("INSERT INTO bits VALUES (NULL, NULL, NULL, NULL), "
242 "(31, 1023, 11834119, 50827156903621017), "
243 "(-59, -517, -575693, -9876543210)"));
244
245 result = con.Query("select bit_count(t), bit_count(s),bit_count(i), bit_count(b) from bits");
246 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 5, 4}));
247 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 10, 14}));
248 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 11, 24}));
249 REQUIRE(CHECK_COLUMN(result, 3, {Value(), 27, 49}));
250}
251
252TEST_CASE("Test invalid input for math functions", "[function]") {
253 unique_ptr<QueryResult> result;
254 DuckDB db(nullptr);
255 Connection con(db);
256 con.EnableQueryVerification();
257
258 // any invalid input in math functions results in a NULL
259 // sqrt of negative number
260 result = con.Query("SELECT SQRT(-1), SQRT(0)");
261 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
262 REQUIRE(CHECK_COLUMN(result, 1, {0}));
263
264 // log of value <= 0
265 result = con.Query("SELECT LN(-1), LN(0), LOG10(-1), LOG10(0), LOG2(-1), LOG2(0)");
266 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
267 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
268 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
269 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
270 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
271 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
272
273 // invalid input to POW function
274 result = con.Query("SELECT POW(1e300,100), POW(-1e300,100), POW(-1.0, 0.5)");
275 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
276 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
277 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
278
279 // overflow in EXP function
280 result = con.Query("SELECT EXP(1e300), EXP(1e100)");
281 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
282 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
283
284 // invalid input to trigonometric functions
285 result = con.Query("SELECT ACOS(3), ACOS(100), DEGREES(1e308)");
286 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
287 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
288 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
289}
290