From ec264a5a2c810394ee4ebd7a78f83fc0ea785c1f Mon Sep 17 00:00:00 2001
From: Federico Caselli <cfederico87@gmail.com>
Date: Sun, 15 Nov 2020 16:58:46 +0100
Subject: [PATCH] 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
---
 doc/build/changelog/unreleased_14/5699.rst  |  8 ++++++++
 lib/sqlalchemy/dialects/sqlite/pysqlite.py  | 10 +++++-----
 lib/sqlalchemy/testing/suite/test_select.py | 19 ++++++++++++++-----
 3 files changed, 27 insertions(+), 10 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_14/5699.rst

diff --git a/doc/build/changelog/unreleased_14/5699.rst b/doc/build/changelog/unreleased_14/5699.rst
new file mode 100644
index 0000000000..627aa0b039
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/5699.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, sqlite
+    :tickets: 5699
+
+    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.
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 377411520a..dac04e0ca3 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 e7b7332613..f8d9b3d88c 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1125,11 +1125,6 @@ class LikeFunctionsTest(fixtures.TablesTest):
         self._test(col.contains("b%cd", autoescape=True, escape="#"), {3})
         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
@@ -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
-- 
2.47.3