1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar LIKE statement with custom ESCAPE", "[like]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // scalar like with escape
13 result = con.Query("SELECT '%++' LIKE '*%++' ESCAPE '*';");
14 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
15 // Not Like
16 result = con.Query("SELECT '%++' NOT LIKE '*%++' ESCAPE '*';");
17 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
18 // Default tests
19 result = con.Query("SELECT '\\' LIKE '\\\\' ESCAPE '\\';");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
21
22 result = con.Query("SELECT '\\\\' LIKE '\\\\' ESCAPE '\\';");
23 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
24
25 result = con.Query("SELECT '%' LIKE '*%' ESCAPE '*';");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
27
28 result = con.Query("SELECT '_ ' LIKE '*_ ' ESCAPE '*';");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
30
31 result = con.Query("SELECT ' a ' LIKE '*_ ' ESCAPE '*';");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
33
34 result = con.Query("SELECT '\%_' LIKE '\%_' ESCAPE '';");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
36
37 result = con.Query("SELECT '*%' NOT LIKE '*%' ESCAPE '*';");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
39
40 // It should fail when more than one escape character is specified
41 REQUIRE_FAIL(con.Query("SELECT '\%_' LIKE '\%_' ESCAPE '\\\\';"));
42 REQUIRE_FAIL(con.Query("SELECT '\%_' LIKE '\%_' ESCAPE '**';"));
43}
44
45TEST_CASE("Test LIKE statement with ESCAPE in the middle of the pattern", "[like]") {
46 unique_ptr<QueryResult> result;
47 DuckDB db(nullptr);
48 Connection con(db);
49
50 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s STRING, pat STRING);"));
51 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('abab', 'ab%'), "
52 "('aaa', 'a*_a'), ('aaa', '*%b'), ('bbb', 'a%');"));
53
54 result = con.Query("SELECT s FROM strings;");
55 REQUIRE(CHECK_COLUMN(result, 0, {"abab", "aaa", "aaa", "bbb"}));
56
57 result = con.Query("SELECT pat FROM strings;");
58 REQUIRE(CHECK_COLUMN(result, 0, {"ab%", "a*_a", "*%b", "a%"}));
59
60 result = con.Query("SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';");
61 REQUIRE(CHECK_COLUMN(result, 0, {"bbb"}));
62
63 result = con.Query("SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*';");
64 REQUIRE(CHECK_COLUMN(result, 0, {"abab", "bbb"}));
65
66 result = con.Query("SELECT s FROM strings WHERE s LIKE pat ESCAPE '*';");
67 REQUIRE(CHECK_COLUMN(result, 0, {"abab"}));
68}
69