1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("CONCAT test", "[function]") {
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 strings(a STRING, b STRING)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
15 "('HuLlD', NULL), ('MotörHead','RÄcks')"));
16
17 result = con.Query("select CONCAT(a, 'SUFFIX') FROM strings");
18 REQUIRE(CHECK_COLUMN(result, 0, {"HelloSUFFIX", "HuLlDSUFFIX", "MotörHeadSUFFIX"}));
19
20 result = con.Query("select CONCAT('PREFIX', b) FROM strings");
21 REQUIRE(CHECK_COLUMN(result, 0, {"PREFIXWorld", "PREFIX", "PREFIXRÄcks"}));
22
23 result = con.Query("select CONCAT(a, b) FROM strings");
24 REQUIRE(CHECK_COLUMN(result, 0, {"HelloWorld", "HuLlD", "MotörHeadRÄcks"}));
25
26 result = con.Query("select CONCAT(a, b, 'SUFFIX') FROM strings");
27 REQUIRE(CHECK_COLUMN(result, 0, {"HelloWorldSUFFIX", "HuLlDSUFFIX", "MotörHeadRÄcksSUFFIX"}));
28
29 result = con.Query("select CONCAT(a, b, a) FROM strings");
30 REQUIRE(CHECK_COLUMN(result, 0, {"HelloWorldHello", "HuLlDHuLlD", "MotörHeadRÄcksMotörHead"}));
31
32 result = con.Query("select CONCAT('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')");
33 REQUIRE(CHECK_COLUMN(result, 0, {"1234567890"}));
34
35 // concat a long string
36 result = con.Query("select '1234567890' || '1234567890', '1234567890' || NULL");
37 REQUIRE(CHECK_COLUMN(result, 0, {"12345678901234567890"}));
38 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
39
40 result = con.Query("select CONCAT('1234567890', '1234567890'), CONCAT('1234567890', NULL)");
41 REQUIRE(CHECK_COLUMN(result, 0, {"12345678901234567890"}));
42 REQUIRE(CHECK_COLUMN(result, 1, {"1234567890"}));
43}
44
45TEST_CASE("CONCAT_WS 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 strings(a STRING, b STRING)"));
52 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
53 "('HuLlD', NULL), ('MotörHead','RÄcks')"));
54
55 result = con.Query("select CONCAT_WS(',',a, 'SUFFIX') FROM strings");
56 REQUIRE(CHECK_COLUMN(result, 0, {"Hello,SUFFIX", "HuLlD,SUFFIX", "MotörHead,SUFFIX"}));
57
58 result = con.Query("select CONCAT_WS('@','PREFIX', b) FROM strings");
59 REQUIRE(CHECK_COLUMN(result, 0, {"PREFIX@World", "PREFIX", "PREFIX@RÄcks"}));
60
61 result = con.Query("select CONCAT_WS('$',a, b) FROM strings");
62 REQUIRE(CHECK_COLUMN(result, 0, {"Hello$World", "HuLlD", "MotörHead$RÄcks"}));
63
64 result = con.Query("select CONCAT_WS(a, b, 'SUFFIX') FROM strings");
65 REQUIRE(CHECK_COLUMN(result, 0, {"WorldHelloSUFFIX", "SUFFIX", "RÄcksMotörHeadSUFFIX"}));
66
67 result = con.Query("select CONCAT_WS(a, b, b) FROM strings");
68 REQUIRE(CHECK_COLUMN(result, 0, {"WorldHelloWorld", "", "RÄcksMotörHeadRÄcks"}));
69
70 result = con.Query("select CONCAT_WS('@','1', '2', '3', '4', '5', '6', '7', '8', '9')");
71 REQUIRE(CHECK_COLUMN(result, 0, {"1@2@3@4@5@6@7@8@9"}));
72
73 result = con.Query("select CONCAT_WS(b, '[', ']') FROM strings ORDER BY a");
74 REQUIRE(CHECK_COLUMN(result, 0, {"[World]", Value(), "[RÄcks]"}));
75
76 // filters
77 result = con.Query("select CONCAT_WS(',', a, 'SUFFIX') FROM strings WHERE a != 'Hello'");
78 REQUIRE(CHECK_COLUMN(result, 0, {"HuLlD,SUFFIX", "MotörHead,SUFFIX"}));
79
80 // concat WS needs at least two parameters
81 REQUIRE_FAIL(con.Query("select CONCAT_WS()"));
82 REQUIRE_FAIL(con.Query("select CONCAT_WS(',')"));
83
84 // one entry: just returns the entry
85 result = con.Query("select CONCAT_WS(',', 'hello')");
86 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
87
88 // NULL in separator results in null
89 result = con.Query("select CONCAT_WS(NULL, 'hello')");
90 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
91 // NULL in data results in empty string
92 result = con.Query("select CONCAT_WS(',', NULL)");
93 REQUIRE(CHECK_COLUMN(result, 0, {""}));
94
95 // NULL separator returns in entire column being NULL
96 result = con.Query("select CONCAT_WS(NULL, b, 'SUFFIX') FROM strings");
97 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
98 // NULL in separator is just ignored
99 result = con.Query("select CONCAT_WS(',', NULL, 'SUFFIX') FROM strings");
100 REQUIRE(CHECK_COLUMN(result, 0, {"SUFFIX", "SUFFIX", "SUFFIX"}));
101
102 // empty strings still get split up by the separator
103 result = con.Query("select CONCAT_WS(',', '', '')");
104 REQUIRE(CHECK_COLUMN(result, 0, {","}));
105 result = con.Query("select CONCAT_WS(',', '', '', '')");
106 REQUIRE(CHECK_COLUMN(result, 0, {",,"}));
107
108 // but NULLs do not
109 result = con.Query("select CONCAT_WS(',', NULL, NULL)");
110 REQUIRE(CHECK_COLUMN(result, 0, {""}));
111 result = con.Query("select CONCAT_WS(',', NULL, NULL, NULL)");
112 REQUIRE(CHECK_COLUMN(result, 0, {""}));
113 result = con.Query("select CONCAT_WS(',', NULL, NULL, 'hello')");
114 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
115
116 // now test for non-constant separators
117 result = con.Query("select CONCAT_WS(a, '', NULL, '') FROM strings ORDER BY a");
118 REQUIRE(CHECK_COLUMN(result, 0, {"Hello", "HuLlD", "MotörHead"}));
119 result = con.Query("select CONCAT_WS(a, NULL, '', '') FROM strings ORDER BY a;");
120 REQUIRE(CHECK_COLUMN(result, 0, {"Hello", "HuLlD", "MotörHead"}));
121
122 // now non-constant separator with a mix of constant and non-constant strings to concatenate
123 result = con.Query("select CONCAT_WS(a, NULL, b, '') FROM strings ORDER BY a");
124 REQUIRE(CHECK_COLUMN(result, 0, {"WorldHello", "", "RÄcksMotörHead"}));
125}
126
127TEST_CASE("UPPER/LOWER test", "[function]") {
128 unique_ptr<QueryResult> result;
129 DuckDB db(nullptr);
130 Connection con(db);
131 con.EnableQueryVerification();
132
133 // unicode
134 result = con.Query("select UPPER('áaaá'), UPPER('ö'), LOWER('S̈'), UPPER('ω')");
135 REQUIRE(CHECK_COLUMN(result, 0, {"ÁAAÁ"}));
136 REQUIRE(CHECK_COLUMN(result, 1, {"ö"}));
137 REQUIRE(CHECK_COLUMN(result, 2, {"s̈"}));
138 REQUIRE(CHECK_COLUMN(result, 3, {"Ω"}));
139
140 // greek
141 result = con.Query("SELECT UPPER('Αα Ββ Γγ Δδ Εε Ζζ Ηη Θθ Ιι Κκ Λλ Μμ Νν Ξξ Οο Ππ Ρρ Σσς Ττ Υυ Φφ Χχ Ψψ Ωω'), "
142 "LOWER('Αα Ββ Γγ Δδ Εε Ζζ Ηη Θθ Ιι Κκ Λλ Μμ Νν Ξξ Οο Ππ Ρρ Σσς Ττ Υυ Φφ Χχ Ψψ Ωω')");
143 REQUIRE(CHECK_COLUMN(result, 0, {"ΑΑ ΒΒ ΓΓ ΔΔ ΕΕ ΖΖ ΗΗ ΘΘ ΙΙ ΚΚ ΛΛ ΜΜ ΝΝ ΞΞ ΟΟ ΠΠ ΡΡ ΣΣΣ ΤΤ ΥΥ ΦΦ ΧΧ ΨΨ ΩΩ"}));
144 REQUIRE(CHECK_COLUMN(result, 1, {"αα ββ γγ δδ εε ζζ ηη θθ ιι κκ λλ μμ νν ξξ οο ππ ρρ σσς ττ υυ φφ χχ ψψ ωω"}));
145
146 // test upper/lower on scalar values
147 result = con.Query("select UPPER(''), UPPER('hello'), UPPER('MotörHead'), UPPER(NULL)");
148 REQUIRE(CHECK_COLUMN(result, 0, {""}));
149 REQUIRE(CHECK_COLUMN(result, 1, {"HELLO"}));
150 REQUIRE(CHECK_COLUMN(result, 2, {"MOTÖRHEAD"}));
151 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
152
153 result = con.Query("select LOWER(''), LOWER('hello'), LOWER('MotörHead'), LOWER(NULL)");
154 REQUIRE(CHECK_COLUMN(result, 0, {""}));
155 REQUIRE(CHECK_COLUMN(result, 1, {"hello"}));
156 REQUIRE(CHECK_COLUMN(result, 2, {"motörhead"}));
157 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
158
159 // test ucase/lcase on scalar values
160 result = con.Query("select UCASE(''), UCASE('hello'), UCASE('MotörHead'), UCASE(NULL)");
161 REQUIRE(CHECK_COLUMN(result, 0, {""}));
162 REQUIRE(CHECK_COLUMN(result, 1, {"HELLO"}));
163 REQUIRE(CHECK_COLUMN(result, 2, {"MOTÖRHEAD"}));
164 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
165
166 result = con.Query("select LCASE(''), LCASE('hello'), LCASE('MotörHead'), LCASE(NULL)");
167 REQUIRE(CHECK_COLUMN(result, 0, {""}));
168 REQUIRE(CHECK_COLUMN(result, 1, {"hello"}));
169 REQUIRE(CHECK_COLUMN(result, 2, {"motörhead"}));
170 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
171
172 // test on entire tables
173 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
174 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
175 "('HuLlD', NULL), ('MotörHead','RÄcks')"));
176
177 result = con.Query("select UPPER(a), UCASE(a) FROM strings");
178 REQUIRE(CHECK_COLUMN(result, 0, {"HELLO", "HULLD", "MOTÖRHEAD"}));
179 REQUIRE(CHECK_COLUMN(result, 1, {"HELLO", "HULLD", "MOTÖRHEAD"}));
180
181 result = con.Query("select LOWER(a), LCASE(a) FROM strings");
182 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "hulld", "motörhead"}));
183 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "hulld", "motörhead"}));
184
185 result = con.Query("select LOWER(b), LCASE(b) FROM strings");
186 REQUIRE(CHECK_COLUMN(result, 0, {"world", Value(), "räcks"}));
187 REQUIRE(CHECK_COLUMN(result, 1, {"world", Value(), "räcks"}));
188
189 // test with selection vector
190 result = con.Query("select UPPER(a), LOWER(a), UCASE(a), LCASE(a) FROM strings WHERE b IS NOT NULL");
191 REQUIRE(CHECK_COLUMN(result, 0, {"HELLO", "MOTÖRHEAD"}));
192 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "motörhead"}));
193 REQUIRE(CHECK_COLUMN(result, 2, {"HELLO", "MOTÖRHEAD"}));
194 REQUIRE(CHECK_COLUMN(result, 3, {"hello", "motörhead"}));
195}
196
197TEST_CASE("LPAD/RPAD test", "[function]") {
198 unique_ptr<QueryResult> result;
199 DuckDB db(nullptr);
200 Connection con(db);
201 con.EnableQueryVerification();
202
203 // test lpad on NULLs
204 result = con.Query("select LPAD(NULL, 7, '-'), LPAD('Base', NULL, '-'), LPAD('Base', 7, NULL), "
205 "LPAD(NULL, NULL, '-'), LPAD(NULL, 7, NULL), LPAD('Base', NULL, NULL), "
206 "LPAD(NULL, NULL, NULL)");
207 for (idx_t col_idx = 0; col_idx < 7; ++col_idx) {
208 REQUIRE(CHECK_COLUMN(result, col_idx, {Value()}));
209 }
210
211 // test rpad on NULLs
212 result = con.Query("select RPAD(NULL, 7, '-'), RPAD('Base', NULL, '-'), RPAD('Base', 7, NULL), "
213 "RPAD(NULL, NULL, '-'), RPAD(NULL, 7, NULL), RPAD('Base', NULL, NULL), "
214 "RPAD(NULL, NULL, NULL)");
215 for (idx_t col_idx = 0; col_idx < 7; ++col_idx) {
216 REQUIRE(CHECK_COLUMN(result, col_idx, {Value()}));
217 }
218
219 // test lpad/rpad on scalar values
220 result = con.Query("select LPAD('Base', 7, '-'), LPAD('Base', 4, '-'), LPAD('Base', 2, ''), LPAD('Base', -1, '-')");
221 REQUIRE(CHECK_COLUMN(result, 0, {"---Base"}));
222 REQUIRE(CHECK_COLUMN(result, 1, {"Base"}));
223 REQUIRE(CHECK_COLUMN(result, 2, {"Ba"}));
224 REQUIRE(CHECK_COLUMN(result, 3, {""}));
225
226 result = con.Query("select RPAD('Base', 7, '-'), RPAD('Base', 4, '-'), RPAD('Base', 2, ''), RPAD('Base', -1, '-')");
227 REQUIRE(CHECK_COLUMN(result, 0, {"Base---"}));
228 REQUIRE(CHECK_COLUMN(result, 1, {"Base"}));
229 REQUIRE(CHECK_COLUMN(result, 2, {"Ba"}));
230 REQUIRE(CHECK_COLUMN(result, 3, {""}));
231
232 result =
233 con.Query("select LPAD('Base', 7, '-|'), LPAD('Base', 6, '-|'), LPAD('Base', 5, '-|'), LPAD('Base', 4, '-|')");
234 REQUIRE(CHECK_COLUMN(result, 0, {"-|-Base"}));
235 REQUIRE(CHECK_COLUMN(result, 1, {"-|Base"}));
236 REQUIRE(CHECK_COLUMN(result, 2, {"-Base"}));
237 REQUIRE(CHECK_COLUMN(result, 3, {"Base"}));
238
239 result =
240 con.Query("select RPAD('Base', 7, '-|'), RPAD('Base', 6, '-|'), RPAD('Base', 5, '-|'), RPAD('Base', 4, '-|')");
241 REQUIRE(CHECK_COLUMN(result, 0, {"Base-|-"}));
242 REQUIRE(CHECK_COLUMN(result, 1, {"Base-|"}));
243 REQUIRE(CHECK_COLUMN(result, 2, {"Base-"}));
244 REQUIRE(CHECK_COLUMN(result, 3, {"Base"}));
245
246 result = con.Query(
247 "select LPAD('MotörHead', 16, 'RÄcks'), LPAD('MotörHead', 12, 'RÄcks'), LPAD('MotörHead', 10, 'RÄcks')");
248 REQUIRE(CHECK_COLUMN(result, 0, {"RÄcksRÄMotörHead"}));
249 REQUIRE(CHECK_COLUMN(result, 1, {"RÄcMotörHead"}));
250 REQUIRE(CHECK_COLUMN(result, 2, {"RMotörHead"}));
251
252 result = con.Query(
253 "select RPAD('MotörHead', 16, 'RÄcks'), RPAD('MotörHead', 12, 'RÄcks'), RPAD('MotörHead', 10, 'RÄcks')");
254 REQUIRE(CHECK_COLUMN(result, 0, {"MotörHeadRÄcksRÄ"}));
255 REQUIRE(CHECK_COLUMN(result, 1, {"MotörHeadRÄc"}));
256 REQUIRE(CHECK_COLUMN(result, 2, {"MotörHeadR"}));
257
258 // test on entire tables
259 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
260 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
261 "('HuLlD', NULL), ('MotörHead','RÄcks')"));
262
263 result = con.Query("select LPAD(a, 16, b), RPAD(a, 16, b) FROM strings");
264 REQUIRE(CHECK_COLUMN(result, 0, {"WorldWorldWHello", Value(), "RÄcksRÄMotörHead"}));
265 REQUIRE(CHECK_COLUMN(result, 1, {"HelloWorldWorldW", Value(), "MotörHeadRÄcksRÄ"}));
266
267 // test with selection vector
268 result = con.Query("select LPAD(a, 12, b), RPAD(a, 12, b), UCASE(a), LCASE(a) FROM strings WHERE b IS NOT NULL");
269 REQUIRE(CHECK_COLUMN(result, 0, {"WorldWoHello", "RÄcMotörHead"}));
270 REQUIRE(CHECK_COLUMN(result, 1, {"HelloWorldWo", "MotörHeadRÄc"}));
271
272 // test incorrect usage
273 REQUIRE_FAIL(con.Query("select LPAD()"));
274 REQUIRE_FAIL(con.Query("select LPAD(1)"));
275 REQUIRE_FAIL(con.Query("select LPAD(1, 2)"));
276 REQUIRE_FAIL(con.Query("select LPAD('Hello', 10, '')"));
277 REQUIRE_FAIL(con.Query("select LPAD('a', 100000000000000000, 0)"));
278
279 REQUIRE_FAIL(con.Query("select RPAD()"));
280 REQUIRE_FAIL(con.Query("select RPAD(1)"));
281 REQUIRE_FAIL(con.Query("select RPAD(1, 2)"));
282 REQUIRE_FAIL(con.Query("select RPAD('Hello', 10, '')"));
283 REQUIRE_FAIL(con.Query("select RPAD('a', 100000000000000000, 0)"));
284}
285
286TEST_CASE("REPEAT test", "[function]") {
287 unique_ptr<QueryResult> result;
288 DuckDB db(nullptr);
289 Connection con(db);
290 con.EnableQueryVerification();
291
292 // test repeat on NULLs
293 result = con.Query("select REPEAT(NULL, NULL), REPEAT(NULL, 3), REPEAT('MySQL', NULL)");
294 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
295 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
296 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
297
298 // test repeat on scalars
299 result = con.Query("select REPEAT('', 3), REPEAT('MySQL', 3), REPEAT('MotörHead', 2), REPEAT('Hello', -1)");
300 REQUIRE(CHECK_COLUMN(result, 0, {""}));
301 REQUIRE(CHECK_COLUMN(result, 1, {"MySQLMySQLMySQL"}));
302 REQUIRE(CHECK_COLUMN(result, 2, {"MotörHeadMotörHead"}));
303 REQUIRE(CHECK_COLUMN(result, 3, {""}));
304
305 // test repeat on tables
306 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
307 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
308 "('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)"));
309
310 result = con.Query("select REPEAT(a, 3) FROM strings");
311 REQUIRE(CHECK_COLUMN(result, 0, {"HelloHelloHello", "HuLlDHuLlDHuLlD", "MotörHeadMotörHeadMotörHead", ""}));
312
313 result = con.Query("select REPEAT(b, 2) FROM strings");
314 REQUIRE(CHECK_COLUMN(result, 0, {"WorldWorld", Value(), "RÄcksRÄcks", Value()}));
315
316 result = con.Query("select REPEAT(a, 4) FROM strings WHERE b IS NOT NULL");
317 REQUIRE(CHECK_COLUMN(result, 0, {"HelloHelloHelloHello", "MotörHeadMotörHeadMotörHeadMotörHead"}));
318
319 // test incorrect usage of reverse
320 REQUIRE_FAIL(con.Query("select REPEAT()"));
321 REQUIRE_FAIL(con.Query("select REPEAT(1)"));
322 REQUIRE_FAIL(con.Query("select REPEAT('hello', 'world')"));
323 REQUIRE_FAIL(con.Query("select REPEAT('hello', 'world', 3)"));
324}
325
326TEST_CASE("REPLACE test", "[function]") {
327 unique_ptr<QueryResult> result;
328 DuckDB db(nullptr);
329 Connection con(db);
330 con.EnableQueryVerification();
331
332 // test replace on NULLs
333 result = con.Query("select REPLACE('This is the main test string', NULL, 'ALT')");
334 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
335
336 result = con.Query("select REPLACE(NULL, 'main', 'ALT')");
337 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
338
339 result = con.Query("select REPLACE('This is the main test string', 'main', NULL)");
340 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
341
342 // test replace on scalars
343 result = con.Query("select REPLACE('This is the main test string', 'main', 'ALT')");
344 REQUIRE(CHECK_COLUMN(result, 0, {"This is the ALT test string"}));
345
346 result = con.Query("select REPLACE('This is the main test string', 'main', 'larger-main')");
347 REQUIRE(CHECK_COLUMN(result, 0, {"This is the larger-main test string"}));
348
349 result = con.Query("select REPLACE('aaaaaaa', 'a', '0123456789')");
350 REQUIRE(CHECK_COLUMN(result, 0, {"0123456789012345678901234567890123456789012345678901234567890123456789"}));
351
352 // test replace on tables
353 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
354 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
355 "('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)"));
356
357 result = con.Query("select REPLACE(a, 'l', '-') FROM strings");
358 REQUIRE(CHECK_COLUMN(result, 0, {"He--o", "HuL-D", "MotörHead", ""}));
359
360 result = con.Query("select REPLACE(b, 'Ä', '--') FROM strings");
361 REQUIRE(CHECK_COLUMN(result, 0, {"World", Value(), "R--cks", Value()}));
362
363 result = con.Query("select REPLACE(a, 'H', '') FROM strings WHERE b IS NOT NULL");
364 REQUIRE(CHECK_COLUMN(result, 0, {"ello", "Motöread"}));
365
366 // test incorrect usage of replace
367 REQUIRE_FAIL(con.Query("select REPLACE(1)"));
368 REQUIRE_FAIL(con.Query("select REPLACE(1, 2)"));
369 REQUIRE_FAIL(con.Query("select REPLACE(1, 2, 3, 4)"));
370}
371
372TEST_CASE("REVERSE test", "[function]") {
373 unique_ptr<QueryResult> result;
374 DuckDB db(nullptr);
375 Connection con(db);
376 con.EnableQueryVerification();
377
378 // test reverse on scalars
379 result = con.Query("select REVERSE(''), REVERSE('Hello'), REVERSE('MotörHead'), REVERSE(NULL)");
380 REQUIRE(CHECK_COLUMN(result, 0, {""}));
381 REQUIRE(CHECK_COLUMN(result, 1, {"olleH"}));
382 REQUIRE(CHECK_COLUMN(result, 2, {"daeHrötoM"}));
383 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
384
385 // test reverse on tables
386 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
387 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('Hello', 'World'), "
388 "('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)"));
389
390 result = con.Query("select REVERSE(a) FROM strings");
391 REQUIRE(CHECK_COLUMN(result, 0, {"olleH", "DlLuH", "daeHrötoM", ""}));
392
393 result = con.Query("select REVERSE(b) FROM strings");
394 REQUIRE(CHECK_COLUMN(result, 0, {"dlroW", Value(), "skcÄR", Value()}));
395
396 result = con.Query("select REVERSE(a) FROM strings WHERE b IS NOT NULL");
397 REQUIRE(CHECK_COLUMN(result, 0, {"olleH", "daeHrötoM"}));
398
399 // test incorrect usage of reverse
400 REQUIRE_FAIL(con.Query("select REVERSE()"));
401 REQUIRE_FAIL(con.Query("select REVERSE(1, 2)"));
402 REQUIRE_FAIL(con.Query("select REVERSE('hello', 'world')"));
403}
404
405TEST_CASE("LTRIM/RTRIM test", "[function]") {
406 unique_ptr<QueryResult> result;
407 DuckDB db(nullptr);
408 Connection con(db);
409 con.EnableQueryVerification();
410
411 // test ltrim on scalars
412 result = con.Query(
413 "select LTRIM(''), LTRIM('Neither'), LTRIM(' Leading'), LTRIM('Trailing '), LTRIM(' Both '), LTRIM(NULL)");
414 REQUIRE(CHECK_COLUMN(result, 0, {""}));
415 REQUIRE(CHECK_COLUMN(result, 1, {"Neither"}));
416 REQUIRE(CHECK_COLUMN(result, 2, {"Leading"}));
417 REQUIRE(CHECK_COLUMN(result, 3, {"Trailing "}));
418 REQUIRE(CHECK_COLUMN(result, 4, {"Both "}));
419 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
420
421 // test rtrim on scalars
422 result = con.Query(
423 "select RTRIM(''), RTRIM('Neither'), RTRIM(' Leading'), RTRIM('Trailing '), RTRIM(' Both '), RTRIM(NULL)");
424 REQUIRE(CHECK_COLUMN(result, 0, {""}));
425 REQUIRE(CHECK_COLUMN(result, 1, {"Neither"}));
426 REQUIRE(CHECK_COLUMN(result, 2, {" Leading"}));
427 REQUIRE(CHECK_COLUMN(result, 3, {"Trailing"}));
428 REQUIRE(CHECK_COLUMN(result, 4, {" Both"}));
429 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
430
431 // test on tables
432 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
433 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('', 'Neither'), "
434 "(' Leading', NULL), (' Both ','Trailing '), ('', NULL)"));
435
436 result = con.Query("select LTRIM(a) FROM strings");
437 REQUIRE(CHECK_COLUMN(result, 0, {"", "Leading", "Both ", ""}));
438
439 result = con.Query("select LTRIM(b) FROM strings");
440 REQUIRE(CHECK_COLUMN(result, 0, {"Neither", Value(), "Trailing ", Value()}));
441
442 result = con.Query("select LTRIM(a) FROM strings WHERE b IS NOT NULL");
443 REQUIRE(CHECK_COLUMN(result, 0, {"", "Both "}));
444
445 // test rtrim on tables
446 result = con.Query("select RTRIM(a) FROM strings");
447 REQUIRE(CHECK_COLUMN(result, 0, {"", " Leading", " Both", ""}));
448
449 result = con.Query("select RTRIM(b) FROM strings");
450 REQUIRE(CHECK_COLUMN(result, 0, {"Neither", Value(), "Trailing", Value()}));
451
452 result = con.Query("select RTRIM(a) FROM strings WHERE b IS NOT NULL");
453 REQUIRE(CHECK_COLUMN(result, 0, {"", " Both"}));
454
455 // test incorrect usage of ltrim
456 REQUIRE_FAIL(con.Query("select LTRIM()"));
457 REQUIRE_FAIL(con.Query("select LTRIM(1, 2)"));
458 REQUIRE_FAIL(con.Query("select LTRIM('hello', 'world')"));
459
460 // test incorrect usage of rtrim
461 REQUIRE_FAIL(con.Query("select RTRIM()"));
462 REQUIRE_FAIL(con.Query("select RTRIM(1, 2)"));
463 REQUIRE_FAIL(con.Query("select RTRIM('hello', 'world')"));
464}
465
466TEST_CASE("LEFT test", "[function]") {
467 unique_ptr<QueryResult> result;
468 DuckDB db(nullptr);
469 Connection con(db);
470 con.EnableQueryVerification();
471
472 // test LEFT on positive positions
473 result = con.Query("SELECT LEFT('abcd', 0), LEFT('abc', 1), LEFT('abc', 2), LEFT('abc', 3), LEFT('abc', 4)");
474 REQUIRE(CHECK_COLUMN(result, 0, {""}));
475 REQUIRE(CHECK_COLUMN(result, 1, {"a"}));
476 REQUIRE(CHECK_COLUMN(result, 2, {"ab"}));
477 REQUIRE(CHECK_COLUMN(result, 3, {"abc"}));
478 REQUIRE(CHECK_COLUMN(result, 4, {"abc"}));
479
480 result = con.Query(
481 "SELECT LEFT('🦆ab', 0), LEFT('🦆ab', 1), LEFT('🦆ab', 2), LEFT('🦆ab', 3), LEFT('🦆ab', 4)");
482 REQUIRE(CHECK_COLUMN(result, 0, {""}));
483 REQUIRE(CHECK_COLUMN(result, 1, {"🦆"}));
484 REQUIRE(CHECK_COLUMN(result, 2, {"🦆a"}));
485 REQUIRE(CHECK_COLUMN(result, 3, {"🦆ab"}));
486 REQUIRE(CHECK_COLUMN(result, 4, {"🦆ab"}));
487
488 result = con.Query(
489 "SELECT LEFT('🦆🤦S̈', 0), LEFT('🦆🤦S̈', 1), LEFT('🦆🤦S̈', 2), LEFT('🦆🤦S̈', 3)");
490 REQUIRE(CHECK_COLUMN(result, 0, {""}));
491 REQUIRE(CHECK_COLUMN(result, 1, {"🦆"}));
492 REQUIRE(CHECK_COLUMN(result, 2, {"🦆🤦"}));
493 REQUIRE(CHECK_COLUMN(result, 3, {"🦆🤦S̈"}));
494
495 // test LEFT on negative positions
496 result = con.Query("SELECT LEFT('abcd', 0), LEFT('abc', -1), LEFT('abc', -2), LEFT('abc', -3), LEFT('abc', -4)");
497 REQUIRE(CHECK_COLUMN(result, 0, {""}));
498 REQUIRE(CHECK_COLUMN(result, 1, {"ab"}));
499 REQUIRE(CHECK_COLUMN(result, 2, {"a"}));
500 REQUIRE(CHECK_COLUMN(result, 3, {""}));
501 REQUIRE(CHECK_COLUMN(result, 4, {""}));
502
503 result = con.Query(
504 "SELECT LEFT('🦆ab', 0), LEFT('🦆ab', -1), LEFT('🦆ab', -2), LEFT('🦆ab', -3), LEFT('🦆ab', -4)");
505 REQUIRE(CHECK_COLUMN(result, 0, {""}));
506 REQUIRE(CHECK_COLUMN(result, 1, {"🦆a"}));
507 REQUIRE(CHECK_COLUMN(result, 2, {"🦆"}));
508 REQUIRE(CHECK_COLUMN(result, 3, {""}));
509 REQUIRE(CHECK_COLUMN(result, 4, {""}));
510
511 result = con.Query(
512 "SELECT LEFT('🦆🤦S̈', 0), LEFT('🦆🤦S̈', -1), LEFT('🦆🤦S̈', -2), LEFT('🦆🤦S̈', -3)");
513 REQUIRE(CHECK_COLUMN(result, 0, {""}));
514 REQUIRE(CHECK_COLUMN(result, 1, {"🦆🤦"}));
515 REQUIRE(CHECK_COLUMN(result, 2, {"🦆"}));
516 REQUIRE(CHECK_COLUMN(result, 3, {""}));
517
518 // test LEFT on NULL values
519 result = con.Query("SELECT LEFT(NULL, 0), LEFT('abc', NULL), LEFT(NULL, NULL)");
520 REQUIRE(CHECK_COLUMN(result, 0, {""}));
521 REQUIRE(CHECK_COLUMN(result, 1, {""}));
522 REQUIRE(CHECK_COLUMN(result, 2, {""}));
523
524 result = con.Query("SELECT LEFT(NULL, 0), LEFT('🦆ab', NULL), LEFT(NULL, NULL)");
525 REQUIRE(CHECK_COLUMN(result, 0, {""}));
526 REQUIRE(CHECK_COLUMN(result, 1, {""}));
527 REQUIRE(CHECK_COLUMN(result, 2, {""}));
528
529 // test on tables
530 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
531 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
532 REQUIRE_NO_FAIL(
533 con.Query("INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', 1), ('abc', 2), ('abc', 3), ('abc', 4)"));
534 result = con.Query("SELECT LEFT(a, b) FROM strings");
535 REQUIRE(CHECK_COLUMN(result, 0, {"", "a", "ab", "abc", "abc"}));
536
537 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
538 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
539 REQUIRE_NO_FAIL(
540 con.Query("INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', -1), ('abc', -2), ('abc', -3), ('abc', -4)"));
541 result = con.Query("SELECT LEFT(a, b) FROM strings");
542 REQUIRE(CHECK_COLUMN(result, 0, {"", "ab", "a", "", ""}));
543
544 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
545 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
546 REQUIRE_NO_FAIL(con.Query("INSERT INTO STRINGS VALUES (NULL, 0), ('abc', NULL), (NULL, NULL)"));
547 result = con.Query("SELECT LEFT(a, b) FROM strings");
548 REQUIRE(CHECK_COLUMN(result, 0, {"", "", ""}));
549}
550
551TEST_CASE("RIGHT test", "[function]") {
552 unique_ptr<QueryResult> result;
553 DuckDB db(nullptr);
554 Connection con(db);
555 con.EnableQueryVerification();
556
557 // test RIGHT on positive positions
558 result = con.Query("SELECT RIGHT('abcd', 0), RIGHT('abc', 1), RIGHT('abc', 2), RIGHT('abc', 3), RIGHT('abc', 4)");
559 REQUIRE(CHECK_COLUMN(result, 0, {""}));
560 REQUIRE(CHECK_COLUMN(result, 1, {"c"}));
561 REQUIRE(CHECK_COLUMN(result, 2, {"bc"}));
562 REQUIRE(CHECK_COLUMN(result, 3, {"abc"}));
563 REQUIRE(CHECK_COLUMN(result, 4, {"abc"}));
564
565 result = con.Query(
566 "SELECT RIGHT('🦆ab', 0), RIGHT('🦆ab', 1), RIGHT('🦆ab', 2), RIGHT('🦆ab', 3), RIGHT('🦆ab', 4)");
567 REQUIRE(CHECK_COLUMN(result, 0, {""}));
568 REQUIRE(CHECK_COLUMN(result, 1, {"b"}));
569 REQUIRE(CHECK_COLUMN(result, 2, {"ab"}));
570 REQUIRE(CHECK_COLUMN(result, 3, {"🦆ab"}));
571 REQUIRE(CHECK_COLUMN(result, 4, {"🦆ab"}));
572
573 result = con.Query(
574 "SELECT RIGHT('🦆🤦S̈', 0), RIGHT('🦆🤦S̈', 1), RIGHT('🦆🤦S̈', 2), RIGHT('🦆🤦S̈', 3)");
575 REQUIRE(CHECK_COLUMN(result, 0, {""}));
576 REQUIRE(CHECK_COLUMN(result, 1, {"S̈"}));
577 REQUIRE(CHECK_COLUMN(result, 2, {"🤦S̈"}));
578 REQUIRE(CHECK_COLUMN(result, 3, {"🦆🤦S̈"}));
579
580 // test RIGHT on negative positions
581 result =
582 con.Query("SELECT RIGHT('abcd', 0), RIGHT('abc', -1), RIGHT('abc', -2), RIGHT('abc', -3), RIGHT('abc', -4)");
583 REQUIRE(CHECK_COLUMN(result, 0, {""}));
584 REQUIRE(CHECK_COLUMN(result, 1, {"bc"}));
585 REQUIRE(CHECK_COLUMN(result, 2, {"c"}));
586 REQUIRE(CHECK_COLUMN(result, 3, {""}));
587 REQUIRE(CHECK_COLUMN(result, 4, {""}));
588
589 result = con.Query("SELECT RIGHT('🦆ab', 0), RIGHT('🦆ab', -1), RIGHT('🦆ab', -2), RIGHT('🦆ab', -3), "
590 "RIGHT('🦆ab', -4)");
591 REQUIRE(CHECK_COLUMN(result, 0, {""}));
592 REQUIRE(CHECK_COLUMN(result, 1, {"ab"}));
593 REQUIRE(CHECK_COLUMN(result, 2, {"b"}));
594 REQUIRE(CHECK_COLUMN(result, 3, {""}));
595 REQUIRE(CHECK_COLUMN(result, 4, {""}));
596
597 result = con.Query(
598 "SELECT RIGHT('🦆🤦S̈', 0), RIGHT('🦆🤦S̈', -1), RIGHT('🦆🤦S̈', -2), RIGHT('🦆🤦S̈', -3)");
599 REQUIRE(CHECK_COLUMN(result, 0, {""}));
600 REQUIRE(CHECK_COLUMN(result, 1, {"🤦S̈"}));
601 REQUIRE(CHECK_COLUMN(result, 2, {"S̈"}));
602 REQUIRE(CHECK_COLUMN(result, 3, {""}));
603
604 // test RIGHT on NULL values
605 result = con.Query("SELECT RIGHT(NULL, 0), RIGHT('abc', NULL), RIGHT(NULL, NULL)");
606 REQUIRE(CHECK_COLUMN(result, 0, {""}));
607 REQUIRE(CHECK_COLUMN(result, 1, {""}));
608 REQUIRE(CHECK_COLUMN(result, 2, {""}));
609
610 result = con.Query("SELECT RIGHT(NULL, 0), RIGHT('🦆ab', NULL), RIGHT(NULL, NULL)");
611 REQUIRE(CHECK_COLUMN(result, 0, {""}));
612 REQUIRE(CHECK_COLUMN(result, 1, {""}));
613 REQUIRE(CHECK_COLUMN(result, 2, {""}));
614
615 // test on tables
616 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
617 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
618 REQUIRE_NO_FAIL(
619 con.Query("INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', 1), ('abc', 2), ('abc', 3), ('abc', 4)"));
620 result = con.Query("SELECT RIGHT(a, b) FROM strings");
621 REQUIRE(CHECK_COLUMN(result, 0, {"", "c", "bc", "abc", "abc"}));
622
623 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
624 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
625 REQUIRE_NO_FAIL(
626 con.Query("INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', -1), ('abc', -2), ('abc', -3), ('abc', -4)"));
627 result = con.Query("SELECT RIGHT(a, b) FROM strings");
628 REQUIRE(CHECK_COLUMN(result, 0, {"", "bc", "c", "", ""}));
629
630 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS strings"));
631 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b BIGINT)"));
632 REQUIRE_NO_FAIL(con.Query("INSERT INTO STRINGS VALUES (NULL, 0), ('abc', NULL), (NULL, NULL)"));
633 result = con.Query("SELECT RIGHT(a, b) FROM strings");
634 REQUIRE(CHECK_COLUMN(result, 0, {"", "", ""}));
635}
636
637TEST_CASE("BIT_LENGTH test", "[function]") {
638 unique_ptr<QueryResult> result;
639 DuckDB db(nullptr);
640 Connection con(db);
641 con.EnableQueryVerification();
642
643 // test on scalars
644 result = con.Query("select BIT_LENGTH(NULL), BIT_LENGTH(''), BIT_LENGTH('\x24'), "
645 "BIT_LENGTH('\xC2\xA2'), BIT_LENGTH('\xE2\x82\xAC'), BIT_LENGTH('\xF0\x90\x8D\x88')");
646 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
647 REQUIRE(CHECK_COLUMN(result, 1, {0 * 8}));
648 REQUIRE(CHECK_COLUMN(result, 2, {1 * 8}));
649 REQUIRE(CHECK_COLUMN(result, 3, {2 * 8}));
650 REQUIRE(CHECK_COLUMN(result, 4, {3 * 8}));
651 REQUIRE(CHECK_COLUMN(result, 5, {4 * 8}));
652
653 // test on tables
654 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
655 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES "
656 "('', 'Zero'), ('\x24', NULL), ('\xC2\xA2','Two'), "
657 "('\xE2\x82\xAC', NULL), ('\xF0\x90\x8D\x88','Four')"));
658
659 result = con.Query("select BIT_LENGTH(a) FROM strings");
660 REQUIRE(CHECK_COLUMN(result, 0, {0 * 8, 1 * 8, 2 * 8, 3 * 8, 4 * 8}));
661
662 result = con.Query("select BIT_LENGTH(b) FROM strings");
663 REQUIRE(CHECK_COLUMN(result, 0, {4 * 8, Value(), 3 * 8, Value(), 4 * 8}));
664
665 result = con.Query("select BIT_LENGTH(a) FROM strings WHERE b IS NOT NULL");
666 REQUIRE(CHECK_COLUMN(result, 0, {0 * 8, 2 * 8, 4 * 8}));
667
668 // test incorrect usage
669 REQUIRE_FAIL(con.Query("select BIT_LENGTH()"));
670 REQUIRE_FAIL(con.Query("select BIT_LENGTH(1, 2)"));
671}
672
673TEST_CASE("UNICODE test", "[function]") {
674 unique_ptr<QueryResult> result;
675 DuckDB db(nullptr);
676 Connection con(db);
677 con.EnableQueryVerification();
678
679 // test on scalars
680 result = con.Query("select UNICODE(NULL), UNICODE(''), UNICODE('\x24'), "
681 "UNICODE('\xC2\xA2'), UNICODE('\xE2\x82\xAC'), UNICODE('\xF0\x90\x8D\x88')");
682 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
683 REQUIRE(CHECK_COLUMN(result, 1, {-1}));
684 REQUIRE(CHECK_COLUMN(result, 2, {0x000024}));
685 REQUIRE(CHECK_COLUMN(result, 3, {0x0000A2}));
686 REQUIRE(CHECK_COLUMN(result, 4, {0x0020AC}));
687 REQUIRE(CHECK_COLUMN(result, 5, {0x010348}));
688
689 // test on tables
690 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a STRING, b STRING)"));
691 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES "
692 "('', 'Zero'), ('\x24', NULL), ('\xC2\xA2','Two'), "
693 "('\xE2\x82\xAC', NULL), ('\xF0\x90\x8D\x88','Four')"));
694
695 result = con.Query("select UNICODE(a) FROM strings");
696 REQUIRE(CHECK_COLUMN(result, 0, {-1, 0x000024, 0x0000A2, 0x0020AC, 0x010348}));
697
698 result = con.Query("select UNICODE(b) FROM strings");
699 REQUIRE(CHECK_COLUMN(result, 0, {90, Value(), 84, Value(), 70}));
700
701 result = con.Query("select UNICODE(a) FROM strings WHERE b IS NOT NULL");
702 REQUIRE(CHECK_COLUMN(result, 0, {-1, 0x0000A2, 0x010348}));
703
704 // test incorrect usage
705 REQUIRE_FAIL(con.Query("select UNICODE()"));
706 REQUIRE_FAIL(con.Query("select UNICODE(1, 2)"));
707}
708