SQLite Forum

deterministic functions behave differently in version 3.32.1 with python3
Login

deterministic functions behave differently in version 3.32.1 with python3

(1) By Ondrej Dubaj (odubaj) on 2020-05-27 08:20:26 [link] [source]

With SQLite 3.32, test_sqlite fails with:

FAIL: CheckFuncDeterministic (sqlite3.test.userfunctions.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/builddir/build/BUILD/Python-3.9.0b1/Lib/sqlite3/test/userfunctions.py", line 290, in CheckFuncDeterministic
    self.assertEqual(mock.call_count, 1)
AssertionError: 2 != 1

This test defines a "deterministic" function and ensures that calling it twice in SQLite with only call the underyling Python function only once.

Copy of the test:

    @unittest.skipIf(sqlite.sqlite_version_info < (3, 8, 3), "deterministic parameter not supported")
    def CheckFuncDeterministic(self):
        mock = unittest.mock.Mock(return_value=None)
        self.con.create_function("deterministic", 0, mock, deterministic=True)
        self.con.execute("select deterministic() = deterministic()")
        self.assertEqual(mock.call_count, 1)

In pysqlite_connection_create_function() of Modules/_sqlite/connection.c, determistic=1 sets the following flag:

        flags |= SQLITE_DETERMINISTIC;

This flag is documented as:
"A deterministic function always gives the same answer when it has the same inputs."

* https://www.sqlite.org/deterministic.html
* https://www.sqlite.org/c3ref/c_deterministic.html

"SELECT 1 WHERE deterministic() = deterministic()" query also calls the mock twice.

Running "SELECT deterministic()" query twice also calls the mock twice.

It seems like SQLite 3.32 behaves differently.

Fedora issue: https://bugzilla.redhat.com/show_bug.cgi?id=1839826

Oh, I also tried with a function taking one argument and then call it twice by running "SELECT deterministic(1)" query twice: again, the mock is also called twice.

(2) By TripeHound on 2020-05-27 09:11:37 in reply to 1 [source]

Although knowing a function is deterministic will allow the query-planner to avoid multiple calls with the same arguments, as far as I can see, neither of those pages guarantee that a function won't be called multiple times, even if the argument(s) are the same.

(3) By Keith Medcalf (kmedcalf) on 2020-05-27 10:27:52 in reply to 1 [link] [source]

There was a change in how "constant functions" are processed. Perhaps the change you are referring to is this one: https://www.sqlite.org/src/info/c5f96a085db9688a which would have appeared at version 3.32.0.

That change basically moved "constant function evaluation" from the preamble to a once block. This was done so that "constant functions" are only evaluated when required thus permitting coalesce and CASE conditionals to short-circuit as documented.