1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7/* Test Case disclaimer
8 *
9 * Assertions built using the Domain Testing technique
10 * at: https://bbst.courses/wp-content/uploads/2018/01/Kaner-Intro-to-Domain-Testing-2018.pdf
11 *
12 */
13TEST_CASE("Contains test", "[function]") {
14 unique_ptr<QueryResult> result;
15 DuckDB db(nullptr);
16 Connection con(db);
17 con.EnableQueryVerification();
18
19 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR, off INTEGER, length INTEGER);"));
20 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello', 1, 2), "
21 "('world', 2, 3), ('b', 1, 1), (NULL, 2, 2)"));
22
23 // Test first letter
24 result = con.Query("SELECT contains(s,'h') FROM strings");
25 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, Value(nullptr)}));
26
27 // Test second letter
28 result = con.Query("SELECT contains(s,'e') FROM strings");
29 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, Value(nullptr)}));
30
31 // Test last letter
32 result = con.Query("SELECT contains(s,'d') FROM strings");
33 REQUIRE(CHECK_COLUMN(result, 0, {false, true, false, Value(nullptr)}));
34
35 // Test multiple letters
36 result = con.Query("SELECT contains(s,'he') FROM strings");
37 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, Value(nullptr)}));
38
39 // Test multiple letters in the middle
40 result = con.Query("SELECT contains(s,'ello') FROM strings");
41 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, Value(nullptr)}));
42
43 // Test multiple letters at the end
44 result = con.Query("SELECT contains(s,'lo') FROM strings");
45 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, Value(nullptr)}));
46
47 // Test no match
48 result = con.Query("SELECT contains(s,'he-man') FROM strings");
49 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, Value(nullptr)}));
50
51 // Test matching needle in multiple rows
52 result = con.Query("SELECT contains(s,'o') FROM strings");
53 REQUIRE(CHECK_COLUMN(result, 0, {true, true, false, Value(nullptr)}));
54
55 // Test NULL constant in different places
56 result = con.Query("SELECT contains(NULL,'o') FROM strings");
57 REQUIRE(CHECK_COLUMN(result, 0, {Value(nullptr), Value(nullptr), Value(nullptr), Value(nullptr)}));
58 result = con.Query("SELECT contains(s,NULL) FROM strings");
59 REQUIRE(CHECK_COLUMN(result, 0, {Value(nullptr), Value(nullptr), Value(nullptr), Value(nullptr)}));
60 result = con.Query("SELECT contains(NULL,NULL) FROM strings");
61 REQUIRE(CHECK_COLUMN(result, 0, {Value(nullptr), Value(nullptr), Value(nullptr), Value(nullptr)}));
62
63 // Test empty pattern
64 result = con.Query("SELECT contains(s,'') FROM strings");
65 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true, Value(nullptr)}));
66}
67
68/* Inspired by the substring test case and C language UTF-8 tests
69 *
70 */
71TEST_CASE("Contains test with UTF8", "[function]") {
72 unique_ptr<QueryResult> result;
73 DuckDB db(nullptr);
74 Connection con(db);
75 con.EnableQueryVerification();
76 string atomo = "\xc3\xa1tomo"; // length 6
77 string portg = "ol\xc3\xa1 mundo"; // olá mundo length 9
78 string nihao = "\xe4\xbd\xa0\xe5\xa5\xbd\xe4\xb8\x96\xe7\x95\x8c"; //你好世界 length 4
79 string potpourri = "two \xc3\xb1 three \xE2\x82\xA1 four \xF0\x9F\xA6\x86 end";
80
81 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR);"));
82 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('" + atomo + "')"));
83 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('" + portg + "')"));
84 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('" + nihao + "')"));
85 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('" + potpourri + "')"));
86
87 // Test one matching UTF8 letter
88 result = con.Query("SELECT contains(s,'\xc3\xa1') FROM strings");
89 REQUIRE(CHECK_COLUMN(result, 0, {true, true, false, false}));
90
91 // Test a sentence with an UTF-8
92 result = con.Query("SELECT contains(s,'ol\xc3\xa1 mundo') FROM strings");
93 REQUIRE(CHECK_COLUMN(result, 0, {false, true, false, false}));
94
95 // Test an entire UTF-8 word
96 result = con.Query("SELECT contains(s,'\xe4\xbd\xa0\xe5\xa5\xbd\xe4\xb8\x96\xe7\x95\x8c') FROM strings");
97 REQUIRE(CHECK_COLUMN(result, 0, {false, false, true, false}));
98
99 // Test a substring of the haystack from the beginning
100 result = con.Query("SELECT contains(s,'two \xc3\xb1 thr') FROM strings");
101 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, true}));
102
103 // Test a single UTF8 substring of the haystack in the middle
104 result = con.Query("SELECT contains(s,'\xc3\xb1') FROM strings");
105 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, true}));
106
107 // Test a multiple UTF8 substring of the haystack in the middle
108 result = con.Query("SELECT contains(s,'\xE2\x82\xA1 four \xF0\x9F\xA6\x86 e') FROM strings");
109 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, true}));
110
111 // Test a substring of the haystack from the middle to the end
112 result = con.Query("SELECT contains(s,'\xF0\x9F\xA6\x86 end') FROM strings");
113 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, true}));
114}
115