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

2 files changed, 19 insertions, 10 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 377411520..dac04e0ca 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -128,7 +128,7 @@ Regular Expression Support
 .. versionadded:: 1.4
 
 Support for the :meth:`_sql.ColumnOperators.regexp_match` operator is provided
-using Python's re.match_ function.  SQLite itself does not include a working
+using Python's re.search_ function.  SQLite itself does not include a working
 regular expression operator; instead, it includes a non-implemented placeholder
 operator ``REGEXP`` that calls a user-defined function that must be provided.
 
@@ -137,7 +137,7 @@ as follows::
 
 
     def regexp(a, b):
-        return bool(re.match(a, b))
+        return re.search(a, b) is not None
 
     sqlite_connection.create_function(
         "regexp", 2, regexp,
@@ -154,9 +154,9 @@ details.
 
 .. _create_function: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
 
-.. _re.match: https://docs.python.org/3/library/re.html#re.match
+.. _re.search: https://docs.python.org/3/library/re.html#re.search
 
-.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.match
+.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.search
 
 
 
@@ -508,7 +508,7 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
         def regexp(a, b):
             if b is None:
                 return None
-            return bool(re.match(a, b))
+            return re.search(a, b) is not None
 
         def set_regexp(connection):
             if hasattr(connection, "connection"):
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