1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar SIMILAR TO statement", "[similar]") {
8 unique_ptr<QueryResult> result;
9 DuckDB database(nullptr);
10 Connection connection(database);
11
12 result = connection.Query("SELECT 'aaa' SIMILAR TO 'bbb'");
13 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
14
15 result = connection.Query("SELECT 'aaa' SIMILAR TO 'aaa'");
16 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
17
18 result = connection.Query("SELECT 'aaa' SIMILAR TO '.*'");
19 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
20
21 result = connection.Query("SELECT 'aaa' SIMILAR TO 'a.*'");
22 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
23
24 result = connection.Query("SELECT 'aaa' SIMILAR TO '.*a'");
25 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
26
27 result = connection.Query("SELECT 'aaa' SIMILAR TO '.*b'");
28 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
29
30 result = connection.Query("SELECT 'aaa' SIMILAR TO 'b.*'");
31 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
32
33 result = connection.Query("SELECT 'aaa' SIMILAR TO 'a[a-z]a'");
34 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
35
36 result = connection.Query("SELECT 'aaa' SIMILAR TO 'a[a-z]{2}'");
37 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
38
39 result = connection.Query("SELECT 'aaa' SIMILAR TO 'a[a-z].*'");
40 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
41
42 result = connection.Query("SELECT 'aaa' SIMILAR TO '[a-z][a-z].*'");
43 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
44
45 result = connection.Query("SELECT 'aaa' SIMILAR TO '[a-z]{3}'");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
47
48 result = connection.Query("SELECT 'aaa' NOT SIMILAR TO '[b-z]{3}'");
49 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
50
51 result = connection.Query("SELECT 'aaa' ~ 'aaa'");
52 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
53
54 result = connection.Query("SELECT 'aaa' !~ 'bbb'");
55 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
56
57 // similar to must match entire expression
58 result = connection.Query("SELECT 'aaa' ~ '^a'");
59 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
60 result = connection.Query("SELECT 'aaa' ~ '^a+'");
61 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
62
63 result = connection.Query("SELECT 'aaa' ~ '(a|b)*'");
64 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(true)}));
65
66 result = connection.Query("SELECT 'abc' ~ '^(b|c)'");
67 REQUIRE(CHECK_COLUMN(result, 0, {Value::BOOLEAN(false)}));
68}
69
70TEST_CASE("Test SIMILAR TO statement with expressions", "[similar]") {
71 unique_ptr<QueryResult> result;
72 DuckDB database(nullptr);
73 Connection connection(database);
74
75 REQUIRE_NO_FAIL(connection.Query("CREATE TABLE strings (s STRING, p STRING);"));
76 REQUIRE_NO_FAIL(connection.Query(
77 "INSERT INTO strings VALUES('aaa', 'a[a-z]a'), ('abab', 'ab.*'), ('aaa', 'a[a-z]a'), ('aaa', '.*b.*');"));
78 result = connection.Query("");
79
80 result = connection.Query("SELECT s FROM strings WHERE s SIMILAR TO 'ab.*'");
81 REQUIRE(CHECK_COLUMN(result, 0, {"abab"}));
82
83 result = connection.Query("SELECT s FROM strings WHERE 'aba' SIMILAR TO p");
84 REQUIRE(CHECK_COLUMN(result, 0, {"aaa", "abab", "aaa", "aaa"}));
85
86 result = connection.Query("SELECT s FROM strings WHERE s SIMILAR TO p");
87 REQUIRE(CHECK_COLUMN(result, 0, {"aaa", "abab", "aaa"}));
88
89 result = connection.Query("SELECT s FROM strings WHERE s NOT SIMILAR TO p");
90 REQUIRE(CHECK_COLUMN(result, 0, {"aaa"}));
91}
92
93TEST_CASE("Test SIMILAR TO statement exceptions", "[similar]") {
94 unique_ptr<QueryResult> result;
95 DuckDB database(nullptr);
96 Connection connection(database);
97
98 REQUIRE_NO_FAIL(connection.Query("CREATE TABLE strings (s STRING, p STRING);"));
99 REQUIRE_NO_FAIL(connection.Query(
100 "INSERT INTO strings VALUES('aaa', 'a[a-z]a'), ('abab', 'ab.*'), ('aaa', 'a[a-z]a'), ('aaa', '.*b.*');"));
101 result = connection.Query("");
102
103 REQUIRE_FAIL(connection.Query("SELECT s FROM strings WHERE s SIMILAR TO 'ab.*\%' {escape '\'}"));
104}
105