1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar LIKE statement", "[like]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // scalar like
13 result = con.Query("SELECT 'aaa' LIKE 'bbb'");
14 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
15
16 result = con.Query("SELECT 'aaa' LIKE 'aaa'");
17 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
18
19 result = con.Query("SELECT 'aaa' LIKE '%'");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
21
22 result = con.Query("SELECT 'aaa' LIKE '%a'");
23 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
24
25 result = con.Query("SELECT 'aaa' LIKE '%b'");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
27
28 result = con.Query("SELECT 'aaa' LIKE 'a%'");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
30
31 result = con.Query("SELECT 'aaa' LIKE 'b%'");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
33
34 result = con.Query("SELECT 'aaa' LIKE 'a_a'");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
36
37 result = con.Query("SELECT 'aaa' LIKE 'a_'");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
39
40 result = con.Query("SELECT 'aaa' LIKE '__%'");
41 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
42
43 result = con.Query("SELECT 'aaa' LIKE '____%'");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
45
46 result = con.Query("SELECT 'ababac' LIKE '%abac'");
47 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
48
49 result = con.Query("SELECT 'ababac' NOT LIKE '%abac'");
50 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
51}
52
53TEST_CASE("Test LIKE statement", "[like]") {
54 unique_ptr<QueryResult> result;
55 DuckDB db(nullptr);
56 Connection con(db);
57
58 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s STRING, pat STRING);"));
59 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('abab', 'ab%'), "
60 "('aaa', 'a_a'), ('aaa', '%b%')"));
61
62 result = con.Query("SELECT s FROM strings WHERE s LIKE 'ab%'");
63 REQUIRE(CHECK_COLUMN(result, 0, {"abab"}));
64
65 result = con.Query("SELECT s FROM strings WHERE 'aba' LIKE pat");
66 REQUIRE(CHECK_COLUMN(result, 0, {"abab", "aaa", "aaa"}));
67
68 result = con.Query("SELECT s FROM strings WHERE s LIKE pat");
69 REQUIRE(CHECK_COLUMN(result, 0, {"abab", "aaa"}));
70}
71