1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("regex search test", "[regex]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // constant strings
14 result = con.Query("SELECT regexp_matches('asdf', '.*sd.*')");
15 REQUIRE(CHECK_COLUMN(result, 0, {true}));
16
17 result = con.Query("SELECT regexp_matches('asdf', '.*yu.*')");
18 REQUIRE(CHECK_COLUMN(result, 0, {false}));
19
20 result = con.Query("SELECT regexp_matches('asdf', '')");
21 REQUIRE(CHECK_COLUMN(result, 0, {true}));
22
23 // partial matches okay
24 result = con.Query("SELECT regexp_matches('asdf', 'sd')");
25 REQUIRE(CHECK_COLUMN(result, 0, {true}));
26 // full match requires entire match
27 result = con.Query("SELECT regexp_full_match('asdf', 'sd')");
28 REQUIRE(CHECK_COLUMN(result, 0, {false}));
29 result = con.Query("SELECT regexp_full_match('asdf', '.sd.')");
30 REQUIRE(CHECK_COLUMN(result, 0, {true}));
31
32 result = con.Query("SELECT regexp_matches('asdf', '^sdf$')");
33 REQUIRE(CHECK_COLUMN(result, 0, {false}));
34
35 // empty strings
36 result = con.Query("SELECT regexp_matches('', '.*yu.*')");
37 REQUIRE(CHECK_COLUMN(result, 0, {false}));
38
39 result = con.Query("SELECT regexp_matches('', '.*')");
40 REQUIRE(CHECK_COLUMN(result, 0, {true}));
41
42 // NULLs
43 result = con.Query("SELECT regexp_matches('asdf', CAST(NULL AS STRING))");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
45
46 result = con.Query("SELECT regexp_matches(CAST(NULL AS STRING), '.*sd.*')");
47 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
48
49 result = con.Query("SELECT regexp_matches(CAST(NULL AS STRING), CAST(NULL AS STRING))");
50 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
51
52 result = con.Query("SELECT regexp_matches('foobarbequebaz', '(bar)(beque)')");
53 REQUIRE(CHECK_COLUMN(result, 0, {true}));
54
55 // postgres says throw error on invalid regex
56 REQUIRE_FAIL(con.Query("SELECT regexp_matches('', '\\X')"));
57
58 REQUIRE_NO_FAIL(con.Query("CREATE TABLE regex(s STRING, p STRING)"));
59 REQUIRE_NO_FAIL(
60 con.Query("INSERT INTO regex VALUES ('asdf', 'sd'), ('asdf', '^sd'), (NULL, '^sd'), ('asdf', NULL)"));
61 result = con.Query("SELECT regexp_matches(s, '.*') FROM regex");
62 REQUIRE(CHECK_COLUMN(result, 0, {true, true, Value(), true}));
63
64 result = con.Query("SELECT regexp_matches(s, p) FROM regex");
65 REQUIRE(CHECK_COLUMN(result, 0, {true, false, Value(), Value()}));
66}
67
68TEST_CASE("regex filter push test", "[regex]") {
69 unique_ptr<QueryResult> result;
70 DuckDB db(nullptr);
71 Connection con(db);
72 con.EnableQueryVerification();
73
74 REQUIRE_NO_FAIL(con.Query("CREATE TABLE regex(s STRING)"));
75 REQUIRE_NO_FAIL(con.Query("INSERT INTO regex VALUES ('asdf'), ('xxxx'), ('aaaa')"));
76
77 result = con.Query("SELECT s FROM regex WHERE REGEXP_MATCHES(s, 'as(c|d|e)f')");
78 REQUIRE(CHECK_COLUMN(result, 0, {"asdf"}));
79
80 result = con.Query("SELECT s FROM regex WHERE NOT REGEXP_MATCHES(s, 'as(c|d|e)f')");
81 REQUIRE(CHECK_COLUMN(result, 0, {"xxxx", "aaaa"}));
82
83 result = con.Query("SELECT s FROM regex WHERE REGEXP_MATCHES(s, 'as(c|d|e)f') AND s = 'asdf'");
84 REQUIRE(CHECK_COLUMN(result, 0, {"asdf"}));
85
86 result = con.Query("SELECT s FROM regex WHERE REGEXP_MATCHES(s, 'as(c|d|e)f') AND REGEXP_MATCHES(s, 'as[a-z]f')");
87 REQUIRE(CHECK_COLUMN(result, 0, {"asdf"}));
88}
89
90TEST_CASE("regex replace test", "[regex]") {
91 unique_ptr<QueryResult> result;
92 DuckDB db(nullptr);
93 Connection con(db);
94 con.EnableQueryVerification();
95
96 result = con.Query("SELECT regexp_replace('foobarbaz', 'b..', 'X')");
97 REQUIRE(CHECK_COLUMN(result, 0, {"fooXbaz"}));
98}
99