1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Substring 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(s VARCHAR, off INTEGER, length INTEGER);"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello', 1, 2), "
15 "('world', 2, 3), ('b', 1, 1), (NULL, 2, 2)"));
16
17 // test zero length
18 result = con.Query("SELECT SUBSTRING('🦆ab', 1, 0), SUBSTRING('abc', 1, 0)");
19 REQUIRE(CHECK_COLUMN(result, 0, {""}));
20 REQUIRE(CHECK_COLUMN(result, 1, {""}));
21
22 // constant offset/length
23 // normal substring
24 result = con.Query("SELECT substring(s from 1 for 2) FROM strings");
25 REQUIRE(CHECK_COLUMN(result, 0, {"he", "wo", "b", Value()}));
26
27 // substring out of range
28 result = con.Query("SELECT substring(s from 2 for 2) FROM strings");
29 REQUIRE(CHECK_COLUMN(result, 0, {"el", "or", "", Value()}));
30
31 // variable length offset/length
32 result = con.Query("SELECT substring(s from off for length) FROM strings");
33 REQUIRE(CHECK_COLUMN(result, 0, {"he", "orl", "b", Value()}));
34
35 result = con.Query("SELECT substring(s from off for 2) FROM strings");
36 REQUIRE(CHECK_COLUMN(result, 0, {"he", "or", "b", Value()}));
37
38 result = con.Query("SELECT substring(s from 1 for length) FROM strings");
39 REQUIRE(CHECK_COLUMN(result, 0, {"he", "wor", "b", Value()}));
40
41 result = con.Query("SELECT substring('hello' from off for length) FROM strings");
42 REQUIRE(CHECK_COLUMN(result, 0, {"he", "ell", "h", "el"}));
43
44 // test substrings with constant nulls in different places
45 result = con.Query("SELECT substring(NULL from off for length) FROM strings");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
47 result = con.Query("SELECT substring('hello' from NULL for length) FROM strings");
48 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
49 result = con.Query("SELECT substring('hello' from off for NULL) FROM strings");
50 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
51 result = con.Query("SELECT substring(NULL from NULL for length) FROM strings");
52 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
53 result = con.Query("SELECT substring('hello' from NULL for NULL) FROM strings");
54 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
55 result = con.Query("SELECT substring(NULL from off for NULL) FROM strings");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
57 result = con.Query("SELECT substring(NULL from NULL for NULL) FROM strings");
58 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
59}
60
61TEST_CASE("Substring test with UTF8", "[function]") {
62 unique_ptr<QueryResult> result;
63 DuckDB db(nullptr);
64 Connection con(db);
65 con.EnableQueryVerification();
66
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR);"));
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('two"
69 "\xc3\xb1"
70 "three"
71 "\xE2\x82\xA1"
72 "four"
73 "\xF0\x9F\xA6\x86"
74 "end')"));
75
76 result = con.Query("SELECT substring(s from 1 for 7) FROM strings");
77 REQUIRE(CHECK_COLUMN(result, 0,
78 {"two"
79 "\xc3\xb1"
80 "thr"}));
81
82 result = con.Query("SELECT substring(s from 10 for 7) FROM strings");
83 REQUIRE(CHECK_COLUMN(result, 0,
84 {"\xE2\x82\xA1"
85 "four"
86 "\xF0\x9F\xA6\x86"
87 "e"}));
88
89 result = con.Query("SELECT substring(s from 15 for 7) FROM strings");
90 REQUIRE(CHECK_COLUMN(result, 0,
91 {"\xF0\x9F\xA6\x86"
92 "end"}));
93}
94