Use ``re.search`` instead of ``re.match`` in sqlite

Use python ``re.search()`` instead of ``re.match()`` as the operation
used by the :meth:`Column.regexp_match` method when using sqlite.
This matches the behavior of regular expressions on other databases
as well as that of well-known SQLite plugins.

Fixes: #5699
Change-Id: I14b2c7faf51fef172842aeb2dba2500f14544f24

1 files changed, 14 insertions, 5 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index e7b733261..f8d9b3d88 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1126,11 +1126,6 @@ class LikeFunctionsTest(fixtures.TablesTest):
         self._test(col.contains("b#cd", autoescape=True, escape="#"), {7})
 
     @testing.requires.regexp_match
-    def test_regexp_match(self):
-        col = self.tables.some_table.c.data
-        self._test(col.regexp_match("a.cde"), {1, 5, 6, 9})
-
-    @testing.requires.regexp_match
     def test_not_regexp_match(self):
         col = self.tables.some_table.c.data
         self._test(~col.regexp_match("a.cde"), {2, 3, 4, 7, 8, 10})
@@ -1142,6 +1137,20 @@ class LikeFunctionsTest(fixtures.TablesTest):
             col.regexp_replace("a.cde", "FOO").contains("FOO"), {1, 5, 6, 9}
         )
 
+    @testing.requires.regexp_match
+    @testing.combinations(
+        ("a.cde", {1, 5, 6, 9}),
+        ("abc", {1, 5, 6, 9, 10}),
+        ("^abc", {1, 5, 6, 9, 10}),
+        ("9cde", {8}),
+        ("^a", set(range(1, 11))),
+        ("(b|c)", set(range(1, 11))),
+        ("^(b|c)", set()),
+    )
+    def test_regexp_match(self, text, expected):
+        col = self.tables.some_table.c.data
+        self._test(col.regexp_match(text), expected)
+
 
 class ComputedColumnTest(fixtures.TablesTest):
     __backend__ = True