Restore connectivity with ancient sqlite

Fixed bug that prevented SQLAlchemy to connect when using a very old
sqlite version (before 3.9) on python 3.8+.

Fixes: #9379
Change-Id: I10ca347398221c952e1a572dc6ef80e491d1f5cf

3 files changed, 51 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_20/9379.rst b/doc/build/changelog/unreleased_20/9379.rst
new file mode 100644
index 000000000..97e9b6c9b
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9379.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: bug, sqlite
+    :tickets: 9379
+
+    Fixed bug that prevented SQLAlchemy to connect when using a very old
+    sqlite version (before 3.8.3) on python 3.8+.
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 294eddaff..a40e3d256 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -544,7 +544,14 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
                 return None
             return re.search(a, b) is not None
 
-        create_func_kw = {"deterministic": True} if util.py38 else {}
+        if util.py38 and self._get_server_version_info(None) >= (3, 9):
+            # sqlite must be greater than 3.8.3 for deterministic=True
+            # https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
+            # the check is more conservative since there were still issues
+            # with following 3.8 sqlite versions
+            create_func_kw = {"deterministic": True}
+        else:
+            create_func_kw = {}
 
         def set_regexp(dbapi_connection):
             dbapi_connection.create_function(
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index c52c9f195..49c57c854 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -2786,6 +2786,43 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "mytable", column("myid", Integer), column("name", String)
         )
 
+    def _only_on_py38_w_sqlite_39():
+        """in python 3.9 and above you can actually do::
+
+            @(testing.requires.python38 + testing.only_on("sqlite > 3.9"))
+            def test_determinsitic_parameter(self):
+                ...
+
+        that'll be cool.  until then...
+
+        """
+        return testing.requires.python38 + testing.only_on("sqlite >= 3.9")
+
+    @_only_on_py38_w_sqlite_39()
+    def test_determinsitic_parameter(self):
+        """for #9379, make sure that "deterministic=True" is used when we are
+        on python 3.8 with modern SQLite version.
+
+        For the case where we are not on py3.8 or not on modern sqlite version,
+        the rest of the test suite confirms that connection still passes.
+
+        """
+        e = create_engine("sqlite://")
+
+        @event.listens_for(e, "do_connect", retval=True)
+        def _mock_connect(dialect, conn_rec, cargs, cparams):
+            conn = e.dialect.loaded_dbapi.connect(":memory:")
+            return mock.Mock(wraps=conn)
+
+        c = e.connect()
+        eq_(
+            c.connection.driver_connection.create_function.mock_calls,
+            [
+                mock.call("regexp", 2, mock.ANY, deterministic=True),
+                mock.call("floor", 1, mock.ANY, deterministic=True),
+            ],
+        )
+
     def test_regexp_match(self):
         self.assert_compile(
             self.table.c.myid.regexp_match("pattern"),